UUIDv4 vs UUIDv7
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.
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.
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!
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)