Hi I have to update monthly my database using this big dump (100gb) I don't have the control over how it's done I only receive a .zip with a lot of .gz i want to speed up the restoring since I only really need 1 table. I have a nvme for my disk, but it's only getting written at like 200 mb/s but it's capable of a lot more. I think this is because it's CPU bound the decompression of the .gz and I can't find a way to paralyze the decompression. the postgres version is 10.20 how can i check where is the table stored, in what file i mean.

  • Start with the docs, paying special attention to -l and -L. postgresql.org/docs/10/app-pgrestore.html
    – jjanes
    Apr 26, 2022 at 14:56
  • i already use that i want to see where the .gz is stored so that i can extract with multiple threads.
    – awaska
    Apr 26, 2022 at 15:08

1 Answer 1


It is in a file whose base name is given by the first number in the relevant line from pg_restore -l

So for example:

4292; 0 17010 TABLE DATA public pgbench_branches jjanes

the data for pgbench_branches is in dumpdir/4292.dat.gz

But note that gz decompression does not parallelize well. The dictionary is built up as it goes, so you need to have decompressed the previous tokens to know how to decompress the current one.

  • i think that i can use Pigz for the decompression
    – awaska
    Apr 26, 2022 at 17:09
  • Right, but it only has one decompression thread as I understand it. It uses other threads to do IO, so the main thread isn't sidetracked doing that. That is not nothing, but it also is not much. It is on the compression where pigz really earns its keep.
    – jjanes
    Apr 26, 2022 at 17:19
  • oh ok thanks for the info
    – awaska
    Apr 26, 2022 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.