Workflows need a trimming task

[deanmarcussen]

Currently workflows can either

• never save their instances
• always save their instances

Suggest they need a background trimming task, like audit trail.

by our estimations, when a workflow reaches, around 13K entries, they no longer become browseable - actually gets to the point where the SQL Client throws out of memory exceptions, trying to parse strings)

This is due to this open ended query here, which also needs fixing.

https://github.com/OrchardCMS/OrchardCore/blob/f409fd55eec4b114d34e6f8d83aac93d9f81fc63/src/OrchardCore.Modules/OrchardCore.Workflows/Controllers/WorkflowTypeController.cs#L129-L132

The query was done like this to add an instance count to the list, but it's retrieving too many rows, when there have been too many workflows.

Suggest we need a reduce index to handle the instance count as well.

However 13K instances is unnecesary, but we do need to keep at least a month's worth, for auditing purposes

[sebastienros]

I use the word pruning.

[Piedone]

More context: https://github.com/OrchardCMS/OrchardCore/issues/16447.

That instance count was removed due to the same reason since then BTW.

[deanmarcussen]

ah good to know thanks @Piedone

[Piedone]

Will you be able to contribute the implementation you've mentioned under https://github.com/OrchardCMS/OrchardCore/issues/16447#issuecomment-2237554735 any time soon @deanmarcussen? Otherwise, I'd look into this.

[deanmarcussen]

yes, good reminder, I have some time today, so will see what I can do.

[Piedone]

Awesome!