Re: How to efficiently duplicate a whole schema?

On Wed, 6 Aug 2003, Sebastien Lemieux wrote:

> On Wed, 6 Aug 2003, Tom Lane wrote:
>
> > Sebastien Lemieux <slemieux@elitra.com> writes:
> > > All the time is taken at the commit of both transaction.
> >
> > Sounds like the culprit is foreign-key checks.
> >
> > One obvious question is whether you have your foreign keys set up
> > efficiently in the first place.  As a rule, the referenced and
> > referencing columns should have identical datatypes and both should
> > be indexed.  (PG will often let you create foreign key constraints
> > that don't meet these rules ... but performance will suffer.)
>
> I've checked and all the foreign keys are setup between 'serial' (the
> primary key of the referenced table) and 'integer not null' (the foreign
> key field).  Would that be same type?  A couple of my foreign keys are not
> indexed, I'll fix that.  The latter seems to do the job, since I can now
> synchronize in about 75 seconds (compared to 30 minutes), which seems good
> enough.

Another thing might be the management of the trigger queue.  I don't think
7.3.2 had the optimization for limiting the scans of the queue when you
have lots of deferred triggers.  It looks like 7.3.4 may though.