Some entities have events that could be considered reset events. That is, after these events are appended the prior history ceases to matter in regards to the current state of the entity. TodoListCleared is an example of this.

It could be extremely useful to read a stream backwards in these cases as it can minimize roundtrips to the database.

I propose we add a function that allows reading a stream backwards from a position where a position of -1 indicates the end of the stream. Messages should be returned in descending position order.

Imagine a stream with 2100 events. In order to reconstitute its events we'd need three calls (given a batch size of 1000)

-- read 1000 messages backwards from end
select * from get_stream_messages_backwards('Stream-123', -1, 1000)
-- positions are inclusive so we read from (1100 - 1)
select * from get_stream_messages_backwards('Stream-123', 1099)
select * from get_stream_messages_backwards('Stream-123', 98)

An example query:

SELECT * -- replace with actual columns
FROM messages
WHERE stream_name = $1
  AND position <= $2
ORDER BY position DESC
LIMIT $3;

Reasons to avoid this:

• Streams should short by nature, and message-db should not encourage technical solutions to modeling problems
• Reading backwards might have worse performance characteristics due to BTree indices being optimized for sequential forward reads