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

Hi,

чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:

> On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote:
>
> > Attached is sketch of small patch that fixes several edge cases with
> > autovacuum. Long story short autovacuum never comes to append only
> tables,
> > killing large productions.
>
> Yeah, autovac is not coping with these scenarios (and probably others).
> However, rather than taking your patch's idea verbatim, I think we
> should have autovacuum use separate actions for those two (wildly
> different) scenarios. For example:
>
> * certain tables would have some sort of partial scan that sets the
> visibility map. There's no reason to invoke the whole vacuuming
> machinery. I don't think this is limited to append-only tables, but
> rather those are just the ones that are affected the most.
>

What other machinery runs on VACUUM invocation that is not wanted there?
Since Postgres 11 index cleanup is already skipped on append-only tables.

> * tables nearing wraparound danger should use the (yet to be committed)
> option to skip index cleaning, which makes the cleanup action faster.
> Again, no need for complete vacuuming.
>

"Nearing wraparound" is too late already. In Amazon, reading table from gp2
after you exhausted your IOPS burst budget is like reading a floppy drive,
you have to freeze a lot earlier than you hit several terabytes of unfrozen
data, or you're dead like Mandrill's Search and Url tables from the link I
shared.

>
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa