A nightly logical dump (pg_dump or mysqldump) of a busy production database now runs for hours. What consistency guarantee does such a dump actually give, and what side effects does a long-running dump have on the live system?
answer
- No snapshot = tables from different instants
- --single-transaction: InnoDB only
- Long snapshot pins old row versions → bloat / undo growth
- Shared table locks queue DDL behind the dump
- Buffer-cache eviction; prefer dumping a replica
basics
~20 sA dump is consistent only if it reads inside one long transaction snapshot; otherwise tables are copied at different times. That snapshot blocks cleanup of old row versions, so the database bloats, and the dump also holds locks that block DDL and pollutes the buffer cache.
solid answer
~60 sBy default a dump is a series of ordinary reads, so different tables can reflect different points in time and a restore can violate foreign keys. Consistency requires an explicit repeatable-read snapshot — `pg_dump` opens one automatically, `mysqldump --single-transaction` does so for InnoDB only, and non-transactional engines need a global read lock instead, which stalls writes. The snapshot is what costs you. Because the dump must still see rows as they were at its start, the engine cannot reclaim superseded row versions for the whole run: PostgreSQL's vacuum stops advancing and tables and indexes bloat, MySQL's undo history grows and purge stalls, and every query paying to skip that history slows down. The dump also holds a shared lock on each table, so migrations and `ALTER`s queue behind it, and it drags the entire dataset through the buffer cache, evicting the hot working set. The usual fixes: run it against a replica, parallelise it, or move the routine backup to a physical one and keep the dump for portability.
go deeper
Know that a dump needs a single transaction snapshot to be consistent, and that running one on a busy primary is heavy.
Explain the snapshot mechanism, name the flags per engine, and mention buffer-cache impact and locking of DDL.
Connect the long snapshot to concrete symptoms — bloat, undo history growth, queued ALTERs behind a shared lock — and propose replica dumps or a physical backup as the routine path.
Treat it as a capacity and change-management decision: what the backup method costs the steady state, which recovery scenarios need a dump at all, and how the backup window interacts with the deployment window.
## What consistency you actually get A logical dump reads tables through the normal query path. Without extra measures it is a *sequence* of reads: orders may be read at 01:00 and order_items at 03:00. Restore that and you get children without parents — a backup that fails its own foreign keys. The fix is to read everything in one **snapshot** at repeatable-read isolation, so every table is seen as of one instant regardless of when it is physically read. `pg_dump` does this by default (and, in parallel mode, exports the snapshot so every worker shares it). `mysqldump --single-transaction` does it for transactional tables only; any non-transactional table in the dump silently falls outside the snapshot. Where no MVCC snapshot exists, the only alternative is a global read lock that blocks writes for the duration — acceptable for seconds, not hours. ## Why the snapshot is expensive MVCC engines keep superseded versions of rows until no transaction can still need them. A dump's snapshot is exactly such a transaction, and it lives for the entire run. - **PostgreSQL**: vacuum can still run, but it cannot remove any row version newer than the oldest live snapshot. Tables and indexes grow, sequential scans read more pages for the same rows, and index-only scans lose the visibility map. On a heavily updated table a multi-hour dump can add substantial permanent bloat. `pg_stat_activity` shows the dump as the oldest `xmin` holder. - **InnoDB**: the undo log must retain every version the snapshot might read, so history-list length grows. Purge falls behind, and queries reading frequently-updated rows must walk longer undo chains, which shows up as CPU and latency creep. A dump left running by accident is a classic cause of a runaway history list. The bloat does not disappear when the dump finishes; the space is reclaimed but the files usually stay large until they are rewritten. ## Locking Even a snapshot-based dump takes a shared lock on each table it reads and holds it until the end. Nothing blocks reads or writes, but **DDL blocks**: a deployment's `ALTER TABLE` waits behind the dump, and in PostgreSQL that waiting exclusive lock then queues every subsequent query on the table behind *it*. A slow dump overlapping a migration window can therefore look like a total outage on one table. Non-transactional engines are worse: `mysqldump` without `--single-transaction` falls back to locking tables, and `--master-data` style options briefly take a global read lock to record a consistent log position. ## Resource side effects - **Buffer cache eviction**: the dump reads every table once, so it can displace the working set and leave the application reading from disk for a while after it ends. - **CPU and network**: serialising rows to text and compressing them is CPU-heavy on the database host. - **On a replica**: running the dump on a standby protects the primary, but the standby's own snapshot conflicts with replay of cleanup records — replication either pauses (if configured to defer) or cancels the dump. Both outcomes need a deliberate choice. ## Making it acceptable 1. **Move it off the primary** — dump a replica, or a physical restore of last night's backup in a scratch instance. 2. **Stop using it as the routine backup** — a physical backup does not hold a long MVCC snapshot at all; keep the dump weekly for portability and object-level recovery. 3. **Parallelise** — a directory-format parallel dump shortens the window, which directly shortens the snapshot. 4. **Watch the right signals** — oldest transaction age, history-list length, dump duration trend, and lock waits during the dump window; alert when the run exceeds its normal envelope rather than discovering it days later. 5. **Split it** — dumping the largest table separately is tempting but breaks cross-table consistency unless the exports share one exported snapshot.
- A colleague adds `--single-transaction` to mysqldump and calls the consistency problem solved. When is that not true?It only covers transactional storage engines. Any MyISAM or other non-transactional table is read outside the snapshot, so the dump can mix points in time without warning. It is also broken by DDL: a schema change during the dump can cause an error or an inconsistent result, since the snapshot protects row visibility, not the catalog. And it does not make the dump cheap — the long-lived snapshot still holds undo history for its full duration.
- Dumping the replica avoids loading the primary. What new problem does that introduce?The dump's snapshot on the standby conflicts with applying cleanup from the primary that would remove row versions the snapshot still needs. The standby must either pause replay while the dump runs, growing lag and the recovery gap, or cancel the query, killing the dump. Either way it is a deliberate tradeoff, tuned with the engine's conflict-handling settings and by sizing the dump window against acceptable lag.
saying these in an interview costs you the question
- Assuming any dump is automatically point-in-time consistent
- Believing a snapshot-based dump is free because it 'takes no locks'
- Not knowing that --single-transaction excludes non-transactional tables
- Blaming bloat on autovacuum being 'too slow' when a long dump is pinning the horizon
- Dumping table by table in separate sessions and expecting a consistent restore