[stormdennis]

Some argue that having a separate vacuum process is a bad idea. That the right time to clean up a no longer transaction-tied version is the next time someone visits the row in a query. That way there are no extra database accesses to each row beyond normal use.

[jeltz]

I see several pretty big issues with that. It would leave empty rows and pages since there is no guarantee that there will ever be a scan which hits a particular page which contains deleted rows. Additionally if you recently have deleted a lot of rows the next sequential scan could become very exprnsive since suddenly a read query needs to dirty a lot of pages, maybe more pages than the number of available buffers forcing our read query to have to wait on write io.

What PostgreSQL has though is micro vacuum where PostgreSQL tries to remove any no longer visible rows when it already has to modify a page. But it only does this as long as no index updates are necessary.

[jsmith45]

MSSQL does this with a two pronged approach.

If a dead record is encountered in a scan, it is queued up to cleaned up by a background task, that runs every 5 seconds. This typically happens soon enough that the record is still cached, so no extra read I/O is needed, and in any case the location of the record is already known, so no extra scans are needed.

To handle other records, it maintains a bitmap of pages with ghost records. Each time it is awakened, it cleans up the queue, and then does up to 10 pages indicated in the bitmap. Then it goes back to sleep, and only wakes up again 5 seconds later.

This super slow cleanup of dead records that have not been recently scanned helps to avoid overloading the system, but does ensure cleanup will eventually occur, even if the table never gets scanned.

Pedantic note: (Technically it is a bytemap, as the bit is part of a larger structure, that holds other flags about a page.)

[stormdennis]

You probably understand it better than I do but FWIW here is what I was basing my comment https://sourceforge.net/p/firebird/mailman/message/17015961/ and in particular Jim Starkey's post

[amitkapila]

Your last sentence is not very clear to me. "But it only does this as long as no index updates are necessary.". Do you mean to say it does only for HOT updates? If so, I don't think that is the case, it can do it during a scan of the heap.