Re: Automatic free space map filling

On Fri, Apr 28, 2006 at 03:58:16PM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > So for you it would certainly help a lot to be able to vacuum the first
> > > X pages of the big table, stop, release locks, create new transaction,
> > > continue with the next X pages, lather, rinse, repeat.
> >
> > > This is perfectly doable, it only needs enough motivation from a
> > > knowledgeable person.
> >
> > Bruce and I were discussing this the other day; it'd be pretty easy to
> > make plain VACUUM start a fresh transaction immediately after it
> > finishes a scan heap/clean indexes/clean heap cycle. The infrastructure
> > for this (in particular, session-level locks that won't be lost by
> > closing the xact) is all there. You'd have to figure out how often to
> > start a new xact ... every cycle is probably too often, at least for
> > smaller maintenance_work_mem settings ... but it'd not be hard or
> > involve any strange changes in system semantics.
>
> Should this be a TODO? One item of discussion was taht people should
> just increase their workmem so the job can be done faster in larger
> batches.

Except that wouldn't help when vacuuming a lot of small tables; each one
would get it's own transaction.

ISTM that tying this directly to maintenance_work_mem is a bit
confusing, since the idea is to keep vacuum transaction duration down so
that it isn't causing dead tuples to build up itself. It seems like it
would be better to have vacuum start a fresh transaction after a certain
number of tuples have died. But since there's no way to actually measure
that without having row level stats turned on, maybe number of
transactions or length of time would be good surrogates.

Since it sounds like we'd want the transaction to start only at the
start of a clean cycle it could just check the limits at the start of
each cycle. That would prevent it from wrapping the vacuum of each small
table with a (rather pointless) new transaction.
--
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