Re: PG13 Autovacuum for Insert

[Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>]

On Thu, Jul 8, 2021 at 8:27 AM Raj kumar <rajkumar820999@xxxxxxxxx> 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