Perhaps an example of this (and perhaps not, because Discord is closed source software and thus I cannot check): quite often (this probably happens at least once a week to me) there is a dispute in a Discord conversation I am having over what order the messages in the client are. It turns out that Discord doesn’t even attempt to synchronise the ordering of messages between clients, despite being a centralised-server-based chat protocol where all my messages to everyone else in the conversation go through some Discord-controlled server that should be able to sync everything up!
I wonder if the issue is using coarse-grained timestamp-based ordering instead of a counter, or if instead it’s a UI issue where they’d rather keep the messages in the order they appeared on your screen rather than inserting messages into your message history to maintain a global order.
: I obviously don’t mean an actual dispute/argument, just that people will for some reason care about the order of messages and they’ll be inconsistent, and people will notice this, so it’s not at all rare.
I agree with the article but “use sequences in databases” might not be sufficient for your use case.
A common use case is syncing some sequence of events among clients. Considering Postgresql, the serials themselves are not covered by your transaction, allowing concurrent transactions on the same sequence but leading to gaps, jumps in the sequence etc.
It is particularly annoying that rows with lower IDs could commit later. So you cannot use that as paging/sync token to retrieve all rows added after your last sync. I don’t know if there is an elegant solution while allowing fully concurrent writes.
But I guess using constraints, optimistic locking etc is good enough usually. Other than that, with some extra config, you can use monotonic transaction IDs and query the range of still active transactions…