Orphaned-message release sweep: non-indexable predicate + unbatched update dominate DB load at scale #3971

Wolverine 6.28.0, PostgreSQL 16/18, Marten-backed persistence, DurabilityMode.Balanced, sharded multi-tenant setup: one message database per tenant shard (466 of them carrying traffic), wolverine_nodes only in the main database. Inbox partitioning enabled.

The orphaned-message release sweep becomes the dominant source of database load and lock contention once the inbox is large. Three separate things combine, and none of them can be tuned away.

1. The predicate cannot use an index

ReleaseOrphanedMessagesForAncillaryOperation (and ReleaseOrphanedMessagesOperation, same shape with a subselect):

update wolverine_incoming_envelopes set owner_id = 0
where owner_id != 0 and owner_id not in (3323, 3324, 3325, 3297) and owner_id <= 3325;

The selective part is NOT IN (<dynamic node list>). Everything else (owner_id <> 0, owner_id <= highWaterMark) matches essentially every row, because in a healthy fleet virtually all envelopes are owned by a live node. So this is a full scan of the whole inbox on every polling cycle, per database — and adding an index on owner_id does not change that.

Reproduced locally with the distribution measured in production (82,586 rows, 82,520 of them owned by one live node, 2 KB bodies):

At the default 5s cadence across 466 databases, that full scan is continuous, and it finds nothing to do in the steady state (66 of 82,586 rows here).

2. The update is unbatched

When a node does go away, every envelope it owned qualifies at once, in a single statement. One shard in our deployment: 82,520 rows in one UPDATE. Across shards, one node loss orphans ~910,000 rows (measured: 3,099,864 envelopes with owner_id <> 0 over 466 databases, owned by three nodes — 1,374,919 / 909,889 / 815,056).

The same local repro for that case: 587,460 buffers touched, 2,151 dirtied, 368 ms — with 2 KB bodies. Production bodies here average ~12 KB (964 MB partition for 81,811 rows), so the real rewrite is several GB per sweep run per shard.

RecoveryBatchSize (default 100) is not applied to this operation, and there is no other bound. Because the sweep is part of buildOperationBatch, it also runs inside the shared recovery transaction — the one the comment on DeleteExpiredHandledEnvelopesCommand (#3116) deliberately moved cleanup deletes out of, for exactly this reason. So while it runs, it blocks the recovery work in the same transaction and competes with ordinary inbox inserts.

What that looks like from the application side: Npgsql.NpgsqlException: Exception while writing to stream ---> System.TimeoutException: Timeout during writing attempt on inbox inserts, ObjectDisposedException: NpgsqlTransaction when Marten then tries to roll back, and projection agents being paused with Other: NpgsqlTransaction. RDS Performance Insights showed the blocking sessions all sitting on this exact update … set owner_id = 0 where owner_id != 0 and owner_id not in (…).

3. No knob for it

The recovery timer that carries the sweep runs on ScheduledJobPollingTime (default 5s), which also drives CheckRecoverableIncoming/OutgoingMessagesOperation, MoveReplayableErrorMessagesToIncomingOperation and scheduled-message execution. Slowing the sweep therefore also delays scheduled message delivery and message recovery, which is an unrelated trade-off. NodeReassignmentPollingTime exists but does not drive this path.

Suggested direction

1. Make the predicate indexable. Determine the dead owners in memory first — a select distinct owner_id from wolverine_incoming_envelopes is cheap (a loose index scan with an index present, and there are only a handful of distinct values) — then issue update … where owner_id = any(@dead). In the table above that is 2 buffers instead of 3,670, and it degrades gracefully: nothing to do means nothing scanned.
2. Ship an index on owner_id (partial where owner_id <> 0 would be enough) once the predicate can use one. On its own, today, it is pure write overhead.
3. Bound the update — apply RecoveryBatchSize, or add a dedicated setting, so one node loss cannot turn into a single multi-GB rewrite.
4. Consider moving it out of the shared recovery transaction, for the same reason Inbox cleanup DELETE on wolverine_incoming_envelopes blocks under high load #3116 moved the expired-handled cleanup out.

Happy to open a PR for (1)–(3) if that direction looks right. I can also supply the repro script.