Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

On 25/09/2007, Vivek Khera <vivek(at)khera(dot)org> wrote:

> Recommending I run vacuum intermixed with the data purge is a non-
> starter; the vacuum on these tables takes a couple of hours. I'd
> never finish purging my data with that kind of delay.

...
> I will investigate the fill-factor. That seems like it may make some
> sense the way I do inserts and updates...

Undoubtedly.

But if most of your indexed keys are gone, then a reindex is useful.
If this is a hugely live system and you don't have a great number of
indexes, then a somewhat kludgish way to try could be to create a copy
of the table, do what you wish with it (delete rows, index them, then
cluster them on that index)...and whenever the process finishes (3
hours, or 3 days...no matter, because it doesn't hurt your live
system), you simply rename the old table to TABLE_OLD and the new
table to TABLE. The renaming operation is instant.

Anyway, what is your maintenance_work_mem? Try increasing your
maintenance_work_mem and see if that helps vacuuming first. Vacuum
operations can be sped up dramatically. We need regular vacuums and
that is critical to our application, so I have a m_w_m of 512K.

Mind you -- even if your DB vacuums for a couple hours, vacuum doesn't
affect the performance of your live system while it is happening, so
frequent vacuuming cannot hurt you one way or another, and it can
surely help.