Rebuilding projections giving time out when having gap of matching events

[schadowfax]

When you do a rebuild (but i also guesse if you have a new async projection later on in je project, since it looks like the same code at first sight) you have a querry like this, to get the events needed to apply =>

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 d.stream_id = s.id and d.tenant_id = s.tenant_id where d.seq_id > :p0 and d.seq_id <= :p1 and d.type = ANY(:p2) and d.is_archived = FALSE order by d.seq_id limit 500

p0 is your current position, p1 is your high watermark and p2 is your array of matching event types.

But for example when you only have matching events by type where you have a high sequence gap between then this querry takes really long and can result in time outs. Should it be a good idea to have a index on type by default? or per haps limit the p1 and p0 gap in some pre defined limit until the high watermark is being achieved?

Also the limit of 500 can be overriden by configuration of your BatchSize of the projection witch can result in more cases like this happening.

Example of batch where it went wrong for our projection to rebuild.

[jeremydmiller]

I would accept a PR for an "opt in" index on event type, yes. By and large, we try to optimize for insert speed in the OOTH configuration. Longer term, if you have separate aggregations that use different event types, you might consider using the "separate store" feature in your system so that you don't run into this case.

Longer term too I guess, we could consider some other mechanism for an optimized rebuild, but it's too late on a Friday afternoon for me to have many ideas on that one.

How did you get to the case where you had nearly a 200M gap in events?

[schadowfax]

@jeremydmiller the real gab between seq_id 348.455.670 and 536.132.710 is "only" 1.955.183.

In our test environment, we encountered issues with a consumer that had an excessively high retry policy. This led to frequent failures and the generation of a large number of tombstone events, consuming significant storage space. We cleared these events to free up space. However, for the query itself, these gaps should not impact the results. The main issue is that there are 1,955,183 events that do not match the query criteria for rebuilding the projection based on type, and this is taking a long time.

The seperate store suggestion is also not a real solution since this can still happen in alot of cases with big data sets. (or each pre defined big projection should have its own database)

Fyi: it also seemed to us that a index on event type is not taking in account on this querry probably it should be a multicolumn Index for that with seq_id tenantId to take in account on this querry. But not sure if this is the best idea since it will slow down the inserts

[jeremydmiller]

@schadowfax What was causing the high rate of failures?

And I guess you could have an opt in model to set the upper seq_id on the search. It actually did do that prior to V7, but I lifted that on purpose to allow the daemon to take in bigger "gulps" of data at a time to reduce the number of batches. I've not aware of anyone else really hitting the same permutations of problems that you're describing. I'm happy to discuss more opt in amelioration PRs I guess.

[schadowfax]

@jeremydmiller can't say with certainty, that happends months ago and didn't work on that item. But it was some concurrency issue that generated the tombstones for what i know.

atm we have a second projection that we are rebuilding and again we hit timeouts. So we might gone need some way of limitting the upper seq_id per batch i'm afraid. Technical wise this also seems correct if you have high amount of other events that don't match the querry it takes a long time to hit your BatchSize. For now we upped the 'CommandTimeout' in our connection string to bypass this issue and that also seems a valid workaround.