The Complete Production SOP: How to Deploy and Run Trino Distributed SQL Query Federation in Autonomous AI Agents
Step-by-step SOP to run Trino Distributed SQL Query Federation in Codex, Claude Code, and Cursor. Tune catalogs, configure memory, and optimize joins.
Technical Overview & Architectural Context
Autonomous coding agents frequently fail when configuring distributed SQL engines like Trino (formerly PrestoSQL) because they treat multi-catalog queries like monolithic ANSI SQL dialects. Common agent failures include hallucinating unsupported cross-catalog JOIN syntax, neglecting connector-specific properties in `etc/catalog/*.properties`, miscalculating coordinator versus worker memory caps (`query.max-memory-per-node`), and failing to analyze distributed physical plans with `EXPLAIN (TYPE DISTRIBUTED)`. The `trino-distributed-sql-federation` skill establishes deterministic guardrails for AI coding assistants. It enforces structured catalog definitions, guides optimal memory allocation to prevent Out of Memory (OOM) worker killed states, and ensures the agent evaluates Cost-Based Optimizer (CBO) plans to favor hash-partitioned or replicated joins appropriately across heterogenous datastores like PostgreSQL, Apache Iceberg, Hive, and Kafka.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Skill Injection & Workspace Mounting
Mount the SKILL.md specification into your agent's designated procedural context.
Create the local skill manifest directory and inject verified procedural rules so agents like Claude Code or Cursor parse frontmatter triggers and execution SOPs automatically when handling Trino queries.
mkdir -p .agents/skills/trino-distributed-sql-federation && curl -fsSL https://askills.dev/api/skills/trino-distributed-sql-federation/raw -o .agents/skills/trino-distributed-sql-federation/SKILL.md---
name: "trino-distributed-sql-federation"
description: "Query data across Hive, PostgreSQL, MySQL, and Kafka in a single unified distributed SQL query."
metadata:
author: "Trino"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "join postgres and iceberg trino"
- "configure trino catalog"
- "tune trino query memory"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---
Place the SKILL.md inside `.claude/skills` or `.agents/skills` to ensure dynamic agent loading without filling initial system prompt tokens.
Verify that `permissions.shell_exec` remains `false` if your deployment strictly requires sandboxed read/write guarantees.
Configuring Declarative Catalogs and Connectors
Provision federated catalog configurations for external heterogeneous datastores.
Trino requires dedicated property files per data source in `etc/catalog/`. Guide the agent to generate standard connector definitions for PostgreSQL and Apache Iceberg using safe parameter templates.
mkdir -p etc/catalog && touch etc/catalog/postgres.properties etc/catalog/iceberg.propertiesconnector.name=postgresql
connection-url=jdbc:postgresql://postgres.internal:5432/production
connection-user=${ENV:POSTGRES_USER}
connection-password=${ENV:POSTGRES_PASSWORD}
case-insensitive-name-matching=trueUtilize `${ENV:VARIABLE_NAME}` substitution in Trino properties rather than hardcoding static secrets in catalog files.
Ensure `.env` or credential sources containing database credentials are included in `.gitignore` to prevent secret exfiltration.
Memory Boundary Tuning & Cost-Based Optimizer (CBO) Setup
Configure coordinator and worker limits in `etc/config.properties`.
Instruct the agent to avoid common OOM pitfalls by calculating distributed query memory caps based on physical node availability, applying strict ratios between general pool and query allocations.
cat << 'EOF' >> etc/config.properties
query.max-memory=50GB
query.max-memory-per-node=8GB
optimizer.join-reordering-strategy=AUTOMATIC
EOFcoordinator=true
node-scheduler.include-coordinator=false
http-server.http.port=8080
query.max-memory=50GB
query.max-memory-per-node=8GB
query.max-total-memory-per-node=10GB
optimizer.join-distribution-type=AUTOMATICSetting `optimizer.join-distribution-type=AUTOMATIC` allows Trino CBO to toggle between broadcast (replicated) and distributed hash joins using table statistics.
Do not set `query.max-memory-per-node` higher than 70% of the JVM maximum heap (`-Xmx`), or the JVM will trigger OS OOM kills.
Executing Federated Plan Validation and Linting
Validate distributed query execution plans and syntactic pushdowns.
Instruct the agent to inspect the query plan via `EXPLAIN (TYPE DISTRIBUTED)` prior to committing queries to production scripts, confirming predicate pushdowns into PostgreSQL and partition pruning in Iceberg.
trino --execute "EXPLAIN (TYPE DISTRIBUTED) SELECT u.id, u.email, sum(o.total_amount) FROM postgres.public.users u JOIN iceberg.analytics.orders o ON u.id = o.user_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY 1, 2;"-- Distributed query joining operational PostgreSQL users with analytical Iceberg orders
EXPLAIN (TYPE DISTRIBUTED)
SELECT
u.id AS user_id,
u.email,
count(o.order_id) AS total_orders,
sum(o.total_amount) AS revenue_usd
FROM postgres.public.users u
INNER JOIN iceberg.analytics.orders o
ON u.id = o.user_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY u.id, u.email
ORDER BY revenue_usd DESC
LIMIT 100;Scan the `EXPLAIN` output for `ScanFilterProject[table = ...]` to confirm predicate pushdown is active at the connector stage.
Missing catalog prefixes (e.g., querying `users` instead of `postgres.public.users`) causes Trino to fallback to default session schema and fail resolution.
Real-World Scenario: Cross-Catalog Customer Segmentation Query with Memory Tuning
“Join postgres and iceberg trino: Write a high-performance federated query joining postgres customer data with historical iceberg web_events, configure the iceberg catalog with AWS S3, and tune query memory settings to handle a 30GB query threshold.”
Runtime Troubleshooting & Failure Modes
Root Cause: The broadcast join strategy replicated a large table across all worker nodes, exceeding `query.max-memory-per-node`, or table statistics were absent for Cost-Based Optimization.
SET SESSION join_distribution_type = 'PARTITIONED'; ANALYZE postgres.public.users; ANALYZE iceberg.analytics.orders;Root Cause: Incomplete three-tier naming convention. Trino requires catalog.schema.table for federated references when schema search paths are unconfigured.
SELECT * FROM postgres.public.users; -- Ensure syntax matches catalog.schema.table explicitlyFrequently Asked Questions (Trino Distributed SQL Query Federation SOP)
Q:Can I use Trino Distributed SQL Query Federation across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the standard SKILL.md specification. Runtimes including Codex, Claude Code, Cursor, and Windsurf parse the triggers and procedures identically from workspace root or system configuration directories.
Q:How does Trino Distributed SQL Query Federation prevent context window overflow during long agent sessions?
It restricts instructions to specific configuration templates, EXPLAIN plan patterns, and deterministic triggers, eliminating repetitive back-and-forth prompt iterations on SQL syntax and connector property formats.
Q:Does this skill require escalated root or container permissions?
No. The skill has a security score of 98/100, requires only workspace filesystem read/write privileges for configuration files, and disallows unauthorized shell execution or external network calls.
Ready to equip your autonomous agent with Trino Distributed SQL Query Federation?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.