How long does a Postgres restore actually take?

When someone asks how long it would take to get the database back, the answer is usually a guess based on the size of the dump. I wanted a real number, so we timed it: one schema at four sizes, from a 400 MB Supabase project up to a 10 GB database with 18.8 million rows, each dump restored into an empty PostgreSQL 17 on a single 2 vCPU / 8 GB VM.

The 10 GB restore took about 12 minutes. Most of those 12 minutes went to rebuilding indexes and foreign keys. Loading the rows was the smaller part, and that changes how you should estimate your own restore.

The numbers

Plain pg_restore with one job, into a fresh PostgreSQL 17 container:

Sizes are pg_database_size of the source. The schema is modeled on a multi-tenant SaaS app with an AI feature: orgs, users, projects, orders, an events table with JSONB payloads, a documents table with 384-dimension pgvector embeddings, 16 indexes, 7 RLS policies, triggers and functions. The three bigger databases are synthetic copies of that schema, and most of their size is event rows.

I also restored every dump twice through the restore drill of BackupDrill, the backup tool I work on. At every size, the restore step of those drills averaged within 5% of the single-job time above. That comparison is the restore alone; a full drill also downloads the dump, starts the database and checks every table.

Two things to keep in mind before you quote any of this:

1. The target ran with durability turned off: fsync, synchronous_commit and full_page_writes off, wal_level=minimal, shared_buffers at 1.9 GB and maintenance_work_mem at 960 MB. That's fine for a test copy you delete afterwards. It's not how you'd run a database you're recovering production into. In a separate run earlier the same day, the 1 GB restore took 61 seconds with these settings and 110 seconds on stock PostgreSQL settings, about 1.8x slower.
2. There's no download time in here. The dump sat in a bucket on the same VM; the 1.74 GiB dump came down in 15 seconds. If your backup lives in another region, add however long it takes to move the file.

Where the 12 minutes went

The 10 GB restore, one job, timed step by step from pg_restore --verbose:

Loading the biggest table, 13.5 million rows, took 141 seconds. One HNSW index over 200,000 vectors took longer than that on its own. Averaged over the two drill restores of this database, 77% of the restore (559 of 722 seconds) went to indexes, constraints and foreign keys, and 163 seconds to loading all 18.8 million rows.

That follows from what a custom-format dump contains. It has the rows and the definition of each index, not the index itself. pg_database_size counts index pages; the dump doesn't carry any. Across all four databases the compressed dump came out at 17–22% of the database size, and every index missing from the file gets built from scratch during the restore. Foreign keys get checked again against every row.

Estimate from your schema, not your dump size

Two databases with the same dump size can restore at very different speeds. One with a few large tables and a primary key on each is mostly COPY. One with GIN indexes on JSONB, trigram indexes, a vector index and a dozen foreign keys spends most of its restore in CREATE INDEX and ALTER TABLE.

You can see how much rebuilding is waiting in a dump before you restore anything:

pg_restore --list app.dump | grep -cE ' INDEX | CONSTRAINT | FK CONSTRAINT ' 

A count won't give you minutes, though. For that, time a restore of your own dump in a throwaway container. This one uses the same kind of tuning as our test target:

docker run -d --name restore-test -e POSTGRES_PASSWORD=pw \ -p 127.0.0.1:5433:5432 --shm-size=1g postgres:17 \ -c fsync=off -c synchronous_commit=off -c full_page_writes=off \ -c wal_level=minimal -c max_wal_senders=0 \ -c shared_buffers=2GB -c maintenance_work_mem=1GB 

Scale shared_buffers and maintenance_work_mem down on a machine with less memory. --shm-size matters because parallel index builds, HNSW builds included, use shared memory, and Docker's default 64 MB can be too small for them. wal_level=minimal only starts with max_wal_senders=0. If your tables have vector columns, swap postgres:17 for an image that ships pgvector, such as pgvector/pgvector:pg17, which is what our test used. Our target also had the extensions the schema needed (pg_trgm, pgvector) and Supabase's auth stubs created before the restore.

Then restore with a timestamp on every line of verbose output:

PGPASSWORD=pw pg_restore --verbose --no-owner --no-privileges \ -h 127.0.0.1 -p 5433 -U postgres -d postgres app.dump 2>&1 \ | perl -MTime::HiRes=time -ne 'printf "%.1f %s", time, $_' > restore.log 

And list the slowest steps:

awk 'NR > 1 { printf "%6.1fs %s\n", $1 - prev_time, prev_line } { prev_time = $1; $1 = ""; prev_line = $0 }' restore.log | sort -rn | head 

With a single job, the gap between one line and the next is roughly how long the step on the first line took, so the top of that list is where your restore time goes. Use a pg_restore of the same major version as the pg_dump that made the file, or newer. If the dump came from Supabase and the restore complains about the auth schema, I wrote up the stubs a plain Postgres target needs a while ago.

What these numbers don't cover

• More cores. Everything here ran on 2 vCPU. pg_restore --jobs can build several indexes at the same time, so a bigger machine can beat these numbers. We didn't test one.
• Restoring into a new Supabase project. Speed then depends on the project's compute size and your connection to it.
• Supabase's own restore from daily backups or point-in-time recovery. The Free plan has neither, so we couldn't time it.
• Other schemas. More indexes per gigabyte, bigger vector indexes or thousands of small tables will spend the time differently.

Taking the backup was the quick part, at least for the small database: 25 seconds to dump the 400 MB Supabase Free project through the Session pooler from a VM in the same region (Ashburn to us-east-1), for a 75 MB file.

The full result tables, the drill timings, the test setup and the list of what we didn't test are on the benchmark page.

I work on BackupDrill, which runs scheduled backups and restore drills for Supabase projects. The drill timings above come from its open-source CLI; the plain pg_restore numbers don't depend on it.