I'm having trouble with physical growth of postgresql system tables.
Server is 7.4.6 and there are several databases in the cluster. The
autovacuum daemon has been running since the data was restored after
an upgrade a few months ago. Unfortunately my system tables are
taking an unreasonable amount of space.
For example, on one of the databases pg_attribute holds fewer than
10,000 records but is using more than 600 megabytes and the
associated indexes are huge, too. Reindexing dropped the total usage
for that database from 3.2G to 2.5G and a vacuum full (when I can do
it off hours) will probably drop it to around 1.9G. In other words,
one system table alone was accounting for around 40% of the storage
used by that database.
Now that 1.9G still includes other oversized files like pg_index for
which the table alone dropped from 48M to 78K with vacuum full.
Vacuum full + index on a selection of other tables yielded savings of:
pg_depend: 200M
pg_type: 120M
pg_class: 50M
My autovacuum config is running and I do see regular periodic vacuums
of these pg_ tables but still they grow.
Any ideas on why, in spite of autovacuum, these files are becoming so
huge and, more importantly, the best way to keep them under control.
Cheers,
Steve