Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

Question

I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs:

pg_restore -d analytics -U postgres  -j 4 -v "D:\Program Files\PostgreSQL\10\backups\Analytics_08_2018__7_53_21.36.compressed"

I ran this from a command line. The restore took about 2 hours longer than a non-parallel restore. it seemed to hang on creating indexes at the end of the restore job

pg_restore: launching item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: creating INDEX "public.nidx_bigrams_inc_hits"
pg_restore: finished item 2965 TABLE DATA trigrams
pg_restore: launching item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: creating INDEX "public.nidx_trigrams_inc_hits"
pg_restore: finished item 2823 INDEX nidx_unigrams_inc_hits
pg_restore: finished item 2822 INDEX nidx_trigrams_inc_hits
pg_restore: finished item 2817 INDEX nidx_bigrams_inc_hits
pg_restore: finished main parallel loop

Each of these pg_restore "creating index" jobs had a status of "idle" in pg_stat_activity. Another pg_restore job was "idle" on commit.

I was hoping that the parallel restore would complete much faster than the default restore and it seemed to be doing that until it hung there after about 15 minutes. There is not much else happening on this server. The indexes seemed complete when I checked them before the job finished i.e. they were the same size as the original database indexes. Is there any particular reason why a parallel restore job would take longer to finalize/commit as opposed to the non-parallel restore? I'd want to be prepared for that in a disaster recovery situation and rule out parallel if I cannot rely on the job duration.

By the way I am aware of file system based backups as opposed to logical, but I am only interested in logical for the time being.

Answer 1

It's probably just database contention.

Doing a parallel dump, you postgres can write to many different files at once, so the load can be spread around.

Doing a parallel restore, postgres has to manage all that [heavy-weight] traffic coming in from jobs+1 connections, all filling up data pages in the buffer cache and writing them to disk, all at once. To create indexes, you need to read data pages back in and scan them; doing this for lots of tables at once puts a lot of pressure on the buffer cache ... more work for postgres.

Also, you've specified four jobs - does your database [server] have enough CPUs / cores to support that? If not then the extra jobs will still get spun up, but will suffer contention at the operating system level, as they get switched in an out of memory as the [poor] machine tries to run them all "at the same time". On smaller servers, it may be that you get [some] benefit from running with two jobs, but more than that and the benefits are rapidly outweighed by the cost.

Answer 2

Another reason this may happen is that pg_restore requires you to enter the password multiple times (once for each process). For example, if this is your command:

pg_restore --jobs=8 --clean -d example -C --no-owner --no-acl -U example -h 127.0.0.1 -W db.dump 
Password: 
Password: 
Password: Password: Password: Password: Password: Password: Password: Password: 

The final line requires you to enter your password followed by the return key eight times, or ten times in total.