Hey folks, I have split this out from the post in the libraries section. I think this is a great discussion but as it is mostly focused on the abstract challenges of these sorts of systems and isn’t about the specific library posted in the other thread, I felt it important to split it out.

A big disconnect here is about whether you are ordering a set after the fact vs maintaining a cursor. If you have a set of messages and some error, then sure you can look at the set and say “this order is the order I will say it’s in”

With a cursor though that’s totally different. You are saying “I have seen messages up to time X”. If a message is created with a time before X you’re screwed since you will never see it at all.

EDIT: and to be clear I understand the OP to be focused more on this “systemic messaging” type system not “user messaging”. He’s making comparisons to RabbitMQ and such, where the goal is to have systems work together by sending messages and handling them. It’s critical to not miss messages, and it’s also generally critical to offer some means of guaranteeing order (perhaps scoped, perhaps global). These requirements can be difficult to satisfy jointly in Postgres without sacrificing a fair bit of performance.

And importantly in the case of postgres, you could have perfect clocks and it won’t stop the “I’ve observed up to time X, therefore I’ll never get a message before time X” problem, since commit times allow for a ton of ordering issues.

If we maintain strict serializability, the commit order matches the causal order, and there is no extra effort required to read messages in causal order because they are the same!

What you are proposing here is encoding causal information into each message and then handling that case-by-case in application code. This is an outdated approach (Dynamo did this, for example).

The problem is that you do not always know the causal link between messages.

I chose my examples very carefully to demonstrate this, and you have again chosen an example (chat message reply) that happens to be convenient for determining causality.

Here is a more detailed example showing why this does not work:

A doctor and a patient are collaborating on their medical records via cloud storage. The doctor sets the cloud drive to private, and then turns to the patient and says “ok, upload your medical records”. The patient connects, via their phone, to a different server and uploads their records.

If you observe these events out of order, private records will be exposed. These events have no clear causal link from the perspective of the database.