On Wed, Jul 15, 2009 at 4:03 PM, Wayne Conrad<wayne@xxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
