Do or undo – there is no VACUUM
Lots of people mentioning Oracle, but only one mentioning MySQL (which is famous for doing this; I wasn&#x27;t even sure Oracle did ;P). This article hasn&#x27;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&#x27;t even see why this was published: they didn&#x27;t even do a simple comparison to MySQL.<p>Like, this is how I read this article: &quot;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&#x27;t <i>that</i> 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!&quot;.<p>The article I would have wanted to see: &quot;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&quot;.<p>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 &quot;clearly we should just update everything in place&quot;). 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&#x27;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 &quot;why didn&#x27;t PostgreSQL&quot;?<p>This article barely even went into the primary <i>advnatage</i> of the scheme they are describing, which is that if you have a million indexes on a table you don&#x27;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&#x27;t talk about the index issue as the article was supposedly already too long, but... the article didn&#x27;t really say anything.<p>&quot;&quot;&quot;
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.<p><a href="https:&#x2F;&#x2F;dev.mysql.com&#x2F;doc&#x2F;refman&#x2F;5.7&#x2F;en&#x2F;innodb-undo-logs.html" rel="nofollow">https:&#x2F;&#x2F;dev.mysql.com&#x2F;doc&#x2F;refman&#x2F;5.7&#x2F;en&#x2F;innodb-undo-logs.htm...</a>
<a href="https:&#x2F;&#x2F;mariadb.com&#x2F;kb&#x2F;en&#x2F;library&#x2F;undo-log&#x2F;" rel="nofollow">https:&#x2F;&#x2F;mariadb.com&#x2F;kb&#x2F;en&#x2F;library&#x2F;undo-log&#x2F;</a>
<a href="https:&#x2F;&#x2F;blog.jcole.us&#x2F;2014&#x2F;04&#x2F;16&#x2F;the-basics-of-the-innodb-undo-logging-and-history-system&#x2F;amp&#x2F;" rel="nofollow">https:&#x2F;&#x2F;blog.jcole.us&#x2F;2014&#x2F;04&#x2F;16&#x2F;the-basics-of-the-innodb-un...</a>
&quot;&quot;&quot;<p>^ This entire article is just saying exactly this.<p>In the end, this article is frankly leading to more confusion than clarity :&#x2F;. It has people here asking if PostgreSQL did it this way due to patents, and assuming that the simplistic idea that &quot;if 50% of your statements commit then this is a win&quot; 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&#x27;t 50% after the first VACUUM (which you should be doing pretty quickly). I guess I don&#x27;t even understand why this article was published today.