It would be wrong to begin a comparison blog post about PostgreSQL without first acknowledging that it is one of the most reliable and widely used databases in the history of software. The world owes a debt of gratitude to the open source community that has built and supported this important project for the last 30 years.
In this post, we simply unpack some of the architectural differences between PostgreSQL and CockroachDB. In the process we’ll point out where the limitations of single server, single instance architecture might pose challenges for modern cloud infrastructure.
Cloud infrastructure promises easy scale for our apps and services at the push of a button. Whether we need more compute or more storage, these resources have become commodities; however, to take advantage of their seemingly limitless availability, the software we use must use them correctly.
Typically, scale for a PostgreSQL database involves deploying it on the largest instance you can afford. Then, when you near capacity on this instance you upgrade to bigger compute. You’ve increased capacity, but it’s essentially just vertical scale and you will reach its limit.
Once you’ve maxed out vertical scale, you have to shift to horizontal scale, which means manual sharding. Using a shard key or hash or whatever is right for you, you’ll need to split the database into smaller pieces and run it on multiple different compute instances. There are lots of different ways to shard a database, but ultimately all manual sharding leads to complex implementations, and a fair amount of overhead in actually implementing it (both from an operations point of view, and from a development point of view).
Scale in CockroachDB is a bit different. It is a native capability that, in its most basic form, delivers horizontal scale without any operational overhead. It is a distributed database where each node can service both reads and writes across all participating nodes. So, with the simple addition of a new node into a cluster, you scale both storage capacity and transactional capacity. Also, data is not synchronized among the nodes. Rather, the cluster uses distributed consensus, writing the data in triplicate across several member nodes. No matter where the data lives, every node can access data anywhere in the cluster. All without any operational overhead other than starting a new node in the cluster!
High availability (HA) is important as it ensures you can access data and that you have backups in the event of a failure. And for legacy databases, such as PostgreSQL, you will typically obtain HA via deployment of an active/passive topology. In this configuration, you have a primary and a secondary instance of the database, and you’re doing some sort of synchronization between two instances to make sure that they’re in sync. So, when the primary fails, the secondary can come online. And then, when the crisis is resolved, you get the primary back online and you return to the original configuration.
This approach has worked for some time, however, it is wrought with issues. First, the synchronization between primary and secondary can be complex and may be open to alignment issues between the two systems. Second, the time it takes for the secondary to come online after a failure could be substantial and you may lose data or transactions in the process. But what’s really scary is the remediation when two databases have been in production without syncing together. How do you know what’s correct within that data?
Again, CockroachDB naturally delivers high availability and does so without any complex or costly operations. As noted above, data is written in triplicate across several member nodes of the database, so that if an individual node fails, you still have two copies of the data. The database is even smart enough to notice if a replica is missing and will recreate it on another node so it ensures the replicas are complete.
Another way to look at scale is an ability to expand your presence into new geographies. With a legacy database like PostgreSQL, you will typically accomplish this by setting up a mirrored instance and then run each database in each region separately. You will typically have two active systems to manage and operate. Aligning the data, or collecting it for analysis, will rely on some sort of complex synchronization.
The problems here are numerous. Data across the regional database can be in conflict or duplicative. The operational overhead of managing two systems doubles the cost and complexity of managing your overall service. And finally, you’re on the hook for a fully loaded cost of two systems. Basically, it is expensive and complex.
CockroachDB can not only scale as you add more nodes, but it can also deliver a single logical database across multiple regions and even major geographies. It can also do this while ensuring you have always-consistent (serializable) transactions everywhere. There are even capabilities that also allow you to tie data to a particular geography to ensure low latency access to data no matter where your customers may be.
On these few basic considerations above, CockroachDB can deliver a better experience for developers and operators because they no longer need to deal with the complexity of scale, resilience, and geographic expansion. It is perfect for both complex global services and a single instance, single region application.
Most importantly, CockroachDB is compatible with PostgreSQL, so that the tools and services you would typically use with PostgreSQL will work with Cockroach. Both deliver a full and feature rich relational database with powerful SQL syntax.
Want to try this yourself? You can spin up a cluster of CockroachDB right now and have instant access at cockroachlabs.cloud. With a few clicks you will have a simple, scalable, resilient instance of a relational database that delivers most of the power of PostgreSQL. You can even start for free.
One caveat before we dive into this comparison of CockroachDB and Google Cloud Spanner: I am not a Spanner expert. I …Read More
This SQL cheat sheet is designed to make it quick and easy to find that SQL command that’s right on the tip of …Read More