One of the technical questions was "if you have a db and a message queue, how do you get your update to alter both or neither (i.e. transactionally)"?
I thought about it for a couple of minutes, then came back with something like "I can't, and you can't either." Then I proposed the usual spiel about using a replicated-state-machine/write-ahead-log/event-sourcing (whatever it might be called at the time) and leaning into eventual consistency as the only practical solution.
He asked if I'd heard about the outbox pattern, so I let him describe it. Sure enough it sounded like this article. The secret to transacting across the database D and the message queue Q:
(D,Q)
(S,O) Q
If you can't de-duplicate messages it's not possible, that's true.
The motive seems to be a naive process that enqueues a message and then commits to a database - two independent actions. But a well-behaved process would commit to a database, and then only if successful enqueue a message. That's better but still not atomic - commit, crash, and no message queued.
So the solution is a two-table write - the outbox pattern. But the process that reads the outbox must commit both a query and delete before sending the message. That's the same risk as the agreement well-behaved program - commit, crash, and no message queued. Except now you introduced another pipeline element so your overall complexity increases, and so too risk.
What if you never delete messages from the outbox? Well, what you have now is no longer an outbox nor a database nor useful for large volumes. What if you implement a database to track procesed messages. Return to square one - that's the same problem you were initially trying to solve.
What if you fetch, enqueue, and then delete? Ohh... that works. In case of a crash the message remains in the outbox. It may be processed in duplicate, but eventually if successfully it will be deleted from the outbox.
The message broker then receives a possibly duplicate message. It must consult its internal database, and if the message is unique, route it. So right back at square one. Can't have atomicity and uniqueness.
Scenario: The system turns out to have a data dependent bug that prevents that message from being received by the message broker.
FWIW The article literally talks about the challenges with getting this to actually work and recommends removing it and just using the DB for everything.
From the end of the article:
The enqueue_workflow UDF creates this row in the same transaction as the user database update, guaranteeing atomicityHowever the way Postgres keeps around obsolete rows (deleted or modified) until they're vacuumed can cause problems for high throughput queues. So for those systems the complexity might be worth it. But I bet 90% of the time the choice to use a separate queue is premature optimization. And hopefully OrioleDB (undo based storage engine for postgres) will avoid most of these pitfalls reducing the need for separate queues even further.
Step 2: propose a source of truth that everyone can listen to. Hearing the same facts in the same order should put everyone in the same state (eventual consistency)
Step 3 (you are here): try to do better than EC, by merging the external queue into one of the nodes, making it the master.
Step 4: Now there's no distance between the nodes, so no need to solve the distributed systems problem and you can retire the queue.
[boilerplate] Disclosure: I work part time in the Oracle DB team and opinions are my own. [/boilerplate]
This feature is one big reason so many companies use Oracle, it offers this out of the box. It has AQ (Advanced Queuing) and the more modern TxEQ which is all built on the same underlying mechanisms as the relational database engine, so queue pushes and pops are atomic with other transactions.
Postgres has an extension that claims to add an MQ too but I don't consider it safe to use personally, because it doesn't implement proper locking/dequeuing. Instead you get a visibility timeout, so you have to choose how long a message remains dequeued before it goes back onto the queue automatically. That's a harsh choice - in the case of unexpectedly slow message processing a second worker might start processing a message that's already in flight, causing data corruption or business correctness problems (e.g. double charging a customer).
A proper MQ product like TxEQ doesn't have this problem because dequeueing is implemented as you'd expect, so a message that's dq'd into a transaction remains invisible to other workers until either the transaction commits, rolls back or the session is terminated due to abandonment (client no longer responds to pings). You can't get multiple workers processing a message simultaneously unless there's a split brain scenario (really rare in practice and a fundamental limit).
Also useful: AQ/TxEQ are full spec-compliant message queue brokers that support the standard feature sets and semantics you normally need, like exception queues. PGMQ lacks these.
And finally Oracle DB scales horizontally as does the integrated MQ, so it's reasonable to have very high traffic apps that use integrated MQ/DB transactions. The newer TxEQ feature uses a similar scaling design as Kafka.
So it's interesting that this is being used as a technical interview question when the answer would seem trivial to any bank DBA.
Maybe "Two Generals" doesn't work, but "Two Rich Generals" does.
But as someone who builds distributed systems, I can tell you that transactions should be local. Anytime you want to lock something across the network (eg Canisters in ICP) so you can read it, that’s probably a code smell. You probably want to have evented reactive things ripple out, you do need idempotency, but you shouldn’t design your system to read remote state if you can help it. Only subscribe to remote messages.
This is inportant in DBs in general to avoid deadlocks by two requests taking locks in different order.
It’s simple and easy to follow. At scale use multi tenancy.
DB={} Q={}
DB={Bob paid $15} Q={Bob paid $15}
If you invoke 2PC, you want the states to progress thus:
DB={locked} Q={locked}
DB={locked; Q={locked;
Bob paid $15} Bob paid $15}
DB={locked; Q={locked;
Bob paid $15; Bob paid $15;
unlocked} unlocked}
In most services, I often swap out the message broker or the workflow engine, but the database almost always stays the same.
I'm not sure if I've understood this correctly.
In addition to DBOS please check the original data-centric OS proposed by the MIT team based on D4M technology. This new architecture data-centric OS similar to TabulaROSA in concept where data is managed and governed by mathematical relationship in this case associative array based D4M [1],[2].
This concept can be implemented initially on Linux without introducing a totally new OS unless you wanted to (read: VC money to burn), but it's not necessary like DBOS. This is possible now because starting kernel 7.0 Linux support generic non-conventional kernel bypass for memory, storage and compute with io_uring, eBPF and BPF Arena for examples [3].
[1] D4M:
[2] TabulaROSA: Tabular Operating System Architecture for Massively Parallel Heterogeneous Compute Engines [PDF]:
hmhttps://web.mit.edu/ha22286/www/papers/HPEC18.pdf
[3] BPF comes to io_uring at last:
There will always be a window for potential loss due to solar flares/whatever but the key in designing a system like this is to make sure you're aware of how the system can fail, accept that outcome and then work to, as much as possible, shrink the distance in cycles/logic between each persistence committal. Logic should be front-loaded to do as much prep work as possible before any irreversible actions happen and then those irreversible actions should be ordered to your preference and dispatched as quickly and cheaply as possible in a safe manner.
Is it really a distributed system or just a bunch of services with a central database?
*: edit, maybe a better example here is a rail system with a single central dispatcher is centralized but may still be distributed
There are always tradeoffs of course, but building a truly decentralized system requires some really difficult compromises to correctness. The two general's problem is a great piece of reading on this topic - distribution always requires compromises in general, but to fully remove an authority on truth gets quite tricky.
I've asked myself this question every single time I've had to use Zookeeper.
Apache Kafka being the poster child of the problem, with HBase in a close second.
Here's another blog post about how a Postgres-backed task queue can run at scale: https://www.dbos.dev/blog/making-postgres-queues-scale
When workers query the db for jobs the rows get locked by the select and there are no race conditions or duplicate assigned jobs
Something like Restate actually implements distributed transactions.
This sounds a lot like reinventing a message queue. Someone trying this in the future might learn painful lessons about ordering, commits, partitioning, dead-letter-queues, replayability, don't-call-me-I'll-call-you, and anything else a Kafka-like comes with out of the box.
I have rolled my own little durable workflows in Postgres before, in fact before I even knew durable workflows were a thing with solutions like Temporal. That's fine for many cases where you aren't doing enough steps for it to be tedious, and/or you want permanent records. Would do it again, but not for atomicity reasons.
Other comments have already discussed the issue with the outbox UDF, your external system has to poll and retry either way. It works though. Maybe I'm misunderstanding this?
Suppose you use PostgreSQL + Something Else instead of Just PostgreSQL, and PostgreSQL goes down: Is anything still working?
I suspect the answer is "Very little still works when the DB is down", so the opportunity cost of Just PostgreSQL is low.
Also, while it's possible that PostgreSQL still has concurrency bugs, I think for most teams the odds of hitting a concurrency bug in PostgreSQL are much lower than the odds of hitting a concurrency bug in your own complicated bespoke in-house distributed system.
In the OP's case, pg down but luckily workflow works??
It seems this article is trending toward that view: If you can maintain transactional consistency along with application workflow state, then would this generalize to maintaining distributed application state in general?
The follow-up would be: Would this be preferable to Valkey/Redis?
As to which technical solution would be optimal there are a bunch of factors to consider and I think preferences around features could lead you to a variety of options. Postgres is excellent as long as you're minimizing the amount of data piping directly through it or operating at a reasonable scale.
Yes, in the sense of 'too good to be true'
If the 2nd system write can fail for non-transient reasons, the outbox pattern doesn’t work and you need either 2 phase commit or a distributed saga.
I wrote about this here a few years ago.
Distributed coherency is not something you can abstract away, the abstractions all leak.
Neal Ford calls this a distributed monolith because a change to a database schema can break every single service at once, but there are very valid uses of this method.
There are decades of books on the foot guns as we used this even back in the client-server days.
One suggestion I have is to research where the first version of SoA failed, especially as these systems tend to erode into Enterprise Service Busses.
Products like Apache airflow tend to have value not because of the persistence layer, but because they force workflows into DAGs, which is an enforceable structural constraint, while SQL, being declarative, can sometimes force you into trying to enforce governance through observing behavior.
The former is not subject to Rice’s theorem, while the latter is.
If you actively control for these it will greatly increase the lifetime of this system before (or if) you reach the point you have to replace the system.
And doesn’t that mean the job potentially runs twice? Yes.
In DBOS there are two kinds of “things that run”: workflows, and steps (workflows are made of steps).
Workflows must be deterministic (so it’s fine if it runs twice). Steps don’t have to be deterministic but have at-least-once execution (so it’s best if these are idempotent).
Makes sense in the context of the original post though.
It's a recipe for deadlocks and even live locks.
That's a reason industry moved away from this. Bc when it works it's magic. But when the problems start it's pure hell.
What I had landed on was idempotency on a best effort basis and just made the event processing safely retryable without violating any system invariants.
There is much better alternative than this motte-and-bailey argument of "outbox GUARANTEES blah for a distributed system - but only within a single node".
Just write down what happened in Kafka. N followers read from Kafka to find out what happened.
Kafka is actually distributed tech. You can lose nodes and keep operating.
No need to design for atomicity. You either wrote to Kafka or you didn't.
In a second step the message is taken from the outbox and gets sent to the queue/broker. Only after it was sent out, the message is removed from the outbox. If the sending fails, it stays in the outbox and is retried. If the deletion of the message from the outbox fails after sending, it's getting re-sent later. So you can get a duplicated out-message.
Message brokers usually don't de-duplicate messages, they don't have a database that keeps messages, the receivers need to do that. Either with idempotency, or by tracking message ids. Event sourcing brokers can de-duplicate, because it can stores all messages.
If you never delete messages from the outbox, then they are re-sent all the time. You are going to notice such a bug really quickly.
Inbox pattern works very similarly, but the other way around.
And the outbox pattern isn't bs - it DOES help a lot in practice. But exactly how much it _guarantees_ something happens is of course still quite limited. And yes as you note it's an At-least-once strategy.
And message "queues" are probably a waste of time too.
Where you get a real benefit is in using a proper append-only ledger. This is a solved problem. Paxos and Raft both give you this on the theoretical side, and systems like Kafka give you practical implementations.
"Pull-based" systems are far, far easier to reason about. E.g., I'm going to update my packages now. I'm going to pull from git now. I'm going to GET news.ycombinator.com now. Imagine the opposite - news.ycombinator deciding to push the frontpage to whichever device I'm using, at the precise time I'm hoping to read it.
So pull is better, but if you can only pull, then how does anyone change any state? Push a new message into Kafka, and let it handle the switch from push to pull.
It may be absurdly complex, but it's the least absurdly complex option if you want to distribute. And if you don't want to distribute, you don't need outbox.
Stream based systems where you maintain your own curser are a strong architectural decision similiar to a messaging systems. They also have their downsides.
Lastly on the last sentence: as soon as you need reliable processing of external input or output, inbox/outbox are needed. You are distributed because of your payment processor, because of your user email sending, etc. You do not want to block your core job processor just because the email server is overloaded right now.
If you are distributed you have the problem of shared databases. It would break your Microservice ownership etc if you all operate on one database. It is an anti pattern. For very good reasons.
In the past there have been distributed transactions between databases or other systems but they fell out of love due to their proprietary and limited nature (e.g. Microsofts MSDTC)
Or rather, cloud managed Postgres is expensive, especially once you get into the cloud-specific forks of it that try to make it scale, because AWS/Azure/etc know that people will pay a lot of money for the Postgres brand but don't want to admin it themselves.
So the moment you commit to paying for a managed database you should check out the prices to rent an Oracle DB and see how it compares, especially because they flex well so on the smaller end it can end up being cheaper as you're renting only part of a machine. Plus a lot of times people will tell you that Postgres can do this or that, but then it requires some custom extension that is not necessarily available in your cloud's managed product. A lot of stuff that's extensions in Postgres are out of the box features in Oracle e.g. message queues or JavaScript support.
If you mean the CAP theorem then that's an impossibility result, so...
A similar pattern has spilled out of projects like Warpstream[2], which I suspect is using Postgres behind the scenes of their control plane.
It is!
And the solution is to add an extra general on the left side. Let's call him Outus Boxus. The two generals on the left side can communicate in perfect lockstep. Then if you need the general on the right to find out about something, you can send a few workers to tell him or something...
More seriously though, you can have a DS for two reasons: tech or political.
Tech means scaling or reliability. So clients can be serviced by any of the nodes.
Political means different actors don't have a central authority. You can't stick two banks into one db.
This technique doesn't seem to address either aspect.
tangentially, for dbs with large blobs, lot's of easy tricks when uuids are immutable digests.
syncing, say, two blob stores, A and B, boils down to jaccard metric, as a first order approximation
|(A ∩ B)| / |(A ∪ B)|
and don't forget logical replication ...