>
>> If you suspect your tables or indexes are bloated, restore your
>> dump to a test box.
>> Use fsync=off during restore, you don't care about integrity on the
>> test box.
>> This will avoid slowing down your production database.
>> Then look at the size of the restored database.
>> If it is much smaller than your production database, then you have
>> bloat.
>
> I have done that, and I get the following:
>
> the live one is 113G
> the restored one is 78G
Ah.
Good news for you is that you know that you can do something ;)
Now, is the bloat in the tables (which tables ?) or in the indexes (which
indexes ?), or in the toast tables perhaps, or in the system catalogs or
all of the above ? Or perhaps there is a long-forgotten process that got
zombified while holding a huge temp table ? (not very likely, but who
knows).
Use pg_relation_size() and its friends to get an idea of the size of
stuff.
Perhaps you have 1 extremely bloated table or index, or perhaps
everything is bloated.
The solution to your problem depends on which case you have.