Postgres migration · 5 mistakes that cost us weeks

We ship Postgres migrations on every SaaS build. Five mistakes keep showing up in post-mortems · here they are, with the fix we now apply unconditionally.

1 · ALTER TABLE without CONCURRENTLY on a hot table

Adding a NOT NULL column or a non-concurrent index on a 10M-row table blocks writes for minutes. The fix: use `CREATE INDEX CONCURRENTLY`, add columns as nullable first then backfill + set NOT NULL via `NOT VALID` + `VALIDATE CONSTRAINT` in separate steps.

-- WRONG
ALTER TABLE users ADD COLUMN plan TEXT NOT NULL;

-- RIGHT
ALTER TABLE users ADD COLUMN plan TEXT;
UPDATE users SET plan = 'free' WHERE plan IS NULL;
ALTER TABLE users ALTER COLUMN plan SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT plan_valid CHECK (plan IN ('free','pro','enterprise')) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT plan_valid;

2 · Deploying RLS on an existing table with open sessions

Turning on RLS on a table with active reader sessions under the wrong role = silent empty result sets until they reconnect. Fix: deploy with `FORCE ROW LEVEL SECURITY` + explicit SELECT policy BEFORE cutting over app code.

3 · Skipping shadow-read validation

Before flipping reads to the new schema, shadow-read for 24h: run both old + new query paths, compare results. Log divergences. We caught a subtle timezone bug this way that would have corrupted 12h of data.

4 · PgBouncer transaction-pooling + prepared statements

Prepared statements survive across pool-boundary transactions. On transaction-pooling mode, they become unsafe. Either use session-pooling or disable prepared statements at the client (many ORMs default to them · Prisma / Sequelize / Knex).

5 · Tool-chain version drift

Dev machine runs Postgres 15, prod is 14, CI uses 16. Works until you use a 15+ feature (like `GROUPS` window frames). Pin the Postgres version in docker-compose + CI + Vercel env. And patch-bump quarterly.