Skip site navigation (1) Skip section navigation (2)

Re: pg_dump additional options for performance

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_dump additional options for performance
Date: 2008-02-19 22:18:05
Message-ID: 1203459485.3846.123.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2008-02-19 22:59:44
Subject: Re: Permanent settings
Previous:From: Magnus HaganderDate: 2008-02-19 22:03:55
Subject: Re: Permanent settings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group