Re: Best practice when reindexing in production

On Wed, 2013-05-29 at 19:12 +0200, Jesper Krogh wrote:

> Second, if the new index is significantly smaller than the old on, I
> suggest that you try to crank up the autovacuum daemon instead of
> blindly dropping and creating indexes, this will help to mitigate the
> bloat you're seeing accumulating in above test.

In my experience vacuum/autovacuum just don't reclaim any space from the
indexes, which accumulate bloat indefinitely. I've tried to work around
that in so many ways: the show-stopper has been the impossibility to
drop FK indexes in a concurrent way, coupled with VALIDATE CONSTRAINT
not doing what advertised and taking an exclusive lock.

My solution has been to become pg_repack maintainer. YMMV. Just don't
expect vacuum to reduce the indexes size: it doesn't.

--
Daniele