Date: Mon, 16 Mar 2020 16:07:57 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
	David Rowley <dgrowleyml@gmail.com>,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>,
	Amit Kapila <amit.kapila16@gmail.com>,
	Masahiko Sawada <sawada.mshk@gmail.com>,
	Darafei =?utf-8?Q?Kom=D1=8Fpa?= Praliaskouski <me@komzpa.net>,
	Andres Freund <andres@anarazel.de>,
	PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Michael Banck <mbanck@gmx.net>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20200316210757.GO26184@telsasoft.com>
References: 
 <CA+fd4k6DJVU-TzOedMsDmynz7O4fXd9ZNEd19HzCstk=X1PWYg@mail.gmail.com>
 <CAApHDvoUhTFWCGmhfwct6qcXUe1BjaN9KvMEricbEShetZhAQQ@mail.gmail.com>
 <9c312999bb8e0495b88d1422980ecc0abc1b1b86.camel@cybertec.at>
 <CA+fd4k7J95OYDTJn5FT5szPgciwyDGbwP7jkc6FPp63i4a8j6Q@mail.gmail.com>
 <CAApHDvq=Gaycio_60j9bHs3CTgkeLkUCH5kbyHbVuYryQbDfpA@mail.gmail.com>
 <CA+fd4k6MhGk95y53k-tLRB9zz5fecwiJhTrma9p0b3=rDaEr7g@mail.gmail.com>
 <CAApHDvrACF7jB_hbq75XiaRtFRUq9BcXm=f15icu3+Q9xD+YQA@mail.gmail.com>
 <CA+fd4k5HwScjcUbjSuZXWyU6JNENsOebktdi1ft39rJqLZ9N2A@mail.gmail.com>
 <20200316124713.GH26184@telsasoft.com>
 <9c9b7d19d17ad5e4e0ee10b8426fa1b78dd24c0a.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <9c9b7d19d17ad5e4e0ee10b8426fa1b78dd24c0a.camel@cybertec.at>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Mon, Mar 16, 2020 at 08:49:43PM +0100, Laurenz Albe wrote:
> On Mon, 2020-03-16 at 07:47 -0500, Justin Pryzby wrote:
> > It seems to me that the easy thing to do is to implement this initially without
> > FREEZE (which is controlled by vacuum_freeze_table_age), and defer until
> > July/v14 further discussion and implementation of another GUC/relopt for
> > autovacuum freezing to be controlled by insert thresholds (or ratio).
> 
> Freezing tuples is the point of this patch.
> As I have said, if you have a table where you insert many rows in few
> transactions, you would trigger an autovacuum that then ends up doing nothing
> because none of the rows have reached vacuum_freeze_table_age yet.
> 
> Then some time later you will get a really large vacuum run.

Best practice is to vacuum following bulk load.  I don't think this patch is
going to change that.  Bulk-loaded tuples will be autovacuumed, which is nice,
but I don't think it'll be ideal if large bulk loads trigger an autovacuum with
cost delays which ISTM if it runs with FREEZE will take even longer.

If it's a bulk load, then I think it's okay to assume it was vacuumed, or
otherwise that it'll eventually be hit by autovac at some later date.

If it's not a "bulk load" but a normal runtime, and the table continues to
receive inserts/deletes, then eventually it'll hit a vacuum threshold and
tuples can be frozen.

If it receives a bunch of activity, which then stops (like a partition of a
table of timeseries data), then maybe it doesn't hit a vacuum threshold, until
wraparound vacuum.  I think in that case it's not catastrophic, since then it
wasn't big enough to hit any threshold (it's partitioned).  If every day,
autovacuum kicks in and does wraparound vacuum on table with data from (say)
100 days ago, I think that's reasonable.

One case which would suck is if the insert_threshold were 1e6, and you restore
a DB with 1000 tables of historic data (which are no longer being inserted
into) which have 9e5 rows each (just below the threshold).  Then autovacuum
will hit them all at once.  The solution to that is to manual vacuum after bulk
load, same as today.  As a practical matter, some of the tables are likely to
hit the autovacuum insert threshold, and some are likely to be pruned (or
updated) before wraparound vacuum, so the patch usually does improve that case.

-- 
Justin