Experiment with other DBs?

atc3 commented 3 years ago

We're about to be running against the limits of Postgres... maybe time to switch over to a more robust and scalable solution.

My proposal is Google Cloud BigTable (same concept/interoperable API as Apache HBase)

Each genome is a row
Each mutation is a column (BigTable is essentially a big key-value store, so millions of columns are encouraged for efficient schema design)
No aggregation capabilities for the DB itself, but we can stream fetches from BigTable to our container, and have flask run a tally on incoming rows to aggregate on the fly without sorting beforehand
Should be very, very scalable

atc3 commented 2 years ago

Closing for now, we can re-open later. We tried BigTable and Cassandra:

BigTable: not very fast for small queries and very expensive to host a cluster Cassandra: Quick but not trivial to host a cluster as well. This is probably the option to revisit if performance becomes a major issue when we hit 50M genomes or something ridiculous.

atc3 commented 2 years ago

Will be keeping the dev branches (bigtable-dev and cassandra-dev) alive for now

vector-engineering / covidcg

Experiment with other DBs? #419