pg_dump of a Large Database Is Too Slow

Scenario

A 2 TB PostgreSQL database runs nightly pg_dump in plain SQL format. The dump takes 18 hours — longer than the backup window — and sometimes runs into the next business day. The dump file is 800 GB uncompressed. The team wants faster backups with smaller files without switching to a completely different backup tool.

How to Identify

Conditions:

• pg_dump runs in plain text format (--format=plain) with no compression
• No parallel workers (--jobs not used)
• All tables are in one schema with no tablespace separation
• Backup runs on the primary, competing with production read/write workload
• File transfer after dump adds more time before backup is usable

Analysis Steps

-- Identify the largest tables to understand where time is being spent
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename))       AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename))        AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Identify which tables have the most rows (dump time is proportional to row count)
SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 20;

-- Check if the database has tablespaces for parallel dump targeting:
SELECT spcname, pg_tablespace_location(oid) AS location
FROM pg_tablespace;

-- Check current database size:
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

Pitfalls

• Plain text (--format=plain) dumps cannot use --jobs (parallel dump) — only directory format supports parallel workers.
• Running pg_dump on the primary competes with production I/O. Run backups from a streaming standby to offload the primary.
• Large dumps in plain SQL format are slow to restore: every INSERT is parsed and executed individually. Custom format (-Fc) restores 3-5x faster with pg_restore --jobs.
• Without compression, an 800 GB dump file requires 800 GB of temporary disk space plus transfer time. Use --compress=9 or pipe through gzip.
• pg_dump is limited to a single connection — it cannot exploit multiple CPU cores or disks without --jobs in directory format.

Resolution Approach

Switch to --format=directory with --jobs=N (parallel dump) and --compress=9 (gzip compression). Run the backup from a read replica to offload the primary. For 2 TB+, consider pgbackrest or barman which use incremental backups and parallel streaming.