The hidden costs of CREATE TEMP TABLE: catalog bloat and LWLock:SInvalWrite


For years I warned against creating temporary tables in hot code because of catalog bloat. This time I measured it. The bloat was real, and it was only one of the costs.
What does CREATE TEMP TABLE cost if your code runs it in every transaction? On Postgres 18, two things:
- Catalog rows and files, every time. A temp table per transaction ran at 3783 TPS, one that each session keeps at 17866, and a CTE doing the same work at 86997 (16 vCPU, no idle sessions). Creating and dropping the table explains the 4.7x step from 17866 to 3783.
- The shared invalidation queue, when there are many sessions. With 5000 idle sessions and a 500 TPS target, temp-table DDL got 127 TPS in one run and 249 in another.
We measured it with DBLab Forge[1], a system that runs full-scale synthetic database experiments to study Postgres behavior and anomalies. The real case behind it is from Kirk Wolak[2], whose team moved a busy search system from Oracle and kept temp tables in its most frequent query.
Every number here comes from the DBLab Forge study brief[3]. Kirk and I discuss it in Postgres.FM episode 196[4].
Links
- [1] DBLab Forge https://postgres.ai/products/dblab-forge
- [2] Kirk Wolak https://www.linkedin.com/in/kirk-wolak-7706737/
- [3] DBLab Forge study brief https://console.postgres.ai/shared/brief/dfe99713-f5a8-483b-9aab-1606a883b75d
- [4] Postgres.FM episode 196 https://postgres.fm/episodes/the-hidden-costs-of-create-temp-table


