Live data from Hacker News

The part of Postgres we hate the most: Multi-version concurrency control

ottertune.com

131–140 of 148 posts

Re: The part of Postgres we hate the most: Multi-version concurrency control

#131
post #109

Earlier quoted context omitted.

MySQL (then Vitess) ran Youtube, but nowadays I do believe most product teams are using Spanner.

Yeah I think (heard anecdotally) both google/YouTube and Facebook (and many others) started with MySQL. Spanner for distributed writes has inspired most implementations although Google is the only one I know about that implements TrueTime (atomic clocks). The same year that the Spanner paper came out (after Percolator) an alternate approach (Calvin) was also published, and some of us are using that (our DB's design i…

youtube did, but google didn't buy youtube until three years before the end of 'the 2000s'

Re: The part of Postgres we hate the most: Multi-version concurrency control

#132
post #71

Earlier quoted context omitted.

I remember it differently - we needed replication for "hot" backups. At that time, scalability was a major issue - so anyone (including businesspeople) wanted to have a scalable architecture. MySQL spoke to the practical (default install on cPanel hosts, easy replication) and the aspirational (you're going to blow up and need to scale). Digg.com also had a really influential technical team - hearing about how they di…

maybe you were on the more funded side of history in this. As for me, Digg is way after LAMP got solidly plonked into "what I need for a dynamic website on cheap". Essentially, start at 2000-2001 and more and more people going into running websites for all kinds of reasons (forums, blogs, webshops, etc. often hosted on low end offerings)

"Cheap" is the key word here, and that usually meant shared hosting, which was like 99% MySQL.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#133

IMHO part of the issue is that Postgres was built on the assumption that snapshot isolation would be widely used. I don't think this has proven to be the case. Snapshot isolation isn't as robust and straightforward as strict serializability, but it also isn't as performant as READ COMMITTED. It seems like the worst of both worlds.

IMO the best thing about snapshot isolation is that it's conceptually easy to understand and reason about.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#134

Earlier quoted context omitted.

I remember when Uber got roasted by the postgresql mailing list over this: ultimately, a post mortem was done on all of Uber's claims, and it was basically proven that they were incompetent, did not read any available "best practices" guides, did not seek any external help, and treated it like it was some sort of mysql-esque database and used it as wrong as humanly possible. Uber's workload at the time, ironically, w…

That's still the PostgreSQL problem: it has insane defaults. https://www.postgresql.org/docs/current/runtime-config-resou... tells you what all the parameters do, but not why and how to change them. "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system." Why not set it to 25% of the memory in my system by default, then? "Se…

Defaults are hard to change because it makes upgrading even scarier

Re: The part of Postgres we hate the most: Multi-version concurrency control

#135

Earlier quoted context omitted.

I remember when Uber got roasted by the postgresql mailing list over this: ultimately, a post mortem was done on all of Uber's claims, and it was basically proven that they were incompetent, did not read any available "best practices" guides, did not seek any external help, and treated it like it was some sort of mysql-esque database and used it as wrong as humanly possible. Uber's workload at the time, ironically, w…

That's still the PostgreSQL problem: it has insane defaults. https://www.postgresql.org/docs/current/runtime-config-resou... tells you what all the parameters do, but not why and how to change them. "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system." Why not set it to 25% of the memory in my system by default, then? "Se…

If you're running pg at scale, it pays to have a least one person familiar with the config file.

That the defaults don't handle top users is hardly an issue.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#136
post #42

This post has a valid point. But the last line makes it clear why they care so much about it. Yeah, table bloat and transaction ID wraparounds are terrible, but easily avoidable if you follow a few simple guidelines. Typically in my experience, best way to avoid these issues are to set sensible vacuum settings and track long running queries. I do hate the some of the defaults in the Postgres configuration are too con…

> "But making sure that PostgreSQL’s autovacuum is running as best as possible is difficult due to its complexity." The problem, as the article states it, is that a "sensible" vacuum setting for one table is a terrible setting for another depending on how large these tables are. On a 100 million tuple table you'd be waiting 'til there there were 20 million garbage tuples before taking action.

You can set autovacuum reloptions on a per-table basis, if they differ that much for your your use case.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#137
post #58
post #39

One of the weird things about Postgres MVCC is that it is "optimized for rollback," as one person memorably quipped to me. This is not to imply a design principle, it's more a description of how things ended up, and the general argument behind this quip is Postgres lacks "UNDO" segments. On the one hand, this does make the model Postgres uses admirably simple: the WAL is all "REDO," and the heap is all you need to ac…

Coming to Postgres, UNDO logs and no vacuum https://github.com/orioledb/

what is an UNDO log and how does it solve the problem?

Re: The part of Postgres we hate the most: Multi-version concurrency control

#138
post #108

This was a fun read. But now I have a couple of questions 1. Since MySQL keeps delta to save storage costs, wouldn't read and writes slower because now I have to build the full version from the delta 2. On secondary indexes, they highlight the reads will be slower and also say: > Now this may make secondary index reads slower since the DBMS has to resolve a logical identifier, but these DBMS have other advantages in…

1. It's a backward delta. So it's only needed when a transaction touches a row that has been modified by a concurrently running transaction. (Details differing depending on isolation level etc.). Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row. I guess reads can be faster too in some cases, e.g. less fragmentation and wasted space (better cache usa…

> Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row.

what is delta undo log?

Re: The part of Postgres we hate the most: Multi-version concurrency control

#139
post #58

Earlier quoted context omitted.

Coming to Postgres, UNDO logs and no vacuum https://github.com/orioledb/

what is an UNDO log and how does it solve the problem?

In most cases[1], when you update a tuple in Postgres, a new tuple is put somewhere else in the same heap, with different visibility information, "xmin", "xmax". The old tuple remains where it is. Index pointers to it likewise remain unchanged, but a new entry is added for the new tuple. The old version gains an updated "xmax" field indicating that version was deleted at a certain logical point.

Later on, VACUUM has to plow through everything and check the oldest running transaction to see whether the tuple can be "frozen" (old enough to be seen by every transaction, and not yet deleted) or the space reclaimed as usable (deleted and visible to nothing). Index tuples likewise must be pruned at this time.

In systems with an UNDO log, the tuple is mutated in place and the contents of the old version placed into a sequential structure. In the case where the transaction commits, and no existing concurrent repeatable read level transactions exist, the old version in the sequential structure can be freed, rather than forcing the system to fish around doing garbage collection at some later time to obsolete the data. This could be considered "optimized for commit" instead of the memorable "optimized for rollback."

On the read side, however, you need special code to fish around in UNDO (since the copy in the heap is uncommitted data at least momentarily) and ROLLBACK needs to apply the UNDO material back to the heap. Postgres gets to avoid all that, at the cost of VACUUM.

[1] The exception is "HOT" (heap only tuple) chains, which if you squint look a tiny bit UNDO-y. https://www.cybertec-postgresql.com/en/hot-updates-in-postgr...

Re: The part of Postgres we hate the most: Multi-version concurrency control

#140
post #30

Yup. A lot of heavy users of Postgres eventually hit the same barrier. Here's another take from Uber: https://www.uber.com/blog/postgres-to-mysql-migration/ I had a similar personal experience. In my previous job we used Postgres to implement a task queuing system, and it created a major bottleneck, resulting in tons of concurrency failures and bloat. And most dangerously, the system failed catastrophically under loa…

>> In my previous job we used Postgres to implement a task queuing system, and it created a major bottleneck, resulting in tons of concurrency failures and bloat Yet, every month or two an article about doing exactly this is upvoted to near the top of HN. It can of course work but might hard to replace years later once "barnacles" have grown on it. Every situation is different of course.

skip locks are the secret for queues in pg. did you use skip locks?
Post reply on HN