CockroachDB offers a number of features aimed at making it easy to support your application in multiple regions. In CockroachDB 20.1, we introduced a new feature Online Primary Key Changes that allows you to upgrade your application from a single-region to multi-region with zero downtime. We wrote a bit recently about the technical challenges involved in building online primary key changes and in this blog post, we'll walk through some of the use cases and benefits the feature can lead to.
A Quick Example
First, download CockroachDB 20.1 using your preferred method including Linux, Mac, or Windows binaries, Docker images, or building directly from source.
This example will focus on the fictional ride-sharing company MovR that we’ve previously written about when introducing lateral joins. MovR is a ride sharing company that operates in multiple cities around the globe. We have explored its schema in great detail in this multi-region blog post. You can also use the new CockroachDB demo feature that we recently blogged about to try this out in less than five minutes.
To use CockroachDB demo (and the more complex rides table with pre-populated data) enter the following command:
./cockroach demo
This command will automatically load the MovR tables and data:
show tables;
table_name
+----------------------------+
promo_codes
rides
user_promo_codes
users
vehicle_location_histories
vehicles
(6 rows)
You can see that this rides table has already been well set up for multi-region as it has a compound primary key that places the region in front of id:
show create table rides;
table_name | create_statement
-------------+----------------------------------------------------------------------------------------------------------------------------------
rides | CREATE TABLE rides (
| id UUID NOT NULL,
| city VARCHAR NOT NULL,
| vehicle_city VARCHAR NULL,
| rider_id UUID NULL,
| vehicle_id UUID NULL,
| start_address VARCHAR NULL,
| end_address VARCHAR NULL,
| start_time TIMESTAMP NULL,
| end_time TIMESTAMP NULL,
| revenue DECIMAL(10,2) NULL,
| CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| CONSTRAINT fk_city_ref_users FOREIGN KEY (city, rider_id) REFERENCES users(city, id),
| CONSTRAINT fk_vehicle_city_ref_vehicles FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES vehicles(city, id),
| INDEX rides_auto_index_fk_city_ref_users (city ASC, rider_id ASC),
| INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC),
| FAMILY "primary" (id, city, vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time, revenue),
| CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city)
| )
(1 row)
Since we want to show the journey of going from single-region to multi-region, I’ve modified the rides table to remove city from the primary key (note: you could also do this with ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (id ASC); if you want to use the existing MovR rides table). To follow along at home, we will create and use a new database and then create a new rides table as follows:
CREATE DATABASE movr_blog;
USE movr_blog;
CREATE TABLE rides (
id
UUID NOT NULL,
city
VARCHAR NOT NULL,
vehicle_city
VARCHAR NULL,
rider_id
UUID NULL,
vehicle_id
UUID NULL,
start_address
VARCHAR NULL,
end_address
VARCHAR NULL,
start_time
TIMESTAMP NULL,
end_time
TIMESTAMP NULL,
revenue
DECIMAL(10,2) NULL
);
Now, we want to change this primary key so we need to use the following alter table statement:
ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (city ASC, id ASC);
NOTICE: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
ALTER TABLE
Time: 189.061ms
Now in this example, we don’t have a load pointed at the cluster or a large amount of data to change so it is a relatively fast operation. As we will see below, online schema changes are designed to have minimal impact on foreground activity by not locking the table out for use by your customers.
A bit more involved MovR example
Let’s show a more involved example that considers the entire MovR database. We make the entire MovR workload available in this GitHub repository.
To set up this example, make sure that you have docker installed and running.
brew install docker
You'll also need to install CockroachDB 20.1.
brew install cockroachdb
Then, start a cockroachdb node on your laptop:
./cockroach start-single-node --insecure --host localhost --background
Next, create the MovR database:
cockroach sql --insecure --host localhost -e "create database movr;"
Now, let’s generate some fakedata for the MovR database:
docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" load --num-users 100 --num-rides 100 --num-vehicles 10
We can open the webui and see what’s going on by visiting the localhost directly in the browser:
http://localhost:8080/#/overview/list
We can even see what the existing tables look like, for example, the rides table:
http://localhost:8080/#/database/movr/table/rides
Now let’s point load at it:
docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.2 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" run --multi-region
We can also see this load in the webui in the metrics tab:
http://localhost:8080/#/metrics/overview/cluster
Now, let’s upgrade this to a multi-region cluster. Normally, if you were doing this by hand, you’d need to run the following commands see through Docker:
docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" configure-multi-region --preview-queries
[INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable
DDL to convert a single region database to multi-region
===primary key alters===
ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE vehicle_location_histories ALTER PRIMARY KEY USING COLUMNS (city, ride_id, timestamp);
ALTER TABLE vehicles ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE user_promo_codes ALTER PRIMARY KEY USING COLUMNS (city, user_id, code);
===foreign key alters===
DROP INDEX users_city_idx;
ALTER TABLE vehicles DROP CONSTRAINT fk_owner_id_ref_users;
CREATE INDEX ON vehicles (city, owner_id);
DROP INDEX vehicles_auto_index_fk_owner_id_ref_users;
DROP INDEX vehicles_city_idx;
ALTER TABLE vehicles ADD CONSTRAINT fk_owner_id_ref_users_mr FOREIGN KEY (city, owner_id) REFERENCES users (city,id);
ALTER TABLE rides DROP CONSTRAINT fk_rider_id_ref_users;
CREATE INDEX ON rides (city, rider_id);
ALTER TABLE rides ADD CONSTRAINT fk_rider_id_ref_users_mr FOREIGN KEY (city, rider_id) REFERENCES users (city,id);
ALTER TABLE rides DROP CONSTRAINT fk_vehicle_id_ref_vehicles;
CREATE INDEX ON rides (city, vehicle_id);
ALTER TABLE rides ADD CONSTRAINT fk_vehicle_id_ref_vehicles_mr FOREIGN KEY (city, vehicle_id) REFERENCES vehicles (city,id);
DROP INDEX rides_auto_index_fk_rider_id_ref_users;
DROP INDEX rides_auto_index_fk_vehicle_id_ref_vehicles;
ALTER TABLE user_promo_codes DROP CONSTRAINT fk_user_id_ref_users;
ALTER TABLE user_promo_codes ADD CONSTRAINT fk_user_id_ref_users_mr FOREIGN KEY (city, user_id) REFERENCES users (city,id);
We’ve scripted it to complete these commands for you so you can remove the --preview-queries to see this execute:
docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" configure-multi-region
You can see these run in the jobs section of the webui or by running show jobs
We can also verify that this occurred in the webui or by running show create table rides;
How did this affect the cluster?
Back in the metrics table we can see that p99 latency rose only slightly while the schema changes took effect but we had no dropoff in throughput:
You can increase the cluster size, increase the workload, and otherwise try out CockroachDB using MovR!
Future-proof your application
Looking for other ways CockroachDB makes multi-region deployments easy? We previously covered How to Leverage Geo-partitioning, Reducing Multi-Region Latency with Follower Reads, and most recently How to Build a Multi-Region Application on CockroachDB.
CockroachDB provides developers with powerful tools to create multi-region applications. But your application doesn’t need to be designed for multi-region from inception, we also provide you the tools to grow your application from single-region to multi-region. Try CockroachDB Dedicated for free for 30 days.
