User-Defined Functions in Columnar Query Engines

Columnar engines are fast because every layer of the system, from disk layout to CPU instruction, is built around one decision: store values of the same column next to each other, not values of the same row. A scan that only needs two fields out of a fifty-column table reads only those two columns off disk. The other 48 never get touched, never cross the I/O bus, never occupy a byte of memory.

The execution model built on top of that layout is called vectorized processing. Batch processing means the CPU does the same thing to a thousand values before it ever reorders its instruction pipeline. That is a massive difference in throughput, and it is only possible because the data is already organized by column.

Compression falls directly out of the same layout decision. Tighter compression means fewer bytes the CPU has to decode and process per query; more rows get handled per second on the same hardware.

Predicate pushdown and column pruning complete the picture. A query filtering on a timestamp column never has to read the payload columns at all. The engine eliminates data before it reaches the execution layer, so the CPU-bound processing only ever sees the rows and columns that actually matter.

None of these four effects, reduced I/O, tighter memory locality, SIMD-width parallelism, and compression, operate independently. They compound. Compression means more rows per cache line. Each gain amplifies the next, and that multiplicative effect, not any single trick, is what makes columnar engines capable of scanning billions of rows in the time a row-oriented system would still be seeking on disk.

UDFs and their four classifications

A user-defined function (UDF) is custom logic an engineer inserts into a query that the engine's built-in SQL functions can't express on their own. What gets missed is that not all UDFs cost the same, and treating them as one undifferentiated category leads teams to either ban UDFs outright or let them run unchecked, both of which are wrong in different situations.

Analytical engines generally classify UDFs into three structural types. Scalar UDFs take one input row or value and return one output value, the simplest case. Aggregate UDFs (UDAFs) take many input rows and collapse them into one output value per group, the same shape as a built-in aggregate function but with custom logic. Each of these three shapes interacts with the query plan in a fundamentally different way.

A fourth category appears specifically in streaming and real-time systems: stateful UDFs, which carry internal state across successive calls within a window. A fraud-detection function tracking a running count of transactions per user across a sliding time window is a stateful UDF. That state has to be serialized, checkpointed, and recovered, a dimension that simply doesn't exist for a stateless scalar function that only looks at the row in front of it.

The classification matters because of where it leads. A scalar UDF can, in principle, be fused directly into a vectorized pipeline, since it maps cleanly onto the batch model: one input, one output, repeated across the vector. A UDTF that emits a variable number of rows per input breaks that model outright, because vectorized engines are built around fixed-length batches, and a function that might emit zero rows for one input and nine for the next has no fixed length to offer.

One more axis cuts across all four types: the runtime the UDF executes in. SQL-based UDFs, written in the engine's own dialect, are transparent to the optimizer. The planner can read the function body the same way it reads any other SQL expression. External-runtime UDFs, written in Python, Java, or C++, are opaque. The planner sees a black box with a name and a signature, and nothing else.

| UDF type | Input → Output | Plan-time visibility (SQL vs. external runtime) | |---|---|---| | Scalar | 1 row/value → 1 value | Transparent if SQL-based; opaque if external | | Aggregate (UDAF) | many rows → 1 value/group | Transparent if SQL-based; opaque if external | | Table-valued (UDTF) | 0+ inputs → many rows | Transparent if SQL-based; opaque if external | | Stateful | sequential calls, carried state | Opaque regardless of runtime |

The four failure mechanisms UDFs introduce in the columnar contract

UDF overhead doesn't come out of nowhere. It traces back to four specific places where the columnar engine's assumptions stop holding, and each one has a different severity and a different remedy. Diagnosing which failure applies to a given slow query yields a targeted fix; skipping that step leaves only a guess.

The first failure is optimizer black-box treatment. The compression and pruning advantages columnar storage built up get undone before the UDF has executed a single instruction.

The second failure is row-at-a-time invocation. Each of those calls crosses an execution boundary, into a Python interpreter, a JVM, or a subprocess, and each crossing carries context-switch and serialization cost. What was a cache-friendly, tightly packed memory scan turns into a scattered sequence of calls dominated by latency. This is the core mismatch that most UDF-optimization research is aimed at solving.

The third failure is runtime isolation and cross-context serialization. When a UDF runs in a separate runtime, a Python sandbox or an external process, the engine has to serialize data out of its native columnar format, hand it across to the foreign runtime, let it process, then serialize the result back. For wide result sets or binary payloads, this serialization cost can outweigh the UDF's actual computation by a wide margin.

The fourth failure is state and broken parallelism. Aggregate and stateful UDFs that keep internal state can't be split across threads or nodes unless the engine has explicit merge logic for combining partial states, and most engines don't generate that logic automatically for a UDF they can't inspect. Table-valued UDFs add a second problem on top: when a UDTF emits a variable number of rows per input, the engine can't pre-allocate output buffers the way it does for fixed-cardinality operators, so it ends up re-allocating repeatedly or falling back to slower row-mode execution.

What operator fusion and JIT compilation recover

The strongest fixes for UDF overhead don't happen in the UDF's source code. They happen at the query-plan level, inside the engine itself, and how much they help depends on whether the engine can see what the UDF is doing.

QFusor, presented at EDBT 2026, is the clearest research example of this approach. It works by fusing scalar, aggregate, and table UDFs together with the relational operators sitting next to them in the query plan, rather than compiling the UDF section and the relational section as two separate units. That fusion enables longer JIT (just-in-time) compilation traces that span the entire combined pipeline.

Two concrete gains come out of that fusion. QFusor's design is pluggable and engine-agnostic: it integrates with several existing SQL databases rather than demanding each engine be rebuilt from scratch.

A specific kind of JIT matters here too: tracing JIT, as distinct from method JIT. A tracing JIT records the actual paths a UDF takes during real execution, compiles the hot path it observes most often, and falls back to plain interpretation for the rare branches that rarely get hit. That means the compiler is optimizing for the function's real behavior rather than trying to anticipate every possible path through it ahead of time.

Even short of full fusion, vectorizing the UDF call itself recovers a meaningful share of the lost performance. Batching the arguments into arrays and invoking the function once per batch, rather than once per row, is a prerequisite for any SIMD benefit, even when the engine can't inline the function's logic directly into the surrounding plan. It also amortizes call overhead across the whole batch.

None of this works unconditionally, though. These techniques depend on the UDF's source code being visible to the engine at plan time, or at minimum on a declared interface contract the engine can reason about. A UDF that calls an external API at runtime, or dynamically loads a library the engine has never seen, stays opaque no matter how advanced the JIT compiler is. Fusion and tracing can only optimize what they can observe.

Implementation patterns that preserve columnar performance from the UDF author's side

Engine-level fusion is not always available, and plenty of production systems won't have it for years. That puts real weight on how the UDF itself is written, and the gap between a naive implementation and a cooperative one is large enough to change which engine a workload can run on.

Accept and return arrays, not scalars. A UDF written to take a batch of values in a single call and hand back a batch of results lets the engine invoke it once per vector instead of once per row. For an engineer working in an external runtime without access to the engine's internals, this change carries the most leverage of any available option. It directly addresses Failure 2, row-at-a-time invocation, by removing the per-row call overhead at the source.

Declare purity and nullability. Annotating a function as pure lets the optimizer treat it like a built-in expression, opening the door to predicate reordering and common-subexpression elimination. This is the direct answer to Failure 1, optimizer black-box treatment: it doesn't make the UDF transparent, but it gives the planner enough information to stop being conservative around it.

Use the engine's native types at the boundary. Accepting Arrow columnar buffers, or whatever binary format the engine natively uses, instead of forcing a conversion to JSON or native Python objects, eliminates the serialization round-trip that defines Failure 3. Arrow's zero-copy interface exists specifically to let independent runtimes and components share data without that conversion tax, which makes it a natural fit for exactly this UDF boundary.

Keep aggregate UDFs decomposable. A UDAF that provides a separate partial-aggregate step and a separate merge step lets the engine parallelize the aggregation across partitions and combine the partial results afterward, recovering the parallelism that a monolithic, all-in-one UDAF destroys. This is the direct counter to Failure 4.

Push filtering ahead of the UDF call manually. Where the optimizer can't do predicate pushdown automatically, because the UDF is opaque, the engineer writing the query can still restructure it by hand, applying selective filters on cheap columns before the UDF ever gets invoked. It is a manual version of the same gain the optimizer would apply if it could see inside the function.

The schema-rigidity pressure that pushes teams toward UDFs in the first place

A lot of UDF usage in production columnar systems is a workaround for schemas that can't change fast enough to keep up with the data arriving. That reframes the whole conversation: fixing UDF performance means fixing execution and fixing schema design at the same time, not picking one.

When a schema can't evolve as quickly as the data it's meant to describe, engineers reach for UDFs to compensate. And that's precisely the place where columnar engines are weakest: dynamic, row-by-row logic sitting right where the engine wants fixed, typed, vectorizable columns.

Modern columnar systems have started addressing this from the schema side. Apache Doris's documentation describes this as performing "subcolumnization on frequent paths, exposing them as independent columnar subcolumns for both flexibility and performance." An AWS blog on Apache Iceberg's VARIANT support describes a similar approach, where the engine "shreds the structure into columnar Parquet sub-columns" while fields that don't fit the common pattern fall back to a binary representation. That is the schema-side answer to the exact symptom UDF sprawl represents.

The practical implication for anyone adding a new UDF: before writing a function that parses a JSON field, check whether the engine already has a native semi-structured type that would let that parsing happen inside the vectorized execution path. A parser running as a native VARIANT operation gets the SIMD and compression benefits of the columnar engine. A parser running as a UDF does not.

Observability pipelines are the clearest high-volume example of this pressure in practice. Schema design and UDF design are the same problem in that environment, approached from two different directions, and teams that treat them separately end up re-solving the same performance issue every time a log format shifts.

UDF Opacity and Accountability in Agentic Workloads

AI agents issuing queries against columnar engines introduce a second kind of opacity on top of the first. The optimizer already can't see inside an external UDF. An agent constructing that query dynamically often can't explain why it chose to call that UDF. Put those two gaps together: the result is a query that runs fast and produces an answer nobody can audit.

A human analyst writing a UDF call into a SQL query generally knows what the function does and why it was the right choice for the task. It picked a name off a list. It has no model of the function's logic.

That produces a specific kind of lineage blindness. Because the optimizer already treats the UDF as a black box, the query log records that a function was called, with what arguments, but not what logic it applied or what intermediate state it produced along the way. The audit trail has a gap sitting exactly where the custom business logic lives, which tends to be the most consequential part of the computation, not the least.

Volume makes this worse. A UDF that is merely slow in a human-driven workload becomes a throughput bottleneck and a direct cost multiplier once an agent is calling it at that scale.

Logging helps, but only partway. A log of "function X was called with argument Y" is not the same as a reproducible record of what happened.

One response to all this is to restrict agents to built-in functions only, removing UDF opacity from the picture. That solves the problem of accountability cleanly, but it forecloses the custom parsing and enrichment logic that makes agents useful for domain-specific analytical work. It trades away capability to buy back auditability, and for most teams that's not a trade worth making.

A more workable path combines three things: isolated, read-only compute environments for agent-issued queries, full query logging that captures UDF arguments alongside the query itself, and UDF source code managed as versioned, reviewable code rather than an ad hoc script sitting in a notebook somewhere. None of that makes the optimizer able to see inside the UDF. It does mean the audit trail stays complete even when the optimizer can't, and that completeness is what matters once agents are running at production scale.