On Wed, Jul 15, 2009 at 4:03 PM, Wayne Conrad<wayne@databill.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.