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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
