# The Evolution of PostgreSQL Schema Migrations: Less Locking, Less Rewriting, Less Downtime > How PostgreSQL evolved from blocking DDL toward concurrent operations, metadata-only changes, and deferred constraint validation. Tianzhou | 2026-08-14 | Source: https://www.bytebase.com/blog/postgres-schema-migration-evolution/ --- 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. 1. **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. 1. **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. | Release | Migration improvement | | --------------------- | --------------------------------------------------------------------------------------------------- | | PostgreSQL 8.2 (2006) | `CREATE INDEX CONCURRENTLY`: build an index without blocking writes | | PostgreSQL 9.1 (2011) | `NOT VALID` foreign keys, constraints from existing indexes, more rewrite-free type changes | | PostgreSQL 9.2 (2012) | `NOT VALID` checks, `DROP INDEX CONCURRENTLY`, more rewrite-free type changes | | PostgreSQL 9.4 (2014) | Weaker lock for `VALIDATE CONSTRAINT` and other `ALTER TABLE` subcommands | | PostgreSQL 9.5 (2016) | Weaker locks for some trigger and foreign-key DDL | | PostgreSQL 11 (2018) | Fast `ADD COLUMN ... DEFAULT`: no more full table rewrite for a constant default | | PostgreSQL 12 (2019) | `REINDEX CONCURRENTLY`, cheaper `SET NOT NULL`, lower-lock partition attachment, progress reporting | | PostgreSQL 14 (2021) | `DETACH PARTITION CONCURRENTLY`: detach without blocking the partitioned table | | PostgreSQL 18 (2025) | `NOT VALID` not-null constraints: no more temporary-check workaround | | PostgreSQL 19 (beta) | `REPACK CONCURRENTLY`: rebuild a bloated table without `ACCESS EXCLUSIVE` | ## PostgreSQL 8.2: build an index while writes continue [PostgreSQL 8.2](https://www.postgresql.org/docs/8.4/release-8-2.html), released in December 2006, introduced the first landmark feature in this history: ```sql 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](https://www.postgresql.org/docs/9.1/release-9-1.html) allowed foreign keys to be added as `NOT VALID`: ```sql 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/9.2/release-9-2.html) 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](https://www.postgresql.org/docs/release/9.4.0/) 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](https://www.postgresql.org/docs/release/9.5.0/) followed with several trigger and foreign-key operations. This is incremental work, not a one-time switch. PostgreSQL's current [`ALTER TABLE` documentation](https://www.postgresql.org/docs/current/sql-altertable.html) 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](https://www.postgresql.org/docs/release/11.0/). Consider a large `orders` table: ```sql 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: ```sql 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`](/blog/postgres-timeout/) is still part of a safe rollout. ## PostgreSQL 12: several ideas converge [PostgreSQL 12](https://www.postgresql.org/docs/release/12.0/) 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/14/release-14.html) added the other half of the partition lifecycle: ```sql 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](https://www.postgresql.org/docs/18/release-18.html) made not-null constraints first-class catalog objects and allowed them to be added as `NOT VALID` directly: ```sql 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](https://www.postgresql.org/docs/devel/release-19.html) (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. ```sql 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](https://www.postgresql.org/docs/8.4/release-8-2.html) 1. [PostgreSQL 9.1 release notes](https://www.postgresql.org/docs/9.1/release-9-1.html) 1. [PostgreSQL 9.2 release notes](https://www.postgresql.org/docs/9.2/release-9-2.html) 1. [PostgreSQL 9.4 release notes](https://www.postgresql.org/docs/release/9.4.0/) 1. [PostgreSQL 9.5 release notes](https://www.postgresql.org/docs/release/9.5.0/) 1. [PostgreSQL 11 release notes](https://www.postgresql.org/docs/release/11.0/) 1. [PostgreSQL 12 release notes](https://www.postgresql.org/docs/release/12.0/) 1. [PostgreSQL 14 release notes](https://www.postgresql.org/docs/14/release-14.html) 1. [PostgreSQL 18 release notes](https://www.postgresql.org/docs/18/release-18.html) 1. [PostgreSQL 18 `ALTER TABLE`](https://www.postgresql.org/docs/18/sql-altertable.html) 1. [PostgreSQL 19 release notes draft](https://www.postgresql.org/docs/devel/release-19.html) ## Related reading - [Postgres Schema Migration without Downtime](https://www.bytebase.com/blog/postgres-schema-migration-without-downtime/) - [Which Postgres Operation causes a table rewrite](https://www.bytebase.com/blog/postgres-table-rewrite/) - [How to Use Postgres CREATE INDEX CONCURRENTLY](https://www.bytebase.com/blog/postgres-create-index-concurrently/) - [Postgres Timeout Explained](https://www.bytebase.com/blog/postgres-timeout/) - [Top Open Source Postgres Migration Tools in 2026](https://www.bytebase.com/blog/top-open-source-postgres-migration-tools/)