CockroachDB and CedarDB: Better Together

Even simple and innocent looking queries can prove quite challenging for industry-leading transactional databse systems. Take the innocent task of finding the the 10 top-grossing items, along with how much we sold, how much money they made, what we usually charge per unit, how often they ship from a remote warehouse, and how much stock is still left in the warehouse.

The context is the well known TPC-C benchmark, which tends to be used to stress an OLTP database such as CockroachDB, yet it does naturally lead to running analytical queries like the one described above and shown below. CockroachDB doesn’t claim to be the all-in-one database which simultaneously excels at transactional and analytical queries; instead, it provides a very convenient mechanism for offloading data to an adjacent database that is much stronger for analytics, such as CedarDB: CDC, also known as changefeeds.

Reading over the documentation linked above, the approach I found most intriguing was the webhook sink, mainly since it required the fewest external components. Here’s what’s required:

• Create that webhook program, which accepts HTTP POST requests of JSON payloads corresponding to the CRUD operation in the database and translates them into the appropriate SQL which is then run against CedarDB, to keep it in sync.
• Re-create the CockroachDB tables in CedarDB.
• Run that webhook program.
• For each of the tables in CockroachDB, run the CREATE CHANGEFEED FOR TABLE ... SQL command, referencing that webhook endpoint.
• Start up the TPC-C benchmark, running against the CockroachDB instance. This is baked into the cockroach binary, so you just need to run the following, assuming you started the single node in insecure mode (great for experiments):

$ cockroach workload init tpcc "postgresql://root@localhost:26257/tpcc?sslmode=disable"
$ cockroach workload run tpcc "postgresql://root@localhost:26257/tpcc?sslmode=disable"

I drew this diagram to illustrate the general pattern:

• CockroachDB no longer has to contend with the challenging analytical workload.
• There are few moving parts here, so it’s not terribly complex to deploy; you can containerize all of it.
• As the DbVisualizer screenshot below illustrates, now I get query results relatively instantly. For this OLAP query, CedarDB answers the question in 35ms, about 65 times faster than CockroachDB.

Your transactional (OLTP) data is essential, possibly even the lifeblood of the business. This is where a database like CockroachDB excels, as it promises consistency and uptimes that you rely on. Yet most businesses also need to lean on that data for analytical purposes, possibly for forecasting, to build near realtime dashboards, or other essential functions. By deploying CedarDB alongside CockroachDB as shown here, you’re able to exploit this data in ways you may not have considered, potentially opening up new revenue streams, boosting customer retention, or [name your goal here].

The code for this experiment will be available within CedarDB’s examples GitHub repo, if you’re curious and would like a more hands-on experience.

Thank you!

If you’d like to discuss your data challenges with us, we’d love to hear from you.