Re: big database with very small dump !?

Joao Ferreira gmail wrote:
> On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
>> On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
>>
>>> I'm finding it very strange that my pg takes 9Giga on disk but
>>> pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
>>> yesterday.
>> If you've been running VACUUM FULL, it's probably so-called "index bloat".
>> Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
>> figure out where all your space has gone inside the database.
>>
>
>
> egbert=# SELECT nspname || '.' || relname AS "relation",
> egbert-# pg_size_pretty(pg_relation_size(nspname || '.' || relname))
> AS "size"
> egbert-# FROM pg_class C
> egbert-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> egbert-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> egbert-# AND nspname !~ '^pg_toast'
> egbert-# AND pg_relation_size(nspname || '.' || relname)>0
> egbert-# ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> egbert-# LIMIT 20;
>
> relation | size
> ----------------------------------+---------
> public.timeslots_strs_var_ts_key | 5643 MB #this is a UNIQUE clause
> public.timeslots | 2660 MB #this is the only table
> public.timeslots_timestamp_index | 583 MB #this is an index
> public.timeslots_var_index | 314 MB #this is an index
> public.timeslots_timeslot_index | 275 MB "this is an index
> (5 rows)
>
>
> so it seems that the UNIQUE clause is taking up more space than the data
> itself...
>
> stil I have 2660 MB of data but the dump is about 10x smaller !!!
>
> any hints ?

I would try running a cluster on the table. This will usually clean up
things and free diskspace both in the table and the indexes.
It does require quite extensive locking though, so might not be an
option if you can't afford having the database unavailable for a few
(10-15) minutes.

--
Tommy Gildseth