PostgreSQL schema changes under load

A short change can create a lock queue

A plain SELECT acquires an ACCESS SHARE table lock. Adding a nullable column requires ACCESS EXCLUSIVE, which conflicts with every table-lock mode, including that reader’s. These are locks on the table as an object; they are separate from locks protecting an individual product update.

The reader can finish its query and retain its table lock. Inside an explicit transaction, the lock normally lasts until commit or rollback. If a schema change requests its conflicting lock while that transaction remains open, it must wait. A later reader can then queue behind the pending schema change even though that change has not yet copied or altered anything.

The waiting strong-lock request creates the queue. A later ordinary read cannot simply slip past it, although two ordinary reads could run together. By contrast, an ordinary row update does not stop a plain snapshot-based read of that product. Diagnosing every stalled read as a row-lock problem would lead you to the wrong blocker.

Bound the attempt before sending it

Use a dedicated migration connection and set its limits before the command. This example allows half a second for each lock acquisition and five seconds for the statement as a whole. Those are illustrative budgets for the brief column addition, not settings for a large validation scan.

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5s';
SET LOCAL application_name = 'shop_migrate';
ALTER TABLE public.products
  ADD COLUMN price_hundredths integer;
COMMIT;

lock_timeout applies separately to each lock acquisition. statement_timeout includes execution and waiting; giving it a shorter limit can make the lock limit irrelevant. Neither value is a prediction of completion. Choose a longer statement budget for work that legitimately needs a scan, while retaining a deliberate wait policy.

If the statement fails inside this explicit transaction, issue ROLLBACK before reusing the connection. A client’s cancellation request is also something to confirm at the server. Stop new attempts, establish which statement and transaction remain, and end the failed transaction. Repeated immediate retries can keep recreating the same queue.

Inspect the waiting session and its blockers from a monitoring connection:

SELECT pid, application_name, state,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers,
       xact_start, query_start, query
FROM pg_stat_activity
WHERE datname = current_database()
  AND (wait_event_type = 'Lock'
       OR application_name = 'shop_migrate');

Use the returned PIDs to inspect the blockers, including sessions idle inside a transaction. Suitable monitoring privileges are needed to see other sessions’ details. Ending a real customer transaction can undo work; establish its owner and purpose before choosing that intervention. Often the first useful action is to remove the waiting migration attempt and let the shop recover.

Metadata, scans and rewrites

Lock strength and copying work are different properties of a change. Adding our nullable integer column changes metadata without rewriting every product. It still needs the strong lock. Once acquired, keep that transaction short: do not leave it open while deploying callers or copying values.

A default cannot derive each product’s hundredths from its own price. Giving the new column a default of zero would turn missing prices into plausible but wrong values. The fast-default facility answers a different question: how to supply one default to existing rows when adding a column. A volatile default, such as clock_timestamp(), requires per-row work and a rewrite.

A rewrite builds replacement storage while the old storage still exists. Budget for table and index copies, WAL, ongoing activity and eventual cleanup, not just the final column width. PostgreSQL’s rewriting ALTER TABLE forms also have an unusual snapshot caveat. A transaction whose snapshot was taken before the rewrite commits can see the rewritten table as empty, if it had not accessed that table before the change. If it had, its lock would stop the rewrite from passing it. Rehearse the actual command and its reader overlap on a copy.

If a full blocking rewrite cannot fit the service’s interruption budget, expanding and updating in small transactions gives control over when to pause. It costs a period of carrying both representations and retained row versions. PostgreSQL vacuum and transaction age develops that retention and space-reuse work. Dropping the old column later hides it from SQL immediately, but does not compact all its old bytes out of the heap file.

Backfill from the row you protect

The bridge writers must be established before this backfill. They read the decimal price and commit both representations together on every insert or price change. Audit existing source values first. The accepted domain is finite, non-negative numeric(8,2): 0 through 999999.99 becomes 0 through 99999999, which fits an integer. PostgreSQL’s numeric NaN passes the original price >= 0 check, so check explicitly for it and resolve it under an agreed rule before converting. There is no implied price for an undefined value.

Suppose a worker read P7’s 20.00 into application memory. A bridge writer then commits 22.00/2200. Assigning the worker’s earlier 2000 afterwards corrupts the pair. Repeating that assignment safely would only repeat the wrong value.

Calculate inside the protected update instead. The condition also avoids rewriting an already-correct product:

UPDATE public.products
SET price_hundredths = (price * 100)::integer
WHERE product_id = 'P7'
  AND price_hundredths IS DISTINCT FROM (price * 100)::integer
RETURNING product_id, price, price_hundredths;

Under Read Committed, an update that meets a concurrent update of its target can wait, then recheck its condition against the committed replacement. If that bridge writer commits 22.00/2200, this statement finds nothing left to repair. Under stronger isolation a conflicting attempt may fail instead and need a whole-transaction retry. A transformation performed outside the database needs a source-value or revision guard, as the general migration article shows.

A bounded batch can first lock a small ordered group, then derive the values from those locked rows. This version deliberately waits within the chosen limits instead of skipping busy rows:

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5s';
WITH batch AS (
  SELECT product_id
  FROM public.products
  WHERE price_hundredths IS DISTINCT FROM (price * 100)::integer
  ORDER BY product_id COLLATE "C"
  LIMIT 100
  FOR UPDATE
)
UPDATE public.products AS p
SET price_hundredths = (p.price * 100)::integer
FROM batch AS b
WHERE p.product_id = b.product_id
RETURNING p.product_id;
COMMIT;

The size 100 is a starting assumption to rehearse, not a recommended production rate. Commit each completed batch and keep a record of exceptions. If a batch times out, roll it back. The rows it returned do not show that it committed.

A key cursor may make scanning cheaper, but a late P11 tea strainer would sort before P3 under the stated ordering. A permitted bridge insert must already supply its 8.00/800 pair. Reconcile the whole required population even after the cursor reaches its end. If you choose SKIP LOCKED, reconcile the skipped rows too.

SELECT count(*) FILTER (
         WHERE price_hundredths IS NULL) AS missing,
       count(*) FILTER (
         WHERE price_hundredths IS DISTINCT FROM
               (price * 100)::integer) AS different
FROM public.products;

Both counts must be zero. The difference count includes missing values; a zero null count alone misses P7 at 22.00/2000. A zero-row batch can mean this pass found no remaining work, while a stopped launcher says nothing about an already-running transaction. Observe the server state before claiming a pause.

Let useful foreground completions determine whether another batch fits. Watch purchase latency and errors alongside row-lock waits, free space, WAL generation and replica backlog. Updates create version and index work that can outlast the batch. Slow down or stop new batches before that debt consumes the service’s headroom. Keep the bridge write rule in force throughout: the reconciliation query sees one snapshot, and permitted writes are what preserve its result afterwards.

Enforce the rule, then prove the old rows

PostgreSQL can separate installing a rule from checking the entire existing population. An enforced NOT VALID constraint applies to subsequent inserts and updates. It has not yet proved that every old row satisfies it. NOT ENFORCED would make a different promise and is not the procedure here.

A non-null value can still be wrong. P7 at 22.00/2000 has no missing price, but the representations disagree. Proving required values and proving agreement are therefore separate validation tasks.

PostgreSQL 18 supports a named not-null constraint that can be installed as NOT VALID, then validated separately. For this migration we also preserve the original range and require exact agreement while both representations are maintained:

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5s';
SET LOCAL application_name = 'shop_migrate';
ALTER TABLE public.products
  ADD CONSTRAINT products_hundredths_nn
    NOT NULL price_hundredths NOT VALID,
  ADD CONSTRAINT products_hundredths_range
    CHECK (price_hundredths BETWEEN 0 AND 99999999) NOT VALID,
  ADD CONSTRAINT products_price_agrees
    CHECK (price_hundredths = price * 100) NOT VALID;
COMMIT;

The installation needs ACCESS EXCLUSIVE. Commit it before starting validation, so the strong lock does not cover the scan. Then run each validation under its own suitable scan budget:

ALTER TABLE public.products
  VALIDATE CONSTRAINT products_hundredths_nn;
ALTER TABLE public.products
  VALIDATE CONSTRAINT products_hundredths_range;
ALTER TABLE public.products
  VALIDATE CONSTRAINT products_price_agrees;

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'public.products'::regclass;

Here each command runs in autocommit. Validation takes SHARE UPDATE EXCLUSIVE, compatible with ordinary reads and writes but conflicting with some other maintenance. A failed validation leaves the installed rule enforced. Repair the old violations and retry validation. Even an update to an unrelated column can be rejected when its resulting row still violates an installed check or not-null rule, so choose the installation boundary with the caller behaviour in mind.

Foreign keys have a related two-step procedure. Installing FOREIGN KEY … NOT VALID takes SHARE ROW EXCLUSIVE on both the referencing and referenced tables. Validation takes SHARE UPDATE EXCLUSIVE on the referencing table and ROW SHARE on the referenced table. Budget both sides; an idle transaction on the referenced table can matter even when the changed table looks quiet.

Uniqueness needs a completed unique index

UNIQUE is not a NOT VALID shortcut. When adding a new approved uniqueness rule to an ordinary table, build its unique index concurrently, establish that it completed validly, then attach it with ALTER TABLE … ADD CONSTRAINT … UNIQUE USING INDEX …. The attachment still needs a brief strong table lock. The eligible index must be a unique B-tree with default sort ordering, without expressions or a partial predicate. A primary key also needs its columns proved non-null, or attachment can introduce a scan.

This changes no shop identity or business uniqueness rule. In particular, neither equal prices nor repeated customer IDs in orders are errors. The index must express a rule already required by the data model. Next we will follow the concurrent build itself using the existing, non-unique history key.

Build an index while writes continue

The shop’s history request filters by customer and a lower timestamp, orders by descending timestamp and order ID, then stops at ten. PostgreSQL index methods and operators establishes the B-tree choice. Here its definition stays fixed:

CREATE INDEX CONCURRENTLY orders_history
ON public.orders (customer_id, placed_at DESC, order_id DESC);

An ordinary build takes a SHARE table lock: reads can continue, while inserts, updates and deletes wait. A concurrent build uses SHARE UPDATE EXCLUSIVE so those writes can continue. It pays for that overlap with two scans and waits for relevant transactions, including writers before scans and old snapshots before validity. Extra processor and storage work still competes with the application.

A concurrent build makes three properties true at different times: its definition exists, writes maintain it, and queries can rely on it. An intermediate catalogue entry is not yet a complete query path.

Run CREATE INDEX CONCURRENTLY outside a transaction block. A migration tool that wraps every change in one transaction needs a separate execution mode for it. Only one concurrent index build can run on a table at a time; conflicting schema work and maintenance still wait. Partitioned parent indexes require their own procedure: PostgreSQL 18 cannot concurrently build the parent index directly. Build eligible leaf indexes concurrently, then assemble the parent definition with its own brief locking boundary.

Read the current phase

SELECT pid, relid::regclass AS table_name,
       index_relid::regclass AS index_name, phase,
       blocks_done, blocks_total,
       lockers_done, lockers_total, current_locker_pid
FROM pg_stat_progress_create_index
WHERE datid = (SELECT oid FROM pg_database
               WHERE datname = current_database());

waiting for writers before build asks you to inspect transactions, not storage throughput. building index reports access-method work. Validation has further scan phases, followed by waiting for old snapshots when applicable. The block and tuple counters describe their current phase. A ratio of blocks done to blocks total is not a percentage of the whole command, and old-snapshot waiters supply no reliable estimated finish time.

The progress row disappearing is a reason to inspect the outcome. It does not distinguish successful completion from an error. Keep the command result and check the catalogue:

SELECT indexrelid::regclass AS index_name,
       indisready, indisvalid, indislive,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'public.orders'::regclass;

indisready says whether inserts and updates should maintain the index. indisvalid records query validity. indislive distinguishes an index still participating in the lifecycle from one being dropped. These flags are evidence to interpret alongside the command, definition and dependencies, not fields to edit by hand. A valid index is eligible for appropriate queries; the planner is still free to choose another route.

Recover the state a failed build left behind

A concurrent build can fail after committing intermediate work. Inspect the existing definition and flags before retrying, including when the client lost its reply. IF NOT EXISTS merely accepts an existing name. It proves neither the intended columns nor validity.

An invalid index cannot supply a complete query answer. If it reached write readiness, it can still add write overhead. A failed unique build deserves particular care: uniqueness enforcement begins before the index becomes query-valid, and a failure during its second scan can leave that enforcement active. “Invalid” does not mean harmless to writers.

Resolve the error’s cause first: a timeout, deadlock, invalid expression or genuine duplicate needs its corresponding repair. For an unwanted standalone leftover, deliberately drop it and retry the corrected concurrent build. Alternatively, REINDEX INDEX CONCURRENTLY public.orders_history; can rebuild an invalid index while writes continue. Recheck the resulting definition and state, then the supported query. Reissuing an unchanged unique build cannot repair data that violates the intended rule.

A concurrent rebuild has a replacement boundary of its own. It keeps the working index while building a new one, transfers the index’s role to the valid replacement, then retires the old copy. Failure on either side of that transfer leaves a different cleanup task.

An invalid index with a _ccnew suffix is the transient replacement left by a failed concurrent rebuild. The original still serves queries; dropping it would remove the route you intended to retain. An invalid _ccold index is the former original left after successful replacement, with retirement still to finish.

Suffixes can acquire numbers to avoid name collisions, so confirm catalogue state and the exact definition before choosing a target. Inspect constraints and other dependencies before removing either leftover. Choose the supported drop method for the exact object and confirm it is gone afterwards.

Choose the work the index actually needs

Not every slow index needs rebuilding. A B-tree with persistently sparse leaves, a GIN index carrying pending entries and a BRIN index missing a summary hold different unfinished work. Choose the maintenance operation for that unfinished work rather than treating every backlog as a rebuild problem.

Sparse B-tree pages

Vacuum can remove obsolete index entries and make empty pages reusable. That does not promise a smaller file, and partly full leaves can remain costly to scan and cache. Measure the index’s physical size, live workload and buffer work over time. Where available, the pgstattuple extension’s pgstatindex reports leaf density, empty pages and deleted pages. It needs privileges and performs work; concurrent changes make its page-by-page result something other than one instantaneous snapshot.

Compare the observed layout with the index’s fill factor and future insert pattern. Space reserved for expected inserts may be useful. Persistent sparsity in key ranges that new inserts never revisit may be wasteful. Neither the index’s age nor one fixed density threshold decides whether a rebuild pays.

REINDEX INDEX blocks writes and locks the index strongly; planning queries can block too. REINDEX INDEX CONCURRENTLY permits ordinary writes but needs the extra scans, waits and simultaneous copies already described. Budget replacement space, WAL and foreground demand. Afterward confirm validity, size and useful query work, and check whether the workload immediately recreates the same problem. Rebuilding an index does not release a vacuum retention horizon.

GIN pending entries

GIN’s fastupdate path accumulates entries in a pending list before merging them into its main structure. Queries search the pending list too, so a backlog does not make recently inserted matching rows disappear. It can increase query work, and the insertion that crosses the cleanup limit can inherit a large merge.

Inspect pending pages and tuples with pgstatginindex where the extension is available. Ordinary vacuum or autovacuum can process pending entries; gin_clean_pending_list requests that cleanup directly. For the tag projection introduced in the access-method article, the targeted command is:

SELECT gin_clean_pending_list('public.product_tags_gin'::regclass);

Turning fastupdate off changes where future entries go; it does not flush entries already pending. Choose a cleanup window or adequate background upkeep from the backlog and foreground cost. Afterwards check the pending state and representative search and insert work. A quick insertion benchmark taken before the merge cannot establish the settled cost.

BRIN summaries

A new heap range can be unsummarised. It remains a candidate for every relevant BRIN search, preserving correct results while adding heap work. brin_summarize_new_values summarises those missing ranges. Vacuum also performs this work; optional autosummarisation is off by default and is a queued request, not a completion guarantee.

-- For the BRIN timestamp index from the access-method article:
SELECT brin_summarize_new_values('public.orders_time_brin'::regclass);

-- A separate, deliberate refresh of the range containing block 0:
SELECT brin_desummarize_range('public.orders_time_brin'::regclass, 0);
SELECT brin_summarize_range('public.orders_time_brin'::regclass, 0);

The second procedure answers a different problem. An existing min/max summary can remain broad after an outlying value changes or disappears. Ordinary summarisation does not replace an already-present summary; deliberate desummarisation followed by resummarisation can derive it again from current tuples. During that gap, the range cannot be excluded through its missing summary. Use the actual affected block range, rather than copying block 0 without investigation.

Check the returned summarisation count and the resulting query’s heap ranges and rechecks. If current rows still mix distant timestamps on every page, a fresh summary will remain broad. Maintenance cannot create the physical correlation that the chosen BRIN design needs.

Retire the old route after acceptance

Keep an old index until the replacement is valid, produces the expected results and removes the intended work across supported requests. Inspect pg_stat_user_indexes alongside the statistics reset period, server restarts and representative workload coverage. Zero recorded scans during a quiet hour cannot account for month-end reports, standby reads or constraints. Check pg_constraint, dependencies and referencing-side lookups; overlapping key prefixes alone do not make two definitions interchangeable.

For an approved standalone index removal, DROP INDEX CONCURRENTLY avoids locking out ordinary table reads and writes by waiting for conflicting transactions. It accepts one index, cannot use CASCADE or run inside a transaction block, and cannot remove a partitioned parent index this way. An index backing a primary-key or unique constraint needs the constraint’s procedure instead. Removal has a wait and cleanup budget too.

Contracting the old price has a caller boundary as well. While return to bridge readers is promised, keep both prices current and keep the agreement rule. Before permitting new-only writes, retire that promise and all old consumers, remove transitional cross-form requirements, and handle the old column’s NOT NULL requirement or keep supplying it until its drop. Preserve the new column’s required-value, non-negative and range rules. A column that still exists but is no longer maintained is already unsuitable for a simple return.

Accept the change through the state it leaves behind. Under the permitted writer protocol, no prices may be missing or unequal. Constraint validation must have completed, and the index definitions must be exact and valid. Representative application results and query work must behave as expected, and every leftover must be accounted for. A command returning establishes one event. The new route working and obsolete physical work being removed are further outcomes to verify.

On an owned disposable PostgreSQL 18.6 server, three client sessions tested the queued schema-change timeout. The later reader waited behind the change. It completed once the change timed out, while the first reader was still open. An ordinary row update let that plain read complete. In the price race, an unguarded assignment stored 22.00/2000; the guarded current-row update waited for the bridge commit, returned no rows and preserved 22.00/2200.

The same native check completed named not-null and check validation, concurrent index creation and concurrent reindexing. A unique build failing on pre-existing duplicates left an invalid index that had not reached write readiness; that run does not demonstrate the later-phase uniqueness-enforcement case. The twelve displayed SQL listings were also checked in PGlite 0.5.8, PostgreSQL 18.3 compiled to WebAssembly, including a separate failure to validate an unequal price pair. The drawings remain models; these small checks establish no production duration or headroom.