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

21–30 of 148 posts

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

#21

Post by LambdaComplex »

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…

> in the 2010s, it was MongoDB because non-durable writes made it “webscale“

I think this is the best video on that topic: https://www.youtube.com/watch?v=b2F-DItXtZs

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

#22

Post by dang »

garyclarke27 wrote: post #7

Clever Clickbait - Of course at the end of the article they offer a solution - their product (and of course it’s AI enhanced) to the problem they have overhyped.

I asked them to take that bit out at the end and it looks like they did.

General remark for startups wanting attention on HN: it's not good to end an interesting article with a call-to-action that makes your article feel like an ad. Readers who read to the end experience that as a bait-and-switch and end up feeling betrayed.

What works much better is to disclose right up front what your startup is and how it's related to the article content. Once you've gotten that out of the way, the reader can then dive into the (hopefully) interesting content and end the article on a satisfying note.

Btw, I have a set of notes on how to write for HN that I'm working (slowly) on turning into an essay. If anyone wants a copy, email me at hn@ycombinator.com and I'll be happy to send it. It includes the above point and a bunch more.

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

#23

Post by h1fra »

Once you figured out all the point in this article, it's a matter of fine tuning, can take some times but eventually it will works. The only thing I still struggle with is the Table Bloat.

On managed Postgres (i.e: gcp, aws) you pay for the disk, but when you can't run a VACUUM FULL because it locks the table, you end up with a lot of allocated storage for nothing and you can't shrink the disk size (at least on gcp). Storage is cheap but still feels like a waste.

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

#24

Post by dale_glass »

That's interesting, MVCC was the thing that drew me to Postgres to begin with!

Way back I was working on an in-house inventory app written in Visual Basic against SQL Server 2000, I think. That one just put locks on tables. It had the "charming" characteristic of that if you weren't very, very careful with Enterprise Manager, loading a table in the GUI put a lock on it and just keep on holding it until that window was closed.

Then the running app would eventually snag on that lock, maybe keep holding some other lock that something else would snag on, and 5 minutes later I'd hear one of the operators screaming "Nothing is working! I can't take any orders!" from the room next to me.

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

#25

Post by drkp »

apavlo wrote: post #3
Ym9oYW4 wrote: post #2

So why Postgres chooses the worst MVCC design compared to MySQL and Oracle? Is this because of legacy reasons or other factors?

Legacy reasons. The idea was that you wouldn't need a WAL because the table itself is the log. And then you could support time-travel queries if you never cleaned up the expired tuples.

_And_ it wasn't even originally designed to be used for concurrency control at all...

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

#26

Post by miohtama »

dale_glass wrote: post #24

That's interesting, MVCC was the thing that drew me to Postgres to begin with! Way back I was working on an in-house inventory app written in Visual Basic against SQL Server 2000, I think. That one just put locks on tables. It had the "charming" characteristic of that if you weren't very, very careful with Enterprise Manager, loading a table in the GUI put a lock on it and just keep on holding it until that window wa…

MVCC and optimistic concurrency control are very pleasant to work with for anyone who spent a decade with manually locking SQL databases. It takes the human error and developer mistakes away from the process, or protect against it. You can still slow down your queries with deadlocks, but at least you cannot corrupt your data by accident.

Though, any other optimistic concurrency control scheme can be better, but PostgreSQL was at the right place at the right time when people started to leave from MySQL.

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

#27

Post by audioheavy »

My main takeaway from this article: as popular as Postgres and MySQL are, and understanding the legacy systems built for them, it will always require deep expertise and "black magic" to achieve enough performance and scale for hyper scale use cases. It justifies the (current) trend to have DB's built for distributed tx/writes/reads that you don't have to become a surgeon to scale. There are other DBs and DBaaS that, although not OSS, have solved this problem in a more cost-efficient way than having a team of surgeons.

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

#28

Post by fovc »

h1fra wrote: post #23

Once you figured out all the point in this article, it's a matter of fine tuning, can take some times but eventually it will works. The only thing I still struggle with is the Table Bloat. On managed Postgres (i.e: gcp, aws) you pay for the disk, but when you can't run a VACUUM FULL because it locks the table, you end up with a lot of allocated storage for nothing and you can't shrink the disk size (at least on gcp).…

https://reorg.github.io/pg_repack/

Dead easy to run and no long-held locks

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

#29

Post by zie »

audioheavy wrote: post #27

My main takeaway from this article: as popular as Postgres and MySQL are, and understanding the legacy systems built for them, it will always require deep expertise and "black magic" to achieve enough performance and scale for hyper scale use cases. It justifies the (current) trend to have DB's built for distributed tx/writes/reads that you don't have to become a surgeon to scale. There are other DBs and DBaaS that,…

I would argue, you handle the hyper-scale use case when you are actually in hyper-scale. Trying to pre-maturely optimize this is almost always a waste of time and chances are you will screw it up anyway. Almost nobody gets to that scale anyway. If you do get to that scale, you have the money and resources to fix the problem(s) at that time.

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

#30

Post by cyberax »

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 load. As the load increased, most transactions ended up in concurrent failures, so very little actual work got committed. This increased the amount of outstanding tasks, resulting in even higher rate of concurrent failures.

And this can happen suddenly, one moment the system behaves well, with tasks being processed at a good rate, and the next moment the queue blows up and nothing works.

I re-implemented this system using pessimistic locking, and it turned out to work much better. Even under very high load, the system could still make forward progress.

The downside was having to make sure that no deadlocks can happen.

Post reply on HN