All 112 rules
Each one names the lock a statement takes, what that lock blocks, and the pattern to use instead. Rule names are what you put in .migrationpilotrc.yml, so they are generated from the engine rather than typed here.
- 34
- critical
- 78
- warning
- 20
- fixed by --fix
- 15
- need a database
112 rules
Lock safety
24- MP001require-concurrent-index-creationcriticalauto-fixCREATE INDEX without CONCURRENTLY
- MP003volatile-default-table-rewritecriticalADD COLUMN with volatile DEFAULT
- MP004require-lock-timeoutcriticalauto-fixDDL without SET lock_timeout
- MP006no-vacuum-fullcriticalVACUUM FULL blocks everything
- MP007no-column-type-changecriticalALTER COLUMN TYPE rewrites table
- MP008no-multi-ddl-transactioncriticalMultiple DDL in one transaction
- MP009require-drop-index-concurrentlywarningauto-fixDROP INDEX without CONCURRENTLY
- MP020require-statement-timeoutwarningauto-fixDDL without timeout
- MP021require-concurrent-reindexwarningauto-fixREINDEX without CONCURRENTLY
- MP025ban-concurrent-in-transactioncriticalauto-fixCONCURRENTLY inside transaction
- MP032ban-clustercriticalCLUSTER rewrites table
- MP033require-concurrent-refresh-matviewwarningauto-fixREFRESH without CONCURRENTLY
- MP047ban-set-logged-unloggedcriticalSET LOGGED/UNLOGGED rewrites table
- MP053ban-uncommitted-transactioncriticalBEGIN without COMMIT
- MP058multi-alter-table-same-tablewarningMultiple ALTER TABLE on same table
- MP063warn-do-block-ddlwarningDDL inside DO $$ block
- MP065ban-lock-tablecriticalLOCK TABLE blocks queries
- MP069warn-fk-lock-both-tableswarningFK locks both parent and child
- MP070warn-concurrent-index-invalidwarningCONCURRENTLY can leave invalid index
- MP076warn-xid-consuming-retrywarningRetry loop consuming XIDs
- MP089warn-collation-change-rewritewarningCOLLATE change rewrites and reindexes
- MP090warn-trigger-on-hot-tablewarningFOR EACH ROW trigger on every write
- MP095warn-set-tablespace-rewritewarningSET TABLESPACE copies the whole relation
- MP096warn-matview-with-datawarningCREATE MATERIALIZED VIEW WITH DATA
Types & schema style
19- MP012no-enum-add-value-in-transactionwarningauto-fixADD VALUE inside transaction
- MP015no-add-column-serialwarningSERIAL → use IDENTITY
- MP023require-if-not-existswarningauto-fixNon-idempotent CREATE
- MP037prefer-text-over-varcharwarningauto-fixVARCHAR → use TEXT
- MP038prefer-bigint-over-intwarningauto-fixINT PK → use BIGINT
- MP039prefer-identity-over-serialwarningauto-fixSERIAL → use IDENTITY
- MP040prefer-timestamptzwarningauto-fixTIMESTAMP → use TIMESTAMPTZ
- MP041ban-char-fieldwarningauto-fixCHAR(n) wastes space
- MP042require-index-namewarningauto-fixUnnamed index
- MP048ban-alter-default-volatile-existingwarningVolatile SET DEFAULT
- MP054alter-type-add-value-in-transactioncriticalADD VALUE in transaction
- MP056gin-index-on-jsonb-without-expressionwarningMissing GIN index for JSONB
- MP061suboptimal-column-orderwarningColumn order wastes alignment
- MP062ban-add-generated-stored-columncriticalGENERATED STORED rewrites table
- MP066warn-autovacuum-disabledwarningAutovacuum disabled on table
- MP068warn-integer-pk-capacitywarningInteger PK nearing overflow
- MP075warn-toast-bloat-riskwarningTOAST table bloat risk
- MP077prefer-lz4-toast-compressionwarningauto-fixUse LZ4 over PGLZ for TOAST
- MP078warn-extension-version-pinwarningExtension version not pinned
Data safety
18- MP010no-rename-columnwarningRENAME COLUMN breaks queries
- MP017no-drop-columnwarningDROP COLUMN risks
- MP022no-drop-cascadewarningCASCADE drops dependents
- MP024no-enum-value-removalwarningDROP TYPE destroys enum
- MP026ban-drop-tablecriticalDROP TABLE permanently
- MP028no-rename-tablewarningRENAME TABLE breaks refs
- MP029ban-drop-not-nullwarningDROP NOT NULL risks
- MP034ban-drop-databasecriticalDROP DATABASE in migration
- MP035ban-drop-schemacriticalDROP SCHEMA permanently
- MP036ban-truncate-cascadecriticalTRUNCATE CASCADE across tables
- MP044no-data-loss-type-narrowingwarningNarrowing column type
- MP052warn-dependent-objectswarningDROP/ALTER breaks views/functions
- MP055drop-pk-replica-identity-breakcriticalDROP PK breaks replication
- MP060alter-type-rename-valuecriticalRENAME VALUE holds ACCESS EXCLUSIVE
- MP064ban-disable-triggercriticalDISABLE TRIGGER breaks replication
- MP071ban-rename-in-use-columnwarningRename column used by views/indexes
- MP097ban-drop-constraint-backing-indexcriticalDropping a constraint-backed index
- MP098warn-set-schemawarningSET SCHEMA breaks qualified references
Constraints & keys
16- MP002require-check-not-null-patterncriticalSET NOT NULL without CHECK pattern
- MP005require-not-valid-foreign-keycriticalauto-fixFK without NOT VALID
- MP016require-index-on-fkwarningFK without index
- MP018no-force-set-not-nullwarningSET NOT NULL scan
- MP027disallowed-unique-constraintcriticalUNIQUE without USING INDEX
- MP030require-not-valid-checkcriticalauto-fixCHECK without NOT VALID
- MP031ban-exclusion-constraintcriticalEXCLUSION constraint
- MP043ban-domain-constraintwarningDomain constraint risk
- MP045require-primary-keywarningTable without PK
- MP074require-deferrable-fkwarningauto-fixFK without DEFERRABLE
- MP081prefer-pg18-not-null-not-validwarningCHECK workaround no longer needed on PG18+
- MP082warn-not-enforced-constraintwarningNOT ENFORCED constraint does not enforce
- MP083warn-fk-nondeterministic-collationwarningFK on non-deterministic collation
- MP084require-default-for-not-null-columncriticalADD COLUMN NOT NULL without DEFAULT
- MP086require-explicit-on-deletewarningFK without explicit ON DELETE
- MP087ban-volatile-check-constraintcriticalCHECK constraint calling now()/random()
Extensions
9- MP050prefer-hnsw-over-ivfflatwarningIVFFlat → use HNSW
- MP051require-spatial-indexwarningGeometry column without GIST
- MP105warn-timescale-hypertable-ddlwarningneeds databaseHypertable DDL fans out to chunks
- MP106prefer-timescale-drop-chunkswarningneeds databaseRetention DELETE instead of drop_chunks()
- MP107warn-citus-distributed-ddlwarningneeds databaseALTER propagates to every Citus shard
- MP108warn-partman-managed-parentwarningneeds databaseManual DDL on a pg_partman parent
- MP109require-vector-index-paramswarningpgvector index without build parameters
- MP111warn-timescale-columnstore-ddlcriticalneeds databaseTimescaleDB rejects this on columnstore
- MP112warn-hnsw-build-memorywarningneeds databaseHNSW build spills past maintenance_work_mem
Production context
8- MP013high-traffic-table-ddlwarningneeds databaseDDL on high-traffic table
- MP014large-table-ddlwarningneeds databaseLock on 1M+ row table
- MP019no-exclusive-lock-high-connectionswarningneeds databaseACCESS EXCLUSIVE + many connections
- MP100warn-redundant-indexwarningneeds databaseIndex duplicates an existing prefix
- MP101warn-index-on-write-hot-tablewarningneeds databaseNew index on a write-hot table
- MP102warn-rewrite-disk-headroomwarningneeds databaseRewrite needs a second full copy
- MP103warn-replication-lag-riskwarningneeds databaseWAL-heavy work with replicas connected
- MP104warn-long-index-buildwarningneeds databaseIndex build measured in hours
Partitioning
7- MP046require-concurrent-detach-partitioncriticalauto-fixDETACH PARTITION without CONCURRENTLY
- MP049require-partition-key-in-pkcriticalPK missing partition key column
- MP072warn-partition-default-scanwarningPartition default scans all rows
- MP092require-partitioned-index-strategywarningIndex on partitioned parent locks all
- MP093warn-default-partition-growthwarningDEFAULT partition absorbs missing rows
- MP094require-attach-partition-checkwarningATTACH PARTITION without matching CHECK
- MP110warn-partitioned-parent-fanoutwarningneeds databaseDDL locks parent and every partition
Privileges & RLS
6- MP057rls-enabled-without-policycriticalRLS enabled without policy
- MP073ban-superuser-rolecriticalSUPERUSER role in migration
- MP079warn-rls-policy-completenesswarningRLS policy missing operations
- MP085warn-grant-wideningwarningGRANT to PUBLIC or GRANT ALL
- MP091warn-privilege-driftwarningGRANT/REVOKE mixed into DDL migration
- MP099warn-security-definer-search-pathcriticalSECURITY DEFINER without pinned search_path
Backfills & DML
5- MP011unbatched-data-backfillwarningUPDATE without WHERE
- MP059sequence-not-reset-after-data-migrationwarningSequence not reset after import
- MP067warn-backfill-no-batchingwarningBackfill without batching
- MP080ban-data-in-migrationwarningData changes in schema migration
- MP088require-analyze-after-backfillwarningBackfill without a following ANALYZE
The 15 catalogue-aware rules read table sizes, write traffic, replication state and index definitions. Without --database-url they stay silent rather than guess. The handbook explains the twenty hazards these rules are built on.