Why One PostgreSQL ALTER TABLE Can Stall Every Query Behind It

This article explains why a single ALTER TABLE statement in PostgreSQL can block all subsequent queries on that table, and often on the entire database. The short answer: most ALTER TABLE operations acquire an ACCESS EXCLUSIVE lock on the table, which conflicts with every other lock mode, forcing all reads and writes to wait until the alteration finishes. Below we unpack the locking model, the specific commands that trigger it, and how to avoid or minimize the stall.

Background: PostgreSQL’s Locking Model

PostgreSQL uses a multi-version concurrency control (MVCC) system that allows readers and writers to proceed without blocking each other in normal operations. However, schema changes (DDL) are different. To safely change a table’s structure, PostgreSQL must ensure that no other transaction is using the old structure while the change is in progress. It does this by taking a lock on the table. The lock modes are defined in the pg_locks system view, and the most restrictive is ACCESS EXCLUSIVE.

When a session runs ALTER TABLE, it requests an ACCESS EXCLUSIVE lock on that table. This lock conflicts with all other lock modes, including the ACCESS SHARE lock that plain SELECT queries take, and the ROW EXCLUSIVE lock that INSERT/UPDATE/DELETE take. Consequently, any query that needs a lock on the table will queue behind the ALTER, and the ALTER itself will wait for any currently running query that holds a conflicting lock to finish. The result: a long-running ALTER can stall every query that touches that table, and if the table is central to the application, it can effectively freeze the whole database.

How the Lock Is Acquired and Held

The lock is acquired at the start of the ALTER statement and is held until the transaction commits or rolls back. If the ALTER is part of a larger transaction, the lock is held for the entire transaction duration, not just the ALTER statement. That means if you wrap an ALTER in a transaction that also does other work, the lock is held longer than necessary.

Moreover, PostgreSQL processes lock requests in a queue. If a long-running query (say a heavy analytical SELECT) is already running when the ALTER is issued, the ALTER waits for that query to finish. But while the ALTER is waiting, new queries arrive and try to take a lock. Because the ALTER is already queued, PostgreSQL uses a “no starvationâ€� policy: once a lock request is queued, all later requests for a conflicting lock are placed behind it, even if they could otherwise be granted. This means a single ALTER can block not only the queries that arrive after it, but also those that arrive while it is waiting, creating a cascading stall.

Which ALTER TABLE Operations Cause the Stall?

Not all ALTER TABLE commands are equally disruptive. The ones that require ACCESS EXCLUSIVE include:

• ALTER TABLE ... ADD COLUMN (in most versions, though adding a column with a non-volatile default is optimized in PostgreSQL 11+ to avoid a full rewrite, it still takes an ACCESS EXCLUSIVE lock briefly).
• ALTER TABLE ... DROP COLUMN
• ALTER TABLE ... ALTER COLUMN TYPE (this often rewrites the table, holding the lock for the entire rewrite).
• ALTER TABLE ... SET NOT NULL (requires a full table scan to validate existing rows).
• ALTER TABLE ... ADD CONSTRAINT (like a foreign key or unique constraint).
• ALTER TABLE ... SET DEFAULT (in some cases, it only takes ACCESS EXCLUSIVE briefly, but still blocks).
• ALTER TABLE ... RENAME COLUMN or RENAME TO (takes ACCESS EXCLUSIVE).

On the other hand, operations like CREATE INDEX (non-concurrent) take a SHARE lock, which blocks writes but allows reads. The CREATE INDEX CONCURRENTLY variant avoids blocking reads and writes, but it cannot be run inside a transaction block and takes longer.

Common Problems and Real-World Impact

The most common problem is running an ALTER TABLE on a large table during peak hours. For example, adding a column with a default value to a table with 100 million rows in PostgreSQL 10 or earlier rewrites the entire table, holding the ACCESS EXCLUSIVE lock for minutes or even hours. During that time, every SELECT, INSERT, UPDATE, and DELETE on that table will hang, waiting for the lock. If your application has a connection pool with a timeout, connections will eventually time out, leading to errors and possibly a cascading failure as other services depend on that database.

Another common issue is lock queueing. Suppose you have a long-running query that holds a ACCESS SHARE lock. You issue an ALTER TABLE that waits for it. Meanwhile, a new query arrives and requests a ROW EXCLUSIVE lock. Normally, that lock would be granted immediately because it doesn’t conflict with the ACCESS SHARE lock. But because the ALTER is already queued, the new query is placed behind the ALTER in the queue, so it also waits. This can cause a pile-up of queries that would otherwise have completed quickly.

How to Choose the Right Approach

To avoid stalling every query, you have several options, each with trade-offs:

• Run ALTER TABLE during a maintenance window – the simplest, but not always feasible for 24/7 services.
• Use CREATE INDEX CONCURRENTLY for indexes – it does not block reads or writes, but it takes longer and can fail, leaving an invalid index that you must drop.
• Use ADD COLUMN ... DEFAULT with a constant default in PostgreSQL 11+ – this is metadata-only and takes the lock only briefly, but the default is applied to new rows, and existing rows return the default on read without a rewrite.
• Break the operation into steps – for example, add a nullable column, then update rows in batches, then set NOT NULL in a separate transaction. This reduces the time the ACCESS EXCLUSIVE lock is held.
• Use pg_sleep or lock_timeout to fail fast – set lock_timeout so that the ALTER fails if it cannot get the lock within a reasonable time, rather than blocking forever.

For a full table rewrite (e.g., changing a column type), consider using a tool like pg_repack or pg_squeeze that can rebuild the table without blocking, but these are external tools and require careful planning.

Costs and Safety Considerations

The cost of an ACCESS EXCLUSIVE lock is not just the waiting time; it also affects the database’s overall health. Long lock waits can cause connection pool exhaustion, application timeouts, and even failover in a high-availability setup if the primary is unresponsive. To monitor locks, use pg_locks and the pg_stat_activity view to see which queries are waiting and what holds the lock. A useful query is:

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Safety-wise, never run an ALTER TABLE on a production table without testing on a staging environment first, especially if the table is large. Also, be aware that some ALTER operations (like SET NOT NULL) scan the table to validate data; if the table is huge, that scan itself takes time, and the lock is held during the scan.

Alternatives to ALTER TABLE for Schema Changes

If you need to change a column type or add a NOT NULL constraint without blocking, consider these alternatives:

• Add a new column with the desired type, then write a trigger or application logic to populate it over time, and finally drop the old column. This is a common pattern for zero-downtime migrations.
• Use a view to present the old column as the new type, but this only helps for reads, not for writes.
• Use logical replication to create a new table with the desired schema and switch over when it’s caught up. This is complex but fully non-blocking.
• For adding a NOT NULL constraint, you can add a check constraint with NOT VALID and then validate it later with VALIDATE CONSTRAINT, which takes a SHARE UPDATE EXCLUSIVE lock that does not block reads or writes (though it still blocks other DDL).

Each alternative has its own complexity, but they all avoid the long ACCESS EXCLUSIVE lock that stalls every query.

Frequently Asked Questions

Why does my ALTER TABLE wait even when no queries are running?

If you see the ALTER waiting even with no active queries, it could be that an idle-in-transaction session holds a lock. For example, a session that has executed a SELECT and then gone idle still holds its ACCESS SHARE lock until the transaction ends. Check pg_stat_activity for sessions with state = 'idle in transaction'.

Can I kill the ALTER TABLE if it’s blocking everything?

Yes, you can cancel the ALTER by running SELECT pg_cancel_backend(pid) for the ALTER session. If that doesn’t work (e.g., it’s in a critical section), you can use pg_terminate_backend. However, killing a DDL statement may leave the table in an inconsistent state, so it’s better to let it finish or to have set a lock_timeout beforehand.

Does adding a column with a default always rewrite the table?

In PostgreSQL 11 and later, adding a column with a constant default (e.g., DEFAULT 0) is metadata-only and does not rewrite the table. In earlier versions, it rewrites the table, which is why the lock is held for a long time. For non-constant defaults (like DEFAULT now()), the rewrite is still required.

What is the difference between ACCESS EXCLUSIVE and SHARE locks?

An ACCESS EXCLUSIVE lock conflicts with all other locks, blocking both reads and writes. A SHARE lock (used by CREATE INDEX without CONCURRENTLY) conflicts with writes but allows reads. So a plain CREATE INDEX will block INSERT/UPDATE/DELETE but not SELECT.

Can I run ALTER TABLE inside a transaction and roll back if it takes too long?

Yes, you can wrap it in a transaction and roll back, but the lock is held until the transaction ends. If you roll back, the lock is released, and the table remains unchanged. However, if the ALTER has already rewritten the table, rolling back will undo that work, which may take additional time.

Does VACUUM FULL also stall queries?

Yes, VACUUM FULL takes an ACCESS EXCLUSIVE lock and rewrites the table, so it has the same blocking effect as a long ALTER TABLE. Use pg_repack or schedule it during a maintenance window.

Related

• Disable Action Center Win10: Steps & Fixes
• Galaxy M14 4G vs M17 5G: Which Fits Your Needs?
• Change Pagefile Size & Location in Windows

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2