The State of Vacuum in Postgres

So the vacuuming of tables to recover disk space from deleted rows needs to be done manually.

There are 3 types of vacuuming in Redshift (DELETE ONLY, SORT, REINDEX). The vacuum SORT operation is done on tables that have a sort key. To the extent that a vacuum SORT is an expensive (high IO) operation, we recommend when possible, to avoid the need to vacuum by loading the rows in sort order. If you do that, you will not need to vacuum the table, and this is the optimal solution for very long tables.

When dealing with a very long table (>10b rows) that has a sort key, it’s useful to partition the table into multiple tables where the table names are modified to indicate the partition. This is useful when pruning the size of the table, you don’t have to run VACUUM DELETE ONLY.

The key is to vacuum on a regular basis, and keep your stats off below 10%. If you wait too long, you might end up in a place where you have to resize your cluster or do a deep copy.

One more thing: Run your vacuum jobs in the queue with the most memory.

What's slow are stuff like java GC Eden management where deletes are an afterthought to what is a time-stamped LRU reference management server, and a lot more.

So, no. I don't think you can compare C++ with systems that run full fledged GCs and threat them in the same bullpark.

This is not to say vacuum-like mechanisms are necessarily the best method but it was a common architectural idiom for database engines from the 1990s because it works well with sequential storage devices. Newer techniques tend to put resource recovery inline with the workload rather than outside of it but that has other disadvantages.

Indexing all rows in a database by transaction id would cause an extreme loss of throughput. That just creates a continuously mutated (and therefore locking) structure every thread constantly uses that is also paged to disk. And deletion of individual records is a problem for indexes generally.

In this case, I'd want to think carefully about the data volume: many places may have hundreds of transactions per second but also have queries which run for much longer periods of time and the visible state has to be accurate for all of them. That sounds a lot of contention for that shared data structure and “efficiently find all rows that aren't visible to any transaction” sounds decidedly non-trivial for busy servers with lots of data, which are generally the only ones where this matters.

Vacuum actually works by looking for tuples where no running transactions can see them anymore. Postgres, in effect, maintains a minimum and maximum transaction ID that any tuple is visible for, and vacuum scans over all of those that have a max visible transaction ID (suggesting it's available to be reclaimed) and which is less than the minimum active transaction ID.

Note that it's not full scans - only pages that have been modified since the last vacuum, or were in a state last vacuum that they couldn't be processed, are vacuumed again. So there essentially is a block-level index for this.

https://www.postgresql.org/docs/current/static/storage-vm.ht...

It has potential pitfalls too though. On a system with very high mutation rate, purging could fall behind. This mainly would happen if the history list overflows available buffer pool space and starts getting paged to disk (e.g. from one very long lived transection). I don't know if newer MySQL versions make purge running off disk more efficient, but 5.5 added a config for multiple purge threads.

Many things can seem this way from the armchair, but you can lend some benefit of the doubt. Postgres is worked on by very smart people and it's not the only system that works this way

Everything has tradeoffs.

Postgres already has the notion of creating and destroying transaction ids for each row version attached to that tuple (row version). That distributes your "structure" across the rows involved in it. Just because you delete a row version (whether by deleting that row, or by updating it — which in MVCC is synchronously deleting the old version and inserting a new one) doesn't mean that row version isn't still visible to other concurrent activity, specifically including read-only activity. You'd therefore have to write to this proposed structure on completing every read or write.

It is determined by the rate of churn.

Edit, re: the parent's edit: Quoting that article, "Perhaps the thorniest problem which Uber raises is that of write amplification caused by secondary index updates." The word "vacuum" is mentioned exactly once, in the context of keeping up with index insertions (writes).