search

akkadotnet / akka.net

Canonical actor model implementation for .NET with local + distributed actors in C# and F#.
http://getakka.net
Other
4.69k stars 1.04k forks source link

Akka.Persistence.Sqlite WriteJournal should create database with indexed tuple (persistence_id, sequence_nr) #4470

Open voltcode opened 4 years ago

voltcode commented 4 years ago

in SqliteQueryExecutor.cs (the Batch... as well)

the primary key is OrderingColumn, which isn't used apart from byTag queries. at the same time , there is only a UNIQUE constraint on columns (PersistenceIdColumnName, SequenceNrColumnName) even though those columns are more often used in basic queries.

It should be evaluated whether making the tuple the clustered index of the table is an even better approach (https://www.sqlite.org/withoutrowid.html) depending on the use case, especially if one does not need the 'byTag' queries.

There are also some discrepancies between MsSql and Sqlite table schema, perhaps there are some optimizations that should be moved to sqlite (I mean the index on the sequence_nr column).

object commented 4 years ago

Unique constraint implies a unique index:

https://sqlite.org/lang_createtable.html#uniqueconst

voltcode commented 4 years ago

updated the description after a mistake on my part.

Aaronontheweb commented 4 years ago

I'd be happy to accept a PR for this - we'll also need a schema migration script that users can run though.