| From: | Andres Freund <andres(at)anarazel(dot)de> |
|---|---|
| To: | "Bossart, Nathan" <bossartn(at)amazon(dot)com> |
| Cc: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>, Julien Rouhaud <rjuju123(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "Jamison, Kirk" <k(dot)jamison(at)jp(dot)fujitsu(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: reloption to prevent VACUUM from truncating empty pages at the end of relation |
| Date: | 2019-03-04 21:43:50 |
| Message-ID: | 20190304214350.jwzuktvrgn3qzzlc@alap3.anarazel.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
On 2019-03-04 21:40:53 +0000, Bossart, Nathan wrote:
> On 3/4/19, 12:11 PM, "Andres Freund" <andres(at)anarazel(dot)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 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.
- Andres
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Justin Pryzby | 2019-03-04 21:45:01 | Re: Question about pg_upgrade from 9.2 to X.X |
| Previous Message | Bossart, Nathan | 2019-03-04 21:40:53 | Re: reloption to prevent VACUUM from truncating empty pages at the end of relation |