Are you measuring index as well as table size? VACUUM FULL is no good
at compacting indexes.
I am measuring pg_total_relation_size which I believe includes indexes.
How does one go about compacting indexes if a VACUUM doesn't do the
trick? I see that a recommendation is to drop and recreate the indexes.
If one has a system running 24-7, then this might not be feasible.
The simplest way is to use REINDEX INDEX, but it needs a strong lock.
The more complex way is to do
CREATE INDEX CONCURRENTLY index_2 ... -- duplicating the original index
DROP INDEX index;
which does not need to grab a lock for a long period.
That does the trick and gets the table size down to what I'd expect from
a 'clean' run. Now I just need to run a few tests to work out what a
stable size is for a table with this many rows. Thanks for all the help
tracking this down. It's really appreciated :)
Kind regards
Stuart
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
