How Readyset Rewrites Your SQL: Infacet the Question Transformation Pipeline

Most databases work the identical means: a question arrives, the engine builds an execution plan, scans tables, joins rows, filters, aggregates, and returns the outcome. Every time the question runs, the work repeats from scratch. This pull-based mannequin has served relational databases for many years, but it surely carries an inherent price: learn latency is proportional to the complexity of the question and the scale of the info it touches.

Readyset takes a basically completely different strategy. Instead of re-executing queries on demand, Readyset compiles every question right into a dataflow graph — a community of operators (joins, filters, aggregations, projections) that constantly maintains the question’s outcome because the underlying information modifications. When a row is inserted, up to date, or deleted within the upstream database, the change propagates by means of the graph, and the cached result’s incrementally up to date. Reads turn out to be lookups right into a pre-computed materialized view, not full question executions.

This is the important thing distinction: conventional engines optimize how to execute a question every time it runs. Readyset optimizes as soon as, at cache-creation time, after which maintains the outcome incrementally perpetually. The tradeoff is that the question have to be expressed in a kind the dataflow engine can compile — and that kind is extra restrictive than what SQL permits.

SQL is a declarative language. The similar logical question could be written in lots of equal methods: correlated subqueries, derived tables, CTEs, LATERAL joins, nested aggregations. A standard optimizer treats these as interchangeable representations and picks the very best execution plan no matter syntax.

Readyset’s dataflow compiler just isn’t a conventional optimizer. It interprets SQL right into a directed acyclic graph of streaming operators, the place every operator receives modifications from its inputs and emits modifications to its outputs. This structure imposes structural constraints that SQL syntax does not:

Binary joins with equality predicates. Each be part of within the dataflow graph connects precisely two inputs through column-equality predicates (a.id = b.id). The engine makes use of these equalities to take care of hash-based be part of state. Range predicates, expression-based be part of keys, or multi-table ON situations will not be straight supported.

No correlated execution. In a conventional engine, a correlated subquery runs as soon as per outer row — a nested-loop sample. The dataflow graph has no idea of “per outer row.” Every operator sees the complete stream of modifications from its inputs. Correlated subqueries have to be rewritten into equal joins that the dataflow can preserve incrementally.

Flat be part of construction most popular. Derived tables (subqueries in FROM) are supported by the dataflow engine, however with a price: a derived desk compiles into a completely materialized intermediate node that can’t be parameterized. Every distinct mixture of enter information produces a saved outcome, no matter whether or not the outer question wants it. Inlining the derived desk — absorbing its FROM objects, WHERE filters, and projections into the outer question — eliminates this intermediate materialization and lets the engine construct parameterized lookups straight towards the bottom tables. The rewrite pipeline aggressively inlines derived tables the place semantically secure, reserving the materialized kind for instances the place inlining would change the question’s that means.

Supported be part of sorts. The engine helps INNER JOIN, LEFT OUTER JOIN , and CROSS JOIN. RIGHT JOIN and FULL OUTER JOIN will not be supported as a result of their incremental upkeep in a streaming context requires monitoring absence of matches on either side — a considerably more durable downside.

Aggregation boundaries. GROUP BY and mixture capabilities (COUNT, SUM, and many others.) compile into stateful operators that preserve operating totals. The engine requires that every aggregated question initiatives a minimum of one aggregate-derived expression, and that GROUP BY keys are express column references (not positional numbers or aliases).

These constraints imply {that a} syntactically legitimate SQL question — one which PostgreSQL or MySQL would execute with out criticism — is probably not straight compilable by Readyset, or could compile right into a much less environment friendly dataflow graph than crucial. The question rewrite pipeline exists to bridge this hole.

When a consumer points CREATE CACHE for a question, Readyset runs the question by means of a multi-pass rewrite pipeline that transforms arbitrary SQL into the canonical kind the dataflow engine expects. The pipeline is organized into three blocks:

Block	Purpose	Example
A — Normalization	Desugar syntax, resolve schemas, qualify columns	SELECT * turns into SELECT t.id, t.identify, …
B — Deep Rewrites	Decorrelate subqueries, flatten derived tables, optimize	WHERE id IN (SELECT …) turns into a be part of
C — Cleanup	Remove redundant clauses, parameterize literals	ORDER BY id LIMIT 10 eliminated when result’s provably single-row

Each move is semantics-preserving: the rewritten question returns the identical outcome as the unique for all attainable information. The passes construct on one another — Block A normalizes the SQL right into a canonical kind that Block B’s transformations can reliably function on, and Block C cleans up artifacts left by Block B.

Block A: Making SQL Canonical

Before any deep transformation can occur, the question have to be in a predictable form. Block A handles this:

• Schema decision binds desk and column names to the precise schema metadata Readyset has replicated from the upstream database. This is crucial for later passes that have to know main keys, distinctive constraints, and column sorts.
• Star enlargement replaces SELECT * with the express column checklist. Every downstream move expects to see named columns, not wildcards.
• Column qualification ensures each column reference is prefixed with its desk identify (id turns into t.id). This prevents ambiguity when a number of tables have columns with the identical identify.
• USING desugaring converts JOIN ... USING(id) into JOIN ... ON (a.id = b.id). The dataflow engine works with ON predicates, not USING clauses.

After Block A, the question is totally resolved, certified, and desugared — a clear basis for the transformations that comply with.

Block B: The Heavy Lifting

Block B is the place the actual work occurs. These passes rework SQL constructs that the dataflow engine can’t deal with into equal constructs that it may well. The ordering issues — every move prepares the bottom for the following.

Array Constructor Rewrite

The first Block B move, and PostgreSQL-specific. PostgreSQL’s ARRAY(SELECT ...) constructor produces an array worth from a subquery’s rows — a per-outer-row scalar aggregation form the dataflow engine has no direct operator for. The pipeline rewrites every incidence right into a LATERAL LEFT JOIN whose physique wraps the unique subquery in array_agg(...), with a COALESCE(..., ARRAY[]) on the outer facet so empty subqueries yield an empty array quite than NULL. ORDER BY and DISTINCT contained in the constructor are copied into the array_agg name (required for correctness when the subquery additionally has LIMIT/Top-Ok). By operating first, this move turns a SQL assemble the later passes would not acknowledge right into a LATERAL be part of they already know how you can deal with.

SELECT u.identify,
       ARRAY(SELECT p.title FROM posts p WHERE p.user_id = u.id) AS post_titles
FROM customers u


SELECT u.identify,
       COALESCE(array_subq.agg_result, ARRAY[]) AS post_titles
FROM customers u
LEFT JOIN LATERAL (
    SELECT array_agg(inner_subq.title) AS agg_result
    FROM (SELECT p.title FROM posts p WHERE p.user_id = u.id) inner_subq
) array_subq ON TRUE

Redundant Join Elimination

Queries generated by ORMs usually include redundant self-joins — the identical desk joined to itself on its main key, with all projected columns coming from one facet. The pipeline detects and eliminates these early, lowering the be part of graph complexity earlier than the heavier transformations that comply with.

Left-Spine Hoisting

Before decorrelating subqueries, the pipeline makes an attempt to inline the leftmost derived desk in FROM — however solely on the prime degree of the question. This is the one place the place a Top-Ok sample (ORDER BY ... LIMIT) is most dear: on the prime degree, the LIMIT could be parameterized (e.g., LIMIT ?) and the dataflow compiler can deploy a local Top-Ok node — a streaming operator purpose-built for sustaining the highest N rows incrementally. If the subquery have been left nested and processed later by the final decorrelation move, the Top-Ok would get replaced with a ROW_NUMBER() primarily based filter — functionally right however much less environment friendly, and with the LIMIT now not parameterizable.

SELECT sq.id, sq.identify, sq.rating
FROM (SELECT id, identify, rating FROM merchandise ORDER BY rating DESC LIMIT ?) AS sq


SELECT id, identify, rating
FROM merchandise
ORDER BY rating DESC
LIMIT ?

With the ORDER BY and LIMIT on the prime degree, the dataflow compiler acknowledges the Top-Ok form and deploys a local streaming operator. The LIMIT stays a parameter (?), so a single cached dataflow graph serves each LIMIT 10, LIMIT 50, LIMIT 1000 variant. Column rebinding, ORDER BY rewriting, and LIMIT/OFFSET composition are all dealt with by a shared inlining API reused by each inlining website within the pipeline.

Subquery Decorrelation

The most advanced transformation within the pipeline, and the rationale the previous passes exist — they put together the question construction so decorrelation can see and function on all correlated references. Consider:

SELECT o.id, o.whole
FROM orders o
WHERE o.whole > (SELECT AVG(whole) FROM orders WHERE area = o.area)

The subquery references o.area from the outer question — it is correlated. A standard engine would execute the subquery as soon as per outer row. Readyset’s dataflow has no per-row execution mannequin, so the pipeline rewrites this right into a be part of:

SELECT o.id, o.whole
FROM orders o
INNER JOIN (
    SELECT area, AVG(whole) AS avg_total
    FROM orders
    GROUP BY area
) AS sq ON o.area = sq.area
WHERE o.whole > sq.avg_total

The correlated predicate area = o.area turns into a GROUP BY key and a be part of situation. The subquery is now a derived desk that may be additional flattened. The dataflow engine compiles this right into a be part of operator that maintains the per-region common incrementally.

This decorrelation handles EXISTS, NOT EXISTS, IN, NOT IN, scalar subqueries, and LATERAL joins. Each has its personal semantics — LATERAL with COUNT requires LEFT JOIN with COALESCE to protect zero-count semantics; nested LATERALs correlating with grandparent scopes require wrapper flattening to get rid of scope boundaries; and IN / NOT IN / scalar subqueries towards potentially-NULL operands require three-valued logic probes whose applicability relies on the operator and the place the predicate seems, coated subsequent.

Three-Valued Logic for IN, NOT IN, and Scalar Subqueries

SQL boolean expressions consider to TRUE, FALSE, or NULL — three-valued logic (3VL). Most operators make this clear, however IN / NOT IN towards a subquery that may produce NULL don’t, and the fitting dealing with relies on the place the predicate seems:

• NOT IN in WHERE requires 3VL guards. If the RHS produces even a single NULL, a NOT IN predicate that may in any other case be FALSE turns into NULL — and in WHERE, NULL filters the row out whereas FALSE was meant to maintain it (or vice versa, relying on surrounding logic). A naive decorrelation right into a LEFT ANTI JOIN loses this distinction and returns fallacious outcomes.
• IN in WHERE does not want 3VL guards. Both FALSE (no match, RHS has no NULLs) and NULL (no match, RHS has NULLs) trigger WHERE to discard the row, so the excellence is immaterial — a plain decorrelation to a semi-join is already right.
• IN or NOT IN within the SELECT checklist at all times wants 3VL guards, no matter operator. The predicate’s worth is projected out of the question, and callers could take a look at it with IS NULL, move it to a different expression, or examine it to a different boolean — the TRUE/FALSE/NULL distinction have to be preserved precisely.
• Scalar subqueries within the SELECT checklist sit alongside these instances: their NULL-vs-empty-result semantics require the identical probe equipment.

Readyset handles all of those by putting in probe joins alongside the decorrelated subquery. Two probes span the complete fact desk:

• NP (null-present): EXISTS(rhs WHERE first_field IS NULL) — does the RHS produce a NULL on this correlation partition?
• EP (existence): EXISTS(rhs) — is the RHS non-empty for this correlation partition?

Both are materialized as LEFT LATERAL joins projecting a sentinel present_ column that downstream predicates take a look at with IS [NOT] NULL. A small boolean formulation over lhs IS NULL, NP.present_, EP.present_, and the anti-join match yields the precise 3VL outcome SQL semantics require.

Probes are put in solely when null inference cannot show them pointless. Before materializing both probe, the pipeline runs a nullability evaluation on either side of the predicate:

• If the RHS comparability column is provably null-free (from NOT NULL constraints, non-nullable expressions, or grouping/aggregation that ensures non-NULL outputs), the NP probe is skipped — a NULL can by no means happen, so the 3VL department it guards is unreachable.
• If the LHS expression is provably null-free, the EP probe is skipped — the “LHS is NULL” department of the 3VL formulation can’t fireplace.
• Only the branches that nullability evaluation can’t statically get rid of produce runtime probes.

This issues as a result of each probe is an extra LEFT LATERAL be part of: further nodes, further state, further work on each upstream change. Suppressing probes which are provably pointless retains the dataflow graph minimal and aligned with what the question truly wants.

Identical probes are materialized as soon as and reused. Real queries usually include a number of subqueries with the identical RHS and correlation — two NOT IN clauses towards the identical subselect, a scalar subquery referenced in each SELECT and WHERE, repeated predicates generated by an ORM, and so forth. A structural ProbeRegistry keys probes by a hash of the normalized RHS physique and the correlation predicate (post-normalization, so beauty variations do not trigger misses). A second incidence finds the prevailing entry and reuses its LATERAL joins as a substitute of emitting duplicates. The registry additionally helps lazy improve: if the primary incidence wanted solely NP however a later incidence additionally wants EP, the EP probe is added to the identical registry entry, avoiding a parallel set of joins for what’s the similar underlying subquery.

The internet impact: 3VL is actual, the dataflow graph carries solely the probe equipment the question truly requires, and repeated subqueries share a single compiled kind.

General Derived Table Inlining

After decorrelation, the question could include derived tables — each unique ones that the left-spine move did not deal with, and new ones launched by decorrelation itself (as be part of targets for unnested subqueries). This second inlining move flattens them into the outer be part of construction, eliminating pointless intermediate materializations.

SELECT sq.identify, sq.whole
FROM (SELECT identify, SUM(quantity) AS whole FROM orders GROUP BY identify) AS sq
WHERE sq.whole > 100


SELECT identify, SUM(quantity) AS whole
FROM orders
GROUP BY identify
HAVING SUM(quantity) > 100

The outer WHERE referencing the combination (sq.whole > 100) migrates to HAVING, since after inlining it applies to a grouped outcome. The GROUP BY keys, mixture projections, and column rebinding are dealt with by the identical shared inlining API utilized by the left-spine move.

Not each derived desk could be inlined. The pipeline checks for window capabilities that may change habits (their partition sizes rely on the row set, which modifications after inlining), nested aggregation conflicts, and self-join introduction. When inlining would change semantics, the question is left as-is and dealt with as a derived desk within the dataflow graph — or rejected if the engine can’t help it.

Join Reordering

Two semantically similar queries that checklist joins in a unique order — FROM a JOIN b ON ... JOIN c ON ... vs FROM a JOIN c ON ... JOIN b ON ... — ought to compile into the identical dataflow graph and share the identical cache. But the dataflow compiler produces a unique graph for every be part of ordering, so structural variations within the SQL textual content result in redundant caches for a similar logical question.

It’s price stressing that this move is not a cost-based be part of optimizer. Its objective is solely canonicalization: producing a deterministic, syntax-independent be part of sequence in order that logically equal queries share the identical cached dataflow graph. Cost, cardinality, and statistics play no function right here — the scoring is a structural tiebreaker, not a efficiency heuristic. True cost-based be part of reordering is a separate upcoming move that may function on this canonical kind: as soon as in place, the downstream optimization step might be configurable to run cost-based be part of reordering, filter hoisting, or neither — and in each case it’s going to begin from the identical canonicalized form produced right here.

The pipeline normalizes be part of order utilizing a deterministic structural algorithm: at every step it scores candidate joins by predicate proximity (preferring joins whose ON situations reference essentially the most just lately joined tables) mixed with the variety of cross-table equalities the candidate brings into scope, and breaks ties lexicographically by relation identify. This produces a canonical be part of sequence no matter how the consumer initially wrote the question. The similar strategy normalizes comma-separated FROM objects into express CROSS JOINs, guaranteeing constant construction.

Clause Normalization for Semantic Fingerprinting

Readyset identifies structurally similar queries to allow them to share a single cached dataflow graph. Two queries that differ solely in superficial methods — GROUP BY 1, 2 vs GROUP BY identify, area, or ORDER BY whole DESC vs ORDER BY 3 DESC — are semantically similar and will hit the identical cache.

The pipeline normalizes these clauses by resolving positional references (GROUP BY 1) and alias references (ORDER BY whole) to their underlying expressions (GROUP BY t.identify, ORDER BY SUM(t.quantity)). After normalization, semantically equal queries produce similar AST buildings, enabling dependable fingerprint-based cache matching.

Filter Hoisting

The ultimate Block B move. After all structural transformations, be part of reordering, and clause normalization have produced the canonical question form, the pipeline hoists parameterized filters (WHERE id = ?) from inside derived tables as much as the outermost WHERE clause. These filters are best on the prime degree, the place the dataflow engine can use them for key-based lookups into materialized state. By operating final, filter hoisting operates on the ultimate semantic fingerprint form — the identical canonical kind that might be used for cache matching — guaranteeing the hoisted filters land in the fitting structural place.

Today this move runs unconditionally because the final step of Block B. Once cost-based be part of reordering lands, this slot turns into a configurable optimization step: the pipeline will select between filter hoisting, cost-based be part of reordering, or neither — every ranging from the canonical form produced by the sooner passes.

Block C: Final Cleanup

After Block B’s transformations, the question could have redundant clauses. Block C strips ORDER BY and LIMIT from queries that provably return at most one row (e.g., filtering on a novel key), and auto-parameterizes literal values in order that structurally similar queries with completely different constants share the identical cached dataflow graph.

By the time a question exits the rewrite pipeline, it’s in a kind the dataflow engine can straight compile:

• All subqueries are decorrelated into joins
• All derived tables are inlined (or left as supported derived-table operators)
• All column references are totally certified
• All be part of predicates are column-equality pairs
• Joins are reordered into the canonical semantic-fingerprint form
• GROUP BY keys and ORDER BY expressions are normalized to express column references (no positional or alias indirection)
• Redundant joins and clauses are eradicated
• Parameterizable filters are on the prime degree
• All structural invariants required by the dataflow engine are glad — the question is assured to be compilable with out additional transformation

The dataflow compiler then interprets this canonical SQL right into a graph of streaming operators — and from that time on, each information change flows by means of the graph, conserving the cached outcome updated with out ever re-executing the unique question.

This is how Readyset turns SQL queries into dwell, incrementally-maintained caches: not by executing quicker, however by by no means needing to execute once more.

Today’s pipeline is constructed on normal SQL semantics and relational-algebra ideas. Every move is a broadly-applicable rewrite: a heuristic that works for any question matching a structural form, backed by a soundness argument that holds for any information. This generality is a function — it retains the pipeline compact, composable, and straightforward to motive about — however additionally it is the place the following spherical of labor lives.

Cost-based be part of reordering. The present Join Reordering move is a pure canonicalization step; it does not use statistics or attempt to reduce work. An actual cost-based be part of optimizer (CBJO) is the following main addition. Once it lands, the final Block B slot turns into a configurable optimization step — the pipeline will select between filter hoisting, cost-based be part of reordering, or neither — and each choice will begin from the canonical form produced by the sooner passes.

Relaxing decorrelation and inlining guardrails. Several of the present guards are intentionally cautious: they reject transformations {that a} extra focused evaluation might safely admit. Examples embrace tighter window-function partition-stability evaluation, composite-key redundant-join elimination, and per-column decomposition of blended mixture/non-aggregate expressions after inlining. Each of those is a simple leisure of an present guard — a narrower examine rather than a broader veto.

From generalized heuristics to pattern-specific transformations. The passes that exist in the present day have been deliberately constructed as generalized rewrites — structural patterns that apply broadly. The subsequent section of labor is the inverse: figuring out particular question shapes that the generalized heuristics do not acknowledge, and including focused rewrites for them. Real workloads are filled with patterns — produced by ORMs, BI instruments, or widespread reporting idioms — which are semantically easy however do not match any of the present structural templates. Each such sample turns into a brand new move or a brand new department in an present one: a slender match on a concrete form, with its personal soundness argument, composed into the pipeline alongside the final rewrites.

The pipeline’s structure — a sequence of impartial, composable, semantics-preserving passes — makes all of this incremental: every functionality is a brand new move or a relaxed guard, examined towards the complete suite of regression assessments and verified for semantic preservation.