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:
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.
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:
When inserting a new message for a given persistence_id, use trigger to:
Queries we could improve:
Consider how to partition/shard such table:
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.