Transactions and Vacuums or How PostgreSQL Roombas Your Data
In the last blog, you saw how PostgreSQL keeps old versions of rows around within a transaction just in case there is a need to unwind or roll back the transaction. So, what happens to those rows that have been replaced after the transaction is complete? The answer is nothing. They sit there, taking up disk space. How Do You Get That Disk Space Back? PostgreSQL's design keeps the old rows around. In the early days of the PostgreSQL project, the idea was that those old rows could be rolled off to a WORM drive (write-once read many), or you could 'time travel' through the data. Those were grand goals, but sadly, practicality and the cost of disk space set them aside. Our first example shows the creation of a table and the addition of one row of data. demo=# create table v (a int); CREATE TABLE demo=# insert into v (a) values (1); INSERT 0 1 demo=# The first row of data is stored at CTID(0,1) demo=# select a, ctid from v; a | ctid ---+------- ...