Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)

On 11/09/2012 04:28 PM, Lists wrote:
> As I've spent a considerable amount of time trying to sort this out,
> I'm posting it for the benefit other users.

Having missed the earlier conversation....a couple comments:

> I've experienced persistent, ongoing issues with autovacuum in a mixed
> read/write environment with midrange hardware (16 core Xeon, 128 GB
> RAM, 200 GB SATA3 6 Gb SSDs for disk I/O on RHEL6) using the defaults
> in the 9.1 RPMs provided by Postgres. (yum.postgresql.org)

Don't do that. Defaults are good for ensuring that PostgreSQL will start
on the widest reasonable variety of systems. They are *terrible* for
performance and are certainly wrong for the system you describe.

> The cause of this is not yet determined. It may be related to the any
> or all of the combination of:
>
> A) extensive use of temp tables;
> B) extensive use of multiple dblink() calls in a single query;
> C) use of transactions, especially prepared transactions and
> multiple savepoints;
> D) concurrent use of pg_dump;
> E) use of numerous databases on a single server, average about 50;
>
> To offset this, we turned off autovacuum, and used an old script to
> vacuum the tables in the middle of the night when nobody was looking.
> Unfortunately, the vacuum script only vacuumed the "userland" tables
> and tremendous amounts of disk space were being wasted, particularly
> in the pg_attribute tables.

Bloat in pg_attribute would correlate with A) (or any constant
creation/destruction of tables). You can vacuum and/or reindex the
system tables if you are connected as the superuser but you are better
off preventing bloat by appropriate adjustment of your configuration
settings. However note that if you do frequent bulk creation/destruction
of tables you could end up bloating the attribute table between vacuum
runs and may need to periodically manually shrink it.

Cheers,
Steve