Let’s say you want to take advantage of Follow the Workload.
You have a datacenter with some number of nodes in New York City and a second datacenter with some number of nodes in Sydney.
CREATE TABLE IF NOT EXISTS customer (
username STRING NOT NULL,
password STRING NOT NULL,
PRIMARY KEY (region_id, username)
Assuming you create the nodes specifying the appropriate locality, and set region appropriately when creating new customer records to “New York City” or “Sydney” (or any two distinct values that correspond to the two datacenter locations), “Follow the Workload” will get automatically utilized?
Let’s say the leaseholder range is approximately 10,000 rows. Let’s say you have 2,000 customers, half from New York City, half from Sydney. My assumption is Follow the Workload wouldn’t work here as all customers are in a single leaseholder range. Is there a way to split customers in such a way that each leaseholder range has only a single “region” field (in this case, you’d want 1 leaseholder range with the New York City customers and a second leaseholder range with the Sydney customers).
If a customer in this example moves from New York City to Syndey, what is the easiest way to preserve their Follow the Workload optimization? One idea is to create a new customer record with the same username but the Sydney region, and migrate their data (to the extent other tables are referring to the customer table as a foreign key) and then delete the original row. Any issues with this approach or is there something simpler that reduces the need to migrate data?