Unblocked by the Postgres 18 upgrade (see #4363). Part 3.3 of the audit in docs/postgresql-v18-migration.md.
Documentation only — no code. The natural home is packages/migrations/README.md, alongside the existing guideline that migrations should not take too long to run.
1. NOT NULL NOT VALID for large tables
Migrations run during deploy, blocking. On 16, adding NOT NULL to a large table required a full validating scan while holding a lock. PG18 allows:
ALTER TABLE … ADD CONSTRAINT … NOT NULL NOT VALID; -- fast metadata change
ALTER TABLE … VALIDATE CONSTRAINT …; -- later, non-blocking
Splitting it that way keeps the deploy fast and moves the scan out of the critical path.
2. Generated columns now default to VIRTUAL — write STORED explicitly
From PG18 on, GENERATED ALWAYS AS (expr) without STORED means VIRTUAL (recomputed on read), where 16 and earlier only supported stored. Virtual generated columns cannot be indexed.
This was purely forward-looking when the audit was written. It no longer is: the fix proposed in #4341 (replace an unusable expression index with a STORED generated column) depends on getting this right. Writing it implicitly would silently produce a column no index can cover — which is exactly the failure #4341 is trying to escape.
Worth documenting before someone writes the repo's first generated column, not after.
Unblocked by the Postgres 18 upgrade (see #4363). Part 3.3 of the audit in
docs/postgresql-v18-migration.md.Documentation only — no code. The natural home is
packages/migrations/README.md, alongside the existing guideline that migrations should not take too long to run.1.
NOT NULL NOT VALIDfor large tablesMigrations run during deploy, blocking. On 16, adding
NOT NULLto a large table required a full validating scan while holding a lock. PG18 allows:Splitting it that way keeps the deploy fast and moves the scan out of the critical path.
2. Generated columns now default to VIRTUAL — write
STOREDexplicitlyFrom PG18 on,
GENERATED ALWAYS AS (expr)withoutSTOREDmeans VIRTUAL (recomputed on read), where 16 and earlier only supported stored. Virtual generated columns cannot be indexed.This was purely forward-looking when the audit was written. It no longer is: the fix proposed in #4341 (replace an unusable expression index with a
STOREDgenerated column) depends on getting this right. Writing it implicitly would silently produce a column no index can cover — which is exactly the failure #4341 is trying to escape.Worth documenting before someone writes the repo's first generated column, not after.