A short-looking ALTER TABLE can wait behind one long-running SELECT if the DDL needs a lock that conflicts with the reader’s lock. On a busy service, that wait can become an availability risk. The practical response is to inspect the exact operation, bound its lock wait, and roll out schema and application changes in stages. These are risk-reduction techniques, not a guarantee of literal zero downtime.
The lock and DDL behavior described here is based on PostgreSQL 18 documentation current on October 4, 2026. Check the documentation for the major version you actually run: lock requirements and optimizations can differ.
Why can one slow query hold up an ALTER TABLE?
The reader and the DDL want incompatible locks
A plain read-only SELECT takes an ACCESS SHARE lock on each referenced table. That lock is compatible with every table-level lock mode except ACCESS EXCLUSIVE. PostgreSQL’s explicit-locking documentation says, “The SELECT command acquires a lock of this mode on referenced tables.”
PostgreSQL 18’s ALTER TABLE documentation says, “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” If an ALTER TABLE form needs that lock while a query still holds ACCESS SHARE, the DDL must wait for the query’s lock to be released.
#1 Best Overall
A queued DDL statement is a risk, not proof every query will stop
In a busy system, a waiting strong-lock request can complicate access to the table and contribute to a traffic incident. The exact effect depends on which locks are requested and held, and on the workload; a queued ALTER TABLE does not mean every later query is necessarily blocked. The key operational warning is that an operation that looks instantaneous can spend time waiting before it even begins its work.
Check the exact DDL before calling it safe
Find the strictest lock in the migration
Do not classify a migration by the broad label “ALTER TABLE.” Check every subform against the command reference for your deployed PostgreSQL major version. In PostgreSQL 18, a combined ALTER TABLE uses the strictest lock required by any of its subcommands. Documented exceptions matter: for example, ADD FOREIGN KEY requires SHARE ROW EXCLUSIVE, not the default ACCESS EXCLUSIVE.
Lock mode is only part of the risk assessment. Determine whether the change scans existing rows, rewrites the table or indexes, or can do its work without either. A statement may acquire its lock quickly and still take substantial time or resources after it has acquired it.
Rank #2
Know whether PostgreSQL scans or rewrites data
| Change, as documented for PostgreSQL 18 | Existing-data work and operational implication |
|---|---|
| Add a column with a non-volatile default | Does not rewrite the table. |
| Add a column with a volatile default | Rewrites the table. |
| Many type changes | Can rewrite the table and indexes; assess the exact type change. |
| Verify a constraint against existing rows | Can scan a large table. For supported constraints, installing it as NOT VALID separates installation from verification. |
These are PostgreSQL 18 behaviors, not a promise that similarly named operations have identical costs on every version or schema. Table size, the specific DDL, and the service’s available time and disk headroom all affect deployment planning.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Use lock_timeout to bound the wait—not the migration’s runtime
lock_timeout aborts a statement when an individual lock acquisition takes longer than the configured interval. Its default is zero, which disables the timeout. It limits how long a migration waits to acquire a lock; it does not make a scan, rewrite, or backfill finish faster.
Set a deliberate, nonzero timeout for the migration session rather than imposing one globally in postgresql.conf. PostgreSQL cautions against a global setting because it affects every session. Choose the interval according to the service’s latency budget and the deployment’s retry or abort policy; there is no universally safe duration.
statement_timeout is different: it limits statement execution time overall. If it is nonzero and set at or below lock_timeout, it may fire first. Make both timeout settings intentional for the migration runner rather than assuming the lock timeout governs every failure.
Decide what happens when the lock timeout fires before starting the rollout. The migration should fail in a way the deployment system recognizes, and any retry should be deliberate rather than an unbounded loop. A timeout prevents indefinite waiting under that setting; it does not make an operation safe to retry concurrently or guarantee that a later attempt will succeed.
Stage application and schema changes with expand/contract
Expand/contract is a rollout pattern, not a PostgreSQL command. Its purpose is to keep intermediate application versions compatible with the schema while a change is being deployed. For a column replacement, for example, avoid a transition in which one running application version requires the old representation while another assumes the new one is already complete.
- Expand: Add the new schema in a form compatible with the currently deployed code. First assess the exact lock and whether PostgreSQL must scan or rewrite existing data.
- Deploy compatible application code: Roll out code that can tolerate the old and new representations during the transition. Do not switch all reads or writes on the assumption that every application instance has already updated.
- Backfill in bounded work, if needed: Populate the new representation in controlled batches appropriate to the application and workload. Confirm completion and correctness before relying on it; the safe batch size and schedule depend on the system.
- Switch usage: Move reads or writes to the new representation only after the deployed code and data state support that change. Keep intermediate versions compatible while the rollout is in progress.
- Contract later: Remove the old representation only after the application no longer depends on it and the new path has been verified. Treat removal as a separate schema change with its own lock and runtime assessment.
Each step still needs review as a specific DDL and application change. Expand/contract reduces the need for one all-at-once switch; it does not remove lock acquisition, rewrite, data-correctness, or coordination risks.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Separate constraint installation from validation when supported
For supported constraints, PostgreSQL lets you add a constraint as NOT VALID, then validate existing rows in a separate operation. The initial installation skips checking old rows; the later VALIDATE CONSTRAINT performs that check.
In PostgreSQL 18, validation takes a SHARE UPDATE EXCLUSIVE lock and does not lock out concurrent updates. This separates the existing-row scan from constraint installation, but validation still has work to do. Confirm that the specific constraint supports this workflow and plan for the scan.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use CREATE INDEX CONCURRENTLY with its trade-offs in view
CREATE INDEX CONCURRENTLY avoids locking out normal writes during the index build, which can make it useful for live-table changes. It is not a free or instantaneous build:
- It performs two table scans and uses more work and resources than a regular index build.
- It can wait for relevant transactions to finish.
- It cannot run inside a transaction block.
- If it fails, it may leave an invalid index that needs to be identified and cleaned up before you proceed.
Plan for resource use and failure handling as well as write availability. Do not assume a failed concurrent build left the schema exactly as it was before the command.
Observe lock waits and prepare a recovery path
Before applying DDL, know how the migration runner reports a lock timeout, whether it marks the migration failed safely, and how retries are serialized. Avoid automatic retries without bounds and backoff: repeated attempts can add pressure without resolving the blocker.
PostgreSQL’s explicit-locking documentation identifies pg_locks as a way to examine outstanding locks. Use it as part of your investigation when a migration is waiting; the lock table can help reveal the state, but it does not by itself define the right operational response. Decide in advance who can investigate the wait, how the deployment will be stopped or retried, and how to handle any incomplete work such as an invalid index.
Use a consistent review for each live migration: verify the PostgreSQL version and exact DDL; identify the required lock; assess scans, rewrites, and resource needs; configure a migration-scoped lock timeout; and confirm application compatibility and recovery behavior. No universal ranking makes one migration strategy safest for every workload.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
