RollupDB Adds SQL, Automatic Rollups and Streams That Can Change

RollupDB 0.4.0 is out. Since the first release three things have been added, one per version: rollups that run on their own, stream definitions you can change after the fact, and a read-only SQL interface over the retained buckets. Each one came out of the same question: what does someone need on day thirty that they didn’t need on day one?

The short answer is that people forget to run advance, they find out their retention was wrong, and they want to ask questions the JSON query wasn’t shaped for. So now rollupdb serve rolls up every stream once a minute, a stream can take a new version with different retention or an added sketch, and every stream is a SQL view.

A SQL query over the latency stream, grouped by service, returning readings, mean, worst and p99 for api and web. Each stream is a view with one row per retained bucket, and merge-aware aggregates keep variance and percentiles right across any rows.

Rollups Without a Cron Job

In 0.1, buckets moved to the next tier only when something called advance. That was simple to reason about and easy to forget. From 0.2.0, rollupdb serve does one pass over every stream each minute, and --advance-every changes the interval (0 turns it off). A program embedding the Go library gets the same with db.RunRetention. For a one-off, rollupdb advance FILE without a stream name now does every stream, and so does POST /v1/advance.

A stream that fails, say because the clock jumped, is reported and skipped; the others still roll up.

Changing a Stream

0.3.0 adds stream versions. update-stream stores version 2, then 3, and the newest one applies to the whole history. A new version can change how long each tier is kept, how late readings may arrive, the group cap and the list of statistics. It can also add a percentile sketch to a stream that started without one.

Some things can’t change, and RollupDB refuses them instead of pretending: the dimensions, the tier widths and the start time. Retained buckets were built with those, and changing them would mean rebuilding data that’s gone. A sketch added later covers buckets that start after the change. A percentile over older time comes back marked not_retained rather than estimated from nothing.

Every bucket keeps its full exact state, so a statistic added in version 2, a sample variance for instance, works on last year’s buckets too.

SQL

0.4.0 makes each stream a view, one row per retained bucket per group, with its time range, tier, dimensions and exact state. Because retained buckets never overlap, sum(count), sum(sum), min(min) and max(max) are right over any rows you pick. Variance and percentiles need merging rather than averaging, so there are three aggregates for them: rollup_variance, rollup_variance_sample and rollup_quantile, which merges the buckets’ sketches.

It’s read-only. One statement at a time, starting with SELECT, WITH or VALUES, in a transaction that’s always rolled back, at most 10,000 rows. SQL sees what’s retained now. For coverage and gaps, the JSON query is still the one that says what it covered and what’s missing.

rollupdb sql metrics.rollupdb "SELECT service, sum(count) AS readings,
  round(rollup_quantile(sketch, 0.99), 2) AS p99_ms
  FROM latency GROUP BY service"

The same works over HTTP with POST /v1/sql.

Upgrading

Files from 0.1 open as they are; there’s no migration. An independent review of these three features found bugs, and 0.4.1 fixes them the same day. The worst: a SQL query cut off by a timeout could leave the database refusing writes until it was restarted. Get 0.4.1 or later from the download page, and the reference lists every new command, endpoint and SQL function. The quick start now ends with a SQL query, and its output on this page came from the released binary.