Обсуждение: PG13 Autovacuum for Insert

Поиск
Список
Период
Сортировка

PG13 Autovacuum for Insert

От
Raj kumar
Дата:
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 

Re: PG13 Autovacuum for Insert

От
Keith Fiske
Дата:


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


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

Re: PG13 Autovacuum for Insert

От
Ron
Дата:
On 7/8/21 8:38 AM, Keith Fiske wrote:
[snip]
> 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.

We've got INSERT-only tables, and the query planner only used sequential 
scans (on a freshly loaded 6TB database) until I vacuumed all tables,

-- 
Angular momentum makes the world go 'round.

Re: PG13 Autovacuum for Insert

От
Raj kumar
Дата:
Thanks Keith and Ron. Now, I understood better.

Thanks, 
Raj Kumar Narendiran.

On Thu, 8 Jul 2021, 20:29 Ron, <ronljohnsonjr@gmail.com> wrote:
On 7/8/21 8:38 AM, Keith Fiske wrote:
[snip]
> 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.

We've got INSERT-only tables, and the query planner only used sequential
scans (on a freshly loaded 6TB database) until I vacuumed all tables,

--
Angular momentum makes the world go 'round.