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
Tables
22- MP006no-vacuum-fullcriticalVACUUM FULL blocks everythingACCESS EXCLUSIVEBlocks readsBlocks writesRewrites the table
- MP013high-traffic-table-ddlwarningDDL on high-traffic tableACCESS EXCLUSIVECan stall the lock queueneeds database
- MP014large-table-ddlwarningLock on 1M+ row tableACCESS EXCLUSIVEBlocks writesCan stall the lock queueneeds database
- MP019no-exclusive-lock-high-connectionswarningACCESS EXCLUSIVE + many connectionsACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queueneeds database
- MP022no-drop-cascadewarningCASCADE drops dependentsno table lockCan lose dataBreaks a rolling deploy
- MP023require-if-not-existswarningNon-idempotent CREATEno table lockauto-fix
- MP026ban-drop-tablecriticalDROP TABLE permanentlyACCESS EXCLUSIVECan lose data
- MP028no-rename-tablewarningRENAME TABLE breaks refsACCESS EXCLUSIVEBreaks a rolling deploy
- MP032ban-clustercriticalCLUSTER rewrites tableACCESS EXCLUSIVEBlocks readsBlocks writesRewrites the table
- MP036ban-truncate-cascadecriticalTRUNCATE CASCADE across tablesno table lockCan lose data
- MP045require-primary-keywarningTable without PKno table lockBreaks replication
- MP047ban-set-logged-unloggedcriticalSET LOGGED/UNLOGGED rewrites tableACCESS EXCLUSIVERewrites the tableBlocks readsBlocks writes
- MP058multi-alter-table-same-tablewarningMultiple ALTER TABLE on same tableACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queue
- MP063warn-do-block-ddlwarningDDL inside DO $$ blockno table lock
- MP065ban-lock-tablecriticalLOCK TABLE blocks queriesACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queue
- MP066warn-autovacuum-disabledwarningAutovacuum disabled on tableno table lock
- MP095warn-set-tablespace-rewritewarningSET TABLESPACE copies the whole relationACCESS EXCLUSIVEBlocks readsBlocks writesRewrites the table
- MP102warn-rewrite-disk-headroomwarningRewrite needs a second full copyACCESS EXCLUSIVERewrites the tableBlocks readsBlocks writesneeds database
- MP103warn-replication-lag-riskwarningWAL-heavy work with replicas connectedno table lockBreaks replicationneeds database
- MP105warn-timescale-hypertable-ddlwarningHypertable DDL fans out to chunksACCESS EXCLUSIVEBlocks writesBlocks readsneeds databaseTimescaleDB
- MP107warn-citus-distributed-ddlwarningALTER propagates to every Citus shardACCESS EXCLUSIVEBlocks writesBlocks readsBreaks a rolling deployneeds databaseCitus
- MP111warn-timescale-columnstore-ddlcriticalTimescaleDB rejects this on columnstoreno table lockBreaks a rolling deployneeds databaseTimescaleDB
Columns
20- MP002require-check-not-null-patterncriticalSET NOT NULL without CHECK patternACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP003volatile-default-table-rewritecriticalADD COLUMN with volatile DEFAULTACCESS EXCLUSIVEBlocks readsBlocks writesRewrites the table
- MP010no-rename-columnwarningRENAME COLUMN breaks queriesACCESS EXCLUSIVEBreaks a rolling deployBlocks readsBlocks writes
- MP015no-add-column-serialwarningSERIAL → use IDENTITYACCESS EXCLUSIVERewrites the table
- MP017no-drop-columnwarningDROP COLUMN risksACCESS EXCLUSIVEBreaks a rolling deployBlocks readsBlocks writes
- MP018no-force-set-not-nullwarningSET NOT NULL scanACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP029ban-drop-not-nullwarningDROP NOT NULL risksno table lockBreaks a rolling deploy
- MP037prefer-text-over-varcharwarningVARCHAR → use TEXTno table lockauto-fix
- MP038prefer-bigint-over-intwarningINT PK → use BIGINTno table lockauto-fix
- MP039prefer-identity-over-serialwarningSERIAL → use IDENTITYno table lockBreaks a rolling deployauto-fix
- MP040prefer-timestamptzwarningTIMESTAMP → use TIMESTAMPTZno table lockCan lose dataauto-fix
- MP041ban-char-fieldwarningCHAR(n) wastes spaceno table lockCan lose dataauto-fix
- MP044no-data-loss-type-narrowingwarningNarrowing column typeACCESS EXCLUSIVECan lose dataRewrites the tableBlocks reads
- MP048ban-alter-default-volatile-existingwarningVolatile SET DEFAULTno table lockBreaks a rolling deploy
- MP052warn-dependent-objectswarningDROP/ALTER breaks views/functionsno table lockBreaks a rolling deploy
- MP061suboptimal-column-orderwarningColumn order wastes alignmentno table lock
- MP062ban-add-generated-stored-columncriticalGENERATED STORED rewrites tableACCESS EXCLUSIVEBlocks readsBlocks writesRewrites the table
- MP071ban-rename-in-use-columnwarningRename column used by views/indexesno table lockBreaks a rolling deploy
- MP077prefer-lz4-toast-compressionwarningUse LZ4 over PGLZ for TOASTno table lockauto-fix
- MP084require-default-for-not-null-columncriticalADD COLUMN NOT NULL without DEFAULTno table lock
Indexes
15- MP001require-concurrent-index-creationcriticalCREATE INDEX without CONCURRENTLYACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queueauto-fix
- MP009require-drop-index-concurrentlywarningDROP INDEX without CONCURRENTLYACCESS EXCLUSIVEBlocks readsBlocks writesauto-fix
- MP016require-index-on-fkwarningFK without indexSHAREBlocks writesCan stall the lock queue
- MP021require-concurrent-reindexwarningREINDEX without CONCURRENTLYACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queueauto-fix
- MP042require-index-namewarningUnnamed indexno table lockBreaks a rolling deployauto-fix
- MP050prefer-hnsw-over-ivfflatwarningIVFFlat → use HNSWno table lockScans every rowpgvector
- MP051require-spatial-indexwarningGeometry column without GISTno table lockScans every row
- MP056gin-index-on-jsonb-without-expressionwarningMissing GIN index for JSONBno table lockScans every row
- MP070warn-concurrent-index-invalidwarningCONCURRENTLY can leave invalid indexno table lock
- MP092require-partitioned-index-strategywarningIndex on partitioned parent locks allACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queue
- MP100warn-redundant-indexwarningIndex duplicates an existing prefixno table lockScans every rowneeds database
- MP101warn-index-on-write-hot-tablewarningNew index on a write-hot tableSHAREBlocks writesScans every rowneeds database
- MP104warn-long-index-buildwarningIndex build measured in hoursSHAREBlocks writesScans every rowneeds database
- MP109require-vector-index-paramswarningpgvector index without build parametersno table lockScans every rowpgvector
- MP112warn-hnsw-build-memorywarningHNSW build spills past maintenance_work_memno table lockScans every rowneeds databasepgvector
Constraints
14- MP005require-not-valid-foreign-keycriticalFK without NOT VALIDACCESS EXCLUSIVEBlocks readsBlocks writesScans every rowauto-fix
- MP027disallowed-unique-constraintcriticalUNIQUE without USING INDEXACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP030require-not-valid-checkcriticalCHECK without NOT VALIDACCESS EXCLUSIVEBlocks readsBlocks writesScans every rowauto-fix
- MP031ban-exclusion-constraintcriticalEXCLUSION constraintACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP043ban-domain-constraintwarningDomain constraint riskACCESS EXCLUSIVEScans every rowBlocks writes
- MP055drop-pk-replica-identity-breakcriticalDROP PK breaks replicationno table lockBreaks replication
- MP069warn-fk-lock-both-tableswarningFK locks both parent and childSHARE ROW EXCLUSIVEBlocks writesScans every row
- MP074require-deferrable-fkwarningFK without DEFERRABLEno table lockauto-fix
- MP081prefer-pg18-not-null-not-validwarningCHECK workaround no longer needed on PG18+no table lock
- MP082warn-not-enforced-constraintwarningNOT ENFORCED constraint does not enforceno table lock
- MP083warn-fk-nondeterministic-collationwarningFK on non-deterministic collationno table lock
- MP086require-explicit-on-deletewarningFK without explicit ON DELETEno table lock
- MP087ban-volatile-check-constraintcriticalCHECK constraint calling now()/random()no table lockCan lose data
- MP097ban-drop-constraint-backing-indexcriticalDropping a constraint-backed indexACCESS EXCLUSIVECan lose dataBreaks a rolling deploy
Partitions
7- MP046require-concurrent-detach-partitioncriticalDETACH PARTITION without CONCURRENTLYACCESS EXCLUSIVEBlocks readsBlocks writesCan stall the lock queueauto-fix
- MP049require-partition-key-in-pkcriticalPK missing partition key columnno table lockBreaks a rolling deploy
- MP072warn-partition-default-scanwarningPartition default scans all rowsACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP093warn-default-partition-growthwarningDEFAULT partition absorbs missing rowsno table lockScans every row
- MP094require-attach-partition-checkwarningATTACH PARTITION without matching CHECKACCESS EXCLUSIVEBlocks readsBlocks writesScans every row
- MP108warn-partman-managed-parentwarningManual DDL on a pg_partman parentno table lockCan lose dataneeds databasepg_partman
- MP110warn-partitioned-parent-fanoutwarningDDL locks parent and every partitionACCESS EXCLUSIVEBlocks writesBlocks readsCan stall the lock queueneeds database
Types and domains
2Enums
4- MP012no-enum-add-value-in-transactionwarningADD VALUE inside transactionACCESS EXCLUSIVEBlocks readsBlocks writesauto-fix
- MP024no-enum-value-removalwarningDROP TYPE destroys enumACCESS EXCLUSIVECan lose data
- MP054alter-type-add-value-in-transactioncriticalADD VALUE in transactionno table lockBreaks a rolling deploy
- MP060alter-type-rename-valuecriticalRENAME VALUE holds ACCESS EXCLUSIVEno table lockBreaks replication
Views
2Sequences
1Schemas
3Triggers
2Privileges and RLS
6- MP057rls-enabled-without-policycriticalRLS enabled without policyno table lockBreaks a rolling deploy
- MP073ban-superuser-rolecriticalSUPERUSER role in migrationno table lock
- MP079warn-rls-policy-completenesswarningRLS policy missing operationsno table lockCan lose data
- MP085warn-grant-wideningwarningGRANT to PUBLIC or GRANT ALLno table lock
- MP091warn-privilege-driftwarningGRANT/REVOKE mixed into DDL migrationno table lock
- MP099warn-security-definer-search-pathcriticalSECURITY DEFINER without pinned search_pathno table lockCan lose data
Backfills and DML
7- MP011unbatched-data-backfillwarningUPDATE without WHEREROW EXCLUSIVEBlocks writesBreaks replicationCan stall the lock queue
- MP059sequence-not-reset-after-data-migrationwarningSequence not reset after importno table lock
- MP067warn-backfill-no-batchingwarningBackfill without batchingROW EXCLUSIVEBlocks writesBreaks replication
- MP075warn-toast-bloat-riskwarningTOAST table bloat riskno table lock
- MP080ban-data-in-migrationwarningData changes in schema migrationno table lockCan stall the lock queue
- MP088require-analyze-after-backfillwarningBackfill without a following ANALYZEno table lock
- MP106prefer-timescale-drop-chunkswarningRetention DELETE instead of drop_chunks()ROW EXCLUSIVEBreaks replicationneeds databaseTimescaleDB
Transactions
4- MP008no-multi-ddl-transactioncriticalMultiple DDL in one transactionno table lockCan stall the lock queue
- MP025ban-concurrent-in-transactioncriticalCONCURRENTLY inside transactionno table lockauto-fix
- MP053ban-uncommitted-transactioncriticalBEGIN without COMMITno table lockCan stall the lock queueBlocks writes
- MP076warn-xid-consuming-retrywarningRetry loop consuming XIDsno table lock
Session settings
2Extensions
1The 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.