Push vs pull query engines for security analytics.

Start with the query a SOC analyst runs ten times a day:

SELECT src_endpoint.ip, COUNT(*)
FROM security_events
WHERE severity >= 4 AND time > NOW() - INTERVAL '1 hour'
GROUP BY src_endpoint.ip;

A billion rows sit in the scan window, the query filters on severity, groups by source IP, and counts, which is the shape of most threat-hunting and detection-engineering queries: heavy projection on a few columns, a selective predicate on time and severity, a small aggregation key, and no complex join, no recursive CTE, no window function. It's the unglamorous core of security analytics, and it dominates SOC compute because so much of the day-to-day work reduces to exactly this pattern.

On a Volcano-model pull engine (the architecture Trino, Presto, and pre-2.0 Spark share with decades of database history) that query may take fifty seconds against a cold cache on commodity hardware. On a morsel-driven push engine like DuckDB or ClickHouse, the same query against the same data may finish in five seconds, even though it's the same SQL against the same Parquet files and the same Iceberg metadata, so the ten-times difference is attributable in significant part to how the engine moves data between operators internally.

The architectural distinction goes by several names in the literature: iterator-based versus data-centric, demand-driven versus producer-driven, pull versus push. They all describe the same decision: whether the consumer at the top of the operator tree asks its child for one more row, or the source at the bottom hands its parent a batch of thousands of rows and lets the work flow upward. The choice shapes function-call overhead, CPU cache behavior, vectorization opportunities, and parallel scheduling, so for analytical workloads on columnar data push tends to win, while for federation across heterogeneous sources pull is still the better fit, and both of those reasons matter for security teams designing a data stack.

Push-based execution inverts the control flow. Instead of the consumer asking the producer for the next row, the producer hands the consumer a batch and tells it to deal with it. The table scan at the bottom of the plan reads a chunk of data (often a column buffer of several thousand values) and calls a consume() or push() method on its parent operator. That parent processes the batch, possibly filtering or transforming it, and pushes the result up to its own parent. The data flows from source to sink, batched all the way.

The shape of a push-based filter operator looks like this:

class FilterOperator:
    def consume(self, batch):
        mask = self.predicate.evaluate(batch)   # vectorized over the whole batch
        filtered = batch.filter(mask)           # zero-copy filter on column buffers
        if len(filtered) > 0:
            self.parent.consume(filtered)       # push survivors up

Three things change at once. First, the function-call count drops by roughly three orders of magnitude. A batch size of around a thousand means a billion rows become a million pushes, so function dispatch overhead stops dominating the profile. Second, the predicate evaluation becomes vectorized. The filter operator hands a column buffer to the SIMD unit, which evaluates the predicate on many values per CPU instruction. Third, cache behavior improves dramatically. The column values stay in L1 or L2 cache as the operator chews through the batch, instead of getting evicted and reloaded for every row.

The push model also fits streaming naturally. A stream of new security events arriving from Kafka is producer-driven by definition: the events keep coming whether the consumer is ready or not. A push-based engine just keeps consuming batches as they arrive. A pull-based engine has to model the stream as a special kind of iterator and deal with the impedance mismatch. Apache Flink, which is push-based from the ground up, sits in the right shape for streaming security analytics. So does Falcon LogScale (formerly Humio), whose architecture was designed around push-based brute-force scanning of log data.

Push isn't free, though, because the producer-driven model loses some of the lazy evaluation a pull model gets for free, so a LIMIT query in a pure push model has to push batches until the consumer signals "enough," which is harder to short-circuit cleanly. Modern push engines handle this through backpressure signals or hybrid approaches, but the implementation is fiddlier than the elegant Volcano iterator, and that simplicity is part of why the pull model lasted so long even as the throughput of the push model is what wins it the OLAP corner now.

Security analytics has a workload shape that lines up almost too neatly with push-based execution. Three properties dominate.

Scan-heavy time-window queries. The canonical SOC query asks for everything matching some predicate over the last hour, day, or week. That's a large scan with a selective filter, exactly where push execution shines because the filter can be vectorized over column buffers and the surviving rows can flow up the pipeline as a tight cache-resident stream. The pull model pays a per-row function-call tax across the whole scan window; the push model amortizes that cost across batches.

Simple aggregation patterns. Security queries usually group by a small number of dimensions (source IP, user, host, alert type) and count, sum, or rank. The aggregation hash table stays small enough to fit in cache, partial aggregates per morsel merge cheaply at the end, and the whole pipeline runs hot. Complex multi-way joins where pull-based optimization shines are rare in the detection and threat-hunting paths I see; they show up more in compliance reporting, which tolerates higher latency.

Streaming ingestion. Security telemetry arrives continuously. Kafka pushes events to consumers. The natural fit on the consumer side is a push-based engine that processes batches as they land: Flink for streaming CEP (complex event processing), Falcon LogScale for log analytics at scale, ClickHouse materialized views for incremental rollups. A pull-based engine has to model the stream as a special kind of source and pretends to ask for the next row, which is the wrong shape for the underlying physics.

A fourth property matters for detection engineering specifically. Real-time detection rules need to evaluate against every event, not against a snapshot every few minutes. That's the territory where DBSP (the database stream processor formalism from academic research, with early implementations in Feldera and Materialize) takes push execution further still: represent the detection rule as an incremental dataflow, and update the result with each event rather than rerunning the whole query at intervals. For a rule like "alert when a user has more than 100 failed logins in the last hour," DBSP-style incremental push can move detection from minute-latency down to millisecond-latency, which is why I treat that pattern as the leading edge: promising, with early production deployments, but not yet routine.

The fit isn't universal, because forensic investigation queries that join across many tables and walk relationships look more like classical OLAP, where Trino's pull model handles them perfectly well, and compliance reports that scan a year of data to produce a small summary table tolerate higher latency. But the operational SOC workload (alert triage, threat hunting, real-time correlation) sits squarely in push territory.

The push-versus-pull framing is useful as an architectural distinction, but production engines are rarely pure on either side. Several patterns blend the two:

Planning pulls, execution pushes. Plan generation in most modern optimizers is demand-driven: the planner pulls cost estimates from each operator to decide on a join order or a partitioning strategy. Once the plan is compiled, the runtime hands it off to a push-based executor that streams batches through the pipeline. DuckDB and Velox both work this way, so the planner gets the lazy semantics it needs to reason about the query while the executor gets the throughput.

Apache Arrow Acero. Acero is the streaming execution engine that's part of the Apache Arrow project: explicitly push-based, vectorized, designed to consume and produce Arrow buffers. It powers parts of Arrow Datasets and shows up in pandas, Polars, and DataFusion-adjacent stacks. I mention it not because it's a dominant production engine for security data yet, but because it's the canonical reference implementation of "what a pure push engine looks like in the Arrow ecosystem," and it's worth knowing about when reading source code or evaluating new tools.

Pipeline breaks. Some operators can't pass data straight through without materializing first: hash joins build a hash table on one side before probing with the other, sort operators have to see everything before they can return the first row, aggregations may need to spill to disk on large groupings. These pipeline-breaker operators force a transition from streaming push to materialize-and-push-the-next-stage. Engines that handle pipeline breaks gracefully (with cache-aware blocking, spilling, and partial-aggregate combining) tend to outperform engines that don't. This is one place ClickHouse's mature spill-to-disk and partial-aggregate combining show up in production.

Vectorized iterators. Trino's current execution model is best described as batched pull or vectorized Volcano: operators still implement an iterator contract, but each call returns a batch (a "page" in Trino terminology) rather than a single row. It's not the same as push-based execution, but it captures most of the per-row function-call savings while keeping the pull semantics that make federation tractable. The Presto-on-Velox path goes further by replacing the data plane underneath the pull-based control plane.

A few honest caveats I'd put in front of any architect making a real engine selection on the basis of push-vs-pull arguments:

The 10x performance gap is workload-dependent. The "five seconds versus fifty seconds" framing at the top of this essay is real for the canonical filter-aggregate scan-heavy query against a billion rows. It's not a universal multiplier. Wide joins, complex window functions, queries that hit cold storage with high latency: the gap narrows, and for joins I've now measured how far. On the lab's scored join bench (Tier B, single host, 10M–60M-row tables over shared Iceberg) every engine answered the SOC-shaped join suite in under 1.5 seconds, and the join overhead relative to each engine's own flat scan sat near 1.8× for StarRocks and both ClickHouse arms, with Trino the outlier at 4.35× — single-host numbers, so they say nothing about the TB-scale regime behind the headline framing. Some queries run comparably on Trino and ClickHouse because the bottleneck is somewhere else entirely (network bandwidth, S3 list-operation latency, hash-table sizing). Benchmark your own workload before building an architecture decision around a directional claim.

Operational maturity isn't the same as architectural fit. ClickHouse's push architecture is a real advantage for log analytics, but the operational story (cluster management, replica coordination, schema migration) takes investment to run well. Trino's architectural disadvantage on scan-heavy workloads is partially offset by deeper operational tooling and a longer track record at large enterprise scale, so the architecturally cleaner choice isn't always the one that's easier to run once it's in production.

Security-specific benchmarks are scarce. Most of the published push-vs-pull performance numbers come from TPC-H, TPC-DS, or vendor-curated synthetic benchmarks. OCSF-shaped security event data with realistic cardinality, wide schemas, and time-window-heavy predicates isn't well-represented in public benchmark suites. The directional argument from the literature is sound; the precise improvement number on your firewall logs or EDR telemetry is something you'd have to measure, so I went and measured a first slice of it on the MOAR reference stack.

The bench runs three of the scan-heavy filter-aggregate shapes this essay is about over a 1,000,000-row OCSF network_activity table, taking the median of four trials per query, and the answers were checked equal across the engines before any latency was trusted.

These are single-host figures at one million rows, so read the relative pattern rather than the absolute milliseconds, and the relative pattern holds up: ClickHouse's morsel-driven push beats Trino's Volcano-style pull by roughly three to four times on these workloads (4.4x on the needle, 3.2x on the group-by, 3.8x on the count), and DuckDB's embedded vectorized engine, with no coordinator and no network hop, is far ahead of both at this single-host scale.

So the honest number is closer to three or four times than to the ten times the canonical billion-row framing implies, and the gap I measured is smaller because this is a single-host snapshot at a million rows rather than a billion-row scan against a cold cache. What this slice actually isolates is the execution-model difference at one point on the curve, because Trino's pull-and-coordinate model carries distribution overhead that earns its place at federation and at large scale, not at small single-host batches, which is exactly the case the engine wasn't built for and exactly the nuance the rest of this essay keeps insisting on. The lab will widen this out to larger row counts and a real multi-node Trino deployment, where I'd expect the coordinator overhead to amortize and the gap to read differently.

So I reran the same three shapes at a hundred times the volume, a 100,000,000-row OCSF network_activity table on the same single host, again the median of four trials, and the picture rearranges in a way that's more interesting than a uniform scaling factor. StarRocks joined this run, so the field is four engines. On the count(*) full scan ClickHouse finished first at about 10.5 ms, with DuckDB just behind at 12.4 ms and StarRocks and Trino back at 48.2 and 44.4 ms, which is the throughput-bound scan where the push model's pipeline parallelism finally pays off enough to overtake the embedded engine that swept everything at a million rows. On the needle predicate (dst_port=3389) DuckDB still led at 77.7 ms ahead of StarRocks at 95.0, ClickHouse at 182.4, and Trino at 419.2, and on the GROUP BY dst_port DuckDB led again at 103.1 ahead of StarRocks at 194.4, ClickHouse at 229.1, and Trino at 668.9. Trino stayed slowest on every shape, which is what a single-host run does to a coordinator-and-distribution engine that was built for neither single host nor small batches.

The crossover is the part I'd flag for an architect, because at a million rows the embedded DuckDB was unambiguously fastest on all three shapes, and at a hundred million that advantage turns out to be scale-bounded: ClickHouse's morsel-and-push parallelism caught and passed DuckDB on the full-scan count, where raw throughput is the whole game, while DuckDB held its lead on the selective needle and the group-by, where there's less to parallelize and the embedded engine's lack of a coordinator hop keeps winning. The push-versus-pull and embedded-versus-server patterns this essay is built around are not fixed multipliers but functions of scale and query shape, so the right takeaway from these two runs together is that the engine that wins flips as the row count climbs and as the query moves from throughput-bound to selective. These remain single-host figures, so the relative ordering is the finding and not the absolute milliseconds, and the multi-node Trino run is still the test that would let the pull model show what it was actually designed for.

The single-query latencies above hold one client steady, so to see what happens when a SOC actually loads an engine I ran a concurrency sweep on the same single host (the lab's concurrency_sweep.py), pointing C clients at the same scan-aggregate over a shared 10,000,000-row OCSF table and watching throughput and tail latency as C climbed from 1 to 16. The two push-and-scheduler engines turned added clients into aggregate throughput: ClickHouse's morsel-driven model rose from about 20 to 58 queries a second with the gentlest tail (p95 from 59 to 355 ms), and StarRocks climbed from 16 to 42 q/s before plateauing around eight clients (p95 out to 476 ms). Embedded DuckDB held flat at roughly 46 q/s with its p95 stretching from 57 to 689 ms, which is what one process on a fixed core budget does once the clients outnumber the cores it has to spread them across. Trino was the one that struggled under load, sitting flat and low at 9 to 13 q/s while its p95 ran from 128 ms all the way to 1,736 ms, by far the worst tail in the field, because on a single host the coordinator-and-worker overhead dominates and there is no cluster to spread the concurrent load over.

That is the measured version of the point this essay has been making about where Trino belongs: under concurrency on one box the push and scheduler engines convert each new client into throughput while Trino pays its distribution tax with no nodes to amortize it over, which is the same scope caveat the single-query benches carried, only now visible in the shape of the throughput and tail curves rather than in a single latency. The honest boundary is the same too, since this is still one host, and the multi-node cluster concurrency that Trino's coordinator model was actually designed to win remains a test I'd have to run on real cluster hardware before reading anything into it.

Federation will keep mattering. The clean "push for OLAP, pull for federation" line works as a first approximation, but security operations regularly need both. A SOC team that standardizes on ClickHouse for the operational data plane and Trino for federation queries is running two engines, with the operational complexity that implies, and the cleaner alternative of moving everything into one engine usually means giving up either federation reach or scan throughput, so the decision comes down to which of those trades you're willing to accept. The worked scorecard is where those trades get weighed per environment archetype.

The hybrid future is messier than the architectural framing. Presto-on-Velox, Spark with Photon, and Trino's continued vectorization investments mean the "Volcano vs morsel" line is becoming less sharp at the production edge. The architectural distinction will still matter for understanding why an engine performs the way it does, but vendor selection in 2027 may look less like "pick push or pick pull" and more like "pick which hybrid the vendor has invested in." I'd treat the framing in this essay as a lens for reading new engines, not as a two-bucket sort.