Re: PG13 Autovacuum for Insert

On Thu, Jul 8, 2021 at 8:27 AM Raj kumar <rajkumar820999(at)gmail(dot)com> wrote:

> Hi all,
>
> In PG13, we have a new feature where autovacuum gets triggered based on
> Insert threshold. I wanted to know why we need autovacuum for Insert
> workloads, as there won't be any dead tuples because of them and we already
> have Auto analyze for inserts till PG12.
>
> Thanks,
> Raj Kumar Narendiran
>

Vacuum isn't just used for cleaning up dead rows from updates/deletes. The
biggest reason this was likely added was to account for transaction ID
exhaustion (aka wraparound). Newly inserted rows still get a new
transaction ID and need to be frozen at some point. If vacuum never runs,
that never happens until autovacuum_freeze_max_age is reached, which can
cause a slightly more expensive autovacuum to run. If many tables all reach
that point at the same time, it can cause a higher than usual spike in some
pretty heavy vacuuming activity. If normal autovacuum can kick in before
that, it can help spread that out better.

Vacuum also keeps the freespacemap and visibility maps updated as well. Not
quite as critical for insert-only tables, but it can still influence query
planning. Having an up to date visibility-map can help index-only scans
happen more frequently if possible.

This section of the documentation is a really good read for understanding
vacuuming and MVCC in PostgreSQL.

https://www.postgresql.org/docs/13/routine-vacuuming.html

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com