Re: Bloated tables and why is vacuum full the only option

On Sun, Feb 9, 2014 at 12:50 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 7.2.2014 19:47, Claudio Freire wrote:
>>
>> Question is... why isn't all that free space being used? The table
>> grows in size even though there's plenty (65%) of free space.
>>
>> I've got autovacuum severely crippled and that could be a reason, but
>> I do perform regular vacuum runs weekly that always run to completion.
>> I also do routine reindexing to stop index bloat on its tracks, yet
>> freshly-reindexed indexes get considerably reduced in size with vacuum
>> full.
>
> Are you logging autovacuum actions? I.e. what is
>
>     log_autovacuum_min_duration
>
> set to? It it's set to -1 you won't get any messages because of
> conflicting locks or stuff like that, which might be the culprit here.

It was set to -1. I set it to 5000 and I'll be keeping an eye on the logs.

> Also, when you're running the weekly VACUUM, do VACUUM (VERBOSE) and
> post it here. That might at least help us eliminate some of the usual
> suspects.

I'm using a cron job for this, I'll see about dumping the results to a
log file and post when it's done.