Re: Huge size of Data directory

On Wed, Jan 18, 2006 at 04:02:27PM -0600, Anthony Presley wrote:
> Our postgresql database has been churning along just fine, albeit a little
> slower than I'd like.
>
> However, more surprising to me is that it has (over the past few years)
> become HUGE.  It's currently 606MB.  Almost all of this space is sitting
> in data/base/16556 and data/pg_xlog.
>
> The problem I have with this space ... is that I only have about 8MB of
> data stored in the database (according to pg_dump and pg_dumpall).

Have you been vacuuming regularly?  What's the output of the following
command?

SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database;

If you haven't been vacuuming then your tables and indexes are
probably bloated, which could also account for the unsatisfactory
performance.  You can use VACUUM FULL or CLUSTER to recover the
wasted space; then be sure to schedule regular vacuums to avoid
bloat.  See "Routine Database Maintenance Tasks" in the documentation
for more information.

> wal_files = 16

Uh...what version of PostgreSQL are you running?  wal_files was
removed in 7.3; if you're running a version older than that then
you should certainly upgrade.

--
Michael Fuhr