FrankChen021 opened a new issue, #19902: URL: https://github.com/apache/druid/issues/19902
## 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: ```sql 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: ```text /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`. Would the Druid community be open to this approach, or is there an existing preferred API for efficient point lookups and bounded scans of `sys.tasks`? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
