Re: System catalog vacuum issues

Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> writes:
> On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
>> What pgstattuple shows on this table?

> dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
> table_len | tuple_count | tuple_len | tuple_percent |
> dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
> free_percent
> ------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
> 6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
> 204321460 | 3.21 | 5939017376 | 93.32
> (1 row)

So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space. I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses. There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you. Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.

regards, tom lane