While replying to the "Improving count(*)" thread, the following occured
to me:
Vacuuming a table is only useful if we're nearing xid-wrap or if new
tuples are being created in the table. One way to detect the later case
is to monitor how many pages that table has in the FSM. Of course
there's other ways to do this, but I think there's a distinct advantage
to monitoring FSM: it allows us to vacuum at the rate that the space
marked as being available by a vacuum is actually being used. So for
example, we could set a threshold of keeping X pages is the FSM for each
table. When the number of pages in the FSM falls below X for a table, a
vacuum would be run against that table. But if we only want X pages in
the FSM for that table, we could stop the vacuum once we reach X pages
in the FSM.
Unfortunately, I think that might leave us pretty succeptable to index
bloat from deleted tuples, but maybe there's some clever way around
that. If the proposal to track heap block-level metadata happens, that
might make this idea a lot more doable.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461