Re: pg_dump additional options for performance

On Wed, 2008-02-06 at 15:13 +0000, Simon Riggs wrote:
> The -s option creates the table, as well as creating constraints and
> indexes. These objects need to be dropped prior to loading, if we are to
> follow the performance recommendations in the docs. But the only way to
> do that is to manually edit the script to produce a cut down script.
>
> So it would be good if we could dump objects in 3 groups
> 1. all commands required to re-create table
> 2. data
> 3. all commands required to complete table after data load
>
> My proposal is to provide two additional modes:
> --schema-pre-load corresponding to (1) above
> --schema-post-load corresponding to (3) above

Another thought:

We could also break step #3 into two steps: those objects required for
correctness (e.g. unique indexes, other constraints, etc); and those
objects that are merely for performance (i.e. non-constraining indexes).
Let's call these steps #3A and #3B.

After reading the thread here:

http://archives.postgresql.org/pgsql-performance/2008-02/msg00211.php

it's clear that building indexes can take a long time, and they aren't
strictly required for correct database operation. Often, it's perfectly
reasonable to operate the database without a few of the indexes, so long
as they don't imply a constraint.

Step #3B could be done with "CONCURRENTLY" to allow uninterrupted
operation.

We could even allow pg_restore to run step #3B in multiple sessions
building different indexes to use multiple processor cores.

This is just a thought. I can see that it's getting fairly complex, and
it may be better to just leave these things up to the DBA.

Regards,
Jeff Davis