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:

1. 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.
2. 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:

1. 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).
2. 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.
3. 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.