Five Postgres migration footguns that no linter catches today

There are now five serious Postgres migration linters: Squawk (37 rules), Eugene (15 rules with trace mode), strong_migrations (21 rules, Ruby only), pgrubic (100+ rules with --fix), and pgfence (44 rules with safe rewrites and trace mode). Between them they cover the obvious footguns: ADD COLUMN ... NOT NULL, CREATE INDEX without CONCURRENTLY, missing lock_timeout, foreign keys added inline.

But there is a long tail of operations that take an ACCESS EXCLUSIVE lock, run for minutes on a real table, or quietly amplify cost on every future write, and slip past every one of those tools. Here are five of them. pgfence v0.6 catches each.

1. CLUSTER table USING index

CLUSTER physically reorders a table to match an index. It is sometimes recommended as a performance fix on heavily queried append-mostly tables. It is also catastrophic on production.

CLUSTER acquires ACCESS EXCLUSIVE on the table and holds it for the entire rewrite. On a 100GB table this is hours. Reads and writes are blocked the entire time. The lock is documented in src/backend/commands/cluster.c:

rel = table_open(tableOid, AccessExclusiveLock);

There is no CONCURRENTLY form. The safe alternative is pg_repack, which uses a trigger to capture writes during the rewrite. Or, in PG 12+, REINDEX TABLE CONCURRENTLY if you only care about the indexes.

No mainstream Postgres migration linter we tested flags CLUSTER. Most do not parse it at all.

2. ALTER TABLE ... REPLICA IDENTITY FULL

REPLICA IDENTITY FULL is sometimes set on tables without a primary key so that logical replication can replicate UPDATE and DELETE rows. It is a one-line statement that looks completely innocent:

ALTER TABLE events REPLICA IDENTITY FULL;

The DDL itself is fast: ACCESS EXCLUSIVE lock for a moment, metadata update, done. The damage is in the runtime cost it imposes forever after. With REPLICA IDENTITY FULL, every UPDATE and DELETE writes the entire old row to the WAL, not just the changed columns. Replication lag explodes, WAL volume doubles or triples, downstream consumers (Debezium, pglogical) start falling behind, and the table becomes a hot spot during any backfill.

The Postgres docs are direct about this at https://www.postgresql.org/docs/current/sql-altertable.html:

FULL records the old values of all columns in the row. This generates a lot of WAL traffic, especially if many columns are updated.

The safer alternative is almost always to add a primary key first, even a synthetic id bigserial, and then use REPLICA IDENTITY DEFAULT (which uses the PK). pgfence v0.6 flags every REPLICA IDENTITY FULL as HIGH risk with a recipe pointing at the PK-first pattern. DEFAULT and USING INDEX variants are not flagged.

3. ALTER TABLE ... ENABLE or DISABLE ROW LEVEL SECURITY

Two statements, opposite outcomes, same failure mode: silent data exposure or silent lockout.

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

If no CREATE POLICY exists for the current role when this runs, the default behavior is deny all rows. The application keeps connecting, queries keep parsing, but SELECT * FROM customers returns zero rows. Writes fail. The dashboard says the database is healthy. The product engineers find out from support tickets.

And the opposite:

ALTER TABLE customers DISABLE ROW LEVEL SECURITY;

Every row that was previously gated by a policy is now visible to anyone with table-level SELECT. If the policy was hiding rows for compliance reasons (PII, tenant isolation, deletion soft-flags), they are now visible to every role that can see the table. The disable statement is brief, ACCESS EXCLUSIVE, and silent.

No mainstream migration linter we tested flags either form. pgfence v0.6 flags both as HIGH, and the recipe for ENABLE insists you create the policies first and test as the target role before enabling RLS.

4. ALTER TABLE child INHERIT parent (and NO INHERIT)

Postgres inheritance is rarely used in modern applications but still ships in plenty of production schemas, often as a transitional state before or after declarative partitioning. Both directions of the inheritance toggle are dangerous:

ALTER TABLE measurements_2026_05 INHERIT measurements;

This statement takes ACCESS EXCLUSIVE on BOTH the child and the parent. Postgres has to walk the child table to validate that it matches the parent’s column shape and any CHECK constraints. On a billion-row child table this scan blocks both tables for minutes. The application sees timeouts on the parent it never modified.

NO INHERIT is briefer but takes the same locks. The footgun is that no mainstream linter parses ALTER TABLE far enough to surface the cross-table impact. pgfence v0.6 flags both as HIGH with a maintenance-window-and-lock_timeout recipe.

5. CREATE TYPE x AS ENUM (...) without an exit ramp

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

This looks like every other DDL: brief lock, metadata change, done. The footgun is what is missing. Postgres has ALTER TYPE ... ADD VALUE (since PG12 it is instant), and ALTER TYPE ... RENAME VALUE. There is no ALTER TYPE ... DROP VALUE. Anywhere. In any version. The committed values are forever.

Three years later, when cancelled was renamed to voided in product but the old value still appears in 4% of historical orders, the database is the constraint. You can swap to a lookup table, but the migration is non-trivial (drop the column, add a text column, backfill, rebuild dependent indexes, drop the type, recreate as a CHECK constraint, validate). You can also just keep the old value forever and add documentation. Both options are expensive in different ways.

The fix is to make the choice up front. For evolving categorical sets, a lookup table is editable. For small stable sets, a CHECK constraint is editable. Enums are only the right answer when the set will not change.

pgfence v0.6 flags every CREATE TYPE ... AS ENUM at LOW with a one-liner about DROP VALUE and a recipe pointing at the lookup-table alternative. The warning is intentionally not HIGH: most enums are fine. But the irrevocability is worth knowing about before you commit.

A sixth, briefly: DROP SCHEMA ... CASCADE

We have caught this since v0.5.0 but it is worth surfacing as a reminder. DROP SCHEMA analytics CASCADE walks the dependency graph and drops every table, view, function, sequence, and policy in the schema, plus everything in other schemas that depended on them. CRITICAL exit code. Most of the other linters do not parse it.

What the other linters cover and what they miss

Squawk has rules for ADD COLUMN NOT NULL, CREATE INDEX, DROP COLUMN, but no CLUSTER, no REPLICA IDENTITY, no RLS toggle check, no inheritance check, no enum warning. Eugene catches most lock-mode-level concerns through trace but does not surface RLS or enum footguns. pgrubic has the most rules (100+) but they target style (naming, formatting) more than catastrophic operations: no CLUSTER, no REPLICA IDENTITY, no RLS, no inheritance. strong_migrations is Ruby-only with strong FK-without-index detection but no CLUSTER, REPLICA IDENTITY, RLS, or inheritance rules.

pgfence v0.6 ships rules for all five. We are not claiming completeness: FK-without-index detection and identifier-length checks are on the v0.7 list. The gap between “popular footgun” and “rule that catches it” is large enough that every migration linter you can run still adds value.

Try it

npm install -D @flvmnt/pgfence@0.6
npx @flvmnt/pgfence analyze migrations/*.sql

Single-statement check:

pgfence explain "ALTER TABLE customers ENABLE ROW LEVEL SECURITY"

If you find a sixth footgun pgfence misses, please open an issue. Every false negative is a bug we want to fix.

GitHub · Docs · npm