Search code examples
postgresqlpg-dumpmvcc

Postgres pg_dump effect on production DB

Is it possible to query how many multiversioned rows are there in a db ?

We want to measure the impact of pg_dump on a production database, and to suspend it in case of need: is it creating too many multiversioned rows?

Thanks in advance

Solution

  • pg_dump doesn't create any row versions (a.k.a tuples).

    The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump.

    Normally that is no big problem unless you have very high write activity in the database. To mitigate that problem, you could create a streaming replication standby server, set max_standby_streaming_delay to more than the duration of pg_dump and let pg_dump run there.