Re: big database with very small dump !?

Hello Greg, Vlad, Scott and all,

thanks for the feedback.

O forgot to mention that I execute REINDEX on all tables and INDEXes
every week (right after executing VACUUM FULL).

Is this enough to eliminate the possibility of "index bloat" ?

and, yes, my database has some crazy indexes. I use these indexes, and I
keep them REINDEXed to keep query execution time down. see bellow.

could these indexes be the real reason for taking up all that space ?

thanks
joao

egbert=# \d timeslots;
Table "public.timeslots"
Column | Type | Modifiers
-----------+---------+-----------
str1 | text |
str2 | text |
...
...
str20 | text |
val1 | real |
...
...
val6 | real |
var | text |
count | integer |
total | real |
timeslot | integer | not null
timestamp | integer | not null
tsws | integer | not null
tses | integer | not null
Indexes:
"timeslots_strs_var_ts_key" UNIQUE, btree (str1, str2, str3, str4,
str5, str6, str7, str8, str9, str10, str11, str12, str13, str14, str15,
str16, str17, str18, str19, str20, var, timeslot) CLUSTER
"timeslots_timeslot_index" btree (timeslot)
"timeslots_timestamp_index" btree ("timestamp")
"timeslots_var_index" btree (var)

egbert=#
------------------------------------------------------------

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.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, M