Ray Tracing in SQL: Turing Completeness Meets Relational Abuse

There is a long-standing tradition in software engineering of pushing tools to their absolute breaking points just to see if they will bend. In the database world, this usually manifests as writing complex logic in pure SQL. We have seen a fully functional ray tracer implemented in a single MySQL SELECT statement, and more recently, an ASCII-art Doom clone running on DuckDB via WebAssembly.

But a recent project from the ClickHouse team takes this to a new level: a complete path tracer written entirely in SQL that renders glassy, chrome lettering over procedurally generated terrain, outputting directly to a PNG. It uses no user-defined functions (UDFs) and no external code.

While these projects are easy to dismiss as impressive demoscene stunts, they actually expose something profound about the state of modern analytical database engines. They show how these engines have quietly evolved from simple relational query processors into highly parallel, functional execution runtimes. They also highlight the severe structural friction that occurs when the relational model is forced to handle non-relational computation.

The Mechanics of Relational Rendering

To render a 3D scene in SQL, you must first map the physical coordinate space of an image to a relational structure. In a traditional renderer, you loop over pixels. In SQL, you map pixels to rows.

The ClickHouse implementation achieves this by using a table-generating function, numbers_mt(width * height * samples), to produce a flat relation where each row represents a single ray sample for a specific pixel. To compute the final image, these samples are aggregated using a standard GROUP BY on the pixel ID, averaging the red, green, and blue channels.

SELECT 
    pixel_id % width AS x,
    intDiv(pixel_id, width) AS y,
    avg(r) AS red,
    avg(g) AS green,
    avg(b) AS blue
FROM (
    -- Ray generation and bouncing logic goes here
)
GROUP BY pixel_id

The real trick to making this performant is avoiding a global ORDER BY at the end of the query. Sorting millions of pixels on a single thread would destroy performance. Instead, the query outputs explicit x and y coordinates alongside the color channels. The engine's native PNG writer uses these coordinates to place each pixel directly into the output buffer. Because the rows do not need to be ordered, the heavy lifting of calculating ray intersections can be distributed evenly across every available CPU core.

The Iteration Problem: CTEs vs. Functional Folds

Ray tracing is inherently iterative. A ray is cast into a scene, intersects with an object, and bounces. This process repeats until the ray hits a light source, escapes into the sky, or reaches a maximum bounce depth.

Standard SQL handles iteration through Common Table Expressions (CTEs) using the WITH RECURSIVE syntax. This is how the DuckDB-WASM Doom engine and early versions of the ClickHouse ray tracer managed ray propagation. The query steps the rays forward one unit at a time, joining the active ray state back against the map geometry on each iteration.

However, recursive CTEs are a poor fit for high-performance parallel execution. They act as a global loop over the entire dataset, forcing the engine to coordinate state across threads at the end of every iteration step. This introduces massive synchronization overhead and often forces serialization.

To bypass this bottleneck, the optimized ClickHouse implementation abandons recursive CTEs in favor of functional array manipulation. It uses arrayFold, a functional programming construct that performs a reduction over a specified range.

By running the bounce loop inside each row using arrayFold over range(maxDepth), the state of each ray is kept entirely local to its thread. The engine does not need to synchronize between rows or write intermediate states to temporary tables. The loop runs entirely within the local execution context of a single row, allowing the query planner to treat the entire rendering pipeline as an embarrassingly parallel problem.

The "Let-Binding" Hack and the Call-by-Name Trap

When writing complex mathematical formulas in SQL, code readability quickly degrades. To manage this, developers use WITH clauses to define aliases or lambdas for common operations, such as vector dot products or normalization.

In ClickHouse, however, WITH lambdas are evaluated using call-by-name semantics. This means that if you define a lambda and pass an expression to it, the engine does not evaluate the expression first. Instead, it textually expands the expression everywhere the parameter is used inside the lambda.

If you nest these lambdas, the query tree expands exponentially. A moderately complex formula for calculating a signed distance field for a torus can easily blow up the query tree to millions of nodes, causing the database compiler to run out of memory before execution even begins.

To solve this, the developers used a brilliant hack to force call-by-value evaluation, effectively creating a "let-binding" in pure SQL:

arrayMap(x -> body, [expr])[1]

By wrapping the expression expr inside a single-element array and mapping over it, the engine is forced to evaluate expr exactly once, bind the resulting value to the variable x, and then execute the body. This prevents query tree explosion and allows the engine to compile and execute highly nested vector mathematics efficiently.

The Developer Angle: When Does an Engine Become a Runtime?

No one is going to replace their GPU-accelerated path tracers with a database. But the fact that this is possible, and highly performant, has real implications for data engineering and analytical pipelines.

Traditionally, if you needed to perform complex mathematical modeling, coordinate transformations, or custom machine learning inference on data stored in a database, the workflow was predictable: export the data to a Python environment, run the calculations using NumPy or PyTorch, and write the results back.

These SQL ray tracers demonstrate that modern analytical engines can handle these workloads natively. If an engine can compute signed distance fields, run Perlin noise algorithms, and execute complex vector math on tuples, it can easily handle advanced geospatial calculations, custom signal processing, or feature engineering directly in the query pipeline.

But the trade-offs are severe:

• Lack of First-Class Types: SQL has no native concept of a 3D vector. You are forced to represent vectors as Tuple(Float64, Float64, Float64) and manually implement linear algebra using verbose lambda aliases.
• Write-Only Code: The lack of standard control flow structures forces you into functional gymnastics like the arrayMap let-binding hack. This makes the resulting SQL incredibly difficult to read, maintain, or debug.
• Zero Portability: These queries rely on highly specific, non-standard engine features. A query optimized for ClickHouse's vector engine will not run on DuckDB, let alone a traditional transactional database like PostgreSQL.

If you need to perform complex, row-independent mathematical transformations on large datasets, pushing that logic down to an analytical database engine is highly viable. But unless you are looking to win a demoscene competition, keep the code modular, document the hacks, and remember that just because SQL can do something, it does not mean it should.