Idle sessions are not free. We asked for 500 temp-table tx/s and got:
- 0 idle: 502
- 1,000: 502
- 3,000: 274
- 5,000: 127
At 5,000, the DDL sessions spend 91% of their active time waiting on LWLock:SInvalWrite (PG18, 16 vCPU, synthetic).
First aid: cut idle sessions.
Two different costs:
- always: 13+ catalog rows in and out per temp table, plus files created and unlinked -- that is the 23x
- with many idle sessions: ~114 invalidation messages per create + drop, one 4,096-slot queue, and every session must read them
Kirk on the second one:
CREATE TEMP TABLE in every transaction: 3,931 tx/s 🐢
Same query as a CTE: 88,531 tx/s 🐘🚀
New Postgres FM with @yugedilbertfan and @samokhvalov: why it happens, and what to use instead
With many idle sessions, DDL waits on LWLock:SInvalWrite
Ever seen this one?
thread 🧵👇
Álvaro's answer, in 65 seconds (sound on):
you ask for Postgres + citus + ltree + unaccent, and you get one image with only those.
Nothing more.
This is StackGres Containers (public beta), built on DOCIR, a dynamic OCI registry.
pgvector as its own container image: 0.31 MiB.
A fat Postgres image with 18 extensions: 253 MiB.
Both still cost the same restart to add.
New episode with Álvaro Hernández @ahachete and Kirk Wolak @yugedilbertfan on extensions for Postgres in containers.
New episode link👇
We discuss it on @PostgresFM
Why does a random UUID slow down inserts once the index outgrows memory?
Kirk Wolak explains it in rally well in 75 seconds:
UUIDv4 → each insert lands on a random leaf. Those leaves get evicted, so inserts keep reading pages back from disk.
UUIDv7 → every new key is to the right of the last one. Only the last page is touched. Same as bigint identity.
Full episode: youtu.be/gf8e3JkQ_8M
postgres.ai/blog/20261105-uu…
Our first run said int8 inserts 12% faster than UUIDv7.
Wrong. Each key ran on its own server, and the int8 server had a faster disk.
Same server, same 71M rows: 23,458 vs 23,131 rows/s. A tie.
Random UUID primary keys look fine on day one:
id uuid default gen_random_uuid() primary key
At scale, INSERTs run much slower.
Postgres 18.6, 16 clients, single-row inserts, same server:
→ small table: UUIDv4 at 0.95–1.10x of UUIDv7
→ 100M rows: 0.62–0.69x of an ordered key
→ WAL per row: 1.5–1.9x
→ index size: +33% vs UUIDv7
Each UUIDv4 insert lands on a random leaf, so the whole index is hot.
Past memory, almost every insert reads a page and evicts another one.
Postgres 18 has uuidv7(). For an existing table, one statement under a short lock_timeout:
alter table t alter column id set default uuidv7();
7 runs, 14 hypotheses written down before the first row. Link below 👇
Two UUID traps from our tests on Postgres 18:
1. order by id desc limit 100 on a UUIDv4 table
→ returns the 100 largest random values, not the newest rows
2. UUIDv7 generated in the app
→ 16 clients with clocks spread over ±60 s
→ index 69% full, 45% of leaves out of order
→ about as bad as UUIDv4
Generate uuidv7() in Postgres. On 17 and older: the pg_uuidv7 extension or a SQL function as the column default.
Full post: postgres.ai/blog/20261105-uu…
.Give this a watch if you haven’t yet! This was a fun one.
Always enjoy how candid these conversations get with @samokhvalov and @michristofides. Love @PostgresFM! 🤗❤️
New episode: "Citus + StackGres"
Nik and Michael are joined by Álvaro Hernández to discuss sharding Postgres with Citus and StackGres, especially the new query router support that has been added very recently.
🎧️️️ postgres.fm/episodes/citus-s…
📺️️️ youtu.be/by4Gn3MJJOc
Nice to see our recent blog post:
Open-source Citus matches PlanetScale's Neki at 10M QPS on half the data nodes
stackgres.io/blog/open-sourc…
mentioned on Postgres Weekly news (thanks @cooperx86 )
Mentioned next to the link to @PostgresFM's episode where Citus and the benchmark were discussed: postgres.fm/episodes/citus-s… Have you listened to it?
How we tested: ~2.1B UUID rows across 7 runs, driven by AI agents in DBLab Forge, PostgresAI's experiment platform. ~1.5B tokens (mostly cached context; ~10.5M generated).
Every number, chart and config 📊
console.postgres.ai/shared/b…
It's not just inserts/s.
WAL per insert at 80–100M rows:
→ int8: 745 B
→ UUIDv7: 744 B
→ UUIDv4: 1,324 B 🐌
Postgres 18 ships uuidv7(). And yes, it has a catch. It's in the episode.