Skip to main content

3 posts tagged with "Postgres.FM"

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

UUIDv4 vs UUIDv7: what a random primary key costs in Postgres

· 20 min read
Nikolay Samokhvalov
CEO & Founder

Postgres 18 ships uuidv7()[1], which generates UUIDv7 as defined in RFC 9562[2]. Yet many tables still have id uuid default gen_random_uuid(), which is UUIDv4: 122 random bits. Does a random primary key matter?

Barely, while the index fits in memory. Once it outgrows memory, a lot: in our runs at 100M rows, UUIDv4 inserted at 0.62 to 0.69 of the rate of an ordered key on the same server, and its index crowded the table out of shared_buffers.

WAL is a separate cost, driven less by memory than by how many index leaves each checkpoint cycle touches. At 90M to 100M rows, UUIDv4 wrote 1.5 to 1.9 times the WAL per row, while UUIDv7 kept up with int8.

0.62–0.69×
UUIDv4 insert rate
vs an ordered key on the same server, at 100M rows
1.5–1.9×
UUIDv4 WAL per row
vs an ordered key on the same server, at 90M–100M rows
+33%
UUIDv4 primary key size
vs UUIDv7, at 100M rows

I asked DBLab Forge[3] to measure it: three tables with the same schema and the same insert stream, differing only in the primary key (UUIDv4, UUIDv7 or int8), seven runs on cloud servers, and every hypothesis written down with a falsifier before the first row. This post is the short version; the full brief[4] has every number with its source.

Kirk Wolak and I talk about it in Postgres.FM episode 194, "UUIDv4 vs UUIDv7"[5] (transcript included). The video shows the charts as we talk.

Links

  1. [1] uuidv7() https://www.postgresql.org/docs/18/functions-uuid.html
  2. [2] RFC 9562 https://www.rfc-editor.org/rfc/rfc9562#section-5.7
  3. [3] DBLab Forge https://postgres.ai/products/dblab-forge
  4. [4] full brief https://console.postgres.ai/shared/brief/9fffe2da-00c1-470b-a79d-a210ae2306bc
  5. [5] Postgres.FM episode 194, "UUIDv4 vs UUIDv7" https://postgres.fm/episodes/uuidv4-vs-uuidv7

Dump/restore done. Now what?

· 15 min read
Nikolay Samokhvalov
CEO & Founder

Your pg_restore finished without errors. The data is there. The indexes are in place. Is it time to switch traffic? A successful restore can still leave you with painfully slow queries—and the first read can do more than just read. What is missing from the migration runbook?

Kirk Wolak and I explain why in Postgres.FM episode 193, “Dump/restore done. Now what?”[1] (transcript included).

Links

  1. [1] Postgres.FM episode 193, “Dump/restore done. Now what?” https://postgres.fm/episodes/dump-restore-done-now-what