pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG13 Autovacuum for Insert
4+ messages / 3 participants
[nested] [flat]
* PG13 Autovacuum for Insert
@ 2021-07-08 12:27 Raj kumar <rajkumar820999@gmail.com>
2021-07-08 13:38 ` Re: PG13 Autovacuum for Insert Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 4+ messages in thread
From: Raj kumar @ 2021-07-08 12:27 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG13 Autovacuum for Insert
2021-07-08 12:27 PG13 Autovacuum for Insert Raj kumar <rajkumar820999@gmail.com>
@ 2021-07-08 13:38 ` Keith Fiske <keith.fiske@crunchydata.com>
2021-07-08 14:59 ` Re: PG13 Autovacuum for Insert Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Keith Fiske @ 2021-07-08 13:38 UTC (permalink / raw)
To: Raj kumar <rajkumar820999@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
https://www.postgresql.org/docs/13/routine-vacuuming.html
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG13 Autovacuum for Insert
2021-07-08 12:27 PG13 Autovacuum for Insert Raj kumar <rajkumar820999@gmail.com>
2021-07-08 13:38 ` Re: PG13 Autovacuum for Insert Keith Fiske <keith.fiske@crunchydata.com>
@ 2021-07-08 14:59 ` Ron <ronljohnsonjr@gmail.com>
2021-07-08 15:08 ` Re: PG13 Autovacuum for Insert Raj kumar <rajkumar820999@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ron @ 2021-07-08 14:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PG13 Autovacuum for Insert
2021-07-08 12:27 PG13 Autovacuum for Insert Raj kumar <rajkumar820999@gmail.com>
2021-07-08 13:38 ` Re: PG13 Autovacuum for Insert Keith Fiske <keith.fiske@crunchydata.com>
2021-07-08 14:59 ` Re: PG13 Autovacuum for Insert Ron <ronljohnsonjr@gmail.com>
@ 2021-07-08 15:08 ` Raj kumar <rajkumar820999@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Raj kumar @ 2021-07-08 15:08 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-07-08 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-08 12:27 PG13 Autovacuum for Insert Raj kumar <rajkumar820999@gmail.com>
2021-07-08 13:38 ` Keith Fiske <keith.fiske@crunchydata.com>
2021-07-08 14:59 ` Ron <ronljohnsonjr@gmail.com>
2021-07-08 15:08 ` Raj kumar <rajkumar820999@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox