How to reduce disk usage and found where disk usage is used? + reindex force doesn`t seem to work

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to reduce disk usage and found where disk usage is used? + reindex force doesn`t seem to work
Date: 2005-04-26 20:53:34
Message-ID: 426EAA4E.8030406@siunik.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'll really appreciate any help to reduce the disk usage of
postgresql. I have a web site witch is data are refreshed each night.
Right now the disk usage is about 400 Megs but since I reload data all
nights it getting huge.

I do vacuum each time I am finished loading data.

I look into the db to found where space are used .. here is what I got
SELECT relname, (relpages*8)/1024 FROM pg_class ORDER BY relpages DESC;
relname ?column?
il_idx_1 155
il 83
vd_pk 67
il_pk 61
ic 2
ic_pk 0
iq 0

I see that index on IL (table with 4 column and ~1500000 rows) take
about 155 megs. Is that normal that IL has 83 megs but the index 155 ?

Vd_pk take about 67 megs but it the table is currently empty. Is that
means vacuum don`t shrinks indexes?

I tried a reindex with pgadminIII, here is the result. I noticed that
only system table were reindexed! Is that normal?

NOTICE: table "pg_class" was reindexed
NOTICE: table "pg_attrdef" was reindexed
NOTICE: table "pg_constraint" was reindexed
NOTICE: table "pg_description" was reindexed
NOTICE: table "pg_proc" was reindexed
NOTICE: table "pg_rewrite" was reindexed
NOTICE: table "pg_type" was reindexed
NOTICE: table "pg_attribute" was reindexed
NOTICE: table "pg_index" was reindexed
NOTICE: table "pg_operator" was reindexed
NOTICE: table "pg_opclass" was reindexed
NOTICE: table "pg_am" was reindexed
NOTICE: table "pg_amop" was reindexed
NOTICE: table "pg_amproc" was reindexed
NOTICE: table "pg_language" was reindexed
NOTICE: table "pg_aggregate" was reindexed
NOTICE: table "pg_trigger" was reindexed
NOTICE: table "pg_cast" was reindexed
NOTICE: table "pg_namespace" was reindexed
NOTICE: table "pg_conversion" was reindexed
NOTICE: table "pg_depend" was reindexed
NOTICE: table "pg_statistic" was reindexed
NOTICE: table "pg_inherits" was reindexed
NOTICE: table "pg_largeobject" was reindexed

I look at directory pg_xlog with is 164 megs. I change
checkpoint_segments to 1 in postgresql.conf to reduce this.

I have 2 questions:
-Is there any other stuff I can do to reduce disk space?
-What can I do to have à disk usage with is stable? Remember that I
flush/load data each night.

Thanks for your help
/David

Total query runtime: 141 ms.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Werner Bohl 2005-04-26 20:57:47 Re: plruby on Windows
Previous Message Scott Marlowe 2005-04-26 20:48:44 Re: PRIMARY KEY on a *group* of columns imply that each