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

141–148 of 148 posts

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

#141

Post by jabl »

avinassh wrote: post #138
jabl wrote: post #108

Earlier quoted context omitted.

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 usa…

> Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row. what is delta undo log?

It's an undo log containing the deltas from the latest version (instead of the entire row), so that a previous version can be reconstructed in case of a rollback or if a concurrently running transaction needs the previous version.

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

#143

Post by jasonwatkinspdx »

kerblang wrote: post #120
jasonwatkinspdx wrote: post #65

Earlier quoted context omitted.

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 loc…

If my query started 1000 ms ago, and every 200ms a transaction completed, I'm perfectly fine with getting results of some/most/all of those 5 commits. I usually don't need the database to enforce a 1000-ms-old snapshot for my own sake, which is why I'm using read-committed isolation instead of repeatable read etc. Are we saying that not enforcing this delay would break the database somehow even if I'm fine with it? E…

It gets complex. There are a lot of tradeoffs between short and long running transactions, and different choices in the design space each have pluses and minuses.

Of course if you're running reduced consistency things are easier. Wrong answers are often faster. But when people select a transactional database, it's usually because they at least need snapshot consistency, and often they require full serializability.

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

#144

Post by franckpachot »

zeroimpl wrote: post #73

> Oracle and MySQL do not have this problem in their MVCC implementation because their secondary indexes do not store the physical addresses of new versions. Instead, they store a logical identifier (e.g., tuple id, primary key) that the DBMS then uses to look up the current version’s physical address. This doesn’t have anything to do with MVCC. I’m sure PostgreSQL could implement an index format that piggybacks on a…

And this is wrong. Oracle stores the physical address in the index. The ROWID is the relative file number (in the tablespace) + block offset in the file + an index in the block row directory. The difference is that Oracle rows usually don't move and are updated in place. Because old version diff goes to undo segments.

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

#145

Post by runlaszlorun »

dmak wrote: post #97
runlaszlorun wrote: post #95

As an aside, Andy Pavlo (one the authors here) has his CMU database course videos up on YouTube and they are tremendous. I’ve spent 2 decades developing web applications but am not exaggerating when I say that I’m 10x more knowledgable on databases having watched his courses during Covid.

Could you share the links?

https://youtu.be/oeYBdghaIjc

There are multiple years available for his first DB class but that’s the one I watched. I almost called it his ‘basic’ class but there’s literally only like one or two classes on SQL before he dives into all the various layers of the internals.

There’s also a few of his advanced courses. And then you’ll see guest lectures from industry on about every one of the new DB platforms you can think of.

They’re all under “CMU Database Group” on Youtube.

Highly recommend.

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

#146

Post by runlaszlorun »

runlaszlorun wrote: post #145
dmak wrote: post #97

Earlier quoted context omitted.

Could you share the links?

https://youtu.be/oeYBdghaIjc There are multiple years available for his first DB class but that’s the one I watched. I almost called it his ‘basic’ class but there’s literally only like one or two classes on SQL before he dives into all the various layers of the internals. There’s also a few of his advanced courses. And then you’ll see guest lectures from industry on about every one of the new DB platforms you can th…

For all the crap on YT, there’s some real gold if you dig and clue the algo in. The SICP lectures I’d place in this category as well.

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

#147

Post by lmwnshn »

runlaszlorun wrote: post #145
dmak wrote: post #97

Earlier quoted context omitted.

Could you share the links?

https://youtu.be/oeYBdghaIjc There are multiple years available for his first DB class but that’s the one I watched. I almost called it his ‘basic’ class but there’s literally only like one or two classes on SQL before he dives into all the various layers of the internals. There’s also a few of his advanced courses. And then you’ll see guest lectures from industry on about every one of the new DB platforms you can th…

You can do the projects (the autograder is public) and join a discord community of non-CMU people that are following along too! e.g., [0] for Fall 2022.

[0] https://15445.courses.cs.cmu.edu/fall2022/faq.html#q8

Post reply on HN