←
Postgres FM263 posts
Postgres FM avatar

Postgres FM

@PostgresFM

A weekly podcast about all things PostgreSQL — by @michristofides and @samokhvalov. Also at fosstodon.org/@postgresfm

◷ Joined July 2022▧ 206 Photos and videos
Postgres FM avatar
Postgres FM@PostgresFM·Nov 13

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.

0:36
Postgres FM avatar
Postgres FM@PostgresFM·Nov 13

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:

0:54
Postgres FM avatar
Postgres FM@PostgresFM·Nov 13

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 🧵👇

0:28
Postgres FM avatar
Postgres FM@PostgresFM·Nov 6

Á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.

1:05
Postgres FM avatar
Postgres FM@PostgresFM·Nov 6

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👇

0:56
Nik Samokhvalov avatar
⟳ Postgres FM retweeted
Nik Samokhvalov@samokhvalov·Nov 5

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

1:14
Nik Samokhvalov avatar
⟳ Postgres FM retweeted
Nik Samokhvalov@samokhvalov·Nov 5

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.

Nik Samokhvalov avatar
⟳ Postgres FM retweeted
Nik Samokhvalov@samokhvalov·Nov 5

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 👇

0:16
Nik Samokhvalov avatar
⟳ Postgres FM retweeted
Nik Samokhvalov@samokhvalov·Nov 5

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…

Sai Srirampur avatar
⟳ Postgres FM retweeted
Sai Srirampur@saisrirampur·Oct 2

.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: "ClickHouse Managed Postgres" Nik and Michael are joined by @saisrirampur to discuss @ClickHouseDB's new managed Postgres service. 🎧️ postgres.fm/episodes/clickho… 📺️ youtu.be/jRCjB_Vy7cE

Post attachment
Álvaro Hernández avatar
⟳ Postgres FM retweeted
Álvaro Hernández@ahachete·Oct 16

I have participated in multiple podcasts. This is one of those I enjoy the most. It was fantastic, thank you for inviting me 🙏

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

Post attachment
Postgres FM avatar
Postgres FM@PostgresFM·Oct 30

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…

Postgres FM avatar
Postgres FM@PostgresFM·Oct 30

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.