Re: Poor overall performance unless regular VACUUM FULL

On Wed, Jul 15, 2009 at 4:03 PM, Wayne Conrad<wayne(at)databill(dot)com> wrote:
>>> On Tue, 14 Jul 2009, Scott Marlowe wrote:
>>
>> Are you guys doing anything that could be deemed pathological, like
>> full table updates on big tables over and over?  Had an issue last
>> year where a dev left a where clause off an update to a field in one
>> of our biggest tables and in a few weeks the database was so bloated
>> we had to take it offline to fix the problem.  After fixing the
>> query.
>
> I've just audited the source, looking for any updates without where
> clauses.  None jumped out to bite me.
>
> Almost everything we do happens in transactions which can occasionally
> take 10-20 minutes to complete and span thousands or tens of thousands
> of rows across multiple tables.  Are long-running transactions a
> culprit in table bloat?
>
> I've also used contrib/pgstattuple to try to identify which of our
> large tables and indices are experiencing bloat.  Here are the
> pgstattuple results for our largest tables:

Ouch hurts my eyes :) Can you see something like table_len,
dead_tuple_percent, free_percent order by dead_tuple_percent desc
limit 10 or something like that maybe?

>
> table_len:          56639488
> tuple_count:        655501
> tuple_len:          53573112
> tuple_percent:      94.59
> dead_tuple_count:   0
> dead_tuple_len:     0
> dead_tuple_percent: 0
> free_space:         251928
> free_percent:       0.44
> table_name:         status
Lots more rows deleted.