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
