Re: Unexpectedly high disk space usage

On 11/07/2012 12:58 PM, Scott Marlowe wrote:
> My experience is that if autovac is causing problems with stalled
> queries etc you're either A: running ancient pg versions (pre 8.3), B:
> Running WAY too aggressive settings in autovac (100 threads, no nap
> time, cost limit of 100000 etc.) or C: Your IO subsystem is absolute
> crap. On any modern server, default autovac settings from 8.3 and on
> should only have the possible problem of not being tuned aggressively
> enough.

A) We are running PG 9.1.

B) We used the default settings in the RPMs provided by
yum.postgresql.org. At the bottom of this message is information about
the RPMs we currently are using.

C) I/O subsystem for PG is twin SATA III SSDs in a RAID 1 configuration,
capable of tens of thousands of IO operations per second. Servers are
recent, SATA III, 16-core Xeons with 128 GB ECC RAM in 1U rackmount cases.

As stated previously, we make extensive use of temp tables,
transactions, and dblink, but had no trouble with catalog table bloat in
8.x; this is a new phenomenon for us.

# rpm -qi postgresql91-9.1.5-3PGDG.rhel6.x86_64
Name        : postgresql91                 Relocations: (not relocatable)
Version     : 9.1.5                             Vendor: (none)
Release     : 3PGDG.rhel6                   Build Date: Sun 02 Sep 2012
12:13:18 PM UTC
Install Date: Wed 12 Sep 2012 03:04:24 AM UTC      Build Host:
koji-sl6-x86-64-pg91
Group       : Applications/Databases        Source RPM:
postgresql91-9.1.5-3PGDG.rhel6.src.rpm
Size        : 5193673                          License: PostgreSQL
Signature   : DSA/SHA1, Sun 02 Sep 2012 12:13:20 PM UTC, Key ID
1f16d2e1442df0f8
URL         : http://www.postgresql.org/
Summary     : PostgreSQL client programs and libraries
Description :
PostgreSQL is an advanced Object-Relational database management system
(DBMS) that supports almost all SQL constructs (including
transactions, subselects and user-defined types and functions). The
postgresql package includes the client programs and libraries that
you'll need to access a PostgreSQL DBMS server.  These PostgreSQL
client programs are programs that directly manipulate the internal
structure of PostgreSQL databases on a PostgreSQL server. These client
programs can be located on the same machine with the PostgreSQL
server, or may be on a remote machine which accesses a PostgreSQL
server over a network connection. This package contains the command-line
utilities for managing PostgreSQL databases on a PostgreSQL server.

If you want to manipulate a PostgreSQL database on a local or remote
PostgreSQL
server, you need this package. You also need to install this package
if you're installing the postgresql91-server package.