TL,DR
What is the accepted best practice in scientific computing circles for storing large quantities of hierarchically structured data? For example, SQL does not play nicely with large sparse matrices. Is there a good tool out there for structuring, warehousing, and analyzing this kind of data? What do the guys at the LHC use?
Use case details
I want to store data from protein simulations according to the following hierarchy:
protein
|__simulation conditions
|____|__residues
|____|____|__conformers
|____|____|____|__atoms
Every protein should be aware of each of its residues, every atom should know the conditions used for its simulation, etc. and vice versa.
Originally I figured that a relational database would be perfect for this application, and so I wrote a program using python and sqlalchemey that stores the data in an SQL database. In practice, however, this program is not working so well.
The biggest issue relates to the fact that there is an N x N matrix at the conformer data level that stores the potential energy due to the pairwise interactions between every possible pair of conformers. Most of the entries in the matrix are zeros, so I'm storing the matrix in a separate table in the database in a kind of sparse format, one row per entry. Unfortunately, for a simulation involving several thousand conformers the pairwise table still ends up with several hundred thousand rows and:
a) builds and queries very slowly (hours)
b) takes up an order of magnitude more space on my hard drive than an equivalent plain text representation of the data as a non-sparse matrix
c) takes up more than ten gigabytes of memory when the table is read into memory
My ultimate goal is to store tens of thousands of runs (derived from thousands of proteins under several dozen simulation conditions) in the database so they can all be analyzed together. This would mean that the table representing the pairwise matrices would likely grow to around a billion rows. Currently it's seeming like I'm going to need a Cray or some other shared-memory monster in order to even run a single query on this database.
Do I have any better options here? What do the guys at the LHC use?
