Optimize uniq_fanout_retry index size with partialFilterExpression #327

Description

The uniq_fanout_retry index in the State model could benefit from optimization to reduce index size by using a partialFilterExpression.

Context

Currently, the index is created on all documents regardless of their status or fanout_id presence. This could lead to unnecessary index bloat as the index applies to all states, even those where fanout/retry uniqueness constraints may not be relevant.

Optimization Opportunity

Consider adding a partialFilterExpression to scope the index to only documents where the fanout/retry uniqueness actually matters, such as:

• Documents with specific statuses (e.g., pending/running)
• Documents where fanout_id is present
• Or other relevant conditions based on the application's usage patterns

References

• PR: Implement retry policy and enhance errored state handling #326
• Comment: Implement retry policy and enhance errored state handling #326 (comment)
• Requested by: @NiveditJain

This optimization can be considered in a future release to improve database performance.