DEV Community: BobFerris

CockroachDB and GCP: Assuming Roles

BobFerris — Mon, 30 Jan 2023 03:40:59 +0000

Introduction

In a previous blog I detailed how we can use AUTH=implicit authorization to facilitate access to bulk operations such as backups, bulk imports and changefeeds (change data capture) in CockroachDB. One of the drawbacks for AUTH=implicit is that we have to provide extra permissions to cloud storage to the service account running our Cockroach nodes in GKE or Compute Engine. This may violate security practices for some enterprises and not adhere to the principle of least privilege.

New with CockroachDB v22.2 functionality has been added to allow Cloud Storage assume role. The official Cockroach documentation for assuming roles and GKE workload identity can be found here. Let’s get started and see it in action.

Details

Let’s start out by creating a new bucket. I’ll name our bucket ferris-crl-backups. This will be the target for our backups.

We’ll also create a new service account named ferris-cockroach-backups. We will give this service account permissions to our new cloud storage bucket and this will be the service account that is assumed by the service account used to run our CockroachDB cluster.

Next we’ll need to create a new custom role. To follow Google best practices we will only give this role the permissions necessary to run backups to Cloud Storage. We name the custom role CockroachDB Backups and assign it the three permissions required by Cockroach Backups:

storage.objects.create, storage.object.get and storage.objects.list

Assign this new custom role to our new service account ferris-cockroach-backups.

Finally we need to give the service account ferris-cockroach-backups the service account web token creator role for our compute engine service account. This is what allows our ferris-cockroach-backups service account the ability to generate oauth tokens for our compute engine service account.

With all this configuration now completed, we can do the easy part - run a CockroachDB backup command to backup our movr database to our bucket. Note that we specify AUTH=implicit and the ASSUME_ROLE syntax.

BACKUP DATABASE movr INTO 'gs://ferris-crl-backups/?AUTH=implicit&ASSUME_ROLE=ferris-cockroach-backups@cockroach-ferris.iam.gserviceaccount.com';

We get the following output indicating success!

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  835404627160268803 | succeeded |                  1 | 2592 |             0 | 385657
(1 row)

How do we know it worked?

Let’s take a look if we successfully authenticated with the correct service account. To do this, we need to look at Google Logging. First make sure that Data Access Logging is enabled under IAM & Admin → Audit Logs. We need to enable Data Read and Data Write for Cloud Storage. When the Cockroach backup job is run with Data Access Logging enabled we can view the results in Logs Explorer. In the screenshot below we can see that the authenticated principal is our ferris-cockroach-backups service account and that we were delegated from our compute engine service account which is the result we are looking for!

Conclusion

In this blog we’ve walked through how to assume Service Account roles to run bulk operations such as backups in CockroachDB. Thanks for reading!

Using Liquibase with CockroachDB

BobFerris — Thu, 28 Jul 2022 03:43:52 +0000

CockroachDB is the world’s most evolved cloud SQL database — giving all of your apps effortless scale, bulletproof resilience and low latency performance for users anywhere. Like any SQL database data is stored in tables created using Data Definition Language (DDL). One of the challenges associated with creating and maintaining a SQL database is how to implement tables and then change those table definitions in a consistent, versionable and easy to use manner.

This is where a schema migration tool such as Liquibase can really simplify the process. For a more in depth discussion about the benefits of schema migration tools refer to this blog post on the subject.

Installing Liquibase

Installation of Liquibase is straightforward. The basic steps consist of --

Download and install the Liquibase binary
Download the Postgres driver (CockroachDB is Postgres write protocol compatible and Liquibase uses the Postgres driver to communicate with CockroachDB)
Configure the Liquibase properties file with your CockroachDB information
Create a Liquibase schema change log and add one or more changesets to the change log.
Run Liquibase and you are on your way.

Of course this is a bit of an oversimplification. Detailed installation instructions can be found on the official Cockroach Labs documentation site here.

Data Types

SQL data types can roughly be categorized into 5 different groups.

Numeric (decimal, int, etc)
Character or String
Datetime
Boolean
Miscellaneous (blob, clob, xml, jsonb, etc)

Of course, each database can implement these basic data types in slightly different ways. The database ecosystem can’t always agree on things! Since a schema migration tool like Liquibase is database agnostic, handling these different data type implementations is one of the challenges that Liquibase has to solve. Liquibase allows users to define tables with Liquibase defined data types and then it converts those data types to the corresponding data type in the target database. I have had several Cockroach users ask for the details of exactly how Liquibase converts its data types to CockroachDB data types. Providing this mapping is the main reason for writing this blog.

Before we get into that mapping it is also important to realize that Liquibase allows flexibility in how a user can define their changesets for table creation. Liquibase supports writing changesets in SQL, XML, YAML and JSON. I will focus on SQL and XML.

When using a SQL changeset such as the one shown here -

<changeSet id="12" author="max" runInTransaction="false">
        <validCheckSum>ANY</validCheckSum>
        <sqlFile path="create_next.sql"/>
</changeSet>

Where the create_next.sql file contains the following -

create table balance
(
    id      int            not null primary key,
    balance numeric(19, 2) not null,
    name    varchar(128)   not null,
    type    varchar(25)    not null
);

Liquibase will interpret this as straight DDL and create the table as one would expect using the data types specified. This means only Cockroach supported data types can be specified in SQL changesets. CockroachDB supported data types can be found here. The CockroachDB database table that results from running liquibase update against this changeset is -

root@localhost:26257/liquibase> show create table balance;
  table_name |                create_statement
-------------+-------------------------------------------------
  balance    | CREATE TABLE public.balance (
             |     id INT8 NOT NULL,
             |     balance DECIMAL(19,2) NOT NULL,
             |     name VARCHAR(128) NOT NULL,
             |     type VARCHAR(25) NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
             |     FAMILY "primary" (id, balance, name, type)
             | )

When using XML changesets, Liquibase supports other “non-Cockroach standard” data types and will convert them for you when the changeset is executed. The table below shows the Liquibase data type to specify, the second column shows the data type created in Cockroach when using a SQL changeset and the third column shows the resulting Cockroach data type when the Liquibase data type is used in an XML changeset. If you are really interested in how Liquibase maps data types you can view their code, but I hope you find this table easier to digest.

Liquibase Data Type	SQL Changeset	XML Changeset
bigint	int8	int8
blob	bytes	oid (bytea in postgres)
boolean	bool	bool
char	char(x)	char(x)
clob	n/a	string
currency	n/a	decimal
datetime	n/a	timestamp
date	date	date
decimal	decimal(x,x)	decimal(x,x)
double	n/a	float8
float	float8	float8
int	int8	int8
mediumint	n/a	n/a
nchar	n/a	n/a
ncharvar	n/a	n/a
number	n/a	decimal
smallint	int2	int2
time	time	time
timestamp	timestamp	timestamp
tinyint	n/a	int2
uuid	uuid	uuid
varchar	varchar()	varchar
jsonb	jsonb	jsonb
serial	int8 unique_rowid()	int8 unique_rowid()

For example, notice that the currency data type is not a supported CockroachDB data type so it can’t be used in a SQL changeset. However, if currency is used in a XML changeset, Liquibase will create the column as a decimal data type. Since CockroachDB is Postgres wire compatible, you will notice that the data type conversions generally line up with what you would expect from Postgres.

Primary Keys

In any distributed database, obtaining an even distribution of data across all the nodes in the cluster is an important consideration for a balanced, performant cluster. CockroachDB stores data in its key/value layer ordered lexicographically by key. This means sequential keys have the potential to hot spot or overload the particular node that is the leaseholder for that range of data. Cockroach recommends defining primary keys using a meaningful non-sequential business key or a UUID.

Creating a UUID primary key with Liquibase using a SQL changeset is straightforward. The changeset would look like -

create table uuid_key
(
    id      UUID           not null primary key DEFAULT gen_random_uuid(),
    field1  varchar(25)    not null
);

And the generated table looks like -

root@localhost:26257/liquibase> show create table uuid_key;
  table_name |                create_statement
-------------+--------------------------------------------------
  uuid_key   | CREATE TABLE public.uuid_key (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     field1 VARCHAR(25) NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
             |     FAMILY "primary" (id, field1)
             | )

However, creating this with an XML changeset isn’t quite as intuitive. Using the following XML changeset will create the exact same table -

<changeSet  id="15"  author="max">
        <createTable  tableName="uuid_key">
            <column  name="id"  type="uuid"  defaultValueComputed="gen_random_uuid()">
                <constraints  primaryKey="true"  nullable="false"/>
            </column>
            <column  name="field1"  type="varchar(25)"/>
        </createTable>
</changeSet>

In situations where you don’t want to use a UUID the next best option for a randomly distributed key is to use the serial data type in Cockroach. The serial data type is shorthand to declare an INT8 field with the default value assigned by the unique_rowid() function. A SQL changeset would simply look like the following -

create table serial_key
(
    id      serial         not null primary key,
    type    varchar(25)    not null
);

And the resulting table would be -

root@localhost:26257/liquibase> show create table serial_key;
  table_name |                create_statement
-------------+-------------------------------------------------
  serial_key | CREATE TABLE public.serial_key (
             |     id INT8 NOT NULL DEFAULT unique_rowid(),
             |     type VARCHAR(25) NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
             |     FAMILY "primary" (id, type)
             | )

Using the following XML changeset will create the exact same table -

<changeSet  id="17"  author="max">
        <createTable  tableName="serial_key2">
            <column  name="id"  type="serial">
                <constraints  primaryKey="true"  nullable="false"/>
            </column>
            <column  name="type"  type="varchar(25)"/>
        </createTable>
    </changeSet>

Final Thoughts

One last issue that I’ve seen Cockroach users bump into with Liquibase deals with running multiple changesets against the same table in quick succession. One of CockroachDB’s unique features is the ability to run schema changes online, requiring no downtime. To accomplish this, CockroachDB asynchronously replicates the schema change to all nodes in the cluster. However, all nodes need to finish the schema change before the table can be changed again. On a local test cluster this probably won’t be a problem, but in a larger multi-region deployment this can take a little longer and cause an error if multiple changes are trying to be made back to back, for example, in a CI/CD pipeline. Try to keep all changes to a table in a single changeset if possible!

The goal of this post was to highlight and answer some of the common questions I’ve seen asked when using Liquibase with CockroachDB to manage your database schema. I hope you found it interesting and useful.

CockroachDB Host Based Authentication

BobFerris — Tue, 12 Apr 2022 15:37:03 +0000

CockroachDB is a distributed SQL database designed for speed, scale and resiliency. CockroachDB supports the PostgreSQL wire protocol and the majority of the PostgreSQL syntax.

Host-based authentication is part of the PostgreSQL wire protocol and is configured through the pg_hba.conf file. This allows database administrators to define granular control of how different clients and users are allowed to authenticate. Entries in the pg_hba.conf file consist of connection type, a database name, a user name, a client IP address range (if relevant for the connection type), and the authentication method to be used for connections.

For organizations with stricter access control requirements, host-based authentication can be enabled for example to force all connections to utilize certificate based authentication instead of password authentication. CockroachDB implements the host-based authentication component of the PostgreSQL wire protocol through the cluster setting

server.host_based_authentication.configuration

CockroachDB will automatically create host-based authentication rules in the background if not specified by an administrator in the cluster setting. These rules can be found in the source code for CockroachDB and are as follows -

host  all root all cert-password # CockroachDB mandatory rule

host  all all  all cert-password # built-in CRDB default
local all all      password      # built-in CRDB default

The first rule is always, automatically and transparently included in the host-based authentication configuration to allow root to be able to authenticate in the event that host-based authentication is misconfigured so that all access to the cluster is not blocked.

The positional entries in the configuration lines above correspond to

connection type: host = TCP/IP connection, local = unix-domain socket requests
database: all
user: root, all, or specific user name
address: all or specific IP addresses/ranges
auth method: cert, password, etc

See the PostgreSQL online documentation for full details.

Now let’s take a look at a concrete example of host-based authentication in action on a secure CockroachDB cluster. My goal is to require all connections to the database to use SSL certificates for authentication. I am going to use the DBeaver SQL client tool to illustrate this. By default all users can authenticate via password to a cluster. To change this behavior I add a host-based authentication rule to my cluster setting. From my SQL command line I specify

set cluster setting server.host_based_authentication.configuration='host  all  all  all cert';

To verify the entry use the command

show cluster setting server.host_based_authentication.configuration;

I created the user “cockroach” and assigned that user a password and now when I attempt to verify my connection to CockroachDB through DBeaver using only that user name and password I get the following exception

To perform certificate authentication I must create a client certificate for my “cockroach” user and provide that in the DBeaver configuration dialog screen.

Note that I had to convert my cockroach.client.key file to a .pk8 format using the command

openssl pkcs8 -topk8 -inform PEM -outform DER -in cockroach.client.key -out cockroach.client.key.pk8

Now when I attempt to verify my connection I can successfully connect over SSL

Remember that the PostgreSQL wire protocol host-based authentication provides the ability to control different authentication methods for different users. This is accomplished by providing multiple directive lines to the CockroachDB cluster setting. If for example, I only wanted my “cockroach” user to be forced to use certificate authentication while all others can also use password authentication I can specify the following.

Using show cluster setting I can see that all users except “cockroach” can connect via user name and password or certs but “cockroach” will be required to provide certificates.

One additional note, the permissions in the cluster setting are checked in the order in which they are entered, so enter your rules in order of least permissive to most permissive.

Using the PostgreSQL wire protocol host-based authentication mechanism within CockroachDB provides a method for organizations with elevated security requirements to control authentication to their CockroachDB clusters. As always, before implementing a feature like host-based authentication functionality in production, thoroughly test all the means and methods by which you will allow connections to the database!

CockroachDB & GKE: AUTH=implicit Backups

BobFerris — Thu, 20 Jan 2022 03:34:59 +0000

Let’s look at taking backups on a CockroachDB cluster running on Google Kubernetes Engine. For this discussion we are assuming that you have a functioning GKE environment and have installed CockroachDB using a statefulset template similar to the one found here.

Since we are running on GKE we want to backup our CockroachDB data to a Google Cloud Storage bucket. The following example backup command will backup the example_db to the backup directory in the test_crl Google bucket.

root@cockroachdb-public:26257/defaultdb> BACKUP DATABASE example_db TO 'gs://test_crl/backup?AUTH=xxxx' AS OF SYSTEM TIME '-10s';

The unanswered question with this command is how do we authorize the CockroachDB backup command to use the Google Cloud Storage bucket. We need to pass the Google Cloud service account credentials associated with the GCP instances running the Cockroach nodes to the backup command. We have two options to do this.

The first option is to use AUTH=specified in the backup command. This is accomplished by base64 encoding the Google service account JSON key file and including that encoded value in the backup command. Using this approach the backup command would look like

BACKUP DATABASE example_db TO 'gs://test_crl/backup?AUTH=specified&CREDENTIALS=really_long_base64_encoded_string' AS OF SYSTEM TIME '-10s';

This works fine, but is not very easy to maintain. Also, for those organizations with stringent security requirements this solution will likely run afoul of an InfoSec review.

The second and recommended option is to use AUTH=implicit in the backup command. This approach allows all the individual Kubernetes pods running CockroachDB to access the service account credentials as an environment variable as outlined in the GCP documentation.

To implement AUTH=implicit we will create a Kubernetes secret on GKE and then modify the Kubernetes CockroachDB statefulset to populate the environment variable and mount the secret to all the CockroachDB pods.

Begin by creating a Kubernetes secret manifest file named secret_auth_implicit.yaml

apiVersion: v1
kind: Secret
metadata:
  name: googlesecret
type: Opaque
stringData:
  googlebucket.json: |
    {
    "type": "service_account",
    "project_id": "cockroach-****",
    "private_key_id": "****",
    "private_key": "-----BEGIN PRIVATE KEY-----\n*****\n-----END PRIVATE KEY-----\n",
    "client_email": "626923887835-compute@developer.gserviceaccount.com",
    "client_id": "*****",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/626923887835-compute%40developer.gserviceaccount.com"
    }

Basically everything between the curly braces is the contents of the service account JSON key file. Details have been redacted in the above example. Now, apply this yaml file to create the Kubernetes secret

kubectl apply -f secret_auth_implicit.yaml

Next add the following directives to the env:, volumeMounts: and volumes: sections of the CockroachDB statefulset configuration

        env:
        - name: GOOGLE_APPLICATION_CREDENTIALS
          value: /cockroach/google/googlebucket.json

        volumeMounts:
        - mountPath: /cockroach/google
          name: secretconfig

      volumes:
      - name: secretconfig
        secret:
          defaultMode: 420
          secretName: googlesecret

The environment variable specified in the env: section must be named GOOGLE_APPLICATION_CREDENTIALS and the value googlebucket.json must match up to what is defined in the secret as the name of the stringData.

Apply the statefulset change and connect to one of the CockroachDB nodes and verify the configuration. First display the GOOGLE_APPLICATION_CREDENTIALS environment variable

[root@cockroachdb-0 cockroach]# echo $GOOGLE_APPLICATION_CREDENTIALS
/cockroach/google/googlebucket.json

Next cat the googlebucket.json file in the /cockroach/google/ directory and verify its contents

[root@cockroachdb-0 google]# pwd
/cockroach/google
[root@cockroachdb-0 google]# cat googlebucket.json
{
"type": "service_account",
"project_id": "cockroach-xxxx",
.
.
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/626923887835-compute%40developer.gserviceaccount.com"
}

Once the statefulset configuration has been verified, run the backup using AUTH=implicit

root@cockroachdb-public:26257/defaultdb> BACKUP DATABASE example_db INTO 'gs://test_crl/backup?AUTH=implicit';
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  719317784274370562 | succeeded |                  1 |    5 |             0 |    95
(1 row)

Success!

For more information on CockroachDB backups see the online documentation here.

CockroachDB - Following the Workload

BobFerris — Thu, 28 Oct 2021 14:20:15 +0000

Overview

One of the topologies available in a multi-region deployment of CockroachDB is known as Follow the Workload. Utilizing this topology pattern enables Cockroach to deliver fast reads within the region where the bulk of the read requests originate from. This blog will discuss the implementation details and demonstrate how to verify the performance of the Follow the Workload topology.

Follow the Workload is appropriate for a workload running in an active everywhere deployment across geographically distributed regions. A quick review of key architecture implementation choices within CockroachDB will be helpful for this discussion.

CockroachDB Basics

CockroachDB is a massively scalable, SQL compliant, distributed database solution. Data is stored in a monolithic immutable key value store and data in this store is broken up into manageable chunks called ranges. In a multi-region deployment, CockroachDB distributes range replicas across regions to ensure availability and survivability. The Raft consensus protocol will elect one of these replicas as a leaseholder. The function of the leaseholder is to service all reads and writes for data contained in the range. This allows CockroachDB to deliver globally consistent ACID transactions. The Follow the Workload topology enables CockroachDB to optimize the placement of these leaseholder replicas to realize performance gains.

Follow the Workload in Action

In this example, I deployed a multi-region CockroachDB cluster across the United States using 3 AWS regions. The TPCC workload is being generated in only one region and is being load balanced across the nodes in that one local region. This configuration mimics a follow the sun type workload where different regions will see a majority of the workload throughout the day. The database was configured to run at a replication factor of 3 and CockroachDB automatically distributed one replica to each region based on the locality flags set on each node in the cluster. The leaseholders were not pinned to any specific region meaning we will allow the database to choose which region the leaseholder should reside in.

The expectation is that over time the Follow the Workload topology will kick in and that all leaseholders for the ranges being accessed by the workload would move to the region where the workload was being generated. This has the positive effect of lowering read latencies since leaseholders would be in the local data center and not require communication with nodes outside the local region to satisfy the read query. It should be noted that writes will still require communication with a quorum of replicas and incur inter-region latency. Using the command

SELECT table_name,range_id,lease_holder_locality, count(*) FROM [SHOW RANGES FROM DATABASE tpcc] group by table_name, lease_holder_locality order by table_name, lease_holder_locality;

We can see the initial distribution of leaseholders looks like the following

What I observed with the default CockroachDB configuration is that leaseholders would move to the region local to the workload. However, soon those nodes local to the workload were consuming more resources and CockroachDB tried to balance the load across nodes by moving leaseholders back out of the local region. We can alter this behavior by changing the following database cluster settings.

set cluster setting kv.allocator.lease_rebalancing_aggressiveness = 10;
set cluster setting kv.allocator.load_based_rebalancing = 0;

With these settings, I was able to get the Follow the Workload results I wanted. My test data consisted of 400GB of data and after 30 minutes of the steady, random TPCC workload, all leaseholders moved to the data center local to the workload as seen in the chart below.

Running the show ranges command again confirms that all leaseholders did in fact end up in the west region where my workload was running.

There are a couple of considerations worth mentioning if you intend to utilize Follow the Workload. First, keep in mind we turned off the cluster setting to allow CockroachDB to move leaseholders based on increasing load. You need to test thoroughly and confirm that your cluster is right-sized to support having all the leaseholders in one region. Second, since nodes containing the leaseholder replicas shoulder the burden of coordinating read and write requests you will have to carefully monitor that those nodes do not become saturated especially in failure situations where you may lose a node.

In summary, the Follow the Workload topology can be a useful tool to optimize performance for applications where load can shift to geographically disparate regions throughout the day. I hope you found this useful!

Thoughts on CockroachDB & Cassandra

BobFerris — Thu, 28 Oct 2021 13:53:21 +0000

A Brief History (not of Time but of distributed databases)

While both Apache Cassandra and CockroachDB are massively scalable, open source, distributed databases, they were designed and architected with different goals in mind. Cassandra can trace its origins back to Facebook where its creators drew inspiration from Dynamo and BigTable white papers. Facebook was looking to create a solution that could ingest huge amounts of data and query the dataset using specific lookup patterns. CockroachDB was founded by former Google employees that had worked on Google Spanner. As such, the focus of CockroachDB was to provide a scalable database solution that was SQL compliant while supporting globally distributed transactions.

Both databases have proven to be very successful at what they do. I have spent many years working on and assisting enterprises in implementing distributed database solutions. This blog is a summary of my observations and experiences over the years working with Apache Cassandra and CockroachDB. I hope you find this content useful!

Since we are talking about distributed databases, I should start by defining exactly what that term means. A distributed database can be thought of as a collection of machines, either physical or virtual, typically called nodes that are loosely joined together and work together to function as a single logical database. One of the primary characteristics of distributed databases is their ability to replicate data which guarantees that if a node within the database should fail, the data contained on that node is not lost. Both CockroachDB and Apache Cassandra replicate data, but the mechanism they use to accomplish this task is where they begin to diverge.

The CAP Theorem

Let us start by looking at the CAP theorem. The CAP theorem states that a distributed database has three critical attributes, Consistency, Availability and the ability to survive network Partitions and that a distributed database is only going to be able to fully satisfy two of these criteria. Since any distributed system needs to be architected to be tolerant of network partitions the difference comes down to whether a database solution shows preference to consistency or availability. This distinction isn’t always as obvious as the terms indicate. For now, I will just state that Apache Cassandra is classified as an AP system meaning it can potentially return inconsistent results under certain failure scenarios. CockroachDB is a CP system meaning it will never return inconsistent results, choosing instead to return an error during a failure situation when quorum, meaning consensus from a majority of nodes, cannot be achieved.

How Data is Stored

To understand the strengths of each database it is necessary to understand at a high level how each system stores its data. Under the covers both databases store data in immutable sorted string or SSTables. Cassandra uses a deterministic hashing algorithm on a table's partition key to randomly and evenly distribute data throughout the cluster. Each node becomes the owner for a subset of the hashed values and each piece of data is stored on additional nodes to satisfy the defined replication factor. This architecture means that every node in the cluster is aware of which nodes hold each piece of data. As a result queries acting against the primary key of a table are extremely fast.

All data in CockroachDB is persisted in a large key value store based on the primary key of the table or index. This key value store is ordered lexicographically by key. The key value store is then divided into segments called ranges and these ranges are what is replicated throughout the cluster to maintain availability and survivability. Ranges can be up to 512MB in size. Cockroach maintains a range index so that each node is aware of the locations of all ranges in the cluster. This means that in addition to delivering fast key lookups, scan operations against a table’s key are also performant.

Data Model Design

With a basic understanding of how each database stores and accesses data we can now turn to the methods used to implement an effective data model. Apache Cassandra uses what is commonly known as query driven design. With query driven design, all data access patterns are determined up front and individual tables are created with the proper primary key to satisfy the conditions on the select query. Remember that Cassandra is optimized to select based on the primary key of a table. Data is partitioned based on the hashed value of the partition key component of the primary key. An attempt to select on a non-keyed value in the table results in a complete table scan across the cluster and is highly inefficient. Indexing data in Cassandra is also problematic due to the partitioning strategy and secondary indexes are essentially hidden tables with a different primary key that duplicate all data from the base table. This makes evolving the data model very difficult as the application changes.

CockroachDB is Postgres wire compatible and more traditional relational database design techniques can be employed. Foreign keys and online schema changes are supported within CockroachDB. An effective data model in CockroachDB will closely resemble a third normal form data model used by traditional non-distributed relational database management systems.

Consistency

There continues to be a lot written about consistency within distributed databases. I won’t get too deep into this conversation, but I will highlight some key differences. CockroachDB supports ACID compliant transactions at the highest serializable isolation level. In fact all mutations in CockroachDB are treated as transactions which allows CockroachDB to maintain consistent data even in a globally distributed deployment. Distributed systems require the use of a consensus algorithm and CockroachDB chose to implement the Raft protocol. Raft elects one of the range replicas as a leaseholder. All reads and writes for a range go through this leaseholder range ensuring data remains consistent. A write is considered successful when a majority of replicas have synchronously committed the mutation to their raft log. In the event that a node containing a leaseholder fails, one of the other replica ranges is elected as the leaseholder. One side effect of this implementation is that retry logic generally needs to be written into the application layer for workloads that may experience transaction contention.

Apache Cassandra applies an eventual consistency methodology. A consistency level can be applied to each read or write request and this consistency level determines how many nodes need to respond to satisfy the request. If reads and writes both use a quorum consistency level then consistent results can be achieved. The big difference here is that Cassandra replicates data asynchronously. In a distributed environment asynchronous replication is vulnerable to inconsistencies due to network issues, node availability, etc. Apache Cassandra addresses this concern by continually running a background process called repair which compares data between the nodes and uses the most recent timestamp or last write wins semantics to resolve discrepancies.

Ease of Use

With all the database choices on the market today, one of the often overlooked capabilities of a database system is how easy it is to install, use and maintain. Historically, Apache Cassandra has been notoriously difficult to install and operate. Configuring Cassandra consists of modifying several different yaml configuration files to meet user requirements. This complexity grew largely due to Cassandra’s open source origins where commits to enable features and functionality were inconsistently applied to the code base. Cassandra is written in Java and as such requires a fairly deep understanding of JVM runtime settings to tune for optimal performance. Further there is no easy to use built in monitoring solution. Cassandra does include JMX monitoring endpoints but they are not always intuitive or easily consumed.

CockroachDB is written in the Go programming language and is deployed as a single executable binary. Included in this binary is a browser based user interface which allows for easy monitoring and maintenance of the database. Configuration is controlled by cluster settings and startup flags that are easily set during initialization.

Conclusion

Apache Cassandra and CockroachDB share many characteristics. They are both highly scalable, open source, cloud native and cloud agnostic database solutions. However, as I mentioned at the outset, Apache Cassandra and CockroachDB were designed from the ground up to solve distributed data challenges differently. Hopefully this article highlights those differences and can assist in evaluating the proper database solution for your next application.