[PERF] Add 2i to cover PersistenceIds only (not SeqNo) in all Akka.Persistence.Sql.Common implementations

[Aaronontheweb]

Version Information
Version of Akka.NET? v1.4.28 and earlier
Which Akka.NET Modules? Akka.Persistence.Sql.Common

Describe the performance issue
User reported the following issue in Gitter:

If it takes me 20 minutes to do
SELECT DISTINCT e.PersistenceId as PersistenceId FROM akka.EventJournal e WITH (NOLOCK)
that means after a restart any persistent actors can't come up until the SqlJournal is initialized right? Looking into another problem, back on 1.3.2

That sure sounds like a table scan on the event_journal to me, probably because the unique index is a non-clustered index of the PersistenceId, SeqNo tuple.

We should add a covered index of the PersistenceIds to the following tables:

• Metadata
• SnapshotStore
• EventJournal

In order to accelerate queries such as AllPersistenceIds for very large journals.

Data and Specs
Need to do a before / after benchmark on a very large journal and measure the performance with and without the 2i. My guess is the performance will be dramatically improved with the 2i at a minimal memory / disk utilization cost.

cc @to11mtm should we add this to Linq2Db also?

[to11mtm]

So my original reply got eaten... I don't have an SQL Server instance to play with and test a large number of persistenceIds (User quoted 'million, alas, but I would suggest benchmarking carefully and on both reads as well as writes with such an index, both under this extreme case as well as common case (i.e. journal benches.)

Most Sane RDBMS as well as SQL Server are able to use the first part of the 2 part index without issue or with minimal penalty. Additionally, One more index is one more page write, so this would hurt write performance. I've put together a LinqPad script making it easy-ish to at least compare index behavior under SQLite. With 10M 'events' over 1M 'PersistenceIds' It looks like the overall time on a DISTINCT is the same whether there is One or both indices, however write performance suffers considerably when both indexes are enabled. And note that the final times here are without any network hops for packets/etc

As for your what's up in Persistence.Linq2Db; we treat indexes as 'Consider your workload and consult your DBE'. If you aren't using Persistence.Query, The only important index is PersistenceId,SequenceNr. Admittedly Some of this is because L2Db does Creates but not indices. I have ideas for how to handle that from a code standpoint, but I'll leave that to #5408.

[Aaronontheweb]

That's good to know, so this probably isn't necessary at all then.