Performance and safety Improvements to PartitionedJournalDao

[fredfp]

Safety issue

Since https://github.com/SwissBorg/akka-persistence-postgres/releases/tag/v0.5.0-M1, unicity of sequence_number per persistence_id is guaranteed across a single partition. We still should guarantee it across all partitions.

Performance issues

A few queries are slow because they require scanning all available partitions.

Possible fix

Create a metadata table that is automatically checked and updated on insertions, and query that table instead of scanning all partitions.

Columns for new metadata table:

• persistence_id (PK)
• id (bigserial unique)
• max_sequence_number
• min_ordering
• max_ordering

When inserting a new message for a given persistence_id, use trigger to:

• ensure new sequence_number > metadata.max_sequence_number
• update metadata set max_sequence_number = new sequence_number
• update metadata set min_ordering = min(min_ordering, new ordering
• update metadata set max_ordering = new ordering

Queries we could improve:

• see notes added in 49a552f

Consider how to partition/shard such table:

• it might not be needed for journals with bounded set of persistence_id
• could be sharded per persistence_id otherwise

Later, if data and index sizes need to be reduced, consider using metadata.id as foreign key in the journal table instead of persistence_id.