Configuring Trino Resource Groups for Spatial Workloads

This guide configures resource groups so an unbounded spatial query cannot starve the reporting queue, with limits derived from how spatial workloads actually fail rather than from generic defaults.

Context and prerequisites

Spatial queries fail differently from scalar ones: a missing bounding-box predicate turns a selective query into a full scan, and a join without a broadcast hint turns into a cartesian product. Both consume far more than the author expected, and without isolation both take the cluster down with them. This recipe applies to Trino 400 or later; the governance argument is in Trino spatial SQL federation.

The workload classes worth separating

The ad-hoc class deserves its small share and short timeout precisely because it is where people learn the data. A query missing its bounding-box predicate is not misconduct — it is the most natural query to write — and the correct response is that the author waits, rather than everybody waiting.

Complete working solution

{
  "rootGroups": [
    {
      "name": "global",
      "softMemoryLimit": "90%",
      "hardConcurrencyLimit": 60,
      "maxQueued": 200,
      "subGroups": [
        {
          "name": "scheduled",
          "softMemoryLimit": "40%",
          "hardConcurrencyLimit": 4,
          "maxQueued": 40,
          "schedulingPolicy": "fair",
          "schedulingWeight": 40
        },
        {
          "name": "interactive",
          "softMemoryLimit": "35%",
          "hardConcurrencyLimit": 30,
          "maxQueued": 100,
          "schedulingWeight": 35
        },
        {
          "name": "adhoc",
          "softMemoryLimit": "15%",
          "hardConcurrencyLimit": 6,
          "maxQueued": 20,
          "schedulingWeight": 15
        },
        {
          "name": "bulk",
          "softMemoryLimit": "10%",
          "hardConcurrencyLimit": 2,
          "maxQueued": 10,
          "schedulingWeight": 10
        }
      ]
    }
  ],
  "selectors": [
    { "source": "airflow.*",      "group": "global.scheduled"   },
    { "clientTags": ["dashboard"], "group": "global.interactive" },
    { "source": "tile-builder",   "group": "global.bulk"        },
    { "group": "global.adhoc" }
  ]
}

# Session-level limits per group, set through session properties or a policy.
query.max-execution-time=15m
query.max-memory-per-node=8GB
query.max-scan-physical-bytes=2TB

Step-by-step walkthrough

1. Make the default group the most restricted. The last selector catches everything unmatched, and it should be the ad-hoc group rather than a permissive one. A new client that nobody configured lands somewhere safe.

2. Set maxQueued deliberately. A queue absorbs bursts; an unbounded one absorbs a runaway client until the coordinator is holding thousands of pending queries. Small queues for the interactive group, larger for scheduled work that can wait.

3. Use a scan-bytes limit, not only a time limit. This is the spatial-specific control: a query without a spatial predicate is identifiable by how much it reads, and killing it at two terabytes scanned is more targeted than killing it after fifteen minutes.

4. Separate the bulk class. Tile generation and extract jobs are long, low-priority and predictable. Giving them their own small share prevents them from competing with interactive work while letting them use idle capacity.

5. Route by source and client tag, not by user. Users move between workload classes during a day; the application they are using does not. Tagging at the client is more stable and more accurate than mapping identities.

Common errors and fixes

The spatial-specific control

The message matters as much as the limit. “Query exceeded the maximum scan of 2 TB” tells the author their query is reading the whole table, which points directly at the missing predicate. “Query exceeded the maximum execution time” tells them nothing actionable and usually results in a request for a longer timeout.

Set the limit from the platform’s own distribution: look at the bytes scanned by the ninety-fifth percentile of legitimate queries and set the cap comfortably above it. A cap that fires on normal work will be raised until it means nothing.

Verification

-- After deploying, confirm queries land in the intended groups.
SELECT resource_group_id, count(*) AS queries,
       avg(total_bytes) / 1e9 AS avg_gb,
       approx_percentile(elapsed_time_ms, 0.95) / 1000 AS p95_seconds
FROM system.runtime.queries
WHERE created > current_timestamp - interval '1' day
GROUP BY resource_group_id
ORDER BY queries DESC;

Two things to look for. A group receiving far more queries than expected means a selector is matching too broadly — usually the default group, catching a client whose source string differs from what the selector assumed. And a group whose average bytes scanned is far above the others contains the unbounded queries, which is exactly the population worth investigating with the extent audit described in auditing spatial query extents.

Review the distribution monthly rather than only after an incident. Workload mixes drift as teams adopt the platform, and a configuration that fitted last quarter’s usage will gradually stop fitting without anything failing to draw attention to it.

Rejecting Rather Than Queueing

Resource groups control how much a query may consume. A complementary control decides whether it should start at all, and for spatial workloads it is unusually effective.

A query against a table partitioned by day and city-scale cells, carrying neither a day predicate nor a spatial one, is almost certainly a mistake. Rejecting it before it starts costs nothing, and the rejection message can name exactly what is missing — which turns an incident into a two-minute correction by the author.

Implement it as a view constraint where the platform routes through views, or as an access-control rule where the connector supports one. Where neither is available, a monitoring rule that kills such queries shortly after they start is a weaker but workable substitute, and it produces the same educational effect.

Be careful to allow the legitimate exceptions. Some queries genuinely need the whole table — a full recompaction check, a global aggregate, a data audit — and those should be submitted with an explicit override tag that routes them to the bulk group. Making the exception explicit is better than widening the rule until it stops protecting anything.

Reviewing the Configuration Over Time

Resource-group configuration is one of the settings most likely to be written once and never revisited, and one where drift is most consequential.

Three signals indicate it needs attention. Queue times rising in a group means its concurrency or share no longer matches demand. Rejections rising in the ad-hoc group means either that people are writing worse queries or that the limit is too tight for legitimate work; the extent audit distinguishes the two. And one group consistently idle means its share is available to everyone else and the split could be rebalanced.

Review quarterly against the query-history distribution rather than against intuition. The most common finding is that the interactive group needs more concurrency and less memory than originally allocated, because dashboards issue many small queries rather than few large ones — the opposite of the profile most initial configurations assume.

Federated Queries Need Their Own Limits

A federated spatial query has a cost the resource group does not see: the bytes it pulls from a remote system, and the load it places on a database somebody else operates.

The scan-bytes limit covers the lakehouse side and says nothing about the remote side, because the connector reports what it received rather than what the source scanned to produce it. A federated query that appears modest in Trino’s metrics can be running an unindexed full scan on a transactional database, and the first indication is a complaint from that system’s owner.

Two controls help. Per-catalog connection limits cap how much concurrency Trino can direct at any one source, which turns an unbounded load into a bounded one and is worth agreeing explicitly with the source’s owner rather than choosing unilaterally. And routing federated queries to their own resource group with a low concurrency limit means that a burst of them queues rather than arriving simultaneously.

Neither substitutes for the query-design discipline covered in spatial joins across catalogs with Trino — a federated query that pulls a whole remote table is a design problem rather than a scheduling one. But the limits bound the damage while the design is being fixed, which is the useful property of any admission control. Agree the limits with the source owner before the first scheduled federated job, not after the first complaint. A limit somebody agreed to is a limit that stays; one imposed unilaterally becomes an argument during an incident. The conversation is short and it converts a technical control into a shared expectation. That shift, from control to expectation, is what makes the limit survive the first time it inconveniences somebody.