Justin Clift <justin@postgresql.org> writes:
> Tom Lane wrote:
>> The deletion procedure could be triggered immediately upon removal of the
>> last item in a page, or when the next VACUUM scan finds an empty page.
>> Not sure yet which way is better.
> Having it triggered immediately upon removal of the last item in a page
> would make for a more "self maintaining" system wouldn't it? That
> sounds nice. :)
Maybe. This isn't about getting rid of VACUUM --- there's still a need
for routine maintenance vacuums. So the question really comes down to
whether it's more efficient to do it "in bulk" during routine
maintenance sweeps, or "retail". I'm not sold yet, but am leaning to
the bulk side.
>> In theory, if we find recyclable page(s) at the physical end of the index,
>> we could truncate the file (ie, give the space back to the filesystem)
>> instead of reporting these pages to FSM. I am not sure if this is worth
>> doing --- in most cases it's likely that little space can be released this
>> way, and there may be some tricky locking issues.
> Sounds like this would be beneficial for environments with high
> update/delete transaction volumes, perhaps on smaller amounts of
> live/valid data.
It would only really be worth doing if you made a substantial reduction
in the number of rows in a table, and had no near-term intention of
loading the table back up again. Seems like it might be sufficient to
tell people to REINDEX if they want the index size to drop in that
scenario. I will look at physically truncating the index during VACUUM,
but I don't think it's worth getting real tense about...
regards, tom lane