Re: Linux/PostgreSQL scalability issue - problem with 8 cores

Jakub Ouhrabka <kuba(at)comgate(dot)cz> 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