Improve SQL sys.tasks performance with Overlord filter and limit pushdown

[FrankChen021]

Background

The sys.tasks table is an important observability surface for active and recently completed tasks. We also use AI agents heavily to investigate Druid incidents, task failures, and ingestion behavior. Those workflows frequently issue narrow queries such as looking up one task by task_id, or retrieving a small sample of tasks matching a datasource/type/status.

Today, the SQL sys.tasks scan retrieves the task list from the Overlord and applies the SQL filter and limit at the Broker. On clusters with many active or recently completed tasks, a query that should return one row can instead transfer and materialize the entire task list. This adds avoidable Overlord, Broker, network, and metadata-storage work at exactly the time an investigation is trying to diagnose a problem.

For example:

SELECT task_id, datasource, type, status, runner_status, error_msg
FROM sys.tasks
WHERE task_id = 'index_parallel_example_2024-01-01T00:00:00.000Z'
LIMIT 1;

This should be able to request one matching task from the Overlord rather than scan all task rows.

Proposed change

Add safe predicate and row-limit pushdown from SQL sys.tasks to a versioned Overlord task-list API. The SQL filter should remain as a residual Calcite filter so that pushdown is an optimization and never changes query results.

SQL-to-Overlord pushdown matrix

For a simple unordered sys.tasks scan, push down single-valued exact equality predicates when they can be represented by the task-list API:

SQL predicate	Overlord parameter
datasource = 'foo'	datasource=foo
type = 'index'	type=index
task_id = 'task-1'	taskId=task-1
group_id = 'group-1'	groupId=group-1
status = 'SUCCESS' or status = 'FAILED'	state=complete as a safe superset; retain the SQL status filter
runner_status = 'RUNNING', 'WAITING', or 'PENDING'	corresponding state value; retain the SQL runner-status filter
LIMIT n OFFSET m	max=n+m for the backend completed-task cap; retain the final SQL limit/offset

These parameters should be combinable in one request, for example:

/druid/indexer/v2/tasks?state=complete&datasource=foo&type=index&taskId=task-1&groupId=group-1&max=1

createdTimeInterval is already an Overlord API parameter, but it does not need to be part of the initial SQL pushdown unless a safe mapping from the SQL created_time column is defined.

Do not push down a limit when the query has ORDER BY, a non-literal limit/offset, or residual predicates that could remove rows after the backend cap. The API's max applies to completed tasks, so active-task and final SQL-limit semantics must remain correct.

Current Overlord API bug / contract gap

The current /druid/indexer/v1/tasks endpoint is not a safe primitive for this optimization:

1. It does not expose taskId or groupId query parameters.
2. Its max parameter can be applied while building the completed-task lookup before all requested filters are applied. In particular, type filtering is applied after the bounded lookup, so a request such as max=10&type=index can return fewer than ten matching index tasks when earlier completed tasks have other types.
3. The endpoint does not guarantee filter-before-limit semantics in its public contract.

A versioned /druid/indexer/v2/tasks endpoint should guarantee that datasource, type, taskId, and groupId are applied before the completed-task limit. The existing v1 endpoint should remain available with its current behavior while clients migrate.

Compatibility and rolling upgrades

• Preserve the v1 endpoint response and behavior for existing clients.
• Prefer v2 from the SQL path and newer clients.
• During a rolling upgrade, if an older Overlord returns 404 for v2, fall back to an unbounded v1 request and apply the residual filters and SQL limit locally. This preserves correctness at the cost of performance.
• Keep the new task-storage query methods backward-compatible with custom or non-metadata-backed TaskStorage implementations by providing a default in-memory filtering path.
• Preserve existing datasource authorization behavior. Authorization filtering must happen before the final SQL limit; restricted rows must not consume the user's requested result count.
• This proposal concerns task metadata and status. Returning the complete task payload through sys.tasks is out of scope.

Acceptance criteria / test plan

• sys.tasks exact filters for datasource, type, task ID, and group ID are passed to Overlord when present.
• Status and runner-status predicates are mapped only when the mapping is a safe superset, with the original SQL predicate retained.
• Literal unordered LIMIT/OFFSET is pushed as max only when doing so cannot omit rows; ordered or residual-filtered queries retain local limiting.
• The backend applies supported filters before max and returns correct results when many nonmatching completed tasks precede matching tasks.
• Mixed-version v2-to-v1 fallback, datasource authorization, active tasks, completed tasks, and zero-result cases are covered by tests.
• SQL metadata-table and task API documentation describe the pushdown behavior, v1/v2 compatibility, and the completed-task meaning of max.

This feature implementation currently is on our track. We hope to push to the community soon.

[FrankChen021]

Reference benchmark: metadata filter pushdown for sys.tasks

I added a JMH microbenchmark that compares the metadata-storage portion of the proposed native sys.tasks path:

• Pushdown: pass a TaskStorageQueryFilter to SQLMetadataStorageActionHandler, which adds the predicates to the metadata SQL.
• Control / previous data flow: retrieve every task row from metadata storage, deserialize it, and apply the identical predicate in Java.
• Both paths use task summary columns rather than task payloads.
• Trial setup verifies that both paths return exactly the same task IDs before measurement.
• The dataset has 10,000 or 100,000 completed tasks, 1,000 datasources, and 100 task types.
• Cases cover the task primary key, indexed datasource, indexed created-time range, and unindexed type column.

A short directional smoke run with 10,000 tasks produced:

Filter	Fetch all, then filter	Filter in metadata SQL	Approx. speedup
task_id = ...	26.764 ms/op	0.060 ms/op	446x
datasource = ...	26.967 ms/op	0.120 ms/op	225x
type = ...	26.453 ms/op	6.172 ms/op	4.3x
created_time range	26.843 ms/op	0.116 ms/op	231x

These numbers used abbreviated JMH settings (one 500 ms warmup iteration and two 500 ms measurement iterations), so they are directional rather than publication-grade. The benchmark's normal configuration uses two 2-second warmups and five 2-second measurements at both dataset sizes.

This intentionally isolates the metadata-storage benefit. An end-to-end SQL benchmark should be added separately to measure the complete user-visible path.