Re: Unexpectedly high disk space usage

Lists <lists@benjamindsmith.com> writes:
>   pg_catalog.pg_attribute                                  | 36727480320

Ouch.

> Our current process is that every night in the middle of the night, a
> script connects to each database on each server and runs a query to get
> all tables in each database and, for each, run
> "VACUUM ANALYZE $table"
> for each table in the database.
> (note: there is a database for the "postgres" user on each DB server)
> The script is a remnant from PG 8.x days, so am I missing something
> fundamental about 9.x? I will note that autovacuum is off ...

So you've turned off autovacuum, and are carefully not vacuuming the
system catalogs.  That's your problem all right.  Is there a
particularly good reason why this script isn't a one-liner "VACUUM"?
Are you sure that once-a-day vacuuming is sufficient, even if it was
covering the system catalogs?  If you've managed to bloat pg_attribute
to 36GB, I suspect you've got enough churn (perhaps from temp tables)
that you really need the catalogs vacuumed more often.

My advice is dump, reload, and *don't* turn off autovacuum.

> ... because it
> occasionally causes transactions and queries to hang when an update
> causes a vacuum mid-day, effectively taking us offline randomly.

I suspect this claim is based on ancient and no longer very relevant
experience.

            regards, tom lane