I have been developer since 2 years, from noob to beginner but I have used only two databases, one in MongoDB and second is pgsql. I have used others as well like firebase and redis and all but I am talking about in wide range. And it become super exciting for me to know internals of the pgsql.
Because Postgres hasn't adopted that yet, and I'm not aware of any actual plan to adopt it? Tom Lane just did an interview where he said he doesn't see any world where they move away from dead tuples and vacuuming.
Users of software often do not blindly do, or not do, whatever the upstream open source project does.
OrioleDB is friendlier to SSDs and is de facto "the other" MVCC of Postgres. OrioleDB may be still be "beta" but that hasn't stopped operators/users.
MariaDB has some alternative MVCC, one of them is particularly outstanding.
SSD endurance at scale has on and off been a major concern for me, so I empathize with that. Others deserve to know of OrioleDB, and Postgres more broadly is probably better off for it too.
All of the problems Uber had, could have been solved back then, had they actually asked for help on the mailing list. Several of the issues they had have also been fixed/changed in Postgres itself as a reaction to that discussion.
Back in 2018, when I was at Greenplum, my team worked through The Internals of PostgreSQL together alongside Andy Pavlo’s CMU database courses. It was invaluable for anyone working on the database kernel. Today, a beginner can get that same interactive learning experience simply by chatting with AI, much like the discussions our team had back then.
Now I’m building Lavik, a new cache service for PostgreSQL. Traditional caches like Redis are DRAM-based, so they can only hold a small slice of hot data. Once your data outgrows memory, you’re exposed to cache miss storms, where a flood of requests falls through to PostgreSQL all at once. Nobody likes those. (OpenAI encounter the similar cache miss storms problem: https://openai.com/index/scaling-postgresql/)
Lavik takes a different path: it runs on NVMe SSDs, and it’s still faster than Redis running on DRAM. That’s not what most people expect. Conventional wisdom says SSD-based systems trade speed for capacity, but Lavik gives you both. You can keep TB-scale data in cache while getting 1M QPS and sub-millisecond P99 latency.
Chapter 12 (full-text search) quietly changed under this book in Postgres 18: FTS now reads its dictionaries and config through the cluster's default collation provider instead of always libc, so clusters on ICU or the new builtin PG_UNICODE_FAST can see different behavior in FTS and pg_trgm — the release notes explicitly tell you to reindex those indexes after pg_upgrade. Also in 18, LIKE with nondeterministic collations stopped being an error, which was the main reason people avoided case-insensitive ICU collations on searchable columns.
The HOT chapter is the one with the most direct practical payoff. Two things worth knowing from it: HOT only kicks in when no indexed column changes and the new tuple fits on the same page, so an index on a frequently-updated column (status, updated_at) quietly disables it for every update to that table. And lowering fillfactor (say 80-90) on update-heavy tables leaves free space on each page so HOT can actually happen. Comparing n_tup_hot_upd to n_tup_upd in pg_stat_user_tables is a cheap way to see whether you're getting HOT updates before tuning anything.
Depends on how wide your columns are and how often your records are modified. Past a certain width and I’m not sure lowering fillfactor is a good idea.
Because it probably was. Pangram say it's too short to be certain but they think 100% of the text is possibly AI.
That doesn't mean the comment is useless, though. There's a whole philosophical discussion to be had here about whether what matters is the comment or the identity of the speaker.
I have been developer since 2 years, from noob to beginner but I have used only two databases, one in MongoDB and second is pgsql. I have used others as well like firebase and redis and all but I am talking about in wide range. And it become super exciting for me to know internals of the pgsql.
Related:
The Internals of PostgreSQL - https://news.ycombinator.com/item?id=43241404 - March 2025 (40 comments)
The Internals of PostgreSQL - https://news.ycombinator.com/item?id=30372750 - Feb 2022 (19 comments)
The Internals of PostgreSQL - https://news.ycombinator.com/item?id=30086374 - Jan 2022 (39 comments)
Internals of PostgreSQL - https://news.ycombinator.com/item?id=18950460 - Jan 2019 (25 comments)
The internals of PostgreSQL - https://news.ycombinator.com/item?id=13488315 - Jan 2017 (53 comments)
The Internals of PostgreSQL - https://news.ycombinator.com/item?id=12142364 - July 2016 (1 comment)
(Reposts are fine after a year or so; links to past threads are just to satisfy extra-curious readers)
The latest Jepsen report on particledb triggered a new found interest in the SSI implementation in postgres.
If you are just skimming this doc, The HoT update path is the section I would jump to.
Understand HoT and then read the zHeap implementation notes (also of TOAST) puts a good mental model of what postgres does and what it doesn't do.
Incidentally, the 2020 Jepsen report on Postgres found a long-standing bug in the SSI implementation [0]
[0] https://news.ycombinator.com/item?id=41560556
The had a safe super intelligence bug dating back to 2011?
Wow so ahead of their time.
(And since I'm bad at sarcasm and don't wish to mislead, SSI = Serializable Snapshot Isolation in this context.)
Didn't find the new MVCC in there.
Because Postgres hasn't adopted that yet, and I'm not aware of any actual plan to adopt it? Tom Lane just did an interview where he said he doesn't see any world where they move away from dead tuples and vacuuming.
Users of software often do not blindly do, or not do, whatever the upstream open source project does.
OrioleDB is friendlier to SSDs and is de facto "the other" MVCC of Postgres. OrioleDB may be still be "beta" but that hasn't stopped operators/users.
MariaDB has some alternative MVCC, one of them is particularly outstanding.
SSD endurance at scale has on and off been a major concern for me, so I empathize with that. Others deserve to know of OrioleDB, and Postgres more broadly is probably better off for it too.
All of the problems Uber had, could have been solved back then, had they actually asked for help on the mailing list. Several of the issues they had have also been fixed/changed in Postgres itself as a reaction to that discussion.
For anyone interested, this is the interview with Tom Lane: https://www.snowflake.com/en/blog/engineering/30-years-of-po...
This article about MVCC is also quite interesting: https://boringsql.com/posts/mvcc-bad-bad/
Thanks, that MVCC article was a great read.
Back in 2018, when I was at Greenplum, my team worked through The Internals of PostgreSQL together alongside Andy Pavlo’s CMU database courses. It was invaluable for anyone working on the database kernel. Today, a beginner can get that same interactive learning experience simply by chatting with AI, much like the discussions our team had back then.
Now I’m building Lavik, a new cache service for PostgreSQL. Traditional caches like Redis are DRAM-based, so they can only hold a small slice of hot data. Once your data outgrows memory, you’re exposed to cache miss storms, where a flood of requests falls through to PostgreSQL all at once. Nobody likes those. (OpenAI encounter the similar cache miss storms problem: https://openai.com/index/scaling-postgresql/)
Lavik takes a different path: it runs on NVMe SSDs, and it’s still faster than Redis running on DRAM. That’s not what most people expect. Conventional wisdom says SSD-based systems trade speed for capacity, but Lavik gives you both. You can keep TB-scale data in cache while getting 1M QPS and sub-millisecond P99 latency.
Lavik is open source under the Apache License. GitHub: https://github.com/eloqdata/lavik Website: https://lavik.dev
Chapter 12 (full-text search) quietly changed under this book in Postgres 18: FTS now reads its dictionaries and config through the cluster's default collation provider instead of always libc, so clusters on ICU or the new builtin PG_UNICODE_FAST can see different behavior in FTS and pg_trgm — the release notes explicitly tell you to reindex those indexes after pg_upgrade. Also in 18, LIKE with nondeterministic collations stopped being an error, which was the main reason people avoided case-insensitive ICU collations on searchable columns.
The HOT chapter is the one with the most direct practical payoff. Two things worth knowing from it: HOT only kicks in when no indexed column changes and the new tuple fits on the same page, so an index on a frequently-updated column (status, updated_at) quietly disables it for every update to that table. And lowering fillfactor (say 80-90) on update-heavy tables leaves free space on each page so HOT can actually happen. Comparing n_tup_hot_upd to n_tup_upd in pg_stat_user_tables is a cheap way to see whether you're getting HOT updates before tuning anything.
I’ll be real, I use Postgres every day and I have no clue what you’re talking about
Damn. That sucks man. Do you have other pants? Do you need to go to the bathroom and fix it?
Depends on how wide your columns are and how often your records are modified. Past a certain width and I’m not sure lowering fillfactor is a good idea.
Why does this comment sound AI generated?
Because it probably was. Pangram say it's too short to be certain but they think 100% of the text is possibly AI.
That doesn't mean the comment is useless, though. There's a whole philosophical discussion to be had here about whether what matters is the comment or the identity of the speaker.