On 5/1/06,
Martijn van Oosterhout <
kleptog@svana.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