Re: Disk space problem...

From: Ken Corey <ken(dot)corey(at)atomic-interactive(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Disk space problem...
Date: 2001-12-03 09:05:50
Message-ID: 01120309055004.09040@kenlinux.bithub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Saturday 01 December 2001 5:20 pm, Tom Lane wrote:
> Consider TRUNCATE.
>
> I suspect if you look at the stats (see pg_class.relpages) you'll find
> that the space VACUUM fails to reclaim is in indexes. Possibly you've
> gone overboard on adding indexes to your database; are there any that
> you don't need?

Amazing! Once again, you've hit it on the head.

Thanks for pointing out pg_class...being able to see the sizes of things in
the database allowed me to directly see the changes in size as I played.

The indexes on the table which had all its rows removed and rebuilt was
indeed the root of the problems I was seeing.

I changed my statistics update script to drop all indexes, truncate the
table, rebuild the table, and then rebuild the indexes, and have seen
dramatically reduced size, and dramatically increased speed. (And my vacuum's
even run faster!)

I'm back down to 380MB to hold my data online, too.

Thanks, Tom!

--
Ken Corey, CTO Atomic Interactive, Ltd. ken(dot)corey(at)atomic-interactive(dot)com

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Burra 2001-12-03 13:44:17 LISTEN & NOTIFY data
Previous Message Nathan Wilson 2001-12-02 19:38:16 --with-python under MacOSX