Comment by gethly
9 hours ago
> Feels like it might be very useful since a lot of new technologies came out since spinning disks.
The MVCC that Postgres uses(and no one else) is like 50yo outdated concept they still cling to. So just by virtue of that, it makes PGSQL the most archaic db on the market nowadays.
I never understood why PGSQL had so many fanboys, yet every major tech company always ditches it for mysql... i guess it is the case of "Any man who must say, "I am the King", is no true king." type of thing. People have to make a lot of noise to excuse their bad choices so they don't have to admit making a mistake.
I can’t believe they still make processors out of sand. Talk about outdated technology.
Sarcasm aside, a great many projects started on MySQL and moved to postgres. As did projects using mongo, couchdb, firebase, oracle etc etc…
And I’m sure many projects switched away from Postgres to other technologies. Right tool for the job at hand.
By MVCC you mean the kind of MVCC that keeps old versions in the same space / requires vacuum? Because I'm pretty sure Oracle also does multiversioning using their undo log / rollback segment.
Calling undo/redo MVCC isn't really accurate. The whole point of Postgres' style of MVCC is that updates leave the original rows unmodified, which allows concurrent transactions to read them without extensive locking or redirection.
In Oracle's database engine, when transaction A updates a row, it begins by reading the old row, updating it in-place, and adding the old row to the undo log. The heap row has a header with a list of transactions currently accessing that row and pointers into the undo log. If a transaction B comes in (while A is still ongoing) and wants to read the row, it startsby reading the current row header, where it sees that someone else has modified it, and then goes to the undo log to read the old version. (This is all very simplified.)
The huge benefit of the undo log is that the main heap doesn't get bloated with old data. Deletes cause holes, of course, but updates do not. Meanwhile, the undo log can be trivially be truncated when rows are no longer needed. Postgres, since it effectively mixes undo data with current versions, needs to do vacuuming. Postgres may win when there's a huge amount of contention around hot spots, but arguably loses when it comes to "normal" transactional volume.
OrioleDB adopts Oracle-style undo logging, among other table layout improvements, and their own OLTP benchmarks show extreme performance improvement over mainline Postgres.