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
