Re: How to efficiently duplicate a whole schema?

Sebastien Lemieux <slemieux(at)elitra(dot)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.)

Also, what procedure are you using to delete all the old data? What
I'd recommend is
ANALYZE table;
TRUNCATE table;
INSERT new data;
The idea here is to make sure that the planner's statistics reflect the
"full" state of the table, not the "empty" state. Otherwise it may pick
plans for the foreign key checks that are optimized for small tables.

regards, tom lane