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.