Hacker News
points by hans_castorp 4 days ago | 0 comments
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/

[–] mike_hearn 4 days ago
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 days 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 3 days 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 3 days 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/...).
[–] mike_hearn 3 days ago
You can do time travel with any MVCC design. Undo logging is well suited to it and Oracle ships several forms of time travelling SQL under the name Flashback.

The simplest form of Flashback just uses the undo logs. Normally undo is deleted once no live transaction uses it, but you can set a retention time to allow SELECT ... AS OF ... queries to go back at least that far.

However, keeping undo logs around forever isn't practical. If you want to go back a year then there's Flashback Time Travel which copies old data into tables in a form that the query engine can use to answer AS OF queries more efficiently.

[–] senderista 2 days ago
The time-split B-tree is designed to make time-travel queries efficient in both time and space. That is not a trivial problem.

I don’t know if Flashback uses this technique, but the SQL Server architect who taught me time-split B-trees now works at Oracle :)

[–] mike_hearn 2 days ago
Hah, interesting. I need to investigate that data structure. I don't know if Flashback uses it either - if you enable the time travel feature then undo logs are processed into internal tables partitioned by SCN, but I suppose it's possible those tables are stored in a different way to regular heap-organized or index-organized tables.
[–] xyzzy_plugh 3 days 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 4 days ago
Thanks, that MVCC article was a great read.
[–] guenthert 4 days 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 4 days 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 days ago
Claude, please remove all Claudisms from this article.