The Evolution of PostgreSQL Schema Migrations: Less Locking, Less Rewriting, Less Downtime

Migration advice usually collapses everything into "locking" or "non-locking." PostgreSQL is not that simple. A schema change carries three separate costs:

1. Waiting for and holding a lock. An ACCESS EXCLUSIVE lock blocks reads and writes. A weaker lock may allow ordinary application traffic to continue.
2. Rewriting the table. PostgreSQL creates a new physical copy of every row. Runtime, temporary disk usage, WAL volume, and replication lag all grow with the table.
3. Scanning existing data. A constraint may not rewrite rows but can still read the entire table before PostgreSQL accepts it.

A release can eliminate one cost without touching the others. PostgreSQL 11's fast column default is the canonical example: it removed the table rewrite, but ALTER TABLE still takes ACCESS EXCLUSIVE. The lock becomes brief because the work under it is now a catalog update, not a table-sized operation.

PostgreSQL has been chipping away at all three costs for 20 years: less locking, less rewriting, less downtime.

PostgreSQL 8.2: build an index while writes continue#

PostgreSQL 8.2, released in December 2006, introduced the first landmark feature in this history:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id);

A regular CREATE INDEX takes a SHARE lock. Reads continue, but inserts, updates, and deletes wait for the index build to finish. On a large production table, that can turn a routine index addition into a long read-only window.

CREATE INDEX CONCURRENTLY uses a weaker lock that permits normal reads and writes. The trade-off is more work:

• PostgreSQL scans the table multiple times and waits out transactions with old snapshots.
• It cannot run inside a transaction block.
• A failed build leaves behind an invalid index you must drop or rebuild.

The caveats matter less than the idea. PostgreSQL split one blocking operation into phases so application traffic could continue during the expensive part. Every later improvement in this story reuses that idea.

PostgreSQL 9.1 and 9.2: install now, validate later#

The 9.x releases brought the same separation to constraints.

PostgreSQL 9.1 allowed foreign keys to be added as NOT VALID:

ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers(id)
NOT VALID;

PostgreSQL installs the foreign key without first scanning every existing order. New inserts and updates must satisfy it immediately, but old rows are not trusted yet. The existing data can be checked later:

ALTER TABLE orders
VALIDATE CONSTRAINT orders_customer_id_fkey;

Validation still scans the table. In 9.1 the scan even held the same strong lock, so the immediate win was scheduling: the constraint enforces right away, and you pick when to pay the scan. The lock relief came in 9.4.

PostgreSQL 9.1 also allowed a unique or primary-key constraint to adopt an existing unique index. That made a low-impact two-step migration possible:

CREATE UNIQUE INDEX CONCURRENTLY users_email_key_tmp
ON users(email);

ALTER TABLE users
ADD CONSTRAINT users_email_key
UNIQUE USING INDEX users_email_key_tmp;

The expensive index build happens concurrently. Converting the finished index into a constraint is the short catalog operation.

PostgreSQL 9.2 extended NOT VALID and later validation to CHECK constraints. It also introduced DROP INDEX CONCURRENTLY, so removing an index no longer had to queue an exclusive table lock behind long-running queries.

The same two releases started removing pointless table rewrites from type changes. PostgreSQL 9.1 made varchar to text rewrite-free. PostgreSQL 9.2 added widening varchar, varbit, and numeric limits.

PostgreSQL 9.4 and 9.5: ask for no more lock than necessary#

PostgreSQL's DDL implementation historically defaulted many ALTER TABLE operations to ACCESS EXCLUSIVE, even when the operation did not need to exclude every reader and writer.

PostgreSQL 9.4 dropped that lock for several ALTER TABLE subcommands. The one that completed the staged-constraint story: VALIDATE CONSTRAINT now runs under SHARE UPDATE EXCLUSIVE, so the validation scan proceeds while normal reads and writes continue. PostgreSQL 9.5 followed with several trigger and foreign-key operations.

This is incremental work, not a one-time switch. PostgreSQL's current ALTER TABLE documentation still says ACCESS EXCLUSIVE is the default unless a subcommand documents a weaker lock. Each reduced-lock path matters because it changes which application queries survive the migration.

PostgreSQL 11: the fast column default#

If I had to pick one release that changed day-to-day migration work the most, it is PostgreSQL 11.

Consider a large orders table:

ALTER TABLE orders
ADD COLUMN status text NOT NULL DEFAULT 'pending';

Before PostgreSQL 11, the database wrote 'pending' into every existing row. The statement rewrote the whole table and its indexes under ACCESS EXCLUSIVE. The cost grew with the table, even though every old row got the same value.

PostgreSQL 11 stores a non-volatile default in the catalog instead. When an old row is read, PostgreSQL supplies the missing value as though it were physically present. The value gets written into the row whenever a later update or rewrite touches it. The result is a metadata-only change whose duration barely depends on row count.

The optimization has a boundary. A volatile default such as random() must be evaluated per row, so PostgreSQL still rewrites the table:

ALTER TABLE orders
ADD COLUMN sample_value double precision DEFAULT random();

And the fast path is not lock-free. It makes the strong lock short; it does not remove the lock. On a busy table, the statement can still wait behind an old transaction, and every query behind it queues up in turn. A sensible lock_timeout is still part of a safe rollout.

PostgreSQL 12: several ideas converge#

PostgreSQL 12 is where the modern migration playbook came together.

First, REINDEX CONCURRENTLY finally provided an in-place way to rebuild a bloated or damaged index without blocking writes:

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Before PostgreSQL 12, the low-downtime workaround was to build a replacement index concurrently, swap names or constraints, and drop the old one concurrently. The new command turned that choreography into a supported operation.

Second, PostgreSQL learned to skip the table scan for SET NOT NULL when a valid CHECK constraint already proved the column contains no nulls. That enabled this staged pattern:

ALTER TABLE users
ADD CONSTRAINT users_email_not_null
CHECK (email IS NOT NULL) NOT VALID;

ALTER TABLE users
VALIDATE CONSTRAINT users_email_not_null;

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

ALTER TABLE users
DROP CONSTRAINT users_email_not_null;

The long validation scan occurs under SHARE UPDATE EXCLUSIVE, which permits ordinary reads and writes. The final SET NOT NULL still requests ACCESS EXCLUSIVE, but it can use the proof from the validated check instead of scanning the table while holding that lock.

Third, ALTER TABLE ... ATTACH PARTITION gained reduced locking. Prepare and load a table separately, add a matching CHECK constraint so PostgreSQL can skip partition validation, then attach it with less interference to queries on the partitioned table.

BTW, PostgreSQL 12 also added progress views for CREATE INDEX, REINDEX, CLUSTER, and VACUUM FULL. Long DDL became not just less disruptive but observable.

PostgreSQL 14: partition maintenance becomes more online#

PostgreSQL 14 added the other half of the partition lifecycle:

ALTER TABLE events
DETACH PARTITION events_2025
CONCURRENTLY;

A regular detach requires ACCESS EXCLUSIVE on the partitioned parent. The concurrent form uses SHARE UPDATE EXCLUSIVE, so queries and data changes continue on the parent while PostgreSQL detaches the partition in phases.

This matters for time-series and retention-heavy systems, where attaching the next partition and retiring an old one are routine operations, not exceptional maintenance.

PostgreSQL 18: NOT NULL joins the NOT VALID model#

The PostgreSQL 12 technique for adding NOT NULL safely worked, but let's be honest, it was a workaround. The database needed a temporary CHECK (column IS NOT NULL) constraint to prove the property before it could set the real column flag.

PostgreSQL 18 made not-null constraints first-class catalog objects and allowed them to be added as NOT VALID directly:

ALTER TABLE users
ADD CONSTRAINT users_email_not_null
NOT NULL email
NOT VALID;

ALTER TABLE users
VALIDATE CONSTRAINT users_email_not_null;

The first statement enforces non-null values for new and updated rows without scanning the table. The second checks old rows under SHARE UPDATE EXCLUSIVE. The temporary CHECK, the conversion, and the cleanup step all disappear. It took 14 years for the NOT VALID model from PostgreSQL 9.1 to reach NOT NULL, but better late than never.

PostgreSQL 18 also extended NOT VALID foreign keys to partitioned tables, exactly the schemas where long validation hurts most.

PostgreSQL 19: the table rewrite loses its lock#

The upcoming PostgreSQL 19 (in beta as of this writing) extends the pattern to the biggest remaining offender: the full table rewrite.

REPACK consolidates VACUUM FULL, CLUSTER, and the third-party pg_repack extension into one in-core command. The CONCURRENTLY form rebuilds the table without ACCESS EXCLUSIVE: reads and writes continue against the original heap while PostgreSQL builds the new one and switches over.

REPACK TABLE orders CONCURRENTLY;

Indexes got their concurrent rebuild in PostgreSQL 12. The heap gets its own in 19. Reclaiming a bloated table was the last major operation stuck in "schedule a window, hope nothing breaks" territory.

The trade-off is familiar from CREATE INDEX CONCURRENTLY: more work for less locking. The concurrent form is backed by logical decoding, so it consumes a replication slot, and a stuck transaction or slow consumer will hold WAL. max_repack_replication_slots caps the pool. Same idea as 2006: split the blocking operation into phases, pay extra resources, keep traffic flowing.

What PostgreSQL still does not make online#

Don't read the timeline as a victory lap. Plenty of operations still have physical work and locking that cannot be wished away:

• Incompatible column type changes normally rewrite the table and rebuild indexes.
• A rewrite is still a rewrite. REPACK CONCURRENTLY removes the lock, not the extra disk, I/O, and WAL.
• Many ALTER TABLE forms still acquire ACCESS EXCLUSIVE, even when they only update metadata.
• CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY use more I/O, take longer, wait for old snapshots, and require recovery steps after some failures.
• A fast catalog change can still cause an outage if it waits indefinitely for its lock and queues application queries behind it.

PostgreSQL improved the primitives. It did not replace migration planning. Production rollouts still need a lock timeout, query and transaction monitoring, staged application compatibility, and a migration runner that knows which statements cannot execute inside a transaction.

References#

1. PostgreSQL 8.2 release notes
2. PostgreSQL 9.1 release notes
3. PostgreSQL 9.2 release notes
4. PostgreSQL 9.4 release notes
5. PostgreSQL 9.5 release notes
6. PostgreSQL 11 release notes
7. PostgreSQL 12 release notes
8. PostgreSQL 14 release notes
9. PostgreSQL 18 release notes
10. PostgreSQL 18 ALTER TABLE
11. PostgreSQL 19 release notes draft