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

41–50 of 148 posts

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

#41

Post by nextaccountic »

spprashant wrote: post #9

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…

What last line? The literal last line is "We’ll cover more about what we can do in our next article."

Do you mean this one?

> At OtterTune, we see this problem often in our customers’ databases. One PostgreSQL RDS instance had a long-running query caused by stale statistics after bulk insertions. This query blocked the autovacuum from updating the statistics, resulting in more long-running queries. OtterTune’s automated health checks identified the problem, but the administrator still had to kill the query manually and run ANALYZE after bulk insertions. The good news is that the long query’s execution time went from 52 minutes to just 34 seconds.

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

#42

Post by spudlyo »

spprashant wrote: post #9

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.

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

#43

Post by p_l »

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…

A non-trivial component to MySQL popularity was that easy installation (not necessarily administration) and comparatively low resource usage with good performance at default settings (even today one needs to run some basic calculations for postgres in production, IMO) meant that cheapest possible dynamic hosting using Linux, Apache, PHP3, and MySQL 3, was what simply was the only available option for many. This codified LAMP stack, people learned from tutorials/courses/word of mouth how to write web apps with PHP and MySQL, used cheap LAMP hosting, optionally installed LAMP servers themselves, etc.

This also led to popularity of bigger reselling setups (I don't miss installing cpanel...) and services like Dreamhost.

MySQL in this way gained a virtuous cycle completely unrelated to Google. Hell, most people I know, who dealt with LAMP space for years, never knew Google had anything to do with MySQL (most people that knew about it were... Lispers. Because of who built the first version of Google Ads)

Even Mac OS X Server shipped with MySQL and PHP because of that, in 2001.

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

#44

Post by apavlo »

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.

> Of course at the end of the article they offer a solution - their product (and of course it’s AI enhanced)

We have been working on automatic database optimization using AI/ML for a decade at Carnegie Mellon University [1][2]. This is not a gimmick. Furthermore, as you can see from the many comments here, the problem is not overhyped.

[1] https://db.cs.cmu.edu/projects/ottertune/

[2] https://db.cs.cmu.edu/projects/noisepage/

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

#45

Post by kerblang »

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.

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

#46

Post by eduction »

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…

I think it’s disturbing you asked someone to change their content and even more disturbing that they complied. You are experienced at moderating Havker News have no business being a global censor for content out in the world. This sucks.

As a reader I’d have appreciated the original. And I’d appreciate a nice HN alternative.

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

#47

Post by zaphirplane »

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…

Part of the popularity of the early MySQL was marketing. I hope I’m not wrong here, there was something written about MySQL people posting misinformation in forums. Another is the ease of having it up and running. Another was I think there was some IP address component to setting up users which made it look complicated

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

#48

Post by hodgesrm »

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…

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

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

#49

Post by dalyons »

zie wrote: post #29
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.

i mean, sort of? There is some subtly lost in this oft-repeated advice. i've worked at 3 companies now that were initially based on a single RDBMS but have outgrown the scale of what is reasonable to serve off that architecture. They are consumer scale (10s of mill) users, but not hyperscale (IMHO 100m+). The amount of engineering cost to migrate a complicated growing company/product off a mono-db architecture is astounding. Conservatively i'm talking 10+ dev years of effort, at each company. Easily 10s of millions of $$$, maybe 100m+. None of them are "finished". It's really really time consuming and hard, once you have 100s of tables, 100s of thousands of lines of code, dozens of teams, etc.

I'm all about avoiding premature optimization, and its fine to start with a classic postgres. But please don't cling to that - if you see MVP success and you actually have a reasonable chance of getting to >1mill users (ie, a successful B2C product) please please dont wait to refactor your datastore to a more scalable solution. You will pay dearly if you wait too long. Absolutist advice serves noone well here - it really does depend on what your goals are as a company.

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

#50

Post by osigurdson »

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…

>> MySQL which IMO is the true darling of web DB's

A "darling" is something you want to use, not something that you are using. Many do not want to use MySQL due to Oracle control. Postgres is definitely the darling of the past few years.

Post reply on HN