Postgres FM | The hidden costs of CREATE TEMP TABLE

November 13, 2026 • 39 Minutes

The hidden costs of CREATE TEMP TABLE

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.

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

Main link

[00:34:15] Temp tables in the request path: where the cliff is, and what LWLock:SInvalWrite really tells you (DBLab Forge brief, PostgresAI) https://console.postgres.ai/shared/brief/dfe99713-f5a8-483b-9aab-1606a883b75d

Michael

[00:00:44] Michael's post about taking a few months off (X) https://x.com/michristofides/status/2117283823314674021

Episodes

[00:06:12] UUIDv4 vs UUIDv7 https://postgres.fm/episodes/uuidv4-vs-uuidv7

Postgres docs

[00:01:32] CREATE TABLE, Compatibility: temporary tables vs the SQL standard's global temporary tables (docs) https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY
[00:04:34] CREATE TABLE, TEMPORARY: autovacuum cannot vacuum or analyze temporary tables (docs) https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-TEMPORARY
[00:15:40] LWLock wait events, including SInvalWrite and SInvalRead (docs) https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE
[00:19:16] max_locks_per_transaction (docs) https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION
[00:27:14] CREATE TABLE, ON COMMIT: PRESERVE ROWS, DELETE ROWS, DROP (docs) https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-ON-COMMIT
[00:32:24] CREATE TABLE, UNLOGGED (docs) https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED
[00:37:45] DISCARD (docs) https://www.postgresql.org/docs/current/sql-discard.html

Postgres source and history

[00:18:11] sinvaladt.c: MAXNUMMESSAGES, the 4,096-message invalidation queue https://github.com/postgres/postgres/blob/3d342deeb433060dbea02fdd276656a5563275d5/src/backend/storage/ipc/sinvaladt.c
[00:18:49] Increase the number of fast-path lock slots (Tomas Vondra, 2024, Postgres 18) https://github.com/postgres/postgres/commit/c4d5cb71d229095a39fda1121a75ee40e6069a2a
[00:23:38] Postgres 14 release notes: better scalability with many connections (Andres Freund's work) https://www.postgresql.org/docs/release/14.0/
[00:35:43] Rewrite the sinval messaging mechanism to reduce contention (Tom Lane, 2008) https://github.com/postgres/postgres/commit/fad153ec45299bd4d4f29dec8d9e04e2f1c08148

Tools

[00:13:10] pg_repack https://github.com/reorg/pg_repack
[00:20:24] PgBouncer, server_reset_query (default DISCARD ALL) https://www.pgbouncer.org/config.html#server_reset_query
[00:24:06] Amazon RDS Performance Insights https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html
[00:24:12] pg_ash, active session history for Postgres https://github.com/NikolayS/pg_ash

Other

[00:24:39] Linux Performance Tools, talk (Brendan Gregg) https://youtu.be/FJW8nGV4jxY
[00:36:41] Don't Do This: Don't create temporary tables in frequently executed code (PostgreSQL wiki) https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_create_temporary_tables_in_frequently_executed_code

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 visible to all its sessions; the invalidation queue is one per cluster.
[00:17:48] Global temporary tables: the old patch stalled in 2022, but a new one by Dean Rasheed is in review for Postgres 20.
[00:18:40] 16 vCPU (8 cores with SMT), not 16 cores.
[00:19:39] "CPU is low" refers to the share of active-session samples; the host CPU was busy, and in another run (1,000 idle sessions, 100 DDL tx/s) idle sessions spent 1.4 to 2 vCPU on catch-up.
[00:23:38] Andres Freund's work on many idle connections is in Postgres 14.
[00:24:06] Samplers that see only active sessions (like Performance Insights, now CloudWatch Database Insights) miss the idle sessions that wait during catch-up.
[00:26:50] The numbers: 3,931 tx/s (temp table per transaction), 17,741 tx/s (reused temp table), 88,531 tx/s (CTE). So the CTE is about 5x the reused temp table [00:28:15].
[00:30:27] select t1 from t1 returns the table's composite row type t1, and the array is t1[].
[00:35:20] What is emitted is invalidation messages; SInvalWrite is the lock writers wait on.
[00:35:43] The sinval design and the 4,096 constant are from Tom Lane's 2008 rewrite; it was changed later too (2011, 2015, 2024, 2026).
[00:37:55] PgBouncer: DISCARD ALL (server_reset_query) runs when a client disconnects in session mode; in transaction mode no reset query runs by default.

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of PostgresAI

With credit to:

Jessie Draws for the elephant artwork
Intro and outro music: our own arrangement of Apple Loops from GarageBand (royalty-free under the GarageBand licence)

The hidden costs of CREATE TEMP TABLE

Some kind things our listeners have said