> Okay, for a table of just a few entries I agree that DELETE is
> probably better. But don't forget you're going to need to have those
> tables vacuumed fairly regularly now, else they'll start to bloat.
I think we'll go with DELETE also for another reason:
Just after we figured out the cause of the spikes we started to
investigate a long-term issue we had with PostgreSQL: pg_dump of big
database was blocking some of our applications. And yes, we replaced
TRUNCATE with DELETE and everything is running as expected.
Looking at the docs now I see there is a new paragraph in 8.3 docs
mentioning that TRUNCATE is not MVCC-safe and also the blocking issue.
It's a pity that the warning wasn't there in 7.1 times :-)
Thanks,
Kuba
Tom Lane napsal(a):
Jakub Ouhrabka <kuba@xxxxxxxxxx> writes:
Huh. One transaction truncating a dozen tables? That would match the
sinval trace all right ...
It should be 4 tables - the shown log looks like there were more truncates?
Actually, counting up the entries, there are close to 2 dozen relations
apparently being truncated in the trace you showed. But that might be
only four tables at the user level, since each index on these tables
would appear separately, and you might have a toast table plus index
for each one too. If you want to dig down, the table OIDs are visible
in the trace, in the messages with type -1:
LOG: sending inval msg -1 0 30036 0 30700 3218341912
^^^^^ ^^^^^
DBOID RELOID
so you could look into pg_class to confirm what's what.
Yes, performance was the initial reason to use truncate instead of
delete many years ago. But today the truncated tables usualy contain
exactly one row - quick measurements now show that it's faster to issue
delete instead of truncate in this case.
Okay, for a table of just a few entries I agree that DELETE is probably
better. But don't forget you're going to need to have those tables
vacuumed fairly regularly now, else they'll start to bloat.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
