Thread: For temporary tables; truncate vs on commit delete all

Does anyone have any idea which is generally better (by better I mean
most efficient/quickest!) in this simple scenario? I have a temporary
table which I wish to retain for the duration of a long-running
session. However, for each transaction it must be purged. So, is it
better to;

a) Always CREATE TEMPORARY TABLE + ON COMMIT DROP for every transaction or;
b) TRUNCATE TABLE or;
c) ON COMMIT DELETE ROWS

Both b & c avoid re-creating the table each time but at the cost of an
explicit purge of some form.
I would assume that despite its name, the latter, example c, is more
akin to a truncation? Or does it actually perform a deletion?

Cheers

Jim

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

On Wed, 2023-03-22 at 11:59 +0000, Jim Vanns wrote:
> Does anyone have any idea which is generally better (by better I mean
> most efficient/quickest!) in this simple scenario? I have a temporary
> table which I wish to retain for the duration of a long-running
> session. However, for each transaction it must be purged. So, is it
> better to;
>
> a) Always CREATE TEMPORARY TABLE + ON COMMIT DROP for every transaction or;
> b) TRUNCATE TABLE or;
> c) ON COMMIT DELETE ROWS
>
> Both b & c avoid re-creating the table each time but at the cost of an
> explicit purge of some form.
> I would assume that despite its name, the latter, example c, is more
> akin to a truncation? Or does it actually perform a deletion?

b) or c) are best.
Yes, ON COMMIT DELETE ROWS will truncate the temporary table.

Creating too many temporary tables in a short time can lead to bloat
in "pg_attribute".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com