Re: Default autovacuum settings too conservative

On Tue, Feb 07, 2006 at 01:39:34PM +0100, Markus Schaber wrote:
> Hi, Jim,
>
> Jim C. Nasby wrote:
>
> > What we really need is a replacement for vacuum_delay that takes
> > PostgreSQL generated IO activity into account...
>
> There are also other ideas which can make vacuum less painfull:
>
> - Use a "delete"-map (like the free space map) so vacuum can quickly
> find the pages to look at.

Already on TODO.

> - Have vacuum end its transaction after a certain amount of work, and
> restart at the same page later.

AFAIK this isn't possible with the current way vacuum works.

> - Have vacuum full search good candidates with non-stopping lock (and
> usage of delete-map and fsm), then doing {lock, recheck, move, unlock}
> in small amounts of data with delay between.

This isn't an issue of locks, it's an issue of long-running
transactions. It *might* be possible for vacuum to break work into
smaller transactions, but I'm pretty sure that would be a non-trivial
amount of hacking.

> - Introducing some load measurement, and a pressure measurement (number
> of deleted rows, TID wraparound etc.). Then start vacuum when load is
> low or pressure is very high. Tune other parameters (like "certain
> amount of work" depending on those measures.

Which is essentially what I was suggesting...

> All of them are a lot of code to hack, but although I'm not a postgresql
> core developer, I am keen enough to invite you to send patches. :-)

Well, if you know C then you're already 1 step closer to being able to
change these kinds of things than I am.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461