RonSQL: a new SQL engine for RonDB with predictable low latency and CTEs
RonSQL: a new SQL engine for RonDB with predictable low latency and CTEs
Today we released RonDB 26.04.1, a beta release. It contains a
lot of new features, but the most interesting one is that RonSQL now
supports pushdown join aggregation and CTEs, so that complex queries run
with low, predictable latency.
RonDB has always been able to answer complex queries through a MySQL Server.
The problem with that path is predictability. The application asks for an
answer, but it has no guarantee about how fast that answer arrives: the MySQL
optimizer picks a plan that may or may not parallelise the query across the RonDB
data nodes, and a plan that looks fine on a small table can fall off a cliff as
the data grows.
RonSQL takes a different contract. The rule is simple:
Anything RonSQL accepts can be pushed down to the RonDB data nodes for
parallel execution.
If a query parses and plans in RonSQL, it runs as a parallel pushdown —
there is no fallback to a slow, single-threaded plan. That means the latency of a
complex query is something the application can actually reason about up front,
instead of discovering it in production.
Why this matters: Feature Stores
RonSQL grew out of the needs of AI applications built on Feature
Stores, and in particular on-demand (real-time)
transformations in Hopsworks.
Traditionally an online Feature Store only does primary-key lookups. To keep
those lookups fast, every feature has to be pre-computed and
written back before serving. That works, but it has two costs:
- Stale features. A feature is only as fresh as the last
batch job that recomputed it. The events from the last few seconds —
often the most predictive ones for fraud, recommendations, or anomaly
detection — are not yet reflected.
- Expensive BLOB packing. A common trick is to pack a range
of values into a single BLOB (often Avro-encoded) so a whole feature group can
be fetched in one lookup. But every change to any value means
re-encoding the whole BLOB, which may hold hundreds of values.
RonSQL attacks both problems:
- Fresh features on the fly. Instead of pre-computing
aggregations, you stream raw rows into RonDB and let RonSQL aggregate them at
query time. A row inserted a second ago is included immediately, so the
feature reflects what just happened.
- Index scans instead of BLOBs. Instead of packing values
into a BLOB and re-encoding on every change, you store the values as ordinary
rows and let RonSQL read them with an index scan. Updates become simple inserts
and deletes — and deletes are usually handled for you by RonDB’s
row-level TTL, so old data ages out without any application
code.
CTEs (Common Table Expressions, the SQL WITH clause) are what let
you combine these two ideas in a single, readable query: aggregate the fresh fact
rows in a CTE, then join the result against your normalised dimension tables.
A worked example: real-time card-fraud features
Consider a fraud-scoring model. At inference time it needs a feature vector for
one card, computed over that card’s most recent activity. The raw transactions
arrive continuously and are inserted straight into RonDB:
-- Fact table: one row per card transaction, inserted in real time.
CREATE TABLE txn (
txn_id BIGINT NOT NULL,
cc_num BIGINT NOT NULL, -- card / account identifier
merchantkey INT NOT NULL, -- references merchant.m_merchantkey
amount INT NOT NULL, -- minor units (cents)
txn_time DATETIME(6) NOT NULL,
is_declined TINYINT NOT NULL,
PRIMARY KEY USING HASH (txn_id),
-- Ordered index: range-scan one card's recent activity cheaply.
INDEX idx_card_time (cc_num, txn_time)
) ENGINE=NDB
COMMENT='NDB_TABLE=TTL=604800@txn_time'; -- auto-expire rows after 7 days
-- Small dimension table: replaces a per-card Avro BLOB of merchant attributes.
CREATE TABLE merchant (
m_merchantkey INT NOT NULL,
m_category VARCHAR(16) NOT NULL,
m_risk_score INT NOT NULL,
PRIMARY KEY USING HASH (m_merchantkey)
) ENGINE=NDB;
Step 1 — a fresh feature vector with a single scan
The simplest on-demand feature is a scalar aggregate over the card’s last hour
of transactions. No pre-computation, no BLOB — just an index range scan that
includes whatever was inserted milliseconds ago:
SELECT
COUNT(*) AS txns_1h,
SUM(amount) AS amount_1h,
MAX(amount) AS max_amount_1h,
AVG(amount) AS avg_amount_1h,
SUM(CASE WHEN is_declined = 1 THEN 1 ELSE 0 END) AS declines_1h
FROM txn
WHERE cc_num = 4716253018273645
AND txn_time >= DATE_SUB('2026-06-29 14:30:00', INTERVAL 1 HOUR);
RonSQL turns the WHERE into an ordered-index range
scan on idx_card_time — it touches only this card’s
last hour — and pushes the COUNT/SUM/MAX/AVG
and the CASE expression down to the data nodes, which aggregate in
parallel and return a single row.
Step 2 — combining fresh aggregation with a dimension join, using a CTE
Now suppose the model wants spend broken down by merchant category.
The category does not live on the transaction — it lives on the
merchant dimension. The classic Feature Store approach would
denormalise the category into a packed BLOB per card. With RonSQL we keep the
data normalised and join at query time:
WITH spend_by_merchant AS (
SELECT merchantkey AS m,
SUM(amount) AS spend,
COUNT(*) AS txns
FROM txn
WHERE cc_num = 4716253018273645
AND txn_time >= DATE_SUB('2026-06-29 14:30:00', INTERVAL 1 HOUR)
GROUP BY merchantkey
)
SELECT m.m_category AS category,
SUM(spend_by_merchant.spend) AS spend_last_hour,
SUM(spend_by_merchant.txns) AS txns_last_hour
FROM merchant AS m
JOIN spend_by_merchant ON spend_by_merchant.m = m.m_merchantkey
GROUP BY m.m_category;
This query is easy to reason about, top to bottom:
- The CTE
spend_by_merchant runs an
ordered-index range scan on idx_card_time, restricted to one card
over the last hour — the only large table in play. The data nodes
aggregate SUM(amount) and COUNT(*) grouped by
merchantkey, returning just a handful of rows (one per merchant
the card touched in that hour).
- The join attaches the merchant attributes.
m_merchantkey is the primary key of merchant, so each
row is resolved with a cheap primary-key lookup rather than another scan.
merchant is a small dimension table.
- The outer query re-aggregates the joined result by
m_category, producing one row per merchant category — a
compact, model-ready feature vector.
Every stage is a pushdown, and stages such as the index scan and the lookups
run in parallel across the data nodes. We could even execute several CTEs in
parallel. Because RonSQL guarantees the whole thing pushes down, the latency is
bounded and predictable — which is exactly the contract a real-time
inference path needs.
Running a RonSQL query
RonSQL is reachable two ways:
- REST (RDRS). The RonDB REST server exposes a RonSQL
endpoint, which is the path used by online serving. It even keeps a built-in
latency histogram so you can watch the predictable-latency promise hold in
production. The rondb-cli shell sends a line straight to it with
the RONSQL prefix.
ronsql_cli. A standalone client for scripting
and experimentation. It reads a query from --execute,
--execute-file, or stdin and can emit results as JSON
(ideal for a feature vector) or TEXT.
Both paths support EXPLAIN. Prefixing a query with
EXPLAIN shows the chosen pushdown plan — which index drives
each scan, which joins become lookups, and where the aggregation happens —
so “will this be fast?” is a question you answer before you
ship, not after.
What RonSQL supports today
RonSQL is a read-only, aggregation-focused SQL subset designed so that
everything it accepts can be pushed down:
- Statements:
SELECT only (plus
EXPLAIN). No DDL/DML.
- CTEs: multiple, comma-separated
WITH clauses
(non-recursive); a CTE can be joined as a child or used as the driving
table.
- Joins:
INNER JOIN,
LEFT [OUTER] JOIN, self-joins, and comma cross-joins over scalar
CTEs. Equi-join conditions, including composite keys
(a.x = b.x AND a.y = b.y).
- Filtering: rich
WHERE —
= <> < <= > >=, LIKE,
IN (list), IS [NOT] NULL,
AND/OR/XOR/NOT, arithmetic, bitwise ops, and
CASE WHEN.
- Subqueries:
EXISTS,
IN (subquery), and scalar subqueries.
- Aggregates:
COUNT(*), COUNT(expr),
SUM, MIN, MAX, AVG.
- Grouping & shaping:
GROUP BY
(multi-column, any table), HAVING,
ORDER BY ASC/DESC, LIMIT.
- Expressions: arithmetic,
CASE WHEN,
GREATEST/LEAST, and date/time functions
DATE_ADD, DATE_SUB, EXTRACT,
INTERVAL.
- Index hints:
FORCE INDEX,
USE INDEX, IGNORE INDEX.
Why express features in SQL at all?
Because the Feature Store has to compute the same feature in two very
different settings. Batch training and batch inference run on
engines like Spark SQL and DuckDB — both
batch query engines, chosen for different characteristics (Spark scales the work
across a cluster for very large datasets; DuckDB runs embedded and is hard to
beat on a single node for moderate data). Online serving runs on
RonSQL, computing the feature fresh at inference time. When all
of them speak SQL, the same feature logic can be expressed as the same query text
on each engine, which eliminates a notorious source of
training/serving skew — features that subtly differ
between the model’s training data and what it sees live at inference.
Where RonSQL goes next
RonSQL is already useful, but there is a clear roadmap, much of it driven
directly by Feature Store needs:
- Distinct-count features.
COUNT(DISTINCT ...),
and an approximate variant (HyperLogLog), to answer “how many distinct
merchants / devices / countries in the last hour?” — a staple fraud
signal. DISTINCT and OFFSET more generally.
- More aggregate functions.
STDDEV and
VARIANCE (for z-score features), and
GROUP_CONCAT.
- Point-in-time correctness (“time travel”).
As-of joins so the same RonSQL query can reconstruct a feature’s value
at a historical timestamp for training, exactly matching what online serving
would have returned — closing the skew gap completely.
- Richer query shapes. Derived tables / subqueries in
FROM, UNION, RIGHT/FULL OUTER
JOIN, and recursive CTEs for hierarchy/graph features.
- Vector / embedding pushdown. Top-K nearest-neighbour
search pushed to the data nodes, as embeddings increasingly live alongside
scalar features.
- Cost-based join ordering. The planner currently joins
left-to-right; reordering based on table/index statistics would make more
queries fast by default.
- Continuous / materialised features. Incrementally
maintaining a CTE’s result as new rows arrive, blurring the line between
on-demand and pre-computed features.
The core contribution stays the same: predictable low latency for
complex queries over fresh data, expressed in portable SQL —
exactly what an online Feature Store needs to serve fresh, skew-free features to
an AI model.
