PartitionsWithWorkAsync runs a distinct scan over every workflow run, once per action executed
- Dominant language
- C#
- Stars
- 6
- Forks
- 7
- Avg merge
- 4h 42m
- Merged PRs (30d)
- 307
Description
`WorkflowRunner.PartitionsWithWorkAsync` opens a connection and runs raw SQL on every pass:
```sql
select distinct tenant_id from public.mt_doc_workflow_runs
where (data ->> 'Status')::integer in (0, 1)
```
Because `RunOnceAsync` returns after claiming one attempt, a pass happens after every single action executed, not once per idle cycle. On a busy instance this query runs as often as work is done.
Two things to measure before assuming it is fine:
1. **Whether the predicate uses the index.** `ServiceCollectionExtensions` registers `.Index(x => x.Status)` on `WorkflowRun`, which gives Marten's computed index over the JSONB path. This query casts with `::integer`. If the cast does not match the indexed expression, this is a sequential scan of the whole runs table, and the table is only trimmed by `WorkflowRunRetentionService` on its own schedule.
2. **Whether `select distinct` is the right shape at all.** It reads every matching row to return a handful of tenant ids.
### Notes
The comment on the method explains why it reads the runs table rather than the tenant registry, and that reasoning is right and should survive any change here: a run queued before a tenant was deactivated must still execute, and asking the rows cannot miss one. The question is only how the answer is obtained.
The raw SQL also hardcodes `public.mt_doc_workflow_runs`. That is a second, quieter coupling: the alias is set as `DocumentAlias("workflow_runs")` in `ServiceCollectionExtensions` and the schema is assumed to be `public`. A deployment with a non-default schema, or a rename of the alias, breaks this at runtime with no compile-time signal.
### Suggested order
Run `EXPLAIN (ANALYZE, BUFFERS)` on a realistic table before designing anything. This may be a non-issue at current volumes, and the fix should be chosen against a plan rather than a guess. #694 reduces how often this runs as a side effect, so it is worth measuring after that lands rather than before.
### Where I checked
`barakoCMS/Features/Workflows/WorkflowRunner.cs`, the `WorkflowRun` schema registration in `barakoCMS/Extensions/ServiceCollectionExtensions.cs`. Searched open issues for runner query cost; #622 covers retention deleting a window at a time, which is a different query on the same tables.
Contributor guide
Research direction
Start in barakoCMS/Features/Workflows/WorkflowRunner.cs and review the WorkflowRun registration in barakoCMS/Extensions/ServiceCollectionExtensions.cs. Run EXPLAIN (ANALYZE, BUFFERS) for the shown query against a realistic table, preferably after #694 lands. Done means the measured query shape and index usage support the chosen change while preserving queued-run behavior and avoiding untracked schema or alias assumptions.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, postgresql
- Domain
- backend, databases, performance
- Issue type
- Refactor
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100