UUIDv4 vs UUIDv7

Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M

Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really costs compared with a time-ordered UUIDv7 or an int8 key on Postgres 18 — index size, WAL and full-page writes, insert rate once the index outgrows memory — and what DBLab Forge found when it ran the experiment.
 
Here are some links to things they mentioned: 
Times are podcast times of the first mention.

Main link

• [00:00:34] UUIDv4 vs UUIDv7 vs int8 primary keys on Postgres 18 (DBLab Forge brief, PostgresAI) https://console.postgres.ai/shared/brief/9fffe2da-00c1-470b-a79d-a210ae2306bc

Hacking sessions

• [00:00:51] Hacking Postgres 101: ULID function (Postgres.TV, 2023) https://www.youtube.com/watch?v=YPq_hiOE-N8
• [00:00:51] Hacking Postgres sessions with Andrey, Kirk and Nik (YouTube playlist, PostgresTV) https://www.youtube.com/playlist?list=PLH8y1BNPAKjKDdJA7sDmFWUkbbYux3TRz

Episodes

• [00:05:53] Our previous episode with Kirk, also built on DBLab Forge: Dump/restore done. Now what? https://postgres.fm/episodes/dump-restore-done-now-what

Postgres

• [00:01:34] Commit adding uuidv7() to Postgres 18 (78c5e141e9c1, 2024-12-11) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=78c5e141e9c1
• [00:04:16] UUID functions, uuidv7() and uuid_extract_timestamp() (docs) https://www.postgresql.org/docs/18/functions-uuid.html
• [00:20:42] full_page_writes (docs) https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
• [00:25:01] wal_compression, lz4 and zstd (docs) https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSION
• [00:31:07] pg_stat_io (docs) https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW

Standards and ID schemes

• [00:01:20] RFC 9562, Universally Unique IDentifiers (UUIDv7) https://www.rfc-editor.org/rfc/rfc9562
• [00:28:05] ULID spec https://github.com/ulid/spec
• [00:41:29] Snowflake ID (Twitter) https://github.com/twitter-archive/snowflake
• [00:41:29] Sharding & IDs at Instagram https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

Articles, books and tools

• [00:05:34] Sequential UUID generators (Tomas Vondra, 2018) https://www.enterprisedb.com/blog/sequential-uuid-generators
• [00:05:59] Systems Performance, 2nd edition (Brendan Gregg) https://www.brendangregg.com/systems-performance-2nd-edition-book.html
• [00:12:25] fio https://github.com/axboe/fio
• [00:26:17] Amazon Aurora pricing (I/O charges) https://aws.amazon.com/rds/aurora/pricing/
• [00:32:53] UUIDv7 doesn't have the properties you think it does (Robert Alexander, 2026) https://alexsci.com/blog/uuidv7-is-weird/
• [00:32:53] Hacker News discussion of that article https://news.ycombinator.com/item?id=50211746

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of PostgresAI

With credit to:

• Jessie Draws for the elephant artwork
• Intro and outro music: our own arrangement of Apple Loops from GarageBand (royalty-free under the GarageBand licence)