Schema drift: why prod is missing a column and nobody knows

Two error lines, in the backend log, usually attached to a 500:

ERROR: relation "customer_account" does not exist   -- SQLSTATE 42P01
ERROR: column "invoice_no" does not exist           -- SQLSTATE 42703

The first says a whole table is missing, the second that the table is there but short a column. Both say the same thing: the database the code assumes and the database that actually exists are not the same object.

What they share is delayed onset. The request that fails is not the one right after the deploy — it is the first person weeks later to reach that code path. Dev is fine, staging is fine, only prod is broken, because prod's schema is the only one that grew by hand.

How drift grows

Nobody does this on purpose. The actual chain of decisions usually runs:

1. The day before a demo, a column turns out to be missing and the client is looking at it tomorrow
2. Connect to the production database, ALTER TABLE ... ADD COLUMN ..., done in thirty seconds
3. Think: I'll add a migration file to the repo in a moment
4. The demo passes and the next urgent thing arrives

Step 3 never happens — or it happens but says something slightly different from what was typed by hand (type, default, nullability, index name). From then on the repo's migration sequence and prod's real state have diverged, and no system anywhere will tell you it happened.

I have seen a repo with a directory called sql/prod_updated/, holding SQL files meant to be "run manually on prod". The existence of that directory is itself the diagnosis: it is a drift generator, not a fix. "Run manually" means there is no record of who ran it, against which host, or whether it succeeded.

A step that depends on someone remembering is a step that will eventually be skipped.

Detection: ask the database, not the repo

The only trustworthy account of how bad the drift is comes from the database itself. information_schema is enough:

SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Run that against prod and against a database that has cleanly run every migration, then diff them. The clean one can be a throwaway local container; what matters is that its schema came entirely from the repo and no human has touched it.

# dump schema only on both sides, then diff
pg_dump --schema-only --no-owner --no-privileges "$PROD_URL"  > /tmp/prod.sql
pg_dump --schema-only --no-owner --no-privileges "$CLEAN_URL" > /tmp/clean.sql
diff -u /tmp/clean.sql /tmp/prod.sql

The first run is usually alarming. The differences sort into three kinds that want very different handling:

• In prod, not in the repo — things added by hand. The main target.
• In the repo, not in prod — a migration that never ran. The dangerous one, because the code already assumes it exists.
• Present in both but defined differently — the sneakiest. Type, length, nullability or default disagree; nothing breaks until some boundary value shows up.

Stop the bleeding: prod is the baseline, not the repo

This is the counterintuitive step. The instinct on finding drift is to change prod back to what the repo says. Do not.

Those hand-added columns are not junk. There is real data behind them and quite possibly code reading them. Dropping them is data loss — the kind that raises no error now and surfaces during month-end reconciliation.

The correct order runs the other way:

1. Write prod's current state into migrations. Add files that follow the diff, until a clean environment ends up identical to prod
2. Verify by diffing again, until the difference is zero. This step is done with a command, not by reading
3. Only then deal with design questions like "this column should not exist" — and deal with them as new migrations, through the normal process

Make the two sides match first; argue about what they should look like second. Doing both at once is the most reliable way I have seen to break prod while cleaning up drift.

Afterwards: use permissions, not discipline

Once the drift is cleaned up the real problem remains: there will be another deadline tomorrow, and the same thing will happen again.

"Everyone remember to write a migration" does not work, because it is abandoned exactly when pressure is highest — which is the only moment it needed to hold. What works is making the manual edit impossible:

• The database account used day to day has no DDL privileges. An ALTER TABLE requires a separately held account, and switching accounts is itself a piece of friction
• Migrations have exactly one execution path: the deploy pipeline. People do not run them directly
• A schema diff runs in CI or on a daily schedule and complains when the two sides disagree — so drift is known the day it happens, not inferred from a 500 three months later

The third has the best return of the three. It turns drift from an archaeology problem into a same-day problem, and the repair costs differ by an order of magnitude.

It is also an audit problem

Someone changed prod's schema by hand, and the trace that action left in the system is: none. No commit, no ticket, no execution record. Six months later, "who added this column and what for?" has an answer that exists only in one person's memory, and that person may have left.

This is the part I care about most when delivering internal systems — whether you can still answer questions about them later. A schema is the shape of your data; if the shape changed without a record, the system has a blank stretch of history. Putting migrations under version control gets you, as a side effect, an author, a timestamp, and a message you can interrogate for every change of shape.

If you remember one thing

The database's actual state is the only fact; the repo is a claim about it. On a delivery project those two will diverge. The only variable is whether you built something to notice, or waited for a user to notice for you.