Re: Automatic free space map filling

On 5/1/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
> On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote:
> > 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.
>
> AIUI, vacuum starts a fresh cycle because it's accumulated a certain
> number of dead tuples to clean up. Isn't that what you're asking for?
> maintenance_work_mem is the limit on the amount of deleted tuple
> information that can be stored (amongst other things I'm sure)...

Hmm, one idea, which may (or may not) be interesting for large
table vacuum is allowing a syntax similar to:

VACUUM table WHERE some_col > now()-'1 hour'::interval;

I.e. Let vacuum run "piggyback" on some index. This would allow
for a quick vacuum of a fraction of a large table. Especially when
the table is large, and only some data (new data) are being modified.

The vacuum for such a table would:
1. scan the index accoriding to the where criteria and create bitmap
of blocks to look at.
2. go through these blocks and vacuum them.

Hmm, another perhaps silly idea -- a special index kind for tracking
tuple deaths. Ie -- something like whenever tuple is updated/deleted,
insert an entry into such index, using last session the tuple is visible
for as a key. Then, perhaps, vacuum could scan such an index and
find tuples which are candidates for removal. I lack the knowledge of
PostgreSQL's internals, so forgive me if I am writing something
completely insane. :)

Regards,
Dawid