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

81–90 of 148 posts

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

#81

Post by evntdrvn »

apavlo wrote: post #44
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/n…

Is there hope of ever seeing Ottertune for MSSQL ?

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

#82

Post by jasonhansel »

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.

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

#83

Post by cipheredStones »

nextaccountic wrote: post #41
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 automa…

It previously had this closing line, with links to their products (https://web.archive.org/web/20230426171217/https://ottertune...):

> A better approach is to use an AI-powered service automatically determine the best way to optimize PostgreSQL. This is what OtterTune does. We’ll cover more about what we can do in our next article. Or you can sign-up for a free trial and try it yourself.

That was removed after the article was posted to HN, at dang's suggestion - he posted about it elsewhere in these comments.

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

#84

Post by [deleted] »

apavlo wrote: post #44
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/n…

[deleted]

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

#85

Post by nextaccountic »

apavlo wrote: post #44
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/n…

Okay, so, Noisepage appears to be open source https://github.com/cmu-db/noisepage/

But I can't find the Ottertune Github page

Is any part of Ottertune open source?

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

#86

Post by ncann »

endorphine wrote: post #75
DiabloD3 wrote: post #62

Earlier quoted context omitted.

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…

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

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

#87

Post by avinassh »

zinclozenge wrote: post #38

I guess the question is, which MVCC strategy would be the "right" one to pick for a modern relational database? The paper linked focuses on main memory databases, and being main memory allows you to do things you can't do when disk based.

I have same the question. I skimmed through the linked paper for conclusion, they highlight the techniques which can be used to improve, but does not say which MVCC to use for a modern database. May be I need to do a careful reading.

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

#88

Post by avinassh »

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 their MVCC implementation to reduce overhead.

What are the other advantages they have to make reads faster?

Compared to MySQL, I remember reading that Postgres MVCC lets you alter the table without locking. Now I found out that MySQL also does not require locks. So, how are they doing?

Are there any similar posts which explain MySQL MVCC architecture?

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

#89

Post by bvm »

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…

READ COMMITTED SNAPSHOT

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

#90

Post by inferiorhuman »

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.

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 JS devs don't have to learn SQL they can just shit out some queries in javascript. Of course that came with some pretty severe drawbacks.

Post reply on HN