Re: Recommendation to run vacuum FULL in parallel

On 2019-04-03 18:49:02 +0400, rihad wrote:
> 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.

And future updates can reuse it, too (an update is very similar to an
insert+delete).

> In case the number of updates is much greater than the number of
> inserts, the unused zombie space gradually creeps up.

Not if autovacuum has a chance to run between updates.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>