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 ? > -- > * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD >
