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

101–110 of 148 posts

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

#101

Post by re-thc »

dalyons wrote: post #93
zie wrote: post #76

Earlier quoted context omitted.

Of course subtlety matters, but as you start scaling and noticing pain points, that is when you start working towards fixing them. First you just throw hardware at the problem and that tends to scale really really well for a really long time. It's pretty rare, even at very large scale that you MUST move off of PG, there are plenty of well tested scaling solutions, if you have the $$$'s to spend. 10+ years of dev work…

Yes again the common refrains - just throw hardware at it. I/we of course know this and all the systems I’m referring to did that first until they couldn’t. But you’re kind of missing my point - im saying by the time you are noticing scale pain points it’s often too late. Too late insofar as your system has likely grown so much in breadth (complexity, features, subsystems, lines of code, services, etc) that all depen…

And what's a more scalable solution? (in your mind)

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

#102

Post by williamdclt »

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…

I’ve never heard of this, it sounds fun but I won’t take it at face value without a source

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

#103

Post by flumpcakes »

porker wrote: post #98
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 ;)

> Of course, at that time, there was no Ubuntu teaching everyone to sudo all the time Maybe that's why I am used to logging in as root rather than a user. I started in 1999 and have been surprised how few users now do

Most prod configs I've seen now days disable SSH-ing in as root and password auth so it just becomes:

$ ssh user@server $ sudo -i #

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

#104

Post by fathyb »

ncann wrote: post #86
endorphine wrote: post #75

Earlier quoted context omitted.

Anyone has a link to that mailing list thread to share?

From what I can Google it seems to be the opposite of that, where they acknowledged Postgres's shortcoming in the mailing list: https://www.reddit.com/r/programming/comments/4vms8x/why_we_... https://www.postgresql.org/message-id/5797D5A1.5030009%40agl...

I didn't look at the reddit link but the full mailing list thread is more nuanced than that: https://www.postgresql.org/message-id/flat/579795DF.10502%40...

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

#105

Post by ericbarrett »

dalyons wrote: post #93
zie wrote: post #76

Earlier quoted context omitted.

Of course subtlety matters, but as you start scaling and noticing pain points, that is when you start working towards fixing them. First you just throw hardware at the problem and that tends to scale really really well for a really long time. It's pretty rare, even at very large scale that you MUST move off of PG, there are plenty of well tested scaling solutions, if you have the $$$'s to spend. 10+ years of dev work…

Yes again the common refrains - just throw hardware at it. I/we of course know this and all the systems I’m referring to did that first until they couldn’t. But you’re kind of missing my point - im saying by the time you are noticing scale pain points it’s often too late. Too late insofar as your system has likely grown so much in breadth (complexity, features, subsystems, lines of code, services, etc) that all depen…

I have managed and written tooling for RDBMS from dinky GB-sized up to the multi-thousand-shard PB-scale. What you're saying is absolutely true. What a small team with vision can do when they see the ramp coming pays off 100-fold just a year or two in the future.

I think this kind of anticipation was part of Pinterest's early success, for example. They got ahead of their database scaling early and were able to focus on the product and UX.

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

#106

Post by The_Colonel »

inferiorhuman wrote: post #90
pier25 wrote: post #55

Earlier quoted context omitted.

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

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…

As I remember, MongoDB got popular before node.js, so there wasn't really a lot of backend JavaScript developers out there to make a difference.

We first used Mongo ~11 years ago with Java. For us the benefit was that we could dump unstructured data into it quickly, but still run queries / aggregations on it later.

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

#107

Post by orthoxerox »

inferiorhuman wrote: post #90
pier25 wrote: post #55

Earlier quoted context omitted.

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

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.

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

#108

Post by jabl »

avinassh wrote: post #88

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 usage,) over time due to in-place updates, and less pointer chasing to find the correct version if the vacuuming isn't keeping up with pruning old versions.

As for MySQL and locks, the original MyISAM table format used locks, but InnoDB tables are MVCC like pgsql.

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

#109

Post by geenat »

kragen wrote: post #94
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…

yeah, i was surprised at the cluelessness of that remark. lamp was definitely not a 'rising tech stars' thing. hopefully the author is more careful about accuracy when it comes to database architecture than when it comes to www history did google even use mysql? certainly if they did they never talked about it publicly in the early 02000s, and of course facebook didn't even exist then lj, though, they used the fuck o…

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

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

#110

Post by barrkel »

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 not how I remember it.

> The Uber guy is right that InnoDB handles this better as long as you don't touch the primary key (primary key updates in InnoDB are really bad).

> This is a common problem case we don't have an answer for yet.

It's still not how I remember it.

Quote from https://www.postgresql.org/message-id/flat/579795DF.10502%40...

I still prefer Postgres by a long way as a developer experience, for the sophistication of the SQL you can write and the smarts in the optimizer. And I'd still pick MySQL for an app which expects to grow to huge quantities of data, because of the path to Vitesse.

Post reply on HN