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