Ten Million Rows Out of Elasticsearch, Parsed Once

We measured the same 10-million-row extraction from the same Elasticsearch index over two SQL stacks — SoftClient4ES over Arrow Flight SQL, and Trino 483 with its Elasticsearch connector — back to back on the same host, plus Elasticsearch’s own ES|QL wherever its row ceiling lets it run. The differences that matter most are not the stopwatch: an aggregation that moves 27.1 KB off the cluster instead of 1.39 GB, and a client that lands the full result in a 2 GB container where the alternative is killed at 8 GB. It is also faster. This is the byte’s journey that explains why — with the honest caveats attached.

Everyone pays the toll

Elasticsearch serializes everything it hands you. _search returns JSON. The scroll API returns JSON. The SQL endpoint will give you CSV, YAML, or a binary cousin like CBOR if you ask — every one of them a row-by-row document serialization. Even Elastic’s own JDBC driver — “a platform independent, stand-alone, Direct to Database, pure Java driver that converts JDBC calls to Elasticsearch SQL”, in their words — is a client of the SQL REST API: your ResultSet starts life as a POST /_sql response the driver parses. There is no external client, from anyone, that gets Elasticsearch data without a serialization step out of the cluster.

One path out is columnar, and it is worth knowing about before anyone sells you anything: ES|QL will answer format=arrow with an Apache Arrow IPC stream. It is genuinely fast — faster than either SQL engine here, at the one scale all three can be compared — and it stops at a ceiling it cannot leave. We measured it as a third stack; the numbers, and the ceiling, are in their own section below.

So whatever you put in front of Elasticsearch — Trino, a homegrown scroll loop, or an Arrow Flight SQL sidecar — that first toll is paid, and above a million rows the row-serialized path is the only one there is. This post is about what happens after the toll, because that is where the stacks diverge, and at ten million rows the divergence is the whole story.

The byte’s journey

Two stacks, same index, same SQL. Here is what one value goes through on each path. One counting convention, applied symmetrically: building a columnar in-memory format counts as a serialization on both legs — Trino’s Page build and our Arrow build alike.

Trino path:
  ES ──JSON (scroll, 1000/page)──► ES connector ──parse #1 + serialize #1──► Trino Pages (columnar Blocks)
     ──exchange──► coordinator ──serialize #2──► client protocol (JSON rows, HTTP)
     ──parse #2──► Python tuples ──convert #3 (if analytics)──► DataFrame / Arrow

SoftClient4ES path:
  ES ──JSON (scroll, 1000/page)──► sidecar ──parse #1 + serialize #1 (the last one)──► Arrow RecordBatches
     ──gRPC Flight SQL──► pyarrow Table (the same buffers) ──register──► DuckDB (zero-copy)

The Trino path. Trino’s Elasticsearch connector scrolls the index 1,000 hits at a time and parses each JSON document into Trino’s internal Page format — which, credit where due, is columnar: a Page is an array of Blocks, one per column. Pages flow through the exchange to the coordinator. Then comes the part that matters to your laptop: the coordinator serializes results into Trino’s client protocol — JSON rows, polled over HTTP — and your Python client parses every single value again, this time into Python objects. For one value, that is two parses and two serializations after Elasticsearch — counting the columnar Page build as one, by the same rule we count our own Arrow build below. And if the destination is a DataFrame or DuckDB, there is one more conversion still to pay.

from trino.dbapi import connect

conn = connect(host="localhost", port=8080, user="bench",
               catalog="elasticsearch", schema="default")
cur = conn.cursor()
cur.execute(
    "SELECT id, event_ts, amount, qty, status, country, category, name "
    "FROM bench_events_10m"
)
rows = cur.fetchall()             # ~10M Python tuples — every value parsed from JSON

The SoftClient4ES path. The Arrow Flight SQL sidecar scrolls the same index 1,000 hits at a time and parses each JSON document once — into Arrow vectors, 1,000 rows per batch. That is the last time any of these bytes are parsed. The batches stream to your client over gRPC as Arrow IPC, and the pyarrow Table your cursor hands back is those buffers — exactly the columns your SQL selected. The client is the stock adbc_driver_flightsql driver from the Arrow project; there is nothing SoftClient4ES-specific to install on the Python side.

import adbc_driver_flightsql.dbapi as flight_sql

conn = flight_sql.connect("grpc://127.0.0.1:32010")
cur = conn.cursor()
cur.execute(
    "SELECT id, event_ts, amount, qty, status, country, category, name "
    "FROM bench_events_10m"
)
table = cur.fetch_arrow_table()   # a pyarrow Table — these ARE the wire buffers

Register it in DuckDB and DuckDB scans those buffers in place — no copy, no conversion. One parse. One serialization after Elasticsearch.

import duckdb

con = duckdb.connect()
con.register("events", table)     # zero-copy: DuckDB scans the Arrow buffers in place
con.execute(
    "SELECT category, AVG(amount) AS avg_amount FROM events GROUP BY category"
).fetchall()

That aggregate runs inside DuckDB directly over the Arrow buffers — over all ten million rows already sitting in the client. The three highest-averaging categories, pasted from the full-scale run backing this post:

('cat_096', 502.6150436023308)
('cat_075', 502.1835884952659)
('cat_079', 502.089874846552)

Full disclosure: our own JDBC driver is a JSON-rows path too — JDBC’s ResultSet model is row-oriented and nothing inside the JDBC API can change that. The Arrow story is specifically the Flight SQL / ADBC surface. If your consumer is DBeaver, JDBC is fine; if your consumer is a dataframe, the wire format is the whole game.

The numbers

Everything below comes from a reproducible benchmark: Elasticsearch 8.18.3, Trino 483, and the released softclient4es8-arrow-flight-sql:0.3.0 sidecar, on the same host, reading the same 10,000,000-document index (flat mapping, six primary shards; Trino’s connector creates one split per shard, so the shard count is what bounds its scan parallelism, and the section “But your index had one shard” re-runs the extraction on a single shard). Trino gets more hardware than we do, deliberately: it runs as a 3-node cluster totalling 6 CPU / 8 GB in every scenario, against one SoftClient4ES sidecar on 4 CPU / 4 GB; Elasticsearch gets 4 CPU / 4 GB. Every run asserts the exact expected row count before its timing is recorded. Methodology and configs: on GitHub.

One disclosure up front, because it is about us: SoftClient4ES enforces a result-set quota by licence tier (Community 10,000 / Pro 1,000,000 / Enterprise unlimited), so the 10-million-row scenarios ran under an Enterprise-tier licence. The licence lifts a row quota only — it does not change the batch size, the serialization, or the data path.

Start with the result that has nothing to do with wire formats — because it is the biggest one. A GROUP BY over the 10M rows, returning 100 groups:

SoftClient4ES compiles the GROUP BY into an Elasticsearch terms aggregation: the cluster computes the 100 groups and returns only those 100 rows. Trino’s Elasticsearch connector performs predicate push-down only, per its documentation — so it scans all ten million rows into Trino and aggregates there. To be precise about the attribution: this gap is aggregation pushdown, not the wire format. For work Elasticsearch can do itself, SoftClient4ES does not move the data at all.

Next, what extraction costs the client. Landing all 10M rows as a columnar table: SoftClient4ES spends 2.83 s of client CPU against 24.05 s for Trino’s documented client — 8.5× less — and against 10.52 s for connectorx, its fastest route, 3.7× less. Peak client memory is 921 MB against 4,455 MB for the documented client, 4.8× less. The reason is representation, not cleverness: the Arrow client never builds ten million Python objects. One exception belongs here rather than in a footnote: connectorx builds contiguous buffers in Rust and lands the bare Arrow table in 617 MB — less than we do.

That difference turns into a hard capability line when memory is bounded. Running the client inside a memory-capped container, landing the full 10M-row DataFrame:

And concurrency: in an 8 GB total client budget, SoftClient4ES completes five simultaneous 10M-row extractions. Trino’s documented client cannot complete one — but that is the wrong client to stop at, so we ran connectorx too: it completes two. Five against two is the honest ratio, and it follows from what each client holds per extraction. (This measures client-side capacity, not server-side concurrency.)

Speed last — because it is the least surprising part. The full extraction to a columnar client table: 11.91 s against 44.70 s for the documented client — 3.75× faster — and against 14.66 s for connectorx, its fastest, 1.23× faster. The low end is the serious comparison: nobody extracts ten million rows through the documented client on purpose. To a pandas DataFrame, the artifact an analyst builds, we are 4.7–5.6× faster than the documented client and 1.3–1.6× faster than its fastest route. Landed in DuckDB and aggregated: 4.3–5.1× faster on 8.0× less memory than the documented route.

Is the documented client a strawman? Fair question — Trino has faster clients, and we measured them. connectorx parses Trino’s JSON result pages into columnar buffers in Rust; the ADBC Trino driver returns Arrow tables through the same DB-API our own client uses. On the same 10M-row query:

Quoting the benchmark: “SoftClient4ES keeps the wall-clock lead against every Trino client — 1.23× against the fastest of them, 3.75× against the documented one.” And an honest concession that cuts the other way: connectorx, building contiguous buffers in Rust, lands a smaller bare Arrow table than our chunked Flight batches — 617 MB vs 921 MB — though that advantage does not carry to the full DataFrame, where SoftClient4ES needs less (that is the 2 GB row in the table above).

“But your index had one shard.” The sharpest question this benchmark got, and it deserves a straight answer. Trino’s Elasticsearch connector creates one split per shard, so a single-shard index hands it a single reader — the shard count, not the node count, is what bounds its scan parallelism. Adding Trino workers to a one-shard index changes nothing. So the matrix above runs on a 6-shard index, and the control goes the other way: we re-ran the extraction with the shards taken away, on a single shard. Trino ran as a real 3-node cluster — dedicated coordinator plus two workers — in every scenario in this benchmark, holding 6 CPU / 8 GB against our 4 CPU / 4 GB throughout; only the shard count changed.

Removing them costs us far more than it costs Trino — which is the honest way round to publish it:

Going from six shards to one costs us 3.50× and Trino only 1.23× — so the gap narrows, from 3.75× to 1.32×. Most of our lead at six shards is concurrent paging, and a single-shard index takes it away; we state it in that direction because that is the direction that cuts against us. What did not move: the 2 GB container threshold, and the pushdown result — the GROUP BY moves 25.9 KB at one shard and 27.1 KB at six, against 1.39 GB either way, because sharding an index does not teach a connector to push aggregations down. That is the distinction worth taking away: wall-clock is topology-sensitive; client cost and pushdown are not. The client is one process consuming one wire format however large the cluster gets.

Shards are exactly what Trino’s connector parallelises over — one split per shard — and its GROUP BY gets the benefit. It just still reads all 10 million rows to get there.

And the control. Fetch 100 rows with LIMIT 100: 37 ms vs 65 ms — parity, as it should be. With a small result the wire format stops mattering; the extraction advantage exists at scale, or when work can be pushed into Elasticsearch.

The third stack: Elasticsearch’s own ES|QL

A benchmark of two SQL engines over Elasticsearch that never measures what Elasticsearch itself can do invites an obvious question, so we measured it — over both of its wire formats, in the same session as everything above.

At a million rows — the largest result all three stacks can return — ES|QL wins outright:

That is not a close call, and the reason is architectural rather than clever engineering: ES|QL reads doc_values — already columnar on disk — while both SQL engines read _source and pay a JSON parse per document.

It does not win everywhere — but one verdict here reversed when we re-measured, and it is worth saying so plainly. On the pushed-down aggregation the two are now level: 0.034 s for ES|QL against our 0.043 s, with overlapping five-run intervals. What still separates them is the bytes — 27.1 KB off the cluster against its 45.2 KB. On a 100-row LIMIT fetch ES|QL takes it outright: its entire round trip costs 6 ms, less than our connection handshake alone.

Where it stops, and this is the part to write down. esql.query.result_truncation_max_size defaults to 10,000 rows and is declared with a hard maximum of 1,000,000 (EsqlPlugin.java, Elasticsearch 8.18.3 — a setting, so check it against your own version). Ask for ten million rows with the setting at its maximum and you get 1,000,000 rows, HTTP 200, and no Warning header: a truncated answer that looks exactly like a complete one. That is a correctness hazard, not a performance note. What changes above a million rows is not that ES|QL becomes slow — it becomes unavailable.

There is a second boundary. LOOKUP JOIN resolves only against an index in lookup mode, and such an index is restricted to a single shard — a dimension table. Asked for a join between two fact indices it answers 400 — invalid [bench_1m] resolution in lookup mode to an index in [standard] mode. So the cross-index join has no ES|QL column either, for the same kind of reason: not slower, outside what the feature accepts.

Why you can’t bolt this on

Could a JSON-protocol engine just add Arrow output? Let’s be precise about what Trino ships today, because its client protocol has been evolving — and honestly, evolving well. Since Trino 466 there is a spooled client protocol: the server writes result segments to object storage (S3-compatible, Azure, or Google Cloud Storage — there is no local-filesystem option) and the client fetches segments directly instead of polling the coordinator. That is real protocol engineering, aimed squarely at large results.

Now look at the encodings the spooled protocol defines: json, json+zstd, json+lz4. Compressed, spooled — still JSON rows. Every value is still parsed by the client, one by one, into engine-agnostic objects. There is no Arrow encoding in the protocol, in the server properties, or in any shipped client driver.

Adding one would not be a config flag. It is a protocol-spec change, plus segment writers on the server, plus decoding support in every client driver — JDBC, CLI, Python, Go — plus a type-mapping contract between Trino’s type system and Arrow’s. And after all of that, results that exist as Trino Pages would still be converted to Arrow at the boundary: one more serialization, in exactly the place the sidecar’s batches are simply born Arrow, at the source adapter.

You do not have to take the protocol argument on faith, either — the closest thing to bolting Arrow onto Trino already exists, on the client side. connectorx re-parses the JSON protocol into Arrow in Rust; the ADBC Trino driver wraps the same protocol in an Arrow API. Both are good engineering, and both showed up above: the gap narrows — 44.70 s documented, 26.97 s through ADBC, 14.66 s through connectorx — and stops there, short of our 11.91 s — because the re-serialization and the re-parse still happen, just in faster code. The floor is set by the protocol, not the client library.

None of this means Trino can’t do it — it’s software, and Trino’s team is excellent. It means the wire format is an architectural commitment, not a feature toggle. If Arrow-native delivery into DuckDB, Pandas, or Polars is the thing you need, you want the stack that made that commitment end-to-end.

Where we don’t win

This benchmark poses exactly one problem: pull a large result set out of one Elasticsearch index on one machine and use it. That is SoftClient4ES’s home turf, and it is fair to say so out loud. Trino is built for problems this post deliberately does not pose — and on the one it does, Trino keeps real wins:

• Cross-index joins — all three of them — Trino is faster on every join shape we measured, by 7.0%, 12.4% and 22.3%, each winning all 25 run-pairings. An earlier edition of this benchmark had us taking the plain join; it did not survive re-measurement, and the honest summary is that joins are Trino’s, not ours.
• A lower-memory Arrow client exists — via connectorx, Trino can land a bare Arrow table in less client memory than SoftClient4ES (617 MB vs 921 MB), as conceded above.
• A million rows is Elasticsearch’s own — at 1M rows, the one scale all three stacks can reach, ES|QL returns the result over Arrow in 0.32 s against our 3.99 s and Trino’s 4.42 s — 12.5× faster than we are. It reads doc_values, columnar on disk, where both SQL engines read _source and pay a JSON parse per document — a real architectural advantage, bounded only by its 1,000,000-row ceiling.
• And a sliced scroll is close behind — six Elasticsearch slices, one per shard, build the same Arrow table in 13.76 s against our 11.91 s, for 8.5× SoftClient4ES’s client CPU on the same extraction, spread over six processes. It used to beat us; concurrent paging closed that gap, and 1.16× is a thin margin over a floor, which is why we publish it. It returns nothing you can compute on — no Arrow table, no DataFrame — which is what makes it a floor rather than a contender. But anyone who says “I’ll just parallelise my scroll” is right about the wall clock, and should hear it here rather than discover it later.
• Distributed scale, spill, fault tolerance — multi-worker parallelism for scans and JOINs far beyond one node’s memory, spill-to-disk, task retry. A single-node extraction benchmark exercises none of it.
• Connector breadth — 40+ sources; Hive, Iceberg, and Delta are its sweet spot. SoftClient4ES is Elasticsearch-centric.
• Licence — Apache 2.0 end to end, no tier gating result-set size. SoftClient4ES’s core — the SQL engine, client API and REPL — is Apache 2.0; the drivers, sidecar, extensions and federation are Elastic License 2.0 (free to use, sources not public), with a licensed quota above 10,000 rows.

If Elasticsearch is one source among a dozen and your JOINs are hundred-million-row affairs, run Trino — and accept its ES connector’s limits. If Elasticsearch is the center of your data world and your pain is getting its data out and into the Arrow ecosystem, that is the workload we built for. Plenty of teams should run both.

Try it

The sidecar is one command in front of any Elasticsearch 6, 7, 8, or 9 cluster:

docker run -p 32010:32010 \
  -e ELASTIC_HOST=<your-es-host> \
  -e ELASTIC_PORT=9200 \
  softnetwork/softclient4es8-arrow-flight-sql:0.3.0

Two notes for a first run: a stock Elasticsearch 8 cluster ships with security enabled — set ELASTIC_AUTH_METHOD and the ELASTIC_CREDENTIALS_* variables for secured clusters (the baked default is noauth). And on the free Community tier, result sets are capped at 10,000 rows; the full 10-million-row pull above needs a licence tier whose quota exceeds it.

Then the three lines from this post, against your own index, with the stock Arrow driver: connect, execute, fetch_arrow_table(). Register the result in DuckDB and run your first aggregate over the full extraction — no copy, no conversion.

The benchmark behind every number here — harness, configs, deterministic data generator, methodology, raw run JSON — is available on GitHub. The short demo clip of both stacks running the same extraction, one after the other, is on the home page.

Resources

• The report (PDF): SoftClient4ES vs Trino — Extraction Benchmark — every scenario, the methodology, and where Trino is stronger, in one document you can send to a team
• Arrow Flight SQL documentation: Full Documentation
• GitHub: SoftClient4ES Repository
• Discussions: Ask Questions
• LinkedIn: SoftNetwork

How many times does your Elasticsearch data get parsed on its way to a DataFrame? If the answer is “more than once”, there might be a shorter path.

P.S. — The Arrow Flight SQL server is part of the free Community tier. ES 6, 7, 8, and 9 supported.