Postgres FM

The hidden costs of CREATE TEMP TABLE

Share this episode
Anyone with this link can open it.
Share Link
Start at 0:00

This episode isn’t summarized yet

Create a free account and summarize it. In a few minutes you’ll have the key takeaways and a section-by-section summary.

Show notes

Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8

 

Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and catalog bloat, and the shared invalidation queue that every session has to read. They cover why thousands of idle sessions make it much worse, what it looks like in wait events, and what to use instead.
 
Here are some links to things they mentioned: 
Times are podcast times of the first mention.


Main link


Michael


Episodes


Postgres docs


Postgres source and history


Tools


Other


Corrections (checked against the Postgres source after recording)

  • [00:13:58] Two temp tables and an index are not "three records in, three out". Each temp table adds at least 13 catalog rows (pg_class, pg_type, pg_attribute, pg_depend), about 31 if it has a text column, and the index adds more. So 10M queries a day means at least 600 million catalog row changes, not 60M.
  • [00:15:47] sinval means shared invalidation (of the catalog and relation caches), not "snapshot invalidation".
  • [00:18:49] LWLock:LockManager: up to Postgres 17 a session has 16 fast-path lock slots; Postgres 18 has 64 by default and scales them with max_locks_per_transaction. But temp-table DDL takes AccessExclusiveLock, which never uses the fast path, so raising the setting does not make this wait go away (the brief measured 6.6 to 12.7% LockManager with it set to 1024).
  • [00:25:09] The invalidation queue is a circular buffer of 4,096 messages, but writers never wait for readers. When it is half full (2,048 messages), the session that is furthest behind gets a catch-up signal, and it passes the signal on, one session at a time. When it is full, sessions that have not read are reset and must rebuild all their caches. Writers wait on SInvalWriteLock because there is one such lock for the whole cluster, and both a write (which flags every session) and a queue cleanup (which scans every session) hold it.
  • [00:27:26] ON COMMIT DELETE ROWS does not make dead tuples: at commit it truncates the table and its indexes. And ON COMMIT DROP is not slow mostly because of more messages: without idle sessions the cost is catalog changes and file create and unlink; sinval is under 1%.

Clarifications

  • [00:04:02] Temp table data is invisible to other sessions, but the definition and statistics are visible (pg_class, pg_stat_all_tables).
  • [00:05:32] The docs glossary defines bloat, and the definition includes dead row versions.
  • [00:14:56] Wraparound: load is not the condition. Autovacuum never freezes temp tables, so any long-lived temp table holds back datfrozenxid.
  • [00:17:05] "Catalog is global": the catalog is per database and ...
Couldn't load the audio. A blocker may be stopping the show's tracking links. Open the file