Potential Async Daemon Optimizations

[jeremydmiller]

See this discussion w/ @erdtsieck & @jeremydmiller on Discord: https://discord.com/channels/1074998995086225460/1074999076896112661/1455232139300831243

Problem: What if an async projection is needing to query against the events table with a sparse matching criteria? That's making the DB do a lot of scanning ahead and leading to smaller batches

Possible Ideas:

• "opt in" indexes on event type. That comes up somewhat often. Want folks to understand that comes with a non-zero cost though on the write side
• Some docs on tuning the async daemon that discuss moving the batch size down
• Have the SubscriptionAgent pool up events that have been fetched, but not yet executed. Effectively combine EventPage that are staged. That gets us back to optimizing the throughput even if it's taking more queries to fetch events
• Maybe try sending all event data back, but selectively reading events in memory based on event type. Don't deserialize events coming back if they don't match the requirements -- actually, not wild about this approach, but brainstorming here.
• Maybe make the daemon fetching adjust based on the behavior? Like, could the daemon tune itself maybe?

[erdtsieck]

I often get time-outs on

select
  d.data,
  d.type,
  d.mt_dotnet_type,
  d.seq_id,
  d.id,
  d.stream_id,
  d.version,
  d.timestamp,
  d.tenant_id,
  d.correlation_id,
  d.causation_id,
  d.headers,
  d.is_archived,
  s.type as stream_type
from public.mt_events as d
inner join public.mt_streams as s
  on s.id = d.stream_id
 and s.tenant_id = d.tenant_id
where d.seq_id > $1
  and d.seq_id <= $2
  and d.type = any($3)
  and d.is_archived = false
order by d.seq_id
limit 500;

If I change the batch size from 500 to 100 it works fine again. Wouldn't it make sense to add some index on type by default? Something like:

create index concurrently if not exists mt_events_seq_type_notarch_idx
on public.mt_events (seq_id, type)
where is_archived = false;

These query's come from projections and I feel like without the index, the IncludeType<T> hurts a bit. Off course there are far less events to consume, but selecting in the db takes a while since there is no index.

Something like opts.Events.EnableUniqueIndexOnEventId = true; but for event type 🤔

[schadowfax]

@jeremydmiller @erdtsieck
seems related/same as to #3705

and a index on type has inpact on writing (and storage) and i think in normal use cases this is never a issue. Unless your rebuilding a projection or using blue/green deployments but for this you can play with the connection time out on your rebuilding or green deployment.

New projection that need to catch up will also have this time out issue but here you can use SubscribeAsInlineToAsync, SubscribeFromSequence, SubscribeFromTime,... to mitigate this.