чт, 28 мар. 2019 г. в 01:01, Alvaro Herrera <alvhe...@2ndquadrant.com>:
> On 2019-Mar-28, Darafei "Komяpa" Praliaskouski wrote: > > > > чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera <alvhe...@2ndquadrant.com>: > > > > > On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote: > > > > * 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. > > Well, I think it would be useful to set all-visible earlier than waiting > for a vacuum to be necessary, even for tables that are not append-only. > So if you think about this just for the append-only table, you leave > money on the table. > Thing is, problem does not exist for non-append-only tables, they're going to be vacuumed after 50 rows got updated, automatically. > > > > * 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. > > OK, then start freezing tuples in the cheap mode (skip index updates) > earlier than that. I suppose a good question is when to start. > Attached (autovacuum_berserk_v1.patch) code achieves that. For append-only tables since https://commitfest.postgresql.org/16/952/ vacuum skips index cleanup if no updates happened. You just need to trigger it, and it already will be "cheap". -- Darafei Praliaskouski Support me: http://patreon.com/komzpa
