Trino Federation: Query the Lake Without Melting Prod Postgres

Checkout p99 went to 4.2 seconds. Postgres CPU sat at 100 percent. pg_stat_activity showed 80 sessions from the Trino worker subnet running SELECT * FROM public.orders. A Looker explore had been pointed at the new postgres catalog “just to join a few columns.” Replica lag hit 41 minutes. We had not deployed the app. We had federated the primary.

Trino stores nothing of yours. It plans SQL, talks to connectors, and streams rows back. Cost is cluster hours plus whatever you do to the sources. That is the feature and the incident.

Federation is a load test on the source

A catalog is a connector plus credentials. Iceberg against a REST catalog is cheap for the source: you read Parquet. Postgres against the primary is an OLTP client with a huge scan budget. There is no warehouse governor in the middle.

flowchart LR
  bi["Looker / notebook"] --> coord["Trino coordinator"]
  coord --> w["Workers"]
  w --> ice["Iceberg / lake"]
  w --> pg["Postgres catalog"]
  pg -->|"SELECT * no limit"| primary["Prod primary"]
  primary --> down["Checkout latency"]
  ice --> parquet["Object storage"]

The lake path is files. The Postgres path is your production database. Do not put both on the same explore.

Rules I now write in the catalog PR:

1. Never point the postgres catalog at the primary. Replica only, and a role that cannot write.
2. No SELECT * from BI. Explicit columns. Row limits on explores that hit JDBC sources.
3. Filter on columns the source can use as an index. If pushdown fails, Trino pulls the table.

-- catalog: iceberg.sales  (safe: files)
-- catalog: postgres.orders_replica  (dangerous if mispointed)

-- This is the query that melted us. No predicate, wide row.
SELECT *
FROM postgres.public.orders;

-- Same join, lake is the fact, replica is a small dim.
SELECT
    o.order_id,
    o.ordered_at,
    c.segment
FROM iceberg.sales.orders o
JOIN postgres.public.customers c
  ON c.customer_id = o.customer_id
WHERE o.ordered_at >= TIMESTAMP '2026-08-01 00:00:00';

I still prefer landing the dim into Iceberg nightly and joining in-lake. Federation is for a column you do not yet own, or a debug. It is not a serving architecture.

Iceberg is the connector I want

Interactive SQL over the lake is why we bought the cluster. The Iceberg connector reads snapshots and manifests; it does not open a Postgres connection. Catalog choice matters more than people think — I covered the format and catalog split in Iceberg vs Delta. In Trino I want a REST catalog, not a filesystem warehouse path shared with three writers.

-- etc/catalog/lake.properties (conceptually)
-- connector.name=iceberg
-- iceberg.catalog.type=rest
-- iceberg.rest-catalog.uri=https://catalog.internal:8181

SHOW TABLES FROM lake.silver;

SELECT count(*)
FROM lake.silver.orders
WHERE order_date >= DATE '2026-08-01';

SELECT DISTINCT and SELECT * on wide Iceberg tables are how you blow worker memory. Name the columns. If the shuffle is huge, that job is Spark, not a 30-second Trino session.

Interactive versus Spark ETL

Trino: analysts, federated checks, “does this join even work.” Spark: incremental MERGE, large compaction, ML features. I expose the same Iceberg tables to both. I do not run the nightly 12-hour rebuild on Trino because someone liked the SQL.

Coordinator size is the other silent outage. One fat coordinator holds the plan. A 200-table join across four catalogs will OOM it while workers sit idle. Split the query or land a mart.

-- Session guards I put on JDBC catalogs in the BI group.
-- Exact property names vary by release; the intent does not.
SET SESSION postgres_replica.use_connection_pool = true;

-- Fail a runaway explore before it becomes 80 backends.
-- Cluster access control: deny postgres*.*.* for the BI role
-- except postgres_replica.public.customers (the dim we allow).

Cost is worker-hours. A federated scan that runs 20 minutes on 12 workers is a cluster bill and a replica bill. I cancel queries over 5 minutes on the BI Trino cluster. ETL Trino, if I even have one, is a different catalog set and a different SLA.

Pitfalls

Naming the catalog postgres and pointing it at the primary. Someone will SELECT *. Name it postgres_replica and put the host in the properties file, not in a wiki that says “prod-ro” and resolves to the writer.

Federating a fact table. orders at 400 GB is a lake table. Federation is for a 2 million row dim you have not landed yet. If the join is daily, land it.

Assuming pushdown always works. Functions on the filter column, or a join that Trino plans as a full pull, send the table over the wire. Explain the query before you give Looker the catalog.

One cluster for ad-hoc and for a dashboard SLA. A user CROSS JOIN will evict the tile refresh. Separate clusters or resource groups with a hard concurrency cap.

Treating Trino as a warehouse. No time travel of its own, no governance catalog, no cheap clone. Those live in Iceberg or in Snowflake. Trino is the SQL layer on top.

Leaving postgres in the catalog list “for debugging.” Someone will find it. Delete the catalog. Debug from a bastion or a replica you named on purpose.

After the checkout incident we deleted the prod Postgres catalog, left a read replica catalog named postgres_replica with a comment in the docs, and put a cluster access control rule on postgres*.*.* for the BI group. Federation stayed. The primary stopped being a dashboard backend. Trino did its job. We had pointed it at the wrong store.