Re: drop database, vacuum full and disk space

On Thu, Feb 28, 2008 at 11:17 AM, Antonio Grassi <agrassic@gmail.com> wrote:
> Hi list. I've searched the archives and I've seen a lot of posts related to
> database size, vacuuming, etc., but I can't totally understand this.
>
> I've a small database of about 300Mb (when initially loaded), whose data is
> almost completely replaced in the night (via various "drop table" and the
> load of dumps of such tables from other postgresql installation).
>
> Altough I've autovacuum activated, the size of this database is now about
> 3Gb. I've also full vacuumed the base by hand from time to time since the
> base was created, but that didn't help either.
>
> Yesterday I recreated the database with another name, thus having database A
> with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran
> vacuumdb -af. But the space used by postgresql didn't go down. This makes me
> think that vacuum full just releases pages associated to existent database
> objects, is this correct?

If you drop all but the template0/1 databases, I'm guessing you'll
still see 3G used, because it sounds like you've got bloat in your
system tables, like pg_class etc.

Just guessing.  You can use oid2name and du -sh to get an idea how
much each db dir is using.