Trino & Presto

Trino is a distributed SQL engine with no storage of its own. That's the defining characteristic: it doesn't ingest, it doesn't own tables, and it doesn't have a preferred file format. It connects to systems that already hold your data — S3 with Iceberg tables, PostgreSQL, Kafka, MongoDB, Elasticsearch, BigQuery — and runs SQL across all of them, in one query, joining between them if you ask it to.

It started at Facebook in 2012 as Presto, to run interactive SQL over an HDFS warehouse that was otherwise only reachable through slow MapReduce jobs. In 2019 the original creators forked it as PrestoSQL and renamed it Trino; the Presto name continued under Meta and the Linux Foundation. Trino is the more actively developed fork and the one to choose for new work, though the architecture and much of the SQL surface are shared.

The thing that makes it genuinely useful is federation. A query joining a Postgres customers table against an Iceberg events table on object storage, with no pipeline moving either one, is a capability most data stacks don't have.

TL;DR

Quick Example

A single query joining three completely different systems:

No pipeline moved the Postgres data into the lake, and no job exported the Iceberg data into the database. That's the whole proposition — and the caveat is that reaching across an operational database in an analytical query needs care, which the practices below cover.

Core Concepts

Architecture

A query is decomposed into stages, each stage into tasks distributed across workers, each task processing splits (a unit of input data). Data flows between stages through in-memory exchanges rather than being written to disk.

That pipelined in-memory model is why Trino is fast for interactive queries and why it has a hard failure mode: a stage exceeding available memory fails the query. Trino added fault-tolerant execution (spooling intermediate results to object storage, so a failed task retries instead of failing the query), which trades latency for reliability and is the right setting for long ETL-style queries.

Connectors

SHOW CATALOGS lists what's configured; a catalog is a connector instance plus its configuration. Two Postgres catalogs pointing at different databases is normal.

Pushdown

This is where Trino query performance is won or lost.

A predicate that appears as a Filter node above the TableScan was not pushed down — Trino is reading everything and filtering itself. Functions applied to a column (WHERE lower(email) = ...), casts, and some expression shapes prevent pushdown, which is the most common cause of a query that should be fast and isn't.

Query planning and joins

Running ANALYZE is the single highest-value tuning step for a Trino deployment on Iceberg or Hive. Without column statistics, the optimizer cannot choose join order or distribution type sensibly, and a query that should broadcast a 10,000-row dimension table instead shuffles a billion-row fact table.

Memory management

Trino's memory failures are informative once you know what to look for. EXCEEDED_LOCAL usually means data skew — one worker got a disproportionate share of a partitioned join key.

Trino vs. the Alternatives

Trino for interactive federated SQL over data you already have. Spark for long batch jobs, ML pipelines, and anything needing fault tolerance through multi-hour execution. A managed warehouse when you want zero operational burden and are willing to load data into it. DuckDB when the data fits on one machine, which it does far more often than people assume.

The complementary pattern is common and sensible: Spark for heavy transformation writing Iceberg tables, Trino for interactive queries over the same tables, and DuckDB locally for development against a sample. They share the table format, so no data is duplicated.

Managed Trino is available from Starburst (the commercial vendor, founded by the Trino creators), AWS Athena (which is Presto/Trino-derived and serverless), and several cloud marketplaces.

Best Practices

Run ANALYZE and keep statistics current

The cost-based optimizer is only as good as its statistics. Without them, join order and distribution decisions are guesswork, and the difference between a good and bad plan on a large join is often two orders of magnitude. Schedule it after significant data changes.

Check EXPLAIN for pushdown on every slow query

If the predicate appears as a Filter above the TableScan rather than inside it, Trino is reading everything. Rewriting to avoid functions on the filtered column, or casting the literal instead of the column, usually fixes it.

Filter on partition columns

Never point Trino at a production database primary

Analytical queries against an OLTP primary compete with application traffic for connections, buffer cache, and I/O. Use a read replica, give the connector its own user with a connection limit, and set a statement timeout on the source side as well as in Trino.

Prefer Iceberg over the Hive connector for new tables

Iceberg gives you schema evolution without rewriting data, hidden partitioning (so queries don't need to know the partition scheme), time travel, and reliable concurrent writes. The Hive connector remains widely deployed and lacks all of those. See Apache Iceberg.

Set resource groups and query limits

Without them, one analyst's SELECT * over a billion rows starves every other query. Resource groups allocate memory and concurrency by user, source, or query type; per-query timeouts and memory limits bound the damage.

Use fault-tolerant execution for long queries

retry-policy=TASK with an S3 exchange spool means a worker failure retries a task instead of failing an hour-long query. It costs latency, so enable it selectively — typically a separate cluster or resource group for ETL-shaped work.

Watch for skew in partitioned joins

EXCEEDED_LOCAL_MEMORY_LIMIT on a partitioned join usually means one join key value dominates. Trino's adaptive planning handles some cases; otherwise salting the key or filtering the outlier separately is the fix.

Common Mistakes

No statistics

Functions that defeat pushdown

SELECT * on a columnar table

Querying the operational primary

Treating Trino as a database

Broadcasting a large table

FAQ

Trino or Presto?

Trino, for new work. The original creators forked PrestoSQL from Facebook's PrestoDB in 2019 and renamed it Trino in 2021; it has the larger community, faster release cadence, and more connector development. PrestoDB continues under Meta and the Linux Foundation and is still used at large scale. They share architecture and much SQL surface, so knowledge transfers.

Is Trino a replacement for a data warehouse?

It replaces the query engine part, not the storage and management part. Trino plus Iceberg on object storage gives you an open lakehouse — no vendor lock-in, cheap storage, and any engine can read the tables. What you take on is running the cluster, tuning memory, managing the catalog, and handling table maintenance (compaction, snapshot expiry). A managed warehouse does all of that for you at a higher cost and with your data in their format. See Data Lakehouse.

How does it compare to Spark?

Different targets. Trino is optimized for interactive SQL — seconds, in-memory pipelined execution, no fault tolerance by default. Spark is optimized for large fault-tolerant batch, with a general programming model covering ML and non-SQL transformation. A query that fails in Trino at hour one because a worker died will complete in Spark. A query that returns in five seconds in Trino will take forty in Spark because of scheduling overhead. Many organizations run both over the same Iceberg tables. See Apache Spark.

Can I use it for application queries?

No. There are no indexes, no point-lookup optimization, coordinator-mediated planning adds latency to every query, and there's no transactional guarantee of its own. It's built for scanning and aggregating large datasets, not for serving a request in 5 ms. Use PostgreSQL or a cache for application reads, and Trino for analysis.

Should I federate across my production database?

Cautiously, and never against the primary. Federation is genuinely useful for joining a small dimension table from Postgres against a large fact table in the lake, and it's a poor substitute for a proper pipeline when you're pulling large volumes repeatedly. The failure mode is an analytical query saturating the database serving your application. Use a replica, a dedicated user with connection limits, and consider whether CDC into the lake is the better answer for that data.

What's the operational burden?

Moderate. You're running a coordinator and a worker fleet, tuning memory limits, configuring resource groups, maintaining catalogs, and handling table maintenance if you own the Iceberg tables. Autoscaling workers is straightforward; sizing memory correctly takes iteration. Managed options — Starburst Galaxy, AWS Athena — remove most of it, with Athena in particular being serverless and Presto-derived.

Does it support writes?

Yes, through connectors that support it — Iceberg, Delta Lake, Hive, and the JDBC connectors all handle INSERT, and Iceberg additionally supports UPDATE, DELETE, and MERGE. It's genuinely usable for ELT: CREATE TABLE AS SELECT transforming raw tables into modelled ones. What it isn't is a transactional writer for application data — the guarantees come from the table format, not from Trino.

Related Topics

References