Re: pg_dump additional options for performance

Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > I've not been advocating improving pg_restore, which is where the -Fc
> > tricks come in.
> > ...
> > I see you thought I meant pg_restore. I don't thinking extending
> > pg_restore in that way is of sufficiently generic use to make it
> > worthwhile. Extending psql would be worth it, since not all psql scripts
> > come from pg_dump.
>
> OK, the reason I didn't grasp what you are proposing is that it's insane.
>
> We can easily, and backwards-compatibly, improve pg_restore to do
> concurrent restores. Trying to make psql do something like this will
> require a complete rewrite, and there is no prospect that it will work
> for any input that didn't come from (an updated version of) pg_dump
> anyway. Furthermore you will have to write a whole bunch of new code
> just to duplicate what pg_dump/pg_restore already do, ie store/retrieve
> the TOC and dependency info in a program-readable fashion.
>
> Since the performance advantages are still somewhat hypothetical,
> I think we should reach for the low-hanging fruit first. If concurrent
> pg_restore really does prove to be the best thing since sliced bread,
> *then* would be the time to start thinking about whether it's possible
> to do the same thing in less-constrained scenarios.

Added to TODO based on this discussion:

o Allow pg_dump to utilize multiple CPUs and I/O channels by dumping
multiple objects simultaneously

The difficulty with this is getting multiple dump processes to
produce a single dump output file.
http://archives.postgresql.org/pgsql-hackers/2008-02/msg00205.php

o Allow pg_restore to utilize multiple CPUs and I/O channels by
restoring multiple objects simultaneously

This might require a pg_restore flag to indicate how many
simultaneous operations should be performed. Only pg_dump's
-Fc format has the necessary dependency information.

o To better utilize resources, restore data, primary keys, and
indexes for a single table before restoring the next table

Hopefully this will allow the CPU-I/O load to be more uniform
for simultaneous restores. The idea is to start data restores
for several objects, and once the first object is done, to move
on to its primary keys and indexes. Over time, simultaneous
data loads and index builds will be running.

o To better utilize resources, allow pg_restore to check foreign
keys simultaneously, where possible
o Allow pg_restore to create all indexes of a table
concurrently, via a single heap scan

This requires a pg_dump -Fc file because that format contains
the required dependency information.
http://archives.postgresql.org/pgsql-general/2007-05/msg01274.php

o Allow pg_restore to load different parts of the COPY data
simultaneously

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +