Excessive growth of pg_attribute and other system tables

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Excessive growth of pg_attribute and other system tables
Date: 2005-03-17 23:15:38
Message-ID: 200503171515.38214.scrawford@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Steve Crawford 2005-03-17 23:36:10 Re: Excessive growth of pg_attribute and other system tables
Previous Message Tom Lane 2005-03-17 22:43:40 Re: Cannot get postgres started on Fedora core 3

Browse pgsql-hackers by date

  From Date Subject
Next Message Eric Parusel 2005-03-17 23:19:31 corrupted tuple (header?), pg_filedump output
Previous Message Juan Pablo Espino 2005-03-17 23:14:11 Re: