CedarDB Takes to the Slopes!

It would be natural to ask about the connection between skiing, or taking to the slopes, and database systems. That connection arose out of the way I related to the graph, below, that I created for a recent POC, showing the relationship between the time to run 750 queries against a table, for various row counts, in PostgreSQL, TimescaleDB, and CedarDB.

In skiing terminology, the blue line might be a black diamond run, the green one a beginner run, and the grey line along the x-axis is almost in an entirely different category and more fit for cross-country skiing. What blew my mind was that the slope of the CedarDB line was essentially zero.

The motivation for these database comparisons is a SQL query workload to support an app built by a biotech startup. The app interacts with their hardware device, which collects telemetry based on a user’s activities and, among other things, provides access to a library of content that the user might enjoy. The team at this startup provided us with some background on their database challenges, and that included lists such as top 20 slowest queries and top 20 heaviest queries. After analyzing these, we identified a query that appeared high on both lists, one that informs their recommendation pipeline, so we started there:

SELECT count(*) AS count_1
FROM (
  SELECT track_plays.time AS track_plays_time
    , track_plays.end_time AS track_plays_end_time
    , track_plays.user_id AS track_plays_user_id
    , track_plays.session_id AS track_plays_session_id
    , track_plays.track_id AS track_plays_track_id
    , track_plays.duration_sec AS track_plays_duration_sec
    , track_plays.source AS track_plays_source
  FROM track_plays
  WHERE
    track_plays.time >= $1
    AND track_plays.end_time <= $2
    AND track_plays.user_id = $3
    AND track_plays.duration_sec >= $4
) AS anon_1;

Looking at it, it’s not complex: just a table scan with a filter on a couple of TIMESTAMP and INTEGER columns.

Credit where credit is due, let’s first take a look at how TimescaleDB managed to flatten the slope considerably for our POC customer. TimescaleDB is a PostgreSQL extension designed to optimize the processing of time-series data, which is exactly what the track_plays table tracks. To really see the effect above with TimescaleDB, you must explicitly set it up using Hypertables, which partition the data by the time column using hourly partitions (chunk_interval). This, however, requires advance knowledge of your data and query patterns.

...
WITH
(
  timescaledb.hypertable,
  timescaledb.partition_column='time',
  timescaledb.chunk_interval='1 hour'
);

TimescaleDB can then use the partitions at query time, selecting only those relevant to the query, which drastically speeds up many time-series workloads. While it’s reasonable to assume that you often know this kind of information beforehand, unfortunately, TimescaleDB’s Hypertables are not without tradeoffs:

• Foreign key constraints are not supported between hypertables.
• UPDATE statements that would move a row between chunks are not allowed.
• All columns used for partitioning dimensions must be included in a unique index.
• The time column used for partitioning cannot contain NULL values.

While these tradeoffs are acceptable for many workloads, the question is: Do you need to make them?

Having seen how a use-case specific database system can make workloads beginner friendly, let’s take a look at how CedarDB manages to play a different sport entirely. How do I account for the vastly different performance results I observed for this workload? After all, TimescaleDB is specifically built to tackle just this type of time series data challenge.

While the performance increase is a mix of a lot of innovations, I want to highlight the one aspect I think best maps to what Hypertables do in TimescaleDB, the concept of “early pruning”. When scanning a table, CedarDB manages to check many predicates on metadata only, avoiding to scan blocks that don’t qualify entirely. In this instance, since a timestamp is just a 64 bit integer, timestamp comparisons are just integer comparisons internally, and CedarDB is able to just skip most data blocks when filtering for a time range, like the heavy-hitter query does.
How is it able to achieve this? Here are some key aspects contributing to that:

• CedarDB stores large chunks of data together into compressed blocks.
• CedarDB stores statistics for each block (e.g., max value, min value, count).
• If there is no value in a given data block that qualifies, per the query predicate, CedarDB avoids scanning that block.
• Time-series data is often loosely inserted in timestamp order. Even if the order is not perfect, overall timestamps close together are stored in the same block.
• Filters are especially useful for pruning in a time series data workload, where it’s common to filter by a timestamp column (e.g., all values of the last month), as illustrated here.

Sounds familiar? That is exactly the effect Hypertables have in TimescaleDB, just on all columns instead of one and without needing to specify anything beforehand.

And the beauty of it is that you get all this without sacrificing flexibility in other areas.

What else did I learn from this exercise, besides that I need to get ready for skiing season? Here’s what I’ve jotted down:

• The observations represented by the graphs up above inspired me to continue, with more data, so I moved the test jig off my M4 MacBook and onto an r7i.8xlarge instance in EC2. There, I gradually increased the row count in this table (via a data generator), from 25 M rows, where I recorded a cumulative runtime (750 queries) of 79 ms, to 1.13 billion rows, where the runtime was 96 ms. That 1.13B rows is 180x the size of the data point represented by the right-most point in the graph, yet the query runtime in CedarDB remained essentially constant.

• … So, while CedarDB is amazing on the 8 vCPU & 32 GB RAM of my MacBook Docker setup, when it has access to the 32 vCPU & 128 GB of RAM of the larger EC2 instance, it takes full advantage of all that CPU and memory.

• The efficiency here takes on two forms: (1) you pay less for IaaS or data center and (2) simplification through simplified DDL, fewer indexes, and the ability to consolidate multiple workloads onto a single, general purpose database.

• Finally, this radical performance boost may embolden you to consider some new use cases you may have been hesitant to explore using your existing database engine.

Note: If the folks we’re collaborating with on this give us the “OK”, we’ll share the code used here and update this with links.

Call to action: If you’re curious and want to give CedarDB a try in AWS, the CloudFormation template that I used to automate setup of my CedarDB in EC2 can help you get started pretty easily.

Thank you!

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