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.
>