Visualizing PostgreSQL Vacuum Progress

Visualizing PostgreSQL Vacuum Progress

How can PostgreSQL find all references to a tuple — a handful of needles in a handful of haystacks — so that it can remove it from the heap without leaving references dangling from indexes? Logically, the process of removing a tuple from the heap involves a few steps (note: this isn’t exactly what happens — it’s a conceptual approximation):

To summarize: if PostgreSQL just reused a given ctid without first removing references from the indexes, then indexes would be left with pointers to who-knows-what in the heap. In the table describing the vacuum phases, the text mentions that “vacuuming indexes…may happen multiple times per vacuum if is insufficient to store the number of dead tuples found.”

Source: dtrace.org