Query Engines¶

This module compares engines that query external storage with managed cloud warehouses, where storage, workload isolation, governance, and operations are bundled behind a service contract.

4:15 PM. An analyst posts in the platform channel: "I need product events, customer records from Postgres, and billing from another team's MySQL, joined, by end of day." Someone replies "sure — we'll pipeline it into a warehouse, give us two weeks." The analyst says they need it today, not in two weeks.

Is a new ETL pipeline actually the right call here?

A. Yes — always land it in a warehouse first; federation is a shortcut that costs you later. B. No — a query engine can run SQL across all three stores today, at the price of paying network and each source's worst access path on every run. C. It depends on whether this becomes a recurring query or stays a one-off.

It's B for today's ask, and C is the honest answer for next quarter — which is exactly the tension this module is built around. You already have the data: product events live as Iceberg tables on object storage, customer records live in Postgres, billing lives in another team's MySQL. Nobody wants to copy 40 TB into a fourth system first — that copy is stale the moment it lands, expensive to keep, and somebody else's on-call. A query engine is the piece that runs SQL over data it does not own. Compute is a cluster. Storage is whatever the connectors can read. That split is the entire product.

Workload this module is built around¶

The SaaS analytics platform stores:

Questions that actually get asked:

• “p95 latency for /checkout last Tuesday, broken down by plan tier” — Iceberg events JOIN Postgres plans.
• “which customers on the enterprise plan still hit the deprecated API?” — same join, different filter.
• “reconcile yesterday’s ClickHouse dashboard with the lake” — federation, not a second ETL.

If the question is a customer-facing dashboard at 200 QPS and 200 ms, this is the wrong module. Go to ClickHouse or Pinot. If the question is “SQL over whatever we already have,” stay here.

What this module covers¶

Trino is the engine we go deep on because it is the one you will actually operate against a lakehouse. Presto, Spark SQL, and BigQuery share pieces of the same mental model — stages, shuffles, stats — but they do not federate the same way.

The split: engine vs database¶

A database owns bytes on disk. Postgres, ClickHouse, Cassandra: you INSERT, they store, they query their own files.

A query engine borrows bytes. Trino never writes a MergeTree part. It asks a connector for splits, workers read them, they shuffle intermediate rows, they return a result set, and they forget.

flowchart LR
    SQL["SQL"] --> C["Coordinator\nparse / plan / schedule"]
    C --> W1["Worker"]
    C --> W2["Worker"]
    C --> W3["Worker"]
    W1 --> ICE["Iceberg / S3"]
    W2 --> PG["PostgreSQL"]
    W3 --> CH["ClickHouse"]

That picture has three consequences you cannot negotiate:

1. You still pay the network. Joining Iceberg to Postgres means workers pull Iceberg columns and Postgres rows. Federation is not free compute over a magic bus.
2. The source’s access path is your access path. If Postgres cannot split a 200 GB table, one Trino worker waits on one JDBC scan. The cluster size does not fix that.
3. There is no storage-side index you control, except what the source already has (Iceberg manifests, Postgres B-trees, ClickHouse marks). Trino can prune; it cannot invent a primary key on someone else’s table.

How a query actually runs¶

Forget “Trino is distributed SQL” for a moment. A query is a tree of stages connected by exchanges.

SELECT c.plan, approx_percentile(e.latency_ms, 0.95)
FROM iceberg.analytics.events e
JOIN postgres.public.customers c ON e.customer_id = c.id
WHERE e.ds = DATE '2024-06-12'
  AND e.endpoint = '/checkout'
GROUP BY c.plan

Rough physical shape:

Two joins of the same SQL are not the same plan. If customers is 50 MB, Trino should broadcast it and skip the shuffle of events. If customers is 80 GB and stats are missing, it may partition both sides — or worse, try to broadcast and kill a worker. Stats are not a nicety; they are the difference between a 12-second query and an incident.

Deep internals, SQL, EXPLAIN, and the coordinator-memory failure mode live in Trino.

What “fast” means here¶

Trino’s per-query tax is real: parse, analyze, split enumeration, scheduling. That tax is noise on a 40-second scan. It is the whole bill on a 20 ms lookup.

Federation is a cost model, not a feature checkbox¶

People sell federation as “query data without ETL.” True, and incomplete.

You still:

• Pay S3 LIST + GET for every Iceberg planning cycle (coordinator / Iceberg connector).
• Pay JDBC for every Postgres split, holding connections and snapshots you may not have thought about.
• Move columns you forgot to prune if the connector cannot push projections.
• Amplify a bad predicate: WHERE lower(email) = '…' over a 2 TB Iceberg table is a full read, federated or not.

ETL is the act of paying that cost once, into a shape the serving engine likes. Federation is paying it on every query. Both are legitimate. Mixing them up is how a “simple Trino join” becomes the most expensive query in the company.

The comparison with a serving store is ClickHouse vs Trino. The lake table that makes Iceberg scans viable is Iceberg.

Scale cliffs (preview)¶

Nothing in Trino removes the need for partitioning and columnar files. The engine can only skip what the table format and file layout make skippable.

How this sits in the platform¶

flowchart TB
    subgraph ingest["Ingest"]
        K[Kafka]
        F[Flink / Spark]
    end
    subgraph lake["Lake"]
        I[Iceberg]
    end
    subgraph serving["Serving"]
        CH[ClickHouse]
        P[Pinot]
    end
    subgraph ops["Operational"]
        PG[Postgres]
    end
    subgraph query["Ad-hoc / federation"]
        T[Trino]
    end
    K --> F --> I
    F --> CH
    K --> P
    I --> T
    PG --> T
    CH --> T

Trino is the SQL front door to the lake and to systems you do not want to copy. It is not the dashboard store, not the OLTP store, and not a replacement for Spark when the job is a 12-hour shuffle with tight memory control.

How to study this module¶

1. Read Trino with one query in your head: Iceberg events ⨝ Postgres customers, filtered to one day and one endpoint.
2. For every operator in EXPLAIN, name whether it runs on a worker or the coordinator, and whether it crosses the network.
3. Predict: broadcast or partitioned join? Then look at what happens when customers grows from 50 MB to 50 GB with no ANALYZE.
4. Only then compare to ClickHouse. If you cannot say which bytes move, you cannot choose.

Check your understanding¶

An on-call dashboard joins 90 days of Iceberg events to Postgres customers on every page load (p95 8 s, 40 QPS at 09:00). A PM asks to “just point Grafana at Trino — we already have the data.”

Decide: keep federation, cache, or ETL into a serving store. Name the first bottleneck you expect.