Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

ottertune.com

Post reply on HN
Print view View original

111–120 of 148 posts

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

#111

Post by orthoxerox »

DiabloD3 wrote: post #62
cyberax wrote: 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…

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?

"Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (4MB)." Yes, and? Should I set it higher? When?

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv... hasn't been updated for two years and explains only a handful of parameters.

"If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents." How much is a lot? Do I need to care if I'm running mostly OLTP queries?

"This is a setting where data warehouse systems, where users are submitting very large queries, can readily make use of many gigabytes of memory." Okay, so I need to set it higher if I'm running OLAP queries. But how high is too high?

https://wiki.postgresql.org/wiki/Performance_Optimization is just a collection of blog posts written by random (probably smart) people that may or may not be outdated.

So when someone complains their Postgres instance runs like ass and smug Postgres weenies tell them to git gud at tuning, they should be less smug, because if your RDBMS requires extensive configuration to support nontrivial loads, you either make this configuration the default one or, if it's significantly different for different load profiles, put a whole section in the manual that covers day 1 and day 2 operations.

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

#112

Post by dathinab »

jasonhansel wrote: post #82

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.

IMHO the problem is that

1) too many people don't understand why they probably should use snapshot isolation (or stricter) and don't understand what guarantees they don't get when read committed is used

2) it's not the default, defaults matter, a lot

3) it makes transactions spurious fallible when the db can't make sure that committing two parallel write transactions won't brake the consistency guarantees, a lot of frameworks don't have the right tools to handle this, people don't expect it, it can make in the transaction interleaved interactions with other systems harder, etc.

(as a side not I assumed you meant REPEATABLE READ when you said snapshot isolation, as it's the least strict isolation level which uses snapshot isolation)

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

#113

Post by p_l »

masklinn wrote: post #92
p_l wrote: post #57

Earlier quoted context omitted.

Replication came later - but the fact that you could do sudo apt-get install mysql-server mysql-client sudo -i mysql and be logged in as admin into mysql database was indeed a huge reason for defaulting to it. EDIT: Of course, at that time, there was no Ubuntu teaching everyone to sudo all the time, so drop all instances of sudo and add a su - at start ;)

Mysql also came with pretty much any webhost.

That's exactly my point. A lot of people started with dynamic websites by using cheap webhosting that you FTP'ed your PHP files to, and used PhpMyAdmin to manage your smallish database, and 2000-2009 they still formed a strong portion of market for starting out (I chose 2009 because that's when EC2 becomes more accessible for this due to RDS)

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

#114

Post by govolckurself »

pier25 wrote: post #55
mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

> MongoDB gained traction not because it's an alternative to MySQL or PostgreSQL. Honestly I think it only gained traction because many Node devs refused to learn SQL and the document model is familiar because it's closer to JSON data. These days Mongo is good but that wasn't the case back 10+ years ago.

[dead]

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

#115

Post by govolckurself »

lmm wrote: post #80
mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

> MongoDB gained traction not because it's an alternative to MySQL or PostgreSQL. Disagree. It gained traction because it was an alternative to MySQL in the ways that mattered - fast, easy to administer, widely known, good enough. Yes, there are significant differences in the details of what they do - but in terms of someone looking for a backing datastore for their webapp, they're actually competing in a very simila…

[dead]

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

#116

Post by Temporary_31337 »

I work for a db vendor and Acid compliance (also implemented with MVCC) is a big selling point. Yet, most use cases I later see don’t require such rigid controls on updates. This means customers are paying for this as transactionally consistent updates are more expensive than eventually consistent ones.

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

#117

Post by pier25 »

orthoxerox wrote: post #107
inferiorhuman wrote: post #90

Earlier quoted context omitted.

Mongo was so comically bad. I remember trying to sort through a slow query and thought: ah ha! I'll just add an index. Unfortunately on that version of Mongo, creating an index would occasionally just crash the server process. I think Mongo became popular because it's ad tech and those guys knew how to be buzzword compliant. JSON-esque documents are one thing, but Mongo is Javascript to the core. All of a sudden your…

My favourite story about MongoDB is that it was so bad and popular at the same time that when a competitor developed a wire-compatible database that was miles better they simply bought it and released it as the next version of MongoDB.

which db was that?

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

#118

Post by orthoxerox »

pier25 wrote: post #117
orthoxerox wrote: post #107

Earlier quoted context omitted.

My favourite story about MongoDB is that it was so bad and popular at the same time that when a competitor developed a wire-compatible database that was miles better they simply bought it and released it as the next version of MongoDB.

which db was that?

I think I meant WiredTiger.

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

#119

Post by tuatoru »

steve_taylor wrote: post #66
mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

PostgreSQL became the internet's darling DBMS long before that. Oracle's acquisition of MySQL in 2008 made people finally take notice of PostgreSQL. Before that, most developers barely knew it existed.

Yeah, Postgresql was the FreeBSD of DBMSes. Solid, conceptually integral, well documented.*

I recall doing an evaluation of open source databases in 2001. MySQL didn't even have row-level locking, let alone any concept of transactions. I summarised it as "easy to use; but only for data you don't care about".

* Not that Postgres (as it was then) was without warts in 2001. A huge one was its "object orientation": table inheritance. What it needed then, and would still be nice to have, is object orientation at data type (column) level, an extension of the SQL domain.

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

#120

Post by kerblang »

jasonwatkinspdx wrote: post #65
kerblang wrote: post #45

Question: Why would I need more than one extra version of the same row? I would think that with transactional locking everybody else is waiting on the first update to commit before getting their own changes in, unless the db is somehow trying to lock columns-per-row instead of entire rows.

That would require all queries, including read only queries, to participate in strict two phase locking. That has very poor performance under even very mild contention, not to mention all that mutual locking and unlocking overhead between read only queries is largely un-needed. So what MVCC databases do is keep enough versions to cover the oldest running query instead. Now read only queries don't need to hold any loc…

If my query started 1000 ms ago, and every 200ms a transaction completed, I'm perfectly fine with getting results of some/most/all of those 5 commits. I usually don't need the database to enforce a 1000-ms-old snapshot for my own sake, which is why I'm using read-committed isolation instead of repeatable read etc. Are we saying that not enforcing this delay would break the database somehow even if I'm fine with it?

Edit: I should clarify that I recognize the need for one extra version, since read-committed txns shouldn't see it until it is committed. Other writes must wait for the commit until they can write, though - it seems like there's some optimistic-writing thing where we let a bunch of writes queue up for one record knowing that we're going to have to a problem when one of them commits and the others find out they should have waited before trying to write or something, because we didn't force them to acquire a write lock before writing.

Post reply on HN