Expose current position to application

[benbjohnson]

Currently, Litestream maintains a replication position in-memory, however, it would be useful to application developers to have access to this position. In addition to the generation file, Litestream should keep a position file in the db-litestream directory that stores the generation (x), index (y), & offset (z) in the litestream.Pos.String() format:

xxxxxxxxxxxxxxxx/yyyyyyyyyyyyyyyy:zzzzzzzzzzzzzzzz

This file doen't need to be fsync'd to disk as it is only meant to be used as a simple IPC mechanism (similar to the -shm SQLite file). Optionally, it could hold a checksum to avoid partial write/read issues but that may be overkill since it's smaller than a sector of data.

/cc @chrismccord

[simonw]

A neat trick I've seen done with this relates to helping users avoid replication lag.

After you make an update to the database, it's really important you see that update on the next GET request you make.

A common solution to this problem is to set a cookie (or similar) for the user such that for the next 5s after they perform a write all of their reads are sent to the primary database - which should ensure the replicas have caught up by the time that cookie expires.

But another trick I've seen is to make the replication position available to the replicas, and then to record the position at the time the user's last write was committed somewhere.

If the user is talking to a replica it can then make a comparison, effectively saying "this user last wrote at position 11234 - I've only replicated up to position 11221 so I should redirect them to the primary".

I think this is how Wikipedia address replica lag, so it definitely works at scale!

[simonw]

Hah, and after I typed all of that it looks like that was the impetus for adding this in the first place!

https://twitter.com/mrkurt/status/1494380016238481415

[benbjohnson]

@simonw I personally like the "redirect to primary for X seconds" approach because it's so simple but it does require replication lag to be below X.

One issue I realized after after chatting with @chrismccord is that Litestream batches up changes every 10ms so you can't check it right away. You could add a sleep although that's hacky. However, before the transaction you could read the position (after this issue is implemented), then run your write transaction, and then keep re-reading the position file until it changes and that should always give you a WAL position that contains your transaction.