Disk space efficient foreign key index for insert intensive scientific database?

Posted on

Question :

I’m working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.

Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn’t helping the disk space issues.

Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?

Answer :

The referencing columns don’t need to be indexed at all. If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn’t matter.

Leave a Reply

Your email address will not be published. Required fields are marked *