Horton

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Nathan Bossart <bossartn@amazon.com>

View thread
From: "Bossart, Nathan" <bossartn@amazon.com>
To: Andres Freund <andres@anarazel.de>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Julien Rouhaud <rjuju123@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, "Jamison, Kirk" <k.jamison@jp.fujitsu.com>, Amit Kapila <amit.kapila16@gmail.com>, "Fujii Masao" <masao.fujii@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-04T22:00:47Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add TRUNCATE parameter to VACUUM.

  2. Add vacuum_truncate reloption.

  3. Allow VACUUM to be run with index cleanup disabled.

On 3/4/19, 1:44 PM, "Andres Freund" <andres@anarazel.de> wrote:
> On 2019-03-04 21:40:53 +0000, Bossart, Nathan wrote:
>> On 3/4/19, 12:11 PM, "Andres Freund" <andres@anarazel.de> wrote:
>> > I'm not quite convinced this is right.  There's plenty sites that
>> > practically can't use autovacuum because it might decide to vacuum the
>> > 5TB index because of 300 dead tuples in the middle of busy periods.  And
>> > without an reloption that's not controllable.
>>
>> Wouldn't it be better to adjust the cost and threshold parameters or
>> to manually vacuum during quieter periods?
>
> No. (auto)vacuum is useful to reclaim space etc. It's just the
> unnecessary index cleanup that's the problem...  Most of the space can
> be reclaimed after all, the item pointer ain't that big...

I see what you mean.

>> I suppose setting DISABLE_INDEX_CLEANUP on a relation during busy
>> periods could be useful if you really need to continue reclaiming
>> transaction IDs, but that seems like an easy way to accidentally never
>> vacuum indexes.
>
> Yea, I do think that's a danger. But we allow disabling autovacuum, so
> I'm not sure it matters that much... And for indexes you'd still have
> the index page-level vacuum that'd continue to work.

I think the difference here is that there isn't something like
autovacuum_freeze_max_age to force index cleanup at some point.
Granted, you can set autovacuum_freeze_max_age to 2B if you want, but
at least there's a fallback available.

Nathan