Schema Migration Expand/Contract, Lock-Aware DDL, CREATE INDEX CONCURRENTLY, and Backfills

Learning outcomes

The PostgreSQL binaries are upgraded, but ServiceHub still changes every week. A product request adds region_code, a new uniqueness/access path, and a consistency rule to a hot orders table. The dangerous migration is a single deployment that grabs a strong lock, rewrites/backfills everything, and assumes the application can switch schemas atomically.

1. Recreate the ServiceHub change table

Run all Chapter 23 DDL as servicehub_owner, or through an approved deployment login that can SET ROLE to that owner. The normal servicehub_app login must remain a non-owner DML identity; granting it ownership or elevated DDL capability just to make migrations convenient would erase the least-privilege boundary established earlier in the course.

DROP TABLE IF EXISTS app.ch23_order CASCADE;SET ROLE servicehub_owner;CREATE TABLE app.ch23_order (  order_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_id integer NOT NULL,  customer_id bigint NOT NULL,  status text NOT NULL CHECK (status IN ('queued','assigned','completed','cancelled')),  amount numeric(12,2) NOT NULL CHECK (amount >= 0),  created_at timestamptz NOT NULL,  note text NOT NULL);INSERT INTO app.ch23_order(tenant_id,customer_id,status,amount,created_at,note)SELECT (g % 32) + 1,       10000 + (g % 4000),       (ARRAY['queued','assigned','completed','cancelled'])[(g % 4)+1],       ((g % 50000) / 100.0)::numeric(12,2),       TIMESTAMPTZ '2026-01-01 00:00+00' + (g || ' seconds')::interval,       'upgrade-change-lab-' || gFROM generate_series(1,100000) AS g;CREATE INDEX ch23_order_tenant_status_idxON app.ch23_order (tenant_id, status);RESET ROLE;ANALYZE app.ch23_order;

Imagine application version A knows only the current columns while version B understands region_code. The database change must allow A and B to overlap during deployment.

2. Prove why a tiny DDL statement can still be dangerous

Many ALTER TABLE forms require ACCESS EXCLUSIVE even when the metadata operation itself is fast. The risk is often waiting behind a long transaction while blocking later work in the lock queue.

SET application_name='ch23_long_tx';BEGIN;UPDATE app.ch23_orderSET note = noteWHERE order_id = 1;-- Keep the transaction open for the lock-timeout demonstration.

SET application_name='ch23_ddl';SET lock_timeout='1500ms';ALTER TABLE app.ch23_orderADD COLUMN region_code text;-- Expected while session A blocks the required lock:-- ERROR: canceling statement due to lock timeoutRESET lock_timeout;

The failed statement changed nothing. The safe reaction is not to set lock_timeout=0 and wait indefinitely; identify the blocker/change window and retry when lock acquisition is acceptable.

3. Observe the blocker instead of guessing

SELECT a.pid, a.application_name, a.state,       a.xact_start, a.wait_event_type, a.wait_event,       pg_blocking_pids(a.pid) AS blocking_pids,       left(a.query,100) AS query_excerptFROM pg_stat_activity AS aWHERE a.application_name IN ('ch23_long_tx','ch23_ddl');SELECT l.pid, a.application_name,       l.locktype, l.mode, l.granted,       l.relation::regclass AS relationFROM pg_locks AS lJOIN pg_stat_activity AS a USING (pid)WHERE a.application_name IN ('ch23_long_tx','ch23_ddl')ORDER BY l.pid,l.granted,l.mode;

After recording the evidence, commit/rollback session A. Retry the expansion with a short lock timeout during a quiet point.

SET lock_timeout='2s';ALTER TABLE app.ch23_orderADD COLUMN region_code text;RESET lock_timeout;

Adding the nullable column is the expand step. Old application code can ignore it. New code can start writing it without requiring old rows to be backfilled immediately.

4. Dual-read/write compatibility comes before backfill speed

Deploy application version B so new/updated rows write region_code, while reads can fall back to the old derivation when the new field is NULL. This keeps old and new application versions compatible during the migration window.

SELECT order_id,       COALESCE(         region_code,         CASE (tenant_id % 4)           WHEN 0 THEN 'west'           WHEN 1 THEN 'north'           WHEN 2 THEN 'east'           ELSE 'south'         END       ) AS effective_regionFROM app.ch23_orderWHERE order_id <= 10ORDER BY order_id;

The derivation is a training rule. A real migration must use the application's real source-of-truth mapping and prove that it is deterministic/idempotent.

5. Backfill in bounded idempotent batches

WITH batch AS (  SELECT order_id  FROM app.ch23_order  WHERE region_code IS NULL  ORDER BY order_id  LIMIT 2000  FOR UPDATE SKIP LOCKED)UPDATE app.ch23_order AS oSET region_code =  CASE (o.tenant_id % 4)    WHEN 0 THEN 'west'    WHEN 1 THEN 'north'    WHEN 2 THEN 'east'    ELSE 'south'  ENDFROM batchWHERE o.order_id = batch.order_id  AND o.region_code IS NULL;

Run batches repeatedly until UPDATE 0. The final IS NULL makes retries idempotent. SKIP LOCKED allows multiple workers without waiting on the same batch, but it is not a guarantee of uniform progress; always finish with a global NULL-count check.

SELECT count(*) AS remaining_nullsFROM app.ch23_orderWHERE region_code IS NULL;SELECT region_code, count(*)FROM app.ch23_orderGROUP BY region_codeORDER BY region_code;

6. Add new-row enforcement first with NOT VALID

Scanning 100,000 rows while holding a stronger lock is unnecessary when the immediate goal is “no new null/invalid values.” A CHECK ... NOT VALID starts enforcing the rule for new/updated rows without validating all existing rows in the same command.

ALTER TABLE app.ch23_orderADD CONSTRAINT ch23_region_code_validCHECK (region_code IN ('north','south','east','west'))NOT VALID;SELECT conname, convalidatedFROM pg_constraintWHERE conrelid='app.ch23_order'::regclass  AND conname='ch23_region_code_valid';ALTER TABLE app.ch23_orderVALIDATE CONSTRAINT ch23_region_code_valid;SELECT conname, convalidatedFROM pg_constraintWHERE conrelid='app.ch23_order'::regclass  AND conname='ch23_region_code_valid';

VALIDATE CONSTRAINT scans existing rows with a SHARE UPDATE EXCLUSIVE lock rather than the stronger default used by many ALTER TABLE forms; concurrent normal row updates can continue while validation checks old rows.

7. Build the new index without blocking ordinary writes

A standard CREATE INDEX permits reads but blocks writes on the table until construction completes. CREATE INDEX CONCURRENTLY performs more work/takes longer but keeps normal INSERT/UPDATE/DELETE available.

SET lock_timeout='2s';CREATE INDEX CONCURRENTLY ch23_order_region_created_idxON app.ch23_order (region_code, created_at DESC);RESET lock_timeout;

Do not wrap this command in a transaction block. PostgreSQL forbids CREATE INDEX CONCURRENTLY inside an explicit transaction because its algorithm spans multiple internal transactions/scans.

SELECT i.indexrelid::regclass AS index_name,       i.indisready,       i.indisvalid,       pg_size_pretty(pg_relation_size(i.indexrelid)) AS sizeFROM pg_index AS iWHERE i.indrelid='app.ch23_order'::regclassORDER BY 1;

8. Concurrent-index failure can leave an INVALID index behind

If a concurrent build is canceled, deadlocks, or encounters a uniqueness/expression failure, PostgreSQL can leave an invalid index. It is ignored for query planning but can still consume storage/update overhead.

SELECT i.indexrelid::regclass AS index_name,       i.indisready,       i.indisvalidFROM pg_index AS iWHERE i.indrelid='app.ch23_order'::regclass  AND NOT i.indisvalid;

-- Option A: drop the invalid index and rebuild:DROP INDEX CONCURRENTLY IF EXISTS app.ch23_order_region_created_idx;CREATE INDEX CONCURRENTLY ch23_order_region_created_idxON app.ch23_order (region_code, created_at DESC);-- Option B for an existing invalid index where appropriate:-- REINDEX INDEX CONCURRENTLY app.ch23_order_region_created_idx;

9. Monitor concurrent index progress

SELECT pid,       datname,       relid::regclass AS table_name,       index_relid::regclass AS index_name,       command,       phase,       lockers_total,       lockers_done,       blocks_total,       blocks_done,       tuples_total,       tuples_doneFROM pg_stat_progress_create_indexORDER BY pid;

Progress phases and totals help explain “stuck” builds that are actually waiting for old transactions/snapshots. Killing the build blindly can create exactly the invalid-index cleanup work described above.

10. Contract only after every deployed reader/writer has moved

Once the backfill is complete, constraints are valid, the new index is valid, and all deployed application versions rely on region_code, remove the old compatibility path. Contracting too early is an application deployment failure, not a SQL syntax problem.

SELECT count(*) FILTER (WHERE region_code IS NULL) AS null_regions,       count(*) AS rowsFROM app.ch23_order;SELECT conname, convalidatedFROM pg_constraintWHERE conrelid='app.ch23_order'::regclassORDER BY conname;SELECT indexrelid::regclass, indisready, indisvalidFROM pg_indexWHERE indrelid='app.ch23_order'::regclassORDER BY 1;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT order_id, created_at, amountFROM app.ch23_orderWHERE region_code='north'ORDER BY created_at DESCLIMIT 100;

Only after application telemetry confirms no old code path is using the legacy derivation should you remove old columns/functions/indexes. Keep destructive contract steps as a separate deployable change so rollback remains possible during the compatibility window.

Authoritative references

Upgrade, compatibility, locking, and migration behavior is version-sensitive. These PostgreSQL primary sources define the mechanisms used here; always read the exact source/target release notes during a real change.

• PostgreSQL 18 — ALTER TABLE
• PostgreSQL 18 — CREATE INDEX / Building Indexes Concurrently
• PostgreSQL 18 — REINDEX
• PostgreSQL 18 — Explicit Locking
• PostgreSQL 18 — Progress Reporting