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.
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:
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.
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://firstname.lastname@example.org: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:
We can even see what the existing tables look like, for example, the rides table:
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://email@example.com:26257/movr?sslmode=disable" run --multi-region
We can also see this load in the webui in the metrics tab:
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://firstname.lastname@example.org:26257/movr?sslmode=disable" configure-multi-region --preview-queries [INFO] (MainThread) connected to movr database @ postgres://email@example.com: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://firstname.lastname@example.org:26257/movr?sslmode=disable" configure-multi-region
You can see these run in the jobs section of the webui or by running
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!
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.
As of our 20.1 release, CockroachDB supports online primary key changes. This means that it is now possible to …Read more
When you’re distributing a SQL database geographically, it can be tough to get fast application …Read more
If your company has a global customer base, you’ll likely be building an application for users in different …Read more