>>> 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