Re: big database with very small dump !?

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@gregsmith.com http://www.gregsmith.com Baltimore, MD
>