How to perform schema changes without downtime

In the absence of a crystal ball, schema changes are inevitable when running databases in the wild. As your application grows and your requirements change, so do the tables that hold its data.

In this article, I’ll perform schema changes with a variety of database technologies, to demonstrate the importance of performing schema changes without downtime. For those of you who’d prefer to watch a video demonstration of these online schema changes you can watch this tutorial:

What are schema changes?

A schema change can be anything from adding or removing columns from a table, to modifying the type or column composition of a table’s indices, including PRIMARY KEY. Consequently, schema changes are often the subject, and cause of, application downtime; be that scheduled or otherwise.

Why do online schema changes matter?

Performing any kind of blocking operation against a database can render it, your application, and its users, blocked until the operation completes.

By implementing online schema changes, we make the process of performing schema changes less impactful to your database and the applications that consume it. Simply issue the change command and CockroachDB will perform it in the background, with no table locking or read/write impact.

This is made possible thanks to CockroachDB’s use of Multi Version Concurrency Control (MVCC), whereby data changes, including additions, modifications, and deletions, create concurrent versions in the table; allowing you to travel back in time and view data as it was before you performed an operation.

MVCC and time-travel queries

I’ll run through some example queries to demonstrate CockroachDB’s use of MVCC and how this supports what we call, time-travel queries.

First, I’ll create a table:

CREATE TABLE person (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  country STRING NOT NULL,
  full_name STRING NOT NULL,
  date_of_birth DATE NULL
);

Into which, I’ll insert some data; taking a snapshot of the current system time before doing so:

SELECT NOW();
               now
---------------------------------
  2023-03-28 14:19:39.301439+00

INSERT INTO person (country, full_name, date_of_birth)
VALUES ('uk', 'Alan Turing', '1912-06-23');

I’ll take another snapshot of the system system before inserting a second row, which will allow me to hop between timestamps to view data between these times:

SELECT NOW();
              now
--------------------------------
  2023-03-28 14:21:15.07211+00

INSERT INTO person (country, full_name, date_of_birth)
VALUES ('us', 'Dennis Ritchie', '1941-09-09');

Let’s run three queries; one to see the current state of the table, one to see the table before we inserted our second row, and another to see the table before we inserted our first row:

SELECT * FROM person;
                   id                  | country |   full_name    | date_of_birth
---------------------------------------+---------+----------------+----------------
  26f9a413-cb7f-4a92-9e8e-4b02c3a4c03f | us      | Dennis Ritchie | 1941-09-09
  7125f3db-3cf3-4d2a-b001-04447aeb5ca6 | uk      | Alan Turing    | 1912-06-23



SELECT * FROM person AS OF SYSTEM TIME '2023-03-28 14:21:15';
                   id                  | country |  full_name  | date_of_birth
---------------------------------------+---------+-------------+----------------
  7125f3db-3cf3-4d2a-b001-04447aeb5ca6 | uk      | Alan Turing | 1912-06-23



SELECT * FROM person AS OF SYSTEM TIME '2023-03-28 14:19:39';
  id | country | full_name | date_of_birth
-----+---------+-----------+----------------

This is all very cool but what does MVCC have to do with online schema changes? Well, without MVCC, online schema changes wouldn’t be possible, so it’s important to get a feel for how they work.

When you request a schema change, CockroachDB will create a new version (a copy) of your table in a background job. This background job will perform the schema migration without locking the your table, meaning users can operate against the original version of the schema until the migration is complete; at which point, they’ll switch over to the new version.

If, during the process of migration, you have a change of heart; the migration job can be paused, resumed, and cancelled with the following statements:

SHOW JOBS; -- Find the job_id

PAUSE JOB job_id;
RESUME JOB job_id;
CANCEL JOB job_id;

As you’d expect, this behavior doesn’t come for free. The additional MVCC version will double the amount of data used for the table, so be mindful of this when performing a schema migration when your cluster is close to capacity. CockroachDB’s garbage collector will delete the old version of data when it next runs, which is - by default - every 25 hours but can be changed as follows:

ALTER TABLE person CONFIGURE ZONE USING gc.ttlseconds = 43200; -- 12 hours

How to perform online schema changes

As exotic as they sound, CockroachDB makes online schema changes a reassuringly uneventful affair. They’re simply operations like Primary Key, column, index, and locality changes and table and materialized view creation. Nothing out of the ordinary but potentially destructive, if the table needs to be locked in order to complete them.

The following examples demonstrate some common schema changes that CockroachDB will perform in background jobs; leaving users free to continue using your database unaffected:

-- Update primary key to comprise the country and id.
ALTER TABLE person ALTER PRIMARY KEY USING COLUMNS (country, id);

-- Update locality.
ALTER TABLE person SET LOCALITY REGIONAL BY ROW;

-- Add a column.
ALTER TABLE person ADD COLUMN country STRING NOT NULL AS
  (regexp_extract("region"::STRING, '^(\w+)-')) STORED;

Online schema changes vs stop-the-world schema changes

The benefits of CockroachDB’s online schema changes really become apparent when compared with a database like Postgres, which does not natively provide online schema changes.

In the examples that follow, I’ll be testing CockroachDB v22.2.6 against Postgres 15.2. I’d like to start by acknowledging that Postgres is an incredible database and this test is designed merely to highlight the importance of online schema changes.

In the following steps, I’ll create a table with 10,000,000 rows (pretty small in the grand scheme of things, but large enough to demonstrate the point) in both CockroachDB and Postgres. I’ll then run an application to monitor request durations against both, whilst updating the primary key.

First, I’ll create a CSV file with 10,000,000 rows. For this, I’ll use the Crystal programming language. It’s fast, fun, and looks like Ruby (which for me, is rarely a bad thing):

require "csv"
require "faker" # Imported from https://github.com/askn/faker
require "uuid"

result = CSV.build do |csv|
  csv.row ["id", "country", "full_name", "date_of_birth"]
  1.upto(10000000) do
    csv.row [
      UUID.random,
      Faker::Address.country_code,
      Faker::Name.name,
      Faker::Date.birthday.to_s("%Y-%m-%d"),
    ]  
  end
end

File.write("people.csv", result)

Next, I’ll create the databases and import the data. I’ll run both databases in podman containers:

podman machine init --cpus 4 --memory 6144
podman machine start

podman run -d \
	--name postgres \
	-p 5432:5432 \
	-e POSTGRES_PASSWORD=password \
		postgres:15.2-alpine \
			-c 'max_connections=10000'

podman run -d \
	--name cockroachdb \
	-p 26257:26257 \
	-p 8080:8080 \
		cockroachdb/cockroach:v22.2.6 \
			start-single-node \
			--max-sql-memory=50% \
			--insecure

Next I’ll create a file called create_table.sql with the following content and apply it to both databases:

CREATE TABLE person (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  country VARCHAR(255) NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  date_of_birth DATE NULL
);

PGPASSWORD=password psql -h localhost -U postgres -f create_table.sql

cockroach sql --insecure < create_table.sql

The final step in readying these databases for the schema change test is to import the CSV file into both of them. I’ll import the data into Postgres first:

psql -U postgres -h localhost

\COPY person(id, country, full_name, date_of_birth)
FROM 'people.csv'
DELIMITER ','
CSV HEADER;

And then CockroachDB:

python3 -m http.server 3000

cockroach sql --insecure

IMPORT INTO person(id, country, full_name, date_of_birth)
    CSV DATA (
      'http://host.containers.internal:3000/people.csv'
    )
    WITH skip='1';

Finally, I’ll create an application to collect query duration statistics. I’ll write this in Go, if - for nothing other than - the sheer joy of it. Here’s the central function of the application; a loop that executes, and measures the elapsed execution time of, a simple SELECT statement that runs once every second:

db, err := sql.Open(driver, connStr)
if err != nil {
	log.Fatalf("opening db connection: %v", err)
}
defer db.Close()

var maxLatencyMS int64
for range time.Tick(time.Second) {
	start := time.Now()
	row := db.QueryRow("SELECT id FROM person LIMIT 1")

	var id string
	if err := row.Scan(&id); err != nil {
		log.Printf("error: %v\n", err)
	}
	elapsed := time.Since(start).Milliseconds()

	if maxLatencyMS < elapsed {
		maxLatencyMS = elapsed
		log.Printf("max latency: %dms", maxLatencyMS)
	}
}

With the application running against both databases, let’s alter the primary keys:

-- Statement for CockroachDB
ALTER TABLE person ALTER PRIMARY KEY USING COLUMNS (country, id);

-- Statement for Postgres
ALTER TABLE person DROP CONSTRAINT person_pkey;
ALTER TABLE person ADD PRIMARY KEY (country, id);

Whilst locking the table to perform the schema change, the maximum elapsed query time against Postgres climbed from the low milliseconds to 6,381ms. This represents a 6.3 second period, where the table was completely unavailable. CockroachDB on the other hand, without needing to lock the table, remained in the low milliseconds, with a maximum elapsed query time of 17ms and available to service requests throughout. This difference (~375x or 37,435%) and the importance of online schema changes will only increase as the table grows.

Conclusion

Schema changes are among the scarier tasks I perform as a software engineer. Questions like “if I take the database out of action for maintenance; can I get it back online again after?”, “what if my change takes so long that the database never unlocks?”, and “what if, once I’ve started the changes, I can’t cancel or revert them?”.

With CockroachDB’s online schema changes, none of these questions need to be a concern; the database stays up and free from locks, and, if you change your mind, you can cancel them before they finish. If you change your mind after they’ve finished, you can revert the operation, just as safely as you made it.

To learn more about online schema changes, visit our documentation page: https://www.cockroachlabs.com/docs/stable/online-schema-changes.html.