Why Did My pg_restore Take All Night? Parallelism and Deferred Indexes

A restore that should take an hour ran all night, and the reason is almost never the data. It is the indexes and constraints being rebuilt one at a time, inline, on a single core.

This is the moment a disaster-recovery drill stops being a formality and becomes a governance problem. You have a valid backup. You can prove the bytes are intact. But if the restore takes nine hours and your recovery-time objective is two, the backup is only half a control. The good news is that a slow pg_restore is usually slow for one boring, fixable reason, and PostgreSQL already ships the tools to fix it. What follows assumes PostgreSQL 17, but the mechanics have held for many releases.

What is actually taking all night

A logical restore is not one job. It is three phases, and PostgreSQL names them explicitly: pre-data (schema, types, functions), data (the table rows and large objects), and post-data (indexes, triggers, rules and most constraints). Copying rows in is fast — it is a bulk COPY and the disk does most of the work. The time sink is post-data. Every index has to be built by scanning the whole table and sorting it, and every foreign key has to be validated by checking each row against its parent.

If you restored from a plain SQL dump — the output of pg_dump with no format flag, replayed through psql — then all of that happened on a single connection, one statement after another, using one CPU core. On a database with a few hundred indexes across large tables, that serial index build is the entire overnight run. The other cores sat idle the whole time.

The fix: dump in a format that can be parallelised

A plain SQL dump cannot be restored in parallel — it is just a script, and a script runs top to bottom. Parallel restore needs a dump that records the object dependency graph so pg_restore can work out what is safe to run at the same time. Two formats do this: the custom format (-Fc, a single file) and the directory format (-Fd, a directory of per-table files). Both support parallel restore. The directory format is the one to reach for at scale, because pg_dump can also write it in parallel, so the backup speeds up too.

Dump with parallel workers into a directory:

# Dump in directory format with 8 parallel workers.
# -Fd is the only format pg_dump can parallelise (-j needs it).
pg_dump -Fd -j 8 -Z 3 -f /backups/appdb.dir -d appdb

# One file per table under /backups/appdb.dir, plus a table
# of contents pg_restore reads to schedule the work.

Then restore with parallel jobs. This is the line that turns nine hours into one:

# Restore with 8 concurrent sessions.
# --jobs parallelises the expensive steps: data load,
# index creation and constraint validation.
pg_restore -j 8 -d appdb /backups/appdb.dir

The PostgreSQL documentation is precise about what --jobs does: it runs the most time-consuming steps — those that load data, create indexes, or create constraints — concurrently, using up to the given number of sessions. It works only with the custom and directory formats, and the input has to be a real file or directory, not a pipe or standard input. Pick a job count near your core count, but not far past it, and remember each job is a live connection against a server also doing heavy I/O.

Foreign-key ordering is handled for you

The usual worry about parallelising a restore is ordering: if a child table’s foreign key is validated before its parent is populated, the restore fails. You do not have to solve this yourself. Because the custom and directory formats carry the dependency graph, pg_restore schedules work so that a table is loaded before the indexes and foreign keys that depend on it are built, and it will not start a step whose prerequisites have not finished. You supply the worker count; it supplies the ordering. This is exactly why a plain SQL dump cannot be parallelised — the dependency information a scheduler needs is not in the file.

Tune the session for a load, not for production

Parallelism removes the single-core bottleneck. Two settings then make each index build itself faster, and they are safe precisely because a restore is not a live production workload — there is no data you cannot recreate by re-running the restore.

Raise maintenance_work_mem. This is the memory a single index build or constraint validation may use for sorting. The default is 64MB, which forces large sorts to spill to disk. Give it room and the sorts stay in memory. Note that each parallel job uses up to this amount, so budget against total RAM, not per core.

Turn off synchronous_commit. During the load you do not need every transaction flushed to disk before the next proceeds — if the machine crashes mid-restore you start the restore again. Disabling it removes a per-transaction fsync wait that serial index and constraint creation hits repeatedly.

Set these on the target before you restore, then put them back:

-- Run against the target database before restoring.
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET synchronous_commit = 'off';
SELECT pg_reload_conf();

-- ... run pg_restore -j 8 here ...

-- Restore the production defaults afterwards.
ALTER SYSTEM RESET maintenance_work_mem;
ALTER SYSTEM RESET synchronous_commit;
SELECT pg_reload_conf();

The 2GB figure is illustrative — size it to the RAM you have and the number of parallel jobs, not by copying a number off a blog. And these are load-time settings only. Leaving synchronous_commit off on a production database trades durability for speed, which is the wrong trade the moment real writes are landing.

Confirm the wall-clock drop, then make it evidence

Do not take the improvement on trust. Time the run — time pg_restore … — and record the wall-clock figure before and after. The gap is usually not subtle: a serial overnight restore commonly collapses to a fraction of its former length once the index builds fan out across cores and stop spilling to disk.

That measured number is the point of the exercise. A backup you have never restored under timing is an assumption, not a control, and recovery time is one of the metrics a serious operation actually measures rather than asserts. The same discipline that makes a retried operation safe to repeat applies here: prove the recovery path works, on the clock, before the day you need it. A restore you can complete inside your recovery-time objective is a control. One that runs all night is a liability wearing a green backup tick.