
Do or undo – there is no VACUUM - craigkerstiens
http://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vacuum.html
======
balfirevic
One cool thing about the way MVCC currently works in PostgreSQL is that
transaction rollback is instantaneous - there is no undo work to perform if
you chose to rollback your transaction.

This proposal seems like it would do away with that feature - but perhaps it's
a good trade off in practice. I wonder what people here think.

~~~
arkh
I like their stance on it:

> This system is very much symmetric. A transaction that commits generates
> pretty much the same amount of work as a transaction that aborts. This is
> very elegant, but it’s not best in practice, because very few people run
> workloads where anywhere close to 50% of transactions abort. (Anyone who has
> such a workload will, I suspect, find that PostgreSQL handily outperforms
> the competition.) It would be better to have a system where we try to make
> commits cheaper, and aborts more expensive.

~~~
gfisher
I agree - I know that my organization would see an increase in performance, as
90%+ of our transactions are committed. I am excited to see this in action.

------
weinzierl
It‘s years since I looked deeper into the inner workings of Oracle, but isn‘t
their proposal how Oracle does deal with commit and rollback?

Which makes me think: Maybe the true reason Postgres does it differently isn‘t
technical but legal. Could there be a patent?

~~~
jcriddle4
I would think any patent would have expired by now as Oracle was founded in
1977.

~~~
snaky
Yes, but it wasn't what it is now.

First version programmed in C (not assembler) was version 3 (1983). Row-level
locking was introduced in version 6 (1988). Constraints, stored procedures and
triggers - version 7 (1992).

~~~
gaius
Oracle didn’t have transactions until 5 or 6 if I remember correctly

~~~
snaky
AFAIR transactions were implemented in Version 3.

~~~
gaius
Just checked the manual and we were both wrong - version 4

~~~
snaky
Ah, and version 4 was rewritten in K&R C! (from White Smith C)

------
saurik
Lots of people mentioning Oracle, but only one mentioning MySQL (which is
famous for doing this; I wasn't even sure Oracle did ;P). This article hasn't
showed this is a massive win... and frankly I doubt this is even that
difficult to prototype. Before writing this article I would have loved to see
them do some simple benchmark to show this was an interesting direction. As it
stands I don't even see why this was published: they didn't even do a simple
comparison to MySQL.

Like, this is how I read this article: "PostgreSQL does something one way,
which everyone is used to, and it is clearly pretty stupid; we have an
entirely new way of thinking about this that we are pretty sure--but not 100%
sure as we aren't _that_ arrogant--will be better than the way it is happening
now: we will just do this, this, and this, which certainly seems better. more
to come!".

The article I would have wanted to see: "it is well known in the world of
database software that there are two ways of doing this one thing, and
PostgreSQL is pretty unique in doing it this way which we know has some
downsides, such as this, this, and this; of course, the other way also has
known downsides, such as this, this, and this; we started to ask ourselves if
it would still make sense to try that model for some workloads against
PostgreSQL".

Essentially, a good version of this article would have at least mentioned why
MVCC is interesting at all (as this article skirts dangerously close to
"clearly we should just update everything in place"). It would have talked
about the advantages of delaying write amplification vs. doing two writes
immediately. It would have talked about Heap-Only-Tuples as a form of garbage
collection on that write amplification. It would have talked about reasons why
people would opt to have lower than 100% fillfactor on a table even if they
weren't storing multiple tuple copies. It would have talked about snapshot
serializability and predicate locking and how they are designed around MVCC
and aaked of those still made sense if implemented using an undo log. It would
have admitted that most other databases do it the other way, and asked "why
didn't PostgreSQL"?

This article barely even went into the primary _advnatage_ of the scheme they
are describing, which is that if you have a million indexes on a table you
don't have to update all of them. Seriously: the reason to care about this has
nothing to do with the VACUUM cost as if you sit down with a pencil for a
moment it should be obvious that if you have to do two writes now vs. one
write now and one write later you have only lost flexibility in scheduling
your maintenance. People get burned by VACUUM due to bad planning for later
capacity needs when they least expect it, not due to the raw amplification.
But the index issue? That is serious. They didn't talk about the index issue
as the article was supposedly already too long, but... the article didn't
really say anything.

""" We at EnterpriseDB are intending to try implementing the MySQL InnoDB undo
log technique in PostgreSQL to see if it offers meaningful benefits vs. having
to do a later VACUUM as we currently have to do; wish us luck! For more
information on undo logging, please read the following articles.

[https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-
logs.htm...](https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-logs.html)
[https://mariadb.com/kb/en/library/undo-
log/](https://mariadb.com/kb/en/library/undo-log/)
[https://blog.jcole.us/2014/04/16/the-basics-of-the-innodb-
un...](https://blog.jcole.us/2014/04/16/the-basics-of-the-innodb-undo-logging-
and-history-system/amp/) """

^ This entire article is just saying exactly this.

In the end, this article is frankly leading to more confusion than clarity :/.
It has people here asking if PostgreSQL did it this way due to patents, and
assuming that the simplistic idea that "if 50% of your statements commit then
this is a win" is correct. The article even starts with a totally misleading
concept for how much table bloat you are going to end up with: it isn't 50%
after the first VACUUM (which you should be doing pretty quickly). I guess I
don't even understand why this article was published today.

~~~
rhaas
I wrote a previous post on why MVCC is interesting, and how it relates to the
topic at hand. It's the first link in the article.

I don't think that it's accurate to say to say that there are only two ways of
doing this. There are more than two, and there's another post by my colleague
Amit Kapila which talks about that. That's the second link in the article.

We have in fact done benchmarks. We plan to publish them.

But you can't put everything into one article. Several people mentioned
thinking this one was quite long, and it barely scratches the surface of the
topic. If I'd included an in-depth discussion of all the topics you raise
here, it would have been four or five times longer. To try to avoid that, yet
give the context you want, I linked to previous posts which cover this topic,
some of which were written explicitly to provide context for this article.

But I'm sorry you didn't like the article. I tried my best.

------
solidsnack9000
Does this affect `SERIALIZABLE` isolation level? It seems like it might not
change anything because roughly the same number of pages get marked for
predicate locks.

------
twoodfin
Isn't this roughly how Oracle implements MVCC?

~~~
misframer
MySQL too (with InnoDB).

~~~
gaius
... which is owned by Oracle

~~~
dullgiulio
Yes, but InnoDB wasn't developed by Oracle but by a Finnish company before
MySQL became property of Oracle.

