(Reposts are fine after a year or so; links to past threads are just to satisfy extra-curious readers)
weitendorf 8 hours ago [-]
Didn't see alignment padding mentioned anywhere so I'll drop it here: did you know that the order of a table's columns affects how much space a record uses?
But wait! There is the per-row overhead of ~30bytes. Your 18 byte row actually takes 54bytes of space. Well, multiples of 54 depending on how often you update/vacuum it. But don't make your rows too big either or you're TOAST
podocarp 5 hours ago [-]
This happens in most systems programming languages by default, but it's cool to know it happens in postgres too. Iirc wasn't it zig or some compiler that had some speedup or optimization just by arranging fields around? I may be wrong
weitendorf 1 hours ago [-]
TANGENT WARNING: What bothers me is that unless you are directly plumbing data on disk to the wire, there is literally no benefit to serializing data this way. (I originally realized all this when trying to add varint and byte types to postgres to make storage more efficient, and then finding it was almost impossible to actually serialize them in a way that saved space)
At runtime, if you've allocated a boolean or want to store a byte somewhere, you've either done it on the stack (it's temporary and you know it will need to be accessed at least once as an independent unit of data, and packing it together with other small data structures will make you use an extra instruction to extract it into its own register anyway, so there is not really any benefit to packing them tighter) or heap (you might actually gain something by reducing memory usage here, but packing data together binds the lifecycle and structure of everything at the same address and either requires additional lookups to check the interior structure, or a fancy compiler/build process to bake in the instruction to extract packed data without knowing what other data is packed with it).
When you read serialized data for a record from the disk, while running a typed database, the lifecycle/structure within the record is already shared. Most times you load the record you probably won't be accessing any particular packed data structure (eg if you have 5 bool columns in a table, most queries won't use all or any of them). You already have a global way to handle internal types/optimized reads, because that's what databases do.
So it would actually be feasible to load packed data and efficiently access or calculate with it eg XOR against 0x00..<single bit flipped to match bool index in word>00 to use a bool, or bit shift to load a subword into a register. It would be at worst 1 instruction per read slower if implemented efficiently (the query planner can handle the type lookup/deciding how to do the optimized read, which amortizes and isn't the query bottleneck or even on the critical path).
BUT denser data = more cache coherency and higher read throughput (more rows/s). And if you're doing fancy bit operations and arithmetic to extract and perform calculations on the packed data, it might actually be easier to batch operations with SIMD. So you could get arguably higher performance + much less passive disk usage (saving actual money) by packing data better probably. I'm about 30% of the way towards convincing myself to try this
gopalv 21 hours ago [-]
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.
refset 20 hours ago [-]
Incidentally, the 2020 Jepsen report on Postgres found a long-standing bug in the SSI implementation [0]
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.)
jon128 17 hours ago [-]
Didn't find the new MVCC in there.
https://www.orioledb.com/
amw-zero 16 hours ago [-]
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.
The MVCC article is good but feels like it's trying too hard to be a "on one hand, on the other hand" kind of analysis. Postgres is the only database that uses its design because, as the article admits at the end, it has "operationally loud failure modes".
Compare Postgres and Oracle (block undo based) for what happens when the database is underprovisioned:
• Postgres: bottlenecks on CPU. The database falls behind on vacuuming and dies.
• Oracle: bottlenecks on free disk space. Individual long running statements, SNAPSHOT and READ ONLY transactions get cancelled with a "snapshot too old" error.
In Oracle the database continues running and the blast radius is limited to a single statement. Because it's the longest running statements that die first, that almost certainly means an asynchronous business report and not live user traffic - which is exactly what you'd hope for. Most businesses can tolerate a missing day in a report much better than customer-facing outages, and fixing it is just a single SQL statement if you have free disk space (adding more storage is easy due to the SAN).
That's much preferable! How many post mortems do we see on HN from some startup bemoaning a multi-hour outage caused by Postgres' MVCC choices? Quite a lot. Now think about how often do you recall reading a post mortem of an outage at some big bank, hospital, shipping port etc that traces back to Oracle's MVCC engine? I don't think I ever read one. The startup world likes to make fun of Big Enterprise, but they have a much better track record of database uptime because they stay away from vacuum-based MVCC.
Disclosure: I have a part time role in the Oracle DB group. Unapproved opinions may be found above, caveat emptor.
senderista 3 hours ago [-]
The Postgres approach to MVCC ("append-only storage" in the jargon of https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...) is mandatory if you actually return object addresses to the client and thus need pointer stability (I've implemented such a DB). However, Postgres doesn't actually have this requirement so it makes little sense in that context. The beauty of undo log-based MVCC is that all versions superseded by a committed transaction are freed at the same time and from the same contiguous region of memory or disk, which makes it both memory- and SSD-friendly ("grouping by death time" in the jargon of https://research.cs.wisc.edu/wind/Publications/nvmw18-he.pdf).
xyzzy_plugh 2 hours ago [-]
Yes. This is the thing that tripped me the most when learning postgres internals. It was obvious that append-only enabled stable addressing but then it just never materializes. I was incredibly perplexed. Surely I'm missing something right? Nope!
senderista 2 hours ago [-]
Postgres folks will need to correct me but I believe the append-only MVCC design originated from the original "time-travel" requirement (which IIRC was soon dropped). There are better approaches to time-travel anyway such as time-split B-trees (https://www.microsoft.com/en-us/research/wp-content/uploads/...).
xyzzy_plugh 2 hours ago [-]
Yes and that would make sense, and what I would expect, if it were available. I understand it is load-bearing but it is nonetheless surprising that it hasn't been revisited. It is surprising, at least to me, that time-travel never shipped given its feasibility.
matharmin 12 hours ago [-]
Thanks, that MVCC article was a great read.
guenthert 9 hours ago [-]
Yeah, I love those time-travellers' blogs. Wished only he'd tell us about next years' market movements instead of issues in Oracle's DB.
akoboldfrying 10 hours ago [-]
The boringsql article was very informative, if you can get past some flourishes typical of a coauthor we all know:
> That's the honest shape of the mitigation: HOT is opportunistic, arrives in steady state rather than on demand, and quietly costs you 30% of your table size in reserved space.
senderista 3 hours ago [-]
Claude, please remove all Claudisms from this article.
ksec 11 hours ago [-]
Damn this is sad. I just wish mySQL development isn't stalled as it is.
jon128 15 hours ago [-]
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.
Sesse__ 3 hours ago [-]
It's also hard to even claim they migrated from “PostgreSQL” to “MySQL”; they surely run MySQL underneath in their new system, but it's a really weird hybrid where it's basically only the storage engine. Even secondary indexes, on of their main points of contention in the article, is managed as a separate system on top of the database.
jon128 28 minutes ago [-]
Please elaborate on details if you can.
What Uber posted there did prompt me to investigate the topic of Postgres old MVCC, OrioleDB, and MariaDB's alternative MVCC.
Ultimately, one change in a row results in the entire row being written again, from what I read. Is this not accurate?
MariaDB's MVCC doesn't write the entire row again, only the delta. One of the newer more advanced MVCC for MariaDB are even more efficient.
In my view, "runaway vacuum" has never not been a case of either poorly done IT operations or poorly thought out (or just not thought out) database usage patterns by the in-house application developers. While "throw hardware at the problem" isn't a great answer, I have never not seen NVDIMM for ZFS ZiL/SLOG not mitigate "runaway vacuum", as ugly as that is -- that was a long time ago though.
SSD endurance can get daunting in an advanced hosting environment. It's odd that I don't often see others have these metrics in their Prometheus+Grafana. The people who are telling me that OrioleDB alleviates their endurance concerns aren't at liberty to show me their Grafana.
I might be able to get some real SSD endurance metrics comparisons at meaningful scale over the next couple years but I don't know.
Beyond that, I think the only real way to find out is a snapshot of real-world application production Postgres databases and use logical replication to replay a year of database traffic to find out what the delta on SSD endurance impact is.
I don't claim to be a Postgres expert relative to the upper echelon that frequents this site, always happy to get more insight to Postgres, my attention has generally been on either North or South side of that part of a webscale stack.
hubertzhang 13 hours ago [-]
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.
getbrew 17 hours ago [-]
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.
ComputerGuru 16 hours ago [-]
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.
ak39 15 hours ago [-]
Why does this comment sound AI generated?
mike_hearn 11 hours ago [-]
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.
hmokiguess 17 hours ago [-]
I’ll be real, I use Postgres every day and I have no clue what you’re talking about
jon128 16 hours ago [-]
[flagged]
blenser4 17 hours ago [-]
[dead]
yksalun 13 hours ago [-]
[flagged]
CEOatMindfries 11 hours ago [-]
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.
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)
At runtime, if you've allocated a boolean or want to store a byte somewhere, you've either done it on the stack (it's temporary and you know it will need to be accessed at least once as an independent unit of data, and packing it together with other small data structures will make you use an extra instruction to extract it into its own register anyway, so there is not really any benefit to packing them tighter) or heap (you might actually gain something by reducing memory usage here, but packing data together binds the lifecycle and structure of everything at the same address and either requires additional lookups to check the interior structure, or a fancy compiler/build process to bake in the instruction to extract packed data without knowing what other data is packed with it).
When you read serialized data for a record from the disk, while running a typed database, the lifecycle/structure within the record is already shared. Most times you load the record you probably won't be accessing any particular packed data structure (eg if you have 5 bool columns in a table, most queries won't use all or any of them). You already have a global way to handle internal types/optimized reads, because that's what databases do.
So it would actually be feasible to load packed data and efficiently access or calculate with it eg XOR against 0x00..<single bit flipped to match bool index in word>00 to use a bool, or bit shift to load a subword into a register. It would be at worst 1 instruction per read slower if implemented efficiently (the query planner can handle the type lookup/deciding how to do the optimized read, which amortizes and isn't the query bottleneck or even on the critical path).
BUT denser data = more cache coherency and higher read throughput (more rows/s). And if you're doing fancy bit operations and arithmetic to extract and perform calculations on the packed data, it might actually be easier to batch operations with SIMD. So you could get arguably higher performance + much less passive disk usage (saving actual money) by packing data better probably. I'm about 30% of the way towards convincing myself to try this
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.
[0] https://news.ycombinator.com/item?id=41560556
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.)
This article about MVCC is also quite interesting: https://boringsql.com/posts/mvcc-bad-bad/
Compare Postgres and Oracle (block undo based) for what happens when the database is underprovisioned:
• Postgres: bottlenecks on CPU. The database falls behind on vacuuming and dies.
• Oracle: bottlenecks on free disk space. Individual long running statements, SNAPSHOT and READ ONLY transactions get cancelled with a "snapshot too old" error.
In Oracle the database continues running and the blast radius is limited to a single statement. Because it's the longest running statements that die first, that almost certainly means an asynchronous business report and not live user traffic - which is exactly what you'd hope for. Most businesses can tolerate a missing day in a report much better than customer-facing outages, and fixing it is just a single SQL statement if you have free disk space (adding more storage is easy due to the SAN).
That's much preferable! How many post mortems do we see on HN from some startup bemoaning a multi-hour outage caused by Postgres' MVCC choices? Quite a lot. Now think about how often do you recall reading a post mortem of an outage at some big bank, hospital, shipping port etc that traces back to Oracle's MVCC engine? I don't think I ever read one. The startup world likes to make fun of Big Enterprise, but they have a much better track record of database uptime because they stay away from vacuum-based MVCC.
Disclosure: I have a part time role in the Oracle DB group. Unapproved opinions may be found above, caveat emptor.
> That's the honest shape of the mitigation: HOT is opportunistic, arrives in steady state rather than on demand, and quietly costs you 30% of your table size in reserved space.
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.
What Uber posted there did prompt me to investigate the topic of Postgres old MVCC, OrioleDB, and MariaDB's alternative MVCC.
Ultimately, one change in a row results in the entire row being written again, from what I read. Is this not accurate?
MariaDB's MVCC doesn't write the entire row again, only the delta. One of the newer more advanced MVCC for MariaDB are even more efficient.
In my view, "runaway vacuum" has never not been a case of either poorly done IT operations or poorly thought out (or just not thought out) database usage patterns by the in-house application developers. While "throw hardware at the problem" isn't a great answer, I have never not seen NVDIMM for ZFS ZiL/SLOG not mitigate "runaway vacuum", as ugly as that is -- that was a long time ago though.
SSD endurance can get daunting in an advanced hosting environment. It's odd that I don't often see others have these metrics in their Prometheus+Grafana. The people who are telling me that OrioleDB alleviates their endurance concerns aren't at liberty to show me their Grafana.
I might be able to get some real SSD endurance metrics comparisons at meaningful scale over the next couple years but I don't know.
Beyond that, I think the only real way to find out is a snapshot of real-world application production Postgres databases and use logical replication to replay a year of database traffic to find out what the delta on SSD endurance impact is.
I don't claim to be a Postgres expert relative to the upper echelon that frequents this site, always happy to get more insight to Postgres, my attention has generally been on either North or South side of that part of a webscale stack.
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
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.