Обсуждение: Need Advice In Improving Database Disc Usage

Hello,

We are now planning to move over, from plan text file storage to database storage. However, we do see our disc usage
increasedby factor of 50. 

http://sites.google.com/site/yanchengcheok/Home/du.PNG

(1) Is there any need for me to create idx_fk_measurement_type_id and idx_fk_measurement_unit_id

The above 2 are the index for measurement's foreign key. However, these 2 foreign key are only used in JOIN statement,
butnever used in WHERE statement. So, if I remove the 2 index, will I still achieve the same query performance? 

(2) How can I increase the compression ratio for my database?

Thanks and Regards
Yan Cheng CHEOK

On Tue, Feb 2, 2010 at 11:00 PM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:
> (1) Is there any need for me to create idx_fk_measurement_type_id and idx_fk_measurement_unit_id
>
> The above 2 are the index for measurement's foreign key. However, these 2 foreign key are only used in JOIN
statement,but never used in WHERE statement. So, if I remove the 2 index, will I still achieve the same query
performance?

For the following situation

create table a (
  a_id PRIMARY KEY,
  baz INTEGER
);

create table b (
  b_id PRIMARY KEY,
  a_id INTEGER REFERENCES a(a_id) ON DELETE CASCADE
);

you only want to have an index on b.a_id IF you delete a_id from table
a regularly.  That is, when the reverse of the FK is tested during the
delete, you want it to be fast to find and delete the referring rows
as well (the CASCADE).  If you never or rarely do such a delete, and
can live with doing a sequence scan on table b for such deletes, then
you don't need to waste space and time with the index.

>
> (2) How can I increase the compression ratio for my database?

Whatever compression happens is automatic.

Sound like you may need to investigate how often your indexes are used
and perhaps prune some of them.  That and be sure you run vacuum
regularly to keep the tables from bloating too much.  Have you done
bulk deletes and re-loads of your DB?