Skip to main content

2 posts tagged with "wait events"

View All Tags

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

· 21 min read
Nikolay Samokhvalov
CEO & Founder
An abstract drawing: thin lines, each one a database session, and orange marks that move along them.An abstract drawing: thin lines, each one a database session, and orange marks that move along them.

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:

  1. 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.
  2. 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. [1] DBLab Forge https://postgres.ai/products/dblab-forge
  2. [2] Kirk Wolak https://www.linkedin.com/in/kirk-wolak-7706737/
  3. [3] DBLab Forge study brief https://console.postgres.ai/shared/brief/dfe99713-f5a8-483b-9aab-1606a883b75d
  4. [4] Postgres.FM episode 196 https://postgres.fm/episodes/the-hidden-costs-of-create-temp-table

What your Postgres is waiting for, and why the dashboard can't tell you

· 12 min read
Dmitry Fomin
Sr. Postgres engineer

A checkout request was slow. The dashboard said the time went to the commit path and to disk reads. The team tried the two fixes that follow from that, and the slowest requests stayed about as slow. The cause was a lock that the dashboard ranked fifth, at 2% of the time. This post explains why a wait dashboard can be right about the time and wrong about the problem, using only what Postgres exposes today. It ends where the next post, about a tool that finds such causes, begins.