I'm building a niche social media DB on planetscale that spans users living in multiple countries. Is there a way I can shard my social media user data per country and have that data physically located in the cloud region for that user's country? I need this solution to abide by the Data sovereignty laws. Please note: the dB needs to support queries spanning users in multiple countries (just like any social media use-case)
In planetscale I understand I have to select a primary region for my database, but I'm hoping that by using sharding and vitess I can shard the data into the respective region for that country.
Is what I want to do even possible? I was hoping to include country code in the users and posts tables and shard by country:
CREATE TABLE users (
id CHAR(18) NOT NULL,
user_handle VARCHAR(20),
country_code VARCHAR(2),
created_at TIMESTAMP,
PRIMARY KEY (id, country_code)
);
CREATE TABLE posts (
id CHAR(18) NOT NULL,
user_id CHAR(18) NOT NULL,
text VARCHAR(280),
country_code VARCHAR(2),
created_at TIMESTAMP
PRIMARY KEY (id, country_code) );
Planetscale doesn't offer sharding until the DB reaches a certain size and payment plan, so I'm unable to test this.