Re: Berserk Autovacuum (let's save next Mandrill)

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Darafei Komяpa Praliaskouski <me(at)komzpa(dot)net>, Andres Freund <andres(at)anarazel(dot)de>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Michael Banck <mbanck(at)gmx(dot)net>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Date: 2020-03-03 15:28:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Mon, 2020-03-02 at 14:57 +0100, I wrote:
> But I think it would be good to have *something* that addresses the immediate
> problem (INSERT-only tables are autovacuumed too late), as long as
> that does not have negative side-effects or blocks further improvements.
> I don't feel totally well with the very simplistic approach of this
> patch (use the same metric to trigger autoanalyze and autovacuum),
> but what about this:
> - a new table storage option autovacuum_vacuum_insert_threshold,
> perhaps a GUC of the same name, by default deactivated.
> - if tabentry->tuples_inserted exceeds this threshold, but not one
> of the others, lauch autovacuum with index_cleanup=off.

As a more substantial base for discussion, here is a patch that:

- introduces a GUC and reloption "autovacuum_vacuum_insert_limit",
default 10000000

- introduces a statistics counter "inserts_since_vacuum" per table
that gets reset to 0 after vacuum

- causes autovacuum to run without cleaning up indexes if
inserts_since_vacuum > autovacuum_vacuum_insert_limit
and there is no other reason for an autovacuum

No doc patch is included yet, and perhaps the new counter should
be shown in "pg_stat_user_tables".

Laurenz Albe

Attachment Content-Type Size
0001-Autovacuum-tables-that-have-received-only-inserts.patch text/x-patch 12.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2020-03-03 15:57:16 Re: Minor issues in .pgpass
Previous Message Julien Rouhaud 2020-03-03 15:24:59 Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?