Re: growing disk usage problem: alternative solution?

In article <DED7310D-6DE3-468C-8687-1304522D3AE0(at)khera(dot)org>,
Vivek Khera <vivek(at)khera(dot)org> wrote:
%
% On Jun 26, 2007, at 3:31 PM, Bill Moran wrote:
%
% > VACUUM FULL and REINDEX are not required to maintain disk usage.
% > Good old-
% > fashoned VACUUM will do this as long as your FSM settings are high
% > enough.
% >
%
% I find this true for the data but not necessarily for indexes. The
% other week I reindexed a couple of O(100,000,000) row tables and
% shaved about 20Gb of index bloat. Those tables are vacuumed
% regularly, but we do a large data purge every few weeks. I think
% that causes some issues. I'm running 8.1.

If you have an index on some monotonically increasing field (i.e., a
sequence or date), and you purge by deleting from the low end of this
index, then that space won't be reclaimed by vacuum. Vacuum full won't
help, either. You (only) need to rebuild the affected indices.
--

Patrick TJ McPhee
North York Canada
ptjm(at)interlog(dot)com