postgres-safety

PostgreSQL safety

The rules a migration must obey before AIRLOCK will let anyone see it. Version-specific behaviour is called out because getting it wrong is how a "safe" migration takes a production table offline.

Always establish the server version first (SHOW server_version). Do not reason from memory about lock behaviour; it changed materially at 11, 12 and 13.

---

1. The three questions

Answer these, in order, for every operation:

1. Is it reversible? Can I write an inverse that restores the exact prior bytes?
2. What lock does it take, and for how long? A lock held for 4 seconds on a 1.2M-row table is a 4-second outage for every writer.
3. Does it rewrite the table? A rewrite means the lock is held for the duration of a full copy, not for a catalog update.

If (1) is no, the change needs a scope certificate, not an undo certificate.

---

2. Irreversible in principle

No rollback script restores these. Do not claim reversibility.

Operation	Why it cannot be undone
DROP TABLE / DROP COLUMN	The data is gone. Re-adding the column gives you nulls.
TRUNCATE	No row versions retained.
DELETE without a retained copy	Ditto, once vacuumed.
UPDATE that loses information	See §3 — the important one.
ALTER TYPE narrowing (text→varchar(20), bigint→int)	Truncation and overflow are lossy.
DROP of a constraint whose violations then land	The violating rows now exist.

DROP COLUMN is logically irreversible even though Postgres only marks the column dropped in the catalog. The data is unreachable through SQL. Never present a re-ADD COLUMN as a rollback: it restores the schema, not the data.

---

3. Lossy arithmetic — the failure people miss

An UPDATE is only reversible if its function is injective (different inputs always give different outputs).

-- NOT reversible: ROUND collapses distinct values onto the same result
UPDATE invoices SET amount_minor = ROUND(amount_minor / 1.0871);