{"object":"dbmigrate_ruleset","ruleset_version":"2026.07.16","updated":"2026-07-16","object_scanned":"database migration SQL/DDL (schema-change lock/rewrite/data-loss safety)","rules_total":21,"rules":[{"rule_id":"drop_column","severity":"block","dialects":["postgres","mysql"],"flags":"Dropping a column is irreversible and loses the data in it.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"drop_table","severity":"block","dialects":["postgres","mysql"],"flags":"Dropping a table is irreversible and loses all its rows.","docs_ref":"https://www.postgresql.org/docs/current/sql-droptable.html"},{"rule_id":"truncate","severity":"block","dialects":["postgres","mysql"],"flags":"TRUNCATE deletes every row and cannot be rolled back mid-statement.","docs_ref":"https://www.postgresql.org/docs/current/sql-truncate.html"},{"rule_id":"set_not_null","severity":"caution","dialects":["postgres","mysql"],"flags":"SET NOT NULL scans the whole table under a lock to validate existing rows.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"column_type_change","severity":"block","dialects":["postgres","mysql"],"flags":"Changing a column type rewrites the whole table and holds an ACCESS EXCLUSIVE lock for the duration.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"add_column_not_null_no_default","severity":"block","dialects":["postgres","mysql"],"flags":"ADD COLUMN NOT NULL without a default must scan (and on old engines rewrite) the table, and fails outright on a populated table.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"add_column_volatile_default","severity":"caution","dialects":["postgres","mysql"],"flags":"ADD COLUMN with a volatile / non-constant default rewrites the table (every existing row is materialised with the evaluated value).","docs_ref":"https://www.postgresql.org/docs/current/ddl-alter.html"},{"rule_id":"create_index_not_concurrent","severity":"caution","dialects":["postgres","mysql"],"flags":"Building an index non-concurrently locks the table against writes for the whole build.","docs_ref":"https://www.postgresql.org/docs/current/sql-createindex.html"},{"rule_id":"drop_index_not_concurrent","severity":"caution","dialects":["postgres","mysql"],"flags":"Dropping an index non-concurrently takes an ACCESS EXCLUSIVE lock on the table.","docs_ref":"https://www.postgresql.org/docs/current/sql-dropindex.html"},{"rule_id":"add_fk_without_not_valid","severity":"block","dialects":["postgres","mysql"],"flags":"Adding a foreign key validates every existing row under a lock on both tables.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"add_check_without_not_valid","severity":"block","dialects":["postgres","mysql"],"flags":"Adding a CHECK constraint scans every existing row under a lock to validate it.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"add_unique_constraint_direct","severity":"block","dialects":["postgres","mysql"],"flags":"ADD CONSTRAINT ... UNIQUE builds the backing unique index non-concurrently, locking the table against writes.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"add_primary_key_existing","severity":"block","dialects":["postgres","mysql"],"flags":"Adding a primary key to an existing table builds a unique index non-concurrently and takes an exclusive lock.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"rename_column","severity":"caution","dialects":["postgres","mysql"],"flags":"Renaming a column breaks code that still reads the old name during a rolling deploy.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"rename_table","severity":"caution","dialects":["postgres","mysql"],"flags":"Renaming a table breaks code that still references the old name during a rolling deploy.","docs_ref":"https://www.postgresql.org/docs/current/sql-altertable.html"},{"rule_id":"backfill_in_migration","severity":"caution","dialects":["postgres","mysql"],"flags":"A bulk UPDATE / DELETE in the migration holds a long transaction and row locks, and can bloat the table.","docs_ref":"https://www.postgresql.org/docs/current/sql-update.html"},{"rule_id":"vacuum_full","severity":"block","dialects":["postgres"],"flags":"VACUUM FULL rewrites the whole table under an ACCESS EXCLUSIVE lock.","docs_ref":"https://www.postgresql.org/docs/current/sql-vacuum.html"},{"rule_id":"cluster_table","severity":"block","dialects":["postgres"],"flags":"CLUSTER rewrites and reorders the table under an ACCESS EXCLUSIVE lock.","docs_ref":"https://www.postgresql.org/docs/current/sql-cluster.html"},{"rule_id":"reindex_not_concurrent","severity":"block","dialects":["postgres"],"flags":"REINDEX (non-concurrent) locks the table / index against writes for the rebuild.","docs_ref":"https://www.postgresql.org/docs/current/sql-reindex.html"},{"rule_id":"concurrently_in_transaction","severity":"caution","dialects":["postgres"],"flags":"CREATE/DROP INDEX CONCURRENTLY cannot run inside an explicit transaction block and will error there.","docs_ref":"https://www.postgresql.org/docs/current/sql-createindex.html"},{"rule_id":"missing_lock_timeout","severity":"info","dialects":["postgres","mysql"],"flags":"No lock_timeout is set before locking DDL; a blocked statement can queue behind a long transaction and stall other queries.","docs_ref":"https://www.postgresql.org/docs/current/runtime-config-client.html"}],"dialects":[{"key":"postgres","label":"PostgreSQL","aliases":["postgres","postgresql","pg","psql","redshift"]},{"key":"mysql","label":"MySQL / MariaDB","aliases":["mysql","mariadb"]}],"formats":["sql","alembic"],"verdicts":["pass","caution","block"],"verdict_semantics":{"block":"at least one data-loss or exclusive-lock operation on existing data","caution":"at least one operation that locks or rewrites but has a safe rewrite","pass":"no unsafe pattern detected (info-only advisories may still appear)"},"false_positive_suppression":"operations on a table or column CREATED in the same migration do not fire lock/drop rules (no production data is at risk)","limits":{"max_body_bytes":262144,"max_statements":500,"batch_max":50},"source":"Own clean-room implementation of public Postgres/MySQL migration-safety practice; SQL parsed with sqlglot (MIT).","disclaimer":"Automated migration-safety indicators, not a guarantee. Lock/rewrite behaviour depends on your engine version, table size, and load; a 'pass' verdict is not a promise of zero downtime — review before applying to prod."}