Re: big database with very small dump !?

On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:

> It's likely you've got index bloat.  If you reload a pg_dump of the
> database in question into another server how much space does that take
> up?

right. just loaded the dump into a clean database and everything came
down about 10 times...
----------------------------------------------
NOW: (injected dump into fresh Pg):
             relation             |  size
----------------------------------+--------
 public.timeslots                 | 549 MB
 public.timeslots_strs_var_ts_key | 482 MB
 public.timeslots_var_index       | 59 MB
 public.timeslots_timeslot_index  | 37 MB
 public.timeslots_timestamp_index | 37 MB
(5 rows)


------------------------------------------------
BEFORE:
             relation             |  size
----------------------------------+---------
 public.timeslots_strs_var_ts_key | 5643 MB
 public.timeslots                 | 2660 MB
 public.timeslots_timestamp_index | 583 MB
 public.timeslots_var_index       | 314 MB
 public.timeslots_timeslot_index  | 275 MB


I'm confused here....

on the fresh database the whole set only takes 1.3G

on the original db, even after VACUUM FULL and REINDEX it takes 9G.

can I really do anything about it ?

If I try cluster, I'm guessing I'll choose the big index and forget
about the smaller ones... is this right ?

thanks
joao


thx


>  Look into using CLUSTER or REINDEX to fix the space usage.
>