Make Table and Index Creation Faster in a Multi-Region CockroachDB Environment
In this article, see how to make table and index creation faster in a multi-region CockroachDB environment.
Do you want to make your schema and object creation faster in CockroachDB? I was working with a customer the other day that had a multi-region CockroachDB cluster spun up in US West 2, US East 1 and EU West 2 in AWS. Anytime they created an object, their DDL statements would take a few seconds because of all of the hops they must make to system tables for creating the objects. By default, the system tables are replicated uniformly across the cluster and the leaseholders/RAFT leaders (leaders for reads/writes) are dispersed in the cluster as well. In their case, creating all of their database objects took almost 30 minutes.
One way to optimize the creation of these database objects is to move all of the leaseholders for tables in the system database to a specific region. In the same region you move the leaseholder to, you should run the DDL statements
So, if you want to run all of your DDL changes in US East 1, run this command to move the leaseholder of the system database to US East 1.
This will move all of the leaseholders in the system database to be in US-East-1. This change is not immediate, so do wait a few minutes. Then check the ranges of the system tables to see if their leaseholders were moved to us-east-1. Here are some examples to test if the leaseholders moved:
Once the leaseholders have moved, try running your DDL statements and see if the creation time improves.
This was before...
This was after...
I'm not a fan of changing system database stuff but sometimes this helps for setting up tests or recreating environments. Just as easily it is to move the leaseholders into one region, you can always set it back to the default by running:
Originally published here.