Re: increasingly slow insert/copy performance

Todd Underwood <todd(at)renesys(dot)com> writes:
> $dbh->do("LOCK TABLE $table IN SHARE UPDATE EXCLUSIVE MODE")
> or die "Cannot lock $table...\n";

> $dbh->do("ALTER TABLE $table DROP CONSTRAINT ${table}_pkey")
> or die "Cannot drop constraint ${table}_pkey...\n";

> $dbh->do("DELETE FROM $table WHERE one>0")
> or die "Cannot empty $table...\n";

> $dbh->do("COPY $table (one,two,three,four,five) FROM stdin")
> or die "Cannot COPY to DB ...\n";

The problem with this is that the DELETE doesn't physically remove the
old rows, therefore you are going to be steadily bloating the table.

Can you use a TRUNCATE instead? (Not if you need to keep some rows,
obviously, but it's not clear whether your WHERE is actually useful.)

Can you commit the deletion and then VACUUM before adding the new
rows? (Probably not, if you need other transactions to see a valid
table all the time --- though if you do, I wonder why you are committing
before you add back the index.)

If not, probably the best best is to issue a VACUUM just after
committing each of these cycles. That will at least hold the table
size to twice the theoretical minimum.

BTW, the above is deadlock-prone because ALTER TABLE will take ACCESS
EXCLUSIVE lock; taking a lesser lock earlier in the transaction is
not only useless but counterproductive.

regards, tom lane