Aster TSDB, Part 2: Time-Series Functions, Gap-Fill and Full-Text Search in Plain CQL
21 native functions, gap-fill with locf and interpolate, reset-aware counters, exact percentiles and substring search that handles Korean, all running on the node that owns the data.
In Part 1 we introduced Aster TSDB, our open-source time-series database built on Apache Cassandra, and showed what it does to storage and query latency. If you missed it, Part 1 is here: https://medium.com/@lsb_76413/aster-tsdb-a-time-series-database-built-on-apache-cassandra-part-1-of-3-5225e9294db0 This part is about the query side: what you can now ask the database directly, in ordinary CQL, that used to require pulling raw rows into your application.
The motivation is simple. In stock Cassandra, a dashboard panel showing one tag's hourly average for a day means reading every raw sample for that day (86,400 rows at one-second resolution) over the network, then bucketing and averaging in application code. Multiply that by every panel on every screen in a control room and the database spends most of its time serializing rows that are thrown away a millisecond later. Aster moves that computation to where the data already is.
No installation, no UDFs
All 21 functions are native. There is nothing to register and no user-defined-function sandbox to enable. Once the Aster jar is in place they are available in cqlsh, in every driver, and in prepared statements. We deliberately reused TimescaleDB's vocabulary, so if you already know time_bucket, locf and interpolate, you already know most of this.
Here is the full set:
- time_bucket(duration, ts [, origin]) — the start of the bucket a timestamp falls into. The workhorse for downsampling.
- time_bucket_gapfill(width, ts, start, finish) — a GROUP BY selector that also produces the empty buckets.
- locf(aggregate) — fill an empty bucket with the previous bucket's value.
- interpolate(aggregate) — fill an empty bucket by linear interpolation between its neighbours.
- first(value, ts) and last(value, ts) — the earliest and latest value by time.
- delta(value, ts) — last sample minus first.
- rate(value, ts) — delta divided by elapsed seconds.
- derivative(value, ts) — least-squares regression slope, per second.
- counter_delta(value, ts) and counter_rate(value, ts) — reset-corrected increase and rate.
- percentile(value, q) — exact continuous percentile with linear interpolation.
- time_weighted_average(value, ts) — average weighted by how long each value was in effect.
- integral(value, ts) — area under the curve in value·seconds.
- variance(value) and stddev(value) — sample variance and standard deviation.
- histogram(value, min, max, nbuckets) — fixed-width histogram as a list.
- approx_count_distinct(value) — HyperLogLog cardinality estimate in fixed memory.
- corr, covar_pop, covar_samp — correlation and covariance between two columns.
- regr_slope, regr_intercept, regr_r2 — linear regression of y on x.
One thing to remember throughout: most time-series aggregates take their arguments as (value, timestamp), and the bivariate ones as (y, x).
Downsampling with time_bucket
time_bucket works both as a scalar (to label each row with its bucket) and inside GROUP BY (to downsample). The duration is an unquoted CQL duration literal: 1h, 5m, 1d. Writing '1h' as a string will not match the signature.
-- hourly average, min, max and count of collection latency
SELECT time_bucket(1h, timestamp) AS bucket,
avg(latency), min(latency), max(latency), count(latency)
FROM tm_tag_point
WHERE tag_id = 'TAG-001'
GROUP BY tag_id, time_bucket(1h, timestamp);The important rule: put time_bucket last in the GROUP BY, after the partition key columns. That is what lets the grouping be pushed down into the read path, so the node aggregates while it reads instead of materializing rows first. It works the same on DESC-clustered tables; the buckets simply come back newest first.
If your shifts or reporting periods do not start on the hour, give time_bucket an origin:
-- hourly buckets offset by 30 minutes: [08:30, 09:30), [09:30, 10:30), ...
SELECT time_bucket(1h, timestamp, '2024-01-01 00:30:00+0000') AS bucket,
avg(latency)
FROM tm_tag_point
WHERE tag_id = 'TAG-001'
GROUP BY tag_id, time_bucket(1h, timestamp, '2024-01-01 00:30:00+0000');Gap-fill: an unbroken time axis
Plain time_bucket only returns buckets that contain data. That is correct, but it is not what a chart wants. When a sensor goes quiet for two hours, a dashboard either draws a misleading straight line across the gap or the frontend has to invent the missing buckets itself.
time_bucket_gapfill produces a row for every bucket in [start, finish), whether or not it has data. By default, aggregates in empty buckets are null. Wrap the aggregate in locf() to carry the last observation forward, or in interpolate() to draw a straight line between the values on either side.
SELECT time_bucket_gapfill(1h, timestamp,
'2024-01-01 00:00:00+0000', '2024-01-02 00:00:00+0000'),
locf(avg(latency))
FROM tm_tag_point
WHERE tag_id = 'TAG-001'
AND timestamp >= '2024-01-01 00:00:00+0000'
AND timestamp < '2024-01-02 00:00:00+0000'
GROUP BY tag_id, time_bucket_gapfill(1h, timestamp,
'2024-01-01 00:00:00+0000', '2024-01-02 00:00:00+0000')
ORDER BY timestamp ASC;Which one to use depends on the signal. A setpoint or a valve state holds until it changes, so locf is the honest choice. A temperature drifts continuously, so interpolate is closer to reality. Buckets before the first real value have nothing to carry forward or interpolate from, and stay null.
Get 이상부’s stories in your inbox
Join Medium for free to get updates from this writer.
A few rules worth knowing, all documented in the repository:
- On a DESC-clustered table, add ORDER BY timestamp ASC. Gap-fill assumes buckets arrive in ascending order; the explicit ASC turns the read itself ascending.
- Keep the WHERE timestamp >= start condition. A scanned row older than start makes the query fail rather than return a bucket outside the requested range.
- Do not alias the bucket column or the locf/interpolate expressions. They are located by function name in the result metadata.
- The width must be a fixed length (no months), and a query asking for more than 1,000,000 buckets is rejected.
- For several tags, include the partition key in both SELECT and GROUP BY. Each tag is filled independently.
First, last and OHLC candles
first(value, ts) and last(value, ts) order by the timestamp argument, not by insertion order. That makes them correct with out-of-order writes, which industrial data always has, and on DESC clustering. They are also type-agnostic, so they work on text columns too.
-- hourly OHLC candles for one tag
SELECT time_bucket(1h, timestamp) AS bucket,
first(value_numeric, timestamp) AS open,
max(value_numeric) AS high,
min(value_numeric) AS low,
last(value_numeric, timestamp) AS close
FROM tm_tag_point
WHERE tag_id = 'TAG-001'
GROUP BY tag_id, time_bucket(1h, timestamp);A word of caution from our own schema: if your readings are stored as text, min and max still run, but they compare lexicographically, so the max of '9.1' and '20.76' is '9.1'. Aster rejects arithmetic aggregates such as avg and percentile on non-numeric columns, but it cannot stop a lexicographic max from being the wrong question. Keep a numeric column for numeric readings.
Change over time: delta, rate, derivative
These three look similar but answer different questions:
- delta is the last sample minus the first sample in the bucket.
- rate is delta divided by the elapsed seconds between those two samples — an endpoint-to-endpoint rate.
- derivative is the least-squares regression slope over every point in the bucket. On a noisy or non-linear signal it is much more robust than rate, because one outlier at either end does not dominate it.
SELECT time_bucket(1h, timestamp) AS bucket,
delta(value_numeric, timestamp) AS change,
rate(value_numeric, timestamp) AS per_second,
derivative(value_numeric, timestamp) AS slope_per_second
FROM tm_tag_point
WHERE tag_id = 'TAG-001'
GROUP BY tag_id, time_bucket(1h, timestamp);The time argument must be a timestamp or a bigint of epoch milliseconds.
Counters that reset
Production counters, flow totals and energy meters are monotonically increasing until they are not. A PLC restarts, a register overflows, a meter is replaced, and the running total drops to zero. A naive rate() reads that as an enormous negative step, and your throughput chart shows a cliff that never happened.
counter_delta and counter_rate detect the reset and count the increase correctly across it. They only need a numeric column, not the CQL counter type. A bigint gauge is the right choice anyway: tables using the CQL counter type cannot be tiered, because the re-encoder deletes and re-inserts rows and a deleted counter can never be written again.
-- events per second, per minute, reset-corrected
SELECT time_bucket(1m, timestamp) AS minute,
counter_rate(total, timestamp) AS per_sec
FROM tag_counters
WHERE tag_id = 'TAG-001'
GROUP BY tag_id, time_bucket(1m, timestamp);Percentiles and SLOs
percentile is exact: it holds the group's values and interpolates linearly between the two nearest ranks. That makes it ideal for bounded buckets (p95 per minute, p99 per hour) and a poor fit for unbounded scans, which is why it pairs naturally with time_bucket.
SELECT time_bucket(1m, ts) AS minute,
percentile(latency_ms, 0.50) AS p50,
percentile(latency_ms, 0.95) AS p95,
percentile(latency_ms, 0.99) AS p99
FROM latencies
WHERE service = 'checkout'
GROUP BY service, time_bucket(1m, ts);In the benchmark from Part 1, a per-tag p95 across 90 tags and 3.6 million rows went from 14.2 s to 2.6 s once old data was tiered into compressed chunks — the function runs over the compressed data without any change to the query.
Irregular sampling: time-weighted average and integral
Industrial data is often reported by exception: a value is sent only when it changes by more than a deadband. In that world avg() is simply wrong, because a value that held for 50 minutes counts the same as a value that held for 5 seconds.
time_weighted_average weights each value by how long it was in effect. integral gives the area under the curve in value·seconds, which turns a power reading in watts into energy in joules, or a flow rate into a volume.
SELECT time_bucket(1h, ts) AS bucket,
time_weighted_average(value, ts) AS twa,
integral(value, ts) AS area
FROM metrics
WHERE series = 'feeder-3-power'
GROUP BY series, time_bucket(1h, ts);Distribution, spread and cardinality
- variance and stddev give the spread per bucket, useful for detecting a sensor that is starting to chatter.
- histogram(value, min, max, n) returns a list of n + 2 counts: values below min, n equal-width buckets, and values at or above max.
- approx_count_distinct estimates distinct values with HyperLogLog in fixed memory, for example distinct alarm codes or client IPs per minute.
SELECT histogram(latency_ms, 0, 1000, 10) AS dist
FROM latencies WHERE service = 'checkout';
SELECT time_bucket(1m, ts) AS minute,
approx_count_distinct(client_ip) AS unique_ips
FROM requests WHERE service = 'api'
GROUP BY service, time_bucket(1m, ts);Relationships between signals
The bivariate functions compute the relationship between two columns on the server. A classic industrial use is checking whether power draw tracks ambient temperature, and by how much.
SELECT time_bucket(1h, ts) AS bucket,
corr(power, temperature) AS r,
regr_slope(power, temperature) AS watts_per_degree,
regr_intercept(power, temperature) AS intercept,
regr_r2(power, temperature) AS r_squared
FROM sensors
WHERE site = 'plant1'
GROUP BY site, time_bucket(1h, ts);Putting it together: one statement per dashboard panel
Because the functions compose, a full panel is one statement and one round trip:
SELECT time_bucket(1h, ts) AS bucket,
count(value) AS samples,
first(value, ts) AS open,
last(value, ts) AS close,
min(value) AS low,
max(value) AS high,
avg(value) AS mean,
delta(value, ts) AS change,
rate(value, ts) AS per_second,
percentile(value, 0.95) AS p95
FROM metrics
WHERE series = 'cpu'
AND ts >= '2024-01-01 00:00:00+0000'
AND ts < '2024-01-02 00:00:00+0000'
GROUP BY series, time_bucket(1h, ts);Correctness in a distributed database
Aggregates in a distributed database are easy to get subtly wrong: rows arrive out of order, replicas hold different versions, and pages split a bucket across requests. For each function, the repository documents its signature, its exact semantics, how it behaves under distribution, and where the code lives. Our docker integration suite runs these functions against a real container as part of the release gate, and a three-node cluster test runs alongside it.
Full-text search that handles Korean
Industrial systems produce text as well as numbers: alarm messages, operator notes, device logs. Operators search them in the same pattern as everything else — this device, this period — and they search for fragments, not whole words.
Aster adds index_analyzer support to SAI LIKE with ngram, standard, cjk and keyword analyzers, or a JSON configuration. With the ngram analyzer, LIKE does true substring matching: a fragment in the middle of a word, a fragment that spans a space, and Korean text all match, and you never need ALLOW FILTERING.
CREATE TABLE logs (
device text, ts timestamp, msg text,
PRIMARY KEY (device, ts)
) WITH CLUSTERING ORDER BY (ts ASC);
CREATE INDEX logs_msg_idx ON logs(msg) USING 'sai'
WITH OPTIONS = { 'index_analyzer': 'ngram' };
SELECT ts, msg FROM logs
WHERE device = 'pump-01'
AND ts >= '2026-07-31 00:00' AND ts < '2026-07-31 01:00'
AND msg LIKE '%timeout%';'%imeou%' finds "timeout". Prefix, suffix and exact forms work ('connection%', '%9042', 'connection refused'), and you can AND several fragments together. Search also composes with the time-series functions:
-- timeout errors per 5-minute bucket for one pump
SELECT time_bucket(5m, ts), count(*) FROM logs
WHERE device = 'pump-01' AND ts >= ? AND ts < ?
AND msg LIKE '%timeout%'
GROUP BY device, time_bucket(5m, ts);How it works: each value is indexed as 2- to 3-character n-grams for recall. Candidates come from intersecting the grams of the pattern, and then the LIKE pattern is re-applied to the raw value for precision, so n-gram collisions never produce false positives. Fragments shorter than two characters are rejected with an explicit error, and = keeps its exact-match meaning. The trade-off is index size, several times the size of the column, so apply it to log-shaped tables rather than everywhere.
What's next
In Part 3 we go underneath these queries: the time-aware compaction strategy, the window-aware memtable, and the tiered storage engine whose columnar chunk format stores a production row in about 1.7 bytes, and why that makes these same queries faster rather than slower.
The code, the full function reference with runnable examples, and the design documents are all on GitHub. Try the functions on a copy of one of your own tables, and tell us what is missing.
