ExtendedProgressionWriter heartbeats don't scale under per-tenant agent fan-out: one unbatched connection round-trip per (shard, tenant) per ~10s drove a test cluster to its connection ceiling

[erdtsieck]

Setup

• Marten sharded multi-tenant store (MultiTenantedWithShardedDatabases), Events.UseTenantPartitionedEvents = true, EnableExtendedProgressionTracking = true
• Wolverine-managed event subscription distribution with per-tenant agent fan-out (wolverine#3280)
• 6 async projections/subscriptions on the store, ~650–1300 tenants per shard database, 2 shard databases on one (small) test Postgres server
• JasperFx.Events 2.32.0 / Marten 9.17.1

What happened

After #537 wired up the extended-progression write path, our test environment went down with:

Npgsql.PostgresException (0x80004005): 53300: remaining connection slots are reserved for roles with privileges of the "rds_reserved" role

RDS Performance Insights shows the cause plainly — the top statements by DB load over the incident window:

avg active sessions	calls/sec	statement
60.1	—	DISCARD ALL
56.3	769	select public.mt_mark_event_progression_extended($1, $2, $3, $4, $5, $6)
38.3	780	select public.mt_mark_event_progression_extended($1, $2, $3, $4, $5, $6) (2nd shard DB)
0.7	—	COMMIT

~1,550 heartbeat writes/sec in total, and the DISCARD ALL row is the Npgsql pool reset for those same short-lived connections. Everything else the application does is a rounding error next to it. Connection count climbed past 700 until the server hit its ceiling and the environment fell over. Note this is with the CritterWatch console completely off — the writes come purely from the daemon's own heartbeat path, gated only on EnableExtendedProgressionTracking.

Why it doesn't scale

ExtendedProgressionWriter throttles correctly per shard (min 5s between writes, 10s heartbeat timer), but the total write rate is O(agents) = O(async projections × tenants) under per-tenant fan-out:

• 6 projections × ~1,300 tenants ≈ 7,800 agents per database
• 7,800 agents × 1 write / 10s ≈ 780 writes/sec per database — exactly what PI measured

And each write is maximally expensive for what it carries: MartenDatabase.WriteExtendedProgressionAsync opens its own pooled connection, executes one single-row function call, closes (→ DISCARD ALL):

await using var conn = CreateConnection();
await conn.OpenAsync(token).ConfigureAwait(false);
await conn.CreateCommand(
        $"select {Options.EventGraph.DatabaseSchemaName}.mt_mark_event_progression_extended(...)")
    ...
    .ExecuteNonQueryAsync(token).ConfigureAwait(false);

So a store that opts into per-tenant partitioning + extended progression pays tenants × projections / 10 connection rents per second per database, forever, even when every tenant is idle (idle tenants still heartbeat — that's the point of the heartbeat, but it's also why the load floor is so high).

Suggestions (any subset would help)

1. Batch per database per interval. The writer already serializes on one background block per daemon — a natural coalescing point. Buffer the ShardStates that arrive within an interval and flush them as one statement (multi-row VALUES join against mt_event_progression, or unnest-based update). 7,800 single-row calls become 1 call with 7,800 rows every 10s.
2. Skip or decimate idle-tenant heartbeats. A shard whose sequence and status haven't changed since the last write could heartbeat at a much lower cadence (e.g. 60s) — the persisted heartbeat only needs to be fresh relative to the monitoring consumer's staleness threshold.
3. Make the cadence configurable (HeartbeatWriteInterval is a property today but there's no public knob per store), so high-cardinality tenancy can trade heartbeat freshness for load.

Status transitions staying immediate/unthrottled is fine — those are rare and are exactly the writes that matter. It's the steady-state heartbeat that is O(tenants).

Happy to test a fix against this environment — it reproduces within minutes of startup.

[erdtsieck]

Related (same environment, same per-tenant fan-out, independent bug): JasperFx/wolverine#3580 — the subscription agents' health check compares each per-tenant agent's position against the database-wide high water mark, so the same deployment also floods false 'events behind' criticals.