On 04/03/2019 06:40 PM, Michael Lewis wrote:
"Sometimes a table's usage pattern involves much more updates than
inserts, which gradually uses more and more unused space that is never
used again by postgres, and plain autovacuuming doesn't return it to the
OS."
Can you expound on that? I thought that was exactly what autovacuum
did for old versions of rows whether dead because of delete or update,
so I am surprised by this statement. I thought vacuum full was only
ever needed if storage space is an issue and the table is not expect
to quickly re-expand to current size on disk from new churn of tuples.
From what I understand from the docs updates keep older versions of
rows intact because other transactions might still use them (this is the
essence of MVCC), and autovacuuming (plain VACUUM) marks that space as
available when it is run, so future inserts can reuse it. In case the
number of updates is much greater than the number of inserts, the unused
zombie space gradually creeps up.
