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

61–70 of 148 posts

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

#61

Post by osigurdson »

guywhocodes wrote: post #60
osigurdson wrote: post #53

Earlier quoted context omitted.

>> 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.

I've built this queue system probably 5 times, first 2-3 were failures as consumer concurrency was 1 without us noticing for hours. The bloat comes from updating the work instead of deleting I assume, did for me. There are definitely many ways to not do it right but kinda works.

Yeah, it is strange that hokey, home grown solutions built on top of Postgres are suddenly in vogue.

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

#62

Post by DiabloD3 »

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, was not enough to make a postgresql server running moderately decent hardware to fall over if you actually read the manual.

Uber's engineering team will never be able to live this down.

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

#63

Post by [deleted] »

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.

[deleted]

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

#64

Post by tomnipotent »

p_l wrote: post #57
hodgesrm wrote: post #48

Earlier quoted context omitted.

> A non-trivial component to MySQL popularity was that easy installation ...Along with replication and being joined with the hip to PHP. As to installation, there was a point in time in the early 2000s where you could sudo to root, type 'mysql' and be talking to a live MySQL on most Linux distros that I used. No wonder a lot of people defaulted to it.

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 has had replication since May 2000.

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

#65

Post by jasonwatkinspdx »

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 locks at all, they just prune the newest version older than the transaction id the query started at.

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

#66

Post by steve_taylor »

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.

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

#67

Post by geophile »

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…

There are many alternatives to table locking, including more conventional row locks.

MVCC is great, but this article does identify some of the puzzling design choices of the Postgres implementation. The index problems are particularly bad, and seemingly avoidable.

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

#68

Post by jerrygenser »

dang wrote: post #22
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 relate…

There's another interesting article on front page about oauth that's actually almost exactly the same. A very long article about oauth implementation. And sadly I knew the add was coming the whole time and there it was as the last paragraph. It seems that unfortunately or fortunately some of the best really informative intermediate depth blog posts (read: not medium surface level stuff) tends to be an advert by a company offering a very technical product.

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

#69

Post by edmundsauto »

p_l wrote: post #59
jasonwatkinspdx wrote: post #56

Earlier quoted context omitted.

Another factor besides performance vs earlier versions of Postgres (they're now more at parity) was Postgres didn't come with replication included. I think that was a big hinderance for adoption during the LAMP stack's hey day.

Honestly, at the time when LAMP was gaining the userbase, said userbase for considerable portion did not care about replication because there was only one server they had . Replication was something you did when you got succesful enough to have it, or were a MSP providing it at premium to others.

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 did things set a lot of baseline defaults for a lot of people.

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

#70

Post by p_l »

tomnipotent wrote: post #64
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 has had replication since May 2000.

Replication being easier as driver for developers defaulting to MySQL
Post reply on HN