What pg_dump actually locks

11 Apr 2026

"pg_dump doesn't block anything, it's just a read" is half true, and the other half is how a nightly backup takes an application down.

pg_dump takes an ACCESS SHARE lock on every table it dumps, and holds all of them until it finishes. ACCESS SHARE is the weakest lock there is. It conflicts with exactly one thing: ACCESS EXCLUSIVE.

So SELECT, INSERT, UPDATE, DELETE all proceed normally during a dump. What cannot proceed is anything needing ACCESS EXCLUSIVE:

• DROP TABLE, TRUNCATE
• most ALTER TABLE forms, including ADD COLUMN in older versions
• VACUUM FULL
• REINDEX (without CONCURRENTLY)
• CREATE INDEX without CONCURRENTLY

A dump that runs for forty minutes is a forty-minute window in which migrations block. Run backups and migrations on the same schedule and you will eventually find out.

The lock queue turns this into an outage

This is the part that makes it dangerous rather than merely inconvenient. Postgres grants locks roughly in arrival order, and a waiting request blocks later, otherwise-compatible requests behind it.

1. pg_dump holds ACCESS SHARE on orders.
2. A migration asks for ACCESS EXCLUSIVE. It waits.
3. A plain SELECT on orders arrives. It is compatible with the dump — but it queues behind the migration.

Now every read of orders is stalled by a backup that, on its own, blocked nothing. The application is down and the query that appears to be at fault is an innocent SELECT.

The mitigation is to never let a migration wait indefinitely:

SET lock_timeout = '3s';

Fail, retry later, and the queue never forms.

The snapshot has its own cost

pg_dump runs in a REPEATABLE READ transaction so the output is internally consistent — one point in time, not a smear across the dump's duration.

Holding that snapshot means VACUUM cannot remove any row version that was live when the dump started, anywhere in the database. On a busy table with heavy updates, a long dump means dead tuples accumulate for the whole window and bloat appears afterwards. You will see it as a table whose size grew overnight while its row count did not.

SELECT pid, state, xact_start, now() - xact_start AS age, query
  FROM pg_stat_activity
 WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
 ORDER BY xact_start;

The oldest transaction is what is holding vacuum back, and during a backup window it is usually the dump.

Parallel dumps

pg_dump -Fd -j 8 -f dumpdir mydb

Directory format with -j uses one connection per worker and synchronised snapshots so all workers see the same point in time. It is much faster, and it takes more connections and more locks concurrently — which makes everything above more acute, not less.

The actual answer

Dump from a replica. Everything in this post is about contention with production traffic, and a replica has none. The snapshot cost becomes a replication-lag question instead, which is easier to reason about and easier to alert on.

If you must dump from the primary: do it off-peak, set lock_timeout on your migrations rather than on the dump, and alert on transaction age rather than on dump duration. The dump taking longer than usual is not the problem. The dump still holding a snapshot when the morning traffic arrives is.