BaryoDev / BaryoDev/barakoCMS

PartitionsWithWorkAsync runs a distinct scan over every workflow run, once per action executed

Open
#700 0 comments 0 reactions 0 assignees View on GitHub
core performance
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.