Thread: pg_* files are too large for empty database.

Hello all,

today I've started to change the structure of one of my databases and
removed everything from it. Created new tables, come through database with
vacuumlo, say vacuum; vacuum analyze; I expected that I will have quite small
disk space occupied after this... And the result was a surprise...

Indices became VERY large even when table is empty!!!
-rw-------   1 postgres postgres        0 May 29 14:25 inbox
-rw-------   1 postgres postgres  3162112 May 29 14:25 inbox_id_key

And lots of pg_* indices are incredibly large. Like:

-rw-------   1 postgres postgres    90112 May 29 16:27 pg_attribute
-rw-------   1 postgres postgres 37920768 May 29 16:26 pg_attribute_relid_attnam_index
-rw-------   1 postgres postgres 15671296 May 29 16:26 pg_attribute_relid_attnum_index
-rw-------   1 postgres postgres    16384 May 29 16:27 pg_class
-rw-------   1 postgres postgres  4136960 May 29 16:26 pg_class_oid_index
-rw-------   1 postgres postgres 10846208 May 29 16:26 pg_class_relname_index

I know that I had quite big (>30000) amount of BLOBs.But there're only 5 of them inside now.
And this is what registered in pg_class.
Looks like bug in vacuum... Or I missed something?

BTW, Postgresql 7.0, Linux 2.2.15.

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Denis Perchine <dyp@perchine.com> writes:
> And lots of pg_* indices are incredibly large. Like:

Longstanding problem --- vacuum doesn't have any way to shrink indexes.
This is on the TODO list but there is lots of higher-priority work.

If you are cleaning out your database entirely anyway, I'd suggest just
dropping and recreating the whole database ...
        regards, tom lane