English-language podcasts

Postgres FM

The hidden costs of CREATE TEMP TABLE

39 min

Listen through the publisher, then discuss the episode here or arrange an in-person Roundtable. Your city determines where to meet.

Roundtables are designed for groups of four, with a shared subject and room for everyone to speak. Come for discussion, with no dating expectation.

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 [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 (https://www.postgresql.org/docs/current/sql-createtable.html) [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 (https://www.postgresql.org/docs/current/sql-createtable.html) [00:15:40] LWLock wait events, including SInvalWrite and SInvalRead (docs) https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE (https://www.postgresql.org/docs/current/monitoring-stats.html) [00:19:16] max_locks_per_transaction (docs) https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION (https://www.postgresql.org/docs/current/runtime-config-locks.html) [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 (https://www.postgresql.org/docs/current/sql-createtable.html) [00:32:24] CREATE TABLE, UNLOGGED (docs) https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-UNLOGGED (https://www.postgresql.org/docs/current/sql-createtable.html) [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 (https://www.pgbouncer.org/config.html) [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 (https://wiki.postgresql.org/wiki/Don%27t_Do_This) 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 ...

Episode information is provided by the publisher. Listening takes place on the publisher’s service.

Listen at the source ↗

Featured places to meet in Palo Alto, CA

Explore these places and arrange a conversation through ThinkMatch.

City guide for Palo Alto, CA

More from Postgres FM

Postgres FM

Postgres 19

53 min

Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in September, the features we like most, and what to check before you upgrade. We cover REPACK (CONCURRENTLY), LISTEN/NOTIFY with many listeners, autovacuum prioritization, plan advice, WAIT FOR LSN, new defaults, and how to test an upgrade now that AI makes testing cheaper. Here are some links to things we mentioned: Times are podcast times of the first mention. Main link [00:20:40] Postgres 19 for people who run it: PostgreSQL 19 RC2 vs 18.7 (DBLab Forge study brief, PostgresAI) https://console.postgres.ai/shared/brief/01a1e0e2-b826-73de-8300-9e56a1eb124c The release [00:01:55] PostgreSQL 19 release notes https://www.postgresql.org/docs/19/release-19.html [00:05:19] What LLMs do to PostgreSQL and DBAs (depesz, 2026-11-09) https://www.depesz.com/2026/11/09/what-llms-do-to-postgre

Discuss this episode

Postgres FM

Extensions on demand

69 min

Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in containers gets its extensions: a fat image with all of them, extensions downloaded at run time, or StackGres Containers, built on DOCIR (Dynamic OCI Registry), which composes an image with only the extensions you ask for, on demand. They also discuss what this means for security, pinning versions, and major upgrades. Here are some links to things they mentioned: Times are podcast times of the first mention. Main link [00:18:54] Dynamic containers for PostgreSQL: what we measured (DBLab Forge brief, PostgresAI) https://console.postgres.ai/shared/brief/c501d438-b872-4705-89a6-bdab30ea1680 Álvaro and StackGres [00:00:25] Álvaro Hernández https://postgres.fm/people/alvaro-hernandez-tortosa [00:01:16] StackGres Containers (public beta; the images come from the registry host sgcr.io,

Discuss this episode

Community Poll

Governments should remove practical barriers to voting, even when doing so costs more.

Share your view, then see how readers voted. No sign-in required. This poll is shared across stories.

Do you agree or disagree?

Choose one answer

Common Quandary

A Common Quandary to discuss

Another conversation to explore, separate from this news story.

You are vegetarian, but the leftover steak is headed for the trash. Do you eat it?

A vegetarian avoids buying meat because of animal welfare concerns. At work events, untouched meat is routinely thrown away, and eating it would neither increase demand nor cause another animal to be harmed.

Should a moral practice be judged by its consequences or its consistency?

Explore this quandary