[anarazel]

> > When VACUUM runs, it removes those dead tuples and compacts the remaining rows within each page.

> No it doesn’t. It just removes unused line pointers and marks the space as free in the FSM.

It does:

https://github.com/postgres/postgres/blob/b853e644d78d99ef17...

Which is executed as part of vacuum.

[supermatt]

I guess I stand corrected - I thought that was the purpose behind VACUUM FULL

[anarazel]

VACUUM FULL is about cleanup up things above the level of a single page. Moving stuff around within a page doesn't allow you to reclaim space on the OS level, nor does it "compact" tuples onto fewer pages.

But it's important for normal vacuum to compact the tuples on the page, otherwise the space of deleted tuples couldn't effectively be reused. Imagine a page that's entirely filled with 100 byte tuples, then every other tuple is deleted. Then, after a vacuum, a single 108 byte tuple should be inserted onto the page. Without compacting the space in the page during the vacuum, there would not be any space for that larger tuple.