Presto/Trino – Data/ML Engineer Blog

Presto/Trino for Real-Time SQL on the Data Lake: Architecture, Tuning, and Pitfalls You’ll Actually Hit

Hook: Your exec wants “interactive BI on S3” without moving data or waiting on Spark jobs. You need ANSI SQL, sub-second latencies on hot datasets, and sane ops. That’s exactly the niche Presto/Trino fills: a distributed, MPP SQL engine that queries data where it lives—S3, HDFS, Kafka, Iceberg, Delta, relational systems—without ingest pipelines.

Why this matters

Speed to insight: Query ORC/Parquet on your data lake with millisecond startup—no cluster spin-up, no ETL detours.
Polyglot access: Join MySQL to S3 to Kafka in one query when you must.
Cost control: Decouple compute from storage. Scale workers up/down without shuffling data warehouses around.
Modern lakehouse fit: First-class connectors for Iceberg, Delta, Hudi, plus legacy Hive tables.

Core Concepts & Architecture

MPP at a glance

Coordinator: Parses SQL, plans the query (logical → distributed), schedules tasks, tracks metadata.
Workers: Execute tasks (scan, filter, join, aggregate). Exchange data via distributed shuffles.
Connectors: Storage- and system-specific adapters (Hive/S3, Iceberg, Delta, Kafka, JDBC…).
Catalogs & Schemas: Each catalog is a connector + config; schemas map to namespaces within the source.

Execution flow

Parse & analyze SQL (types, catalogs, column resolution).
Pushdown predicates/projections to connectors when possible.
Plan distributed stages (source, intermediate, sink) with join strategies.
Schedule splits to workers; exchange data for joins/aggregations.
Return results to client with pipeline streaming.

Optimizations you should know

Predicate/column pushdown into ORC/Parquet/Delta/Iceberg, JDBC sources.
Dynamic filtering: Early prune of fact table reads based on small dimension keys (massive S3 savings).
Cost-based optimization: Uses stats for join order & distribution.
Spill to disk: Stabilizes big joins/aggregations when memory is tight.
Fault-tolerant execution (Trino): Optional mode to recover failed tasks on long queries.

A Quick Start You Can Copy

1) Define a Hive/Iceberg catalog on S3

etc/catalog/hive.properties

connector.name=hive
hive.metastore=glue
hive.metastore-glue.region=us-east-1

# S3 settings
hive.s3.aws-access-key=YOUR_KEY
hive.s3.aws-secret-key=YOUR_SECRET
hive.s3.streaming.part.size=128MB
hive.s3.multipart.min-file-size=128MB
hive.s3.max-connections=200

etc/catalog/iceberg.properties

connector.name=iceberg
iceberg.catalog.type=glue
iceberg.file-format=PARQUET
iceberg.hadoop.warehouse=s3://your-bucket/warehouse/

2) Sanity test: read a partitioned table

-- Inspect partitions & stats
SHOW PARTITIONS FROM hive.sales.orders;
SHOW STATS FOR hive.sales.orders;

-- Narrow read (predicate + projection)
SELECT order_id, total_amount
FROM hive.sales.orders
WHERE ds = DATE '2025-11-01' AND total_amount > 100
LIMIT 100;

3) Cross-catalog join (small → big)

-- Broadcast a small dimension from MySQL to filter a big S3 fact
SELECT f.order_id, d.segment
FROM mysql.crm.dim_customers d
JOIN hive.sales.fact_orders f
  ON f.customer_id = d.customer_id
WHERE d.country = 'US' AND f.ds = DATE '2025-11-01';

4) Materialize a serving table in Iceberg

-- CTAS into Iceberg for BI speedups
CREATE TABLE iceberg.analytics.daily_revenue
WITH (
  format = 'PARQUET',
  partitioning = ARRAY['ds']
) AS
SELECT ds,
       region,
       SUM(total_amount) AS revenue,
       COUNT(*) AS orders
FROM hive.sales.fact_orders
GROUP BY ds, region;

Best Practices That Move the Needle

Data layout & files

Use Parquet/ORC with ZSTD or Snappy.
Target 128–512 MB file sizes. Thousands of tiny files will destroy Trino scan throughput.
Partition only on high-selectivity columns you actually filter (e.g., ds, region). Over-partitioning = too many small files.
For Iceberg/Delta, schedule compaction/optimization to fix small-file syndrome.

Statistics & planning

Collect table/column stats where supported. For Hive: ANALYZE hive.sales.fact_orders; ANALYZE hive.sales.fact_orders FOR COLUMNS region, customer_id;
Prefer small → big joins to leverage broadcast. Use JOIN hints sparingly; let the optimizer work when stats are good.

SQL patterns

Avoid SELECT *; project only needed columns for pushdown & IO cuts.
Use dynamic filtering (enabled by default in many builds) by structuring joins where the build side is selective.
When doing real-time, pre-aggregate into Iceberg materialized views or rollups; don’t hammer raw facts for every dashboard tile.

Memory & stability

Right-size worker heaps; start with 8–16 GiB heap, then tune:
- query.max-memory-per-node
- query.max-total-memory-per-node
- memory.heap-headroom-per-node
Enable spill for large joins/aggregations: experimental.spill-enabled=true spiller-spill-path=/mnt/trino-spill spiller-max-used-space-threshold=0.7
Watch exchange traffic; high shuffle = expensive network. Consider co-locating workers or using larger instances with better NICs.

Metastore & catalog health

Hive Metastore/Glue is often the bottleneck. Cache aggressively; avoid chatty partition listings by using Iceberg.
Limit cross-source joins in production. If you need them, materialize to the lake first.

Security & governance

Integrate with Ranger/Lake Formation or catalog-level ACLs.
Use ABAC via tags when using Glue/LF; keep PII isolated per catalog/schema.

Common Pitfalls (and blunt fixes)

“Trino is slow” — 90% of the time it’s tiny files or no predicate pushdown. Fix file sizes, remove SELECT *, and verify pushdown in EXPLAIN.
Skewed joins — One hot key can wreck a stage. Pre-salt heavy keys or pre-aggregate dimensions.
Too many partitions — Don’t daily+hourly+tz partition unless you absolutely need it.
Metastore timeouts — Switch to Iceberg or cache; reduce partition enumeration by pushing predicates.
OOMs during group by — Enable spill, widen workers, or pre-aggregate to serving tables.

Trino vs. PrestoDB vs. Spark SQL — Quick Comparison

Capability	Trino	PrestoDB	Spark SQL
Primary use	Interactive SQL on lakes & federated sources	Same origins; slower release cadence	Batch + streaming + SQL
Fault tolerance	Optional task-level recovery mode	Limited	Strong for batch
Connectors	Very broad, fast-moving	Broad	Many via DataSource V2
Optimizer	Cost-based, dynamic filtering, pushdowns	Similar, fewer features in practice	Catalyst (excellent for ETL)
Latency	Low for interactive	Low	Higher startup; great throughput
Materialization	CTAS to Iceberg/Delta/Hudi	CTAS	Writes via Spark jobs
When to pick	BI/Ad-hoc, cross-source reads, lakehouse serving	Legacy compatibility	Heavy ETL, ML, streaming, complex transforms

Real Example: From Raw S3 to Fast BI

Land raw events in s3://company/data/events/ as hourly Parquet.
Register in Iceberg and compact nightly to 256–512 MB files.
Build rollups for dashboards:

CREATE TABLE iceberg.analytics.event_15m
WITH (partitioning = ARRAY['ds','hour'])
AS
SELECT date_trunc('minute', ts) AS ts_min,
       ds, hour, app, region,
       COUNT(*) AS events, approx_distinct(user_id) AS dau
FROM iceberg.raw.events
WHERE ds BETWEEN DATE '2025-11-01' AND DATE '2025-11-07'
GROUP BY 1,2,3,4,5;

Point BI to analytics.event_15m. Trino answers in milliseconds; heavy crunch stays off your interactive path.

Operations Checklist (pin this)

Autoscale workers with a simple queue metric (queued splits > threshold).
Alert on: task failures, coordinator CPU > 80%, worker GC > 10%, HSM/Glue latency spikes, spill disk > 70%.
Rotate JVM/GC logs and enable query event logs for audit.
Periodically OPTIMIZE/VACUUM Iceberg/Delta and refresh stats.

Internal link ideas

Sharding Strategies 101: How partitioning and bucketing choices impact Trino scans.
Iceberg vs Delta: Table formats for reliable lakehouse ACID and fast queries.
Cost-Based Optimization in Practice: Stats collection and join strategy selection.
S3 Data Layout Guide: File sizes, compression, and compaction pipelines.

Conclusion & Takeaways

Trino/Presto gives you warehouse-like SQL directly on the lake with interactive latencies.
Your success depends far more on table format, partitioning, and file sizes than on cluster size.
Use Trino for serving and federation, not for heavy ETL. Materialize rollups into Iceberg and keep queries simple and selective.
Treat the metastore and small files as first-class risks. Fix them early.

Call to action: Pick one critical dashboard. Materialize a narrow Iceberg table that powers it, enforce 256–512 MB files, collect stats, and measure before/after query latency. Then scale the pattern.

Image prompt (for AI tools)

“A clean, modern lakehouse architecture diagram showing Trino’s coordinator and workers querying Iceberg tables on S3 with partition pruning, dynamic filtering, and a cross-catalog join to MySQL — minimalistic, high contrast, isometric 3D style.”

Data/ML Engineer Blog