What Geopartioning or distributed SQL database can I use?


I want to split medical data on multiple nodes because I have Swiss, Europeans and Americans and I need to store data in citizen country.

I don't want to manage this function in App but in the SQL database engine.
The user’s table has a country code and automatically data are stored on good nodes/data center.

Do you have any solution to do this ?

Best Answer

Since this is medical data, I am going to make the assumption that you can afford a bit of a budget on this project?

I would suggest that you could do worse than taking a look at CockroachDB - from here:

With CockroachDB’s distributed SQL engine, your business can grow without ever manually sharding. Keep your services running with a database that automatically scales, rebalances, and repairs itself.

Their SQL engine is compatible with the PostgreSQL client, so anything that connects to PostgreSQL, can connect.

and with respect to Geo-distribution, you have from here:

The easiest way to maintain compliance with global data regulations Keep customer data where it needs to be. With CockroachDB’s policy-based data controls like geo-partitioning, serving customers in ever-changing geopolitical environments has never been easier or more affordable.

and also this:

Control Data Residency at the Row-Level Implement table and row-level policies that dictate where a user’s data resides using CockroachDB’s geo-partitioning.

They also have a managed offering if that would suit. I've used it for a Masters project and found it to be excellent - their documentation is superb (lovely graphic design!) and their support even for a lowly student on a free tier was great!

I also found that they were open about their product's limitations, but appear to be working well to overcome these. I've used it on AWS, GCP and Azure with no problems.

Also, when I said "budget" above, I meant over and beyond what you'd be paying for a cloud provider anyway. With CRDB, there is a free option, but I would suggest that with sensitive medical data, you should pay a bit more for the extra features of the pay version.

One of the guys who founded the company is Spencer Kimball who, as a student, began writing the Gimp (Gnu Image Manipulation Programme), Open Source's answer to Photoshop! He then worked on the Spanner/F1 system in Google!

If this sounds like a commercial, it's not! I'm just a happy user who was blown away with what you got on the free (and Apache licenced Open Source) tier!

If you already have a system that works off MySQL, you might want to look at TiDB! It's similar in scope to CockroachDB (not suprising as they're both based on Google's Spanner/F1 architecture). That is, if you can live with its limitations which are, IMHO, a bit severe for production still - but, hey, have a look - I've also used it on AWS, GCP and Azure also