The simple question is: how to store relative abundance data in a database? Which database to choose?

I am talking here about metagenomics data, relative abundance of species or some features. Each feature has single abundance value. For each sample, there are 100's to 1000' features, and the number of unique features between samples keeps growing fast with new samples. Target number of samples ranging 1000-10000.

The most lame way of handling that would be some big array (e.g. sampleID x featureID) but that would not be very convenient, as it will be holding mostly zeros (for features not found in given sample). I wanted to organize it into some database, but Which one will be effective to store and query this kind of data? Until now I was working only with relational data bases (MySQL), but I guess that is not the solution here (growing number of samples and unique features). Or is it?

Thanks for suggestions!

  • 1
    $\begingroup$ Not sure what your goal or application is, but if a large array is not feasible due to memory constraints because you're storing mostly zeroes, there's always sparse matrices (docs.scipy.org/doc/scipy/reference/sparse.html) $\endgroup$
    – Pallie
    Commented Mar 11, 2021 at 14:23
  • 1
    $\begingroup$ Thanks for the comment. The thing is that I need it as client/server database (that is why I was initially thinking of MySQL). To store bunch of metadata for each sample (and for that MySQL is OK), but also relative abundance. In that case large array is not so much problem of a memory, but table organization, because table structure will have to be very dynamic (constantly increasing number of features, if features are variables). $\endgroup$
    – Art
    Commented Mar 12, 2021 at 2:22


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.