Re: Recommendation to run vacuum FULL in parallel

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.