Several years ago, it seemed as though the relational database was on its way out. That didn't happen, in large part because many in the industry have realized that relational databases are still the best fit for many use cases. In addition, the industry has innovated, making it possible to easily scale relational databases horizontally. As a result, applications that we've spent many years perfecting and that rely on relational databases may still be best-of-breed. Such is our opinion when it comes to Camunda BPM.
Modern "distributed" relational databases offer traditional relational database capabilities - read: they support Structured Query Language or SQL - and they also offer easy horizontal scaling capabilities. This is exciting, because it provides the best of both worlds... the easy scaling capabilities that we've come to expect from "big data" vendors and relational support. (!) Two of the most interesting such databases are Google Cloud Spanner and CockroachDB.
Google Cloud Spanner (link) is unquestionably a great feat of engineering from a team that has offered many such excellent products over the years, but it has some significant limitations in our view. Specifically, it has its own, very proprietary brand of SQL that is very different from what we've come to expect in the industry, and it's limited to running in the cloud on Google Cloud Platform. While the latter may not seem to be an issue, we believe that even those who currently use Google Cloud Platform should strongly consider using a database tool that will allow them flexibility to run their database software locally or in a cloud environment of their choice.
Fortunately, CockroachDB - from Cockroach Labs (link) - doesn't have those same limitations. While it offers largely the same features that Google Cloud Spanner offers in terms of easy clustering, replication and geographically-distributed failover, it doesn't lock you in to a specific environment. To wit:
You can run it locally or on cloud hardware, such as your own private cloud, on Google Cloud Platform or on - for example - Amazon Web Services.
Their flavor of SQL is compatible with PostgreSQL; if you're a PostgreSQL user, it will "just work". If you use a different relational database, their constructs will be far more similar to yours than will Google Cloud Spanner's flavor of SQL.
You can also elect to use local instances for development and test.
Let's dive into this just a bit in relation to CockroachDB's potential use with Camunda BPM. Camunda BPM - as of version 7.12.0 - doesn't offer official support for CockroachDB, but it does offer support for PostgreSQL. Well, if CockroachDB supports the PostgreSQL "wire protocal" (read: JDBC driver and SQL constructs), then it should be usable with Camunda BPM, right? For the most part, yes. Camunda BPM works out of the box with Cockroach Labs' latest CockroachDB beta, version 20.1.0-beta4, but small modifications are required for it to work with previous versions. We tested four different versions, three in addition to their latest version, and here is what we learned:
For version 19.1.1, you have to modify Camunda's core engine code such that it doesn't attempt to use the "for update" clause, as this version of CockroachDB doesn't support that construct. In addition, you have to remove three recursive foreign keys, which aren't supported in versions of CockroachDB prior to the 20.x series.
For versions 19.1.8 and 19.2.5, you have to remove the aforementioned foreign keys, but CockroachDB will accept the "for update" clause.
Otherwise, in our testing, CockroachDB works great with Camunda BPM! This opens up a wealth of different configuration options and should allow Camunda BPM to benefit from all of the great scaling capabilities that have been built into CockroachDB!
If you need assistance with getting earlier versions of CockroachDB working with Camunda BPM or would like our assistance in building our your Camunda BPM/CockroachDB environments, please don't hesitate to contact us at firstname.lastname@example.org.