i don't understand the last point of UDF.
Either you need the state to be updated atomically across different systems or you don't.
But writing a row in a system in order to update the second one at any random time in the future isn't really much different from enqueuing a job in queue.
Can you use postgres as a state store for a distributed application?
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?
So my understanding is that they're aligning the workflow progression unit and the database commit unit on a one-to-one basis. In other words, each step in the workflow becomes a database commit unit. That's why the outbox pattern gets simplified. But in exchange, the database itself becomes tightly coupled to the workflow, which will make it architecturally difficult to separate later on. Although, to be fair, I almost never actually need to separate the database anyway.
In most services, I often swap out the message broker or the workflow engine, but the database almost always stays the same.
We've leveraged the atomicity of transactions with a fail-safe approach for external service interactions for client email sending. This could certainly be done with a formal queue though it'd operate very similarly and achieve the same guarantees as we have today (and was built when we were too small to justify such an infra spend). Internally we have jobs that execute complex logic to transform data from a pending state to a computed state which lean on the DB's atomicity to guarantee that data is successfully transitions and those tasks are all incredibly resilient - but when a secondary persistence store is involved transactional guarantees need to be compromised in some manner. In our email sending example we have the opinion that it is more important to guarantee a client receives all notifications compared to a notification being guaranteed to be sent precisely once so our mechanism in sending is to confirm email sending was successful and then close a transaction that removes that message from the pending list.
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.
We’ve got an in-house pubsub solution that lives in the main applications database, so pretty much exactly as described in the article. And the atomicity it allows is indeed really nice!
I walked away from a job interview a few years ago on this point.
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)
is to split D into two parts (the State and the Outbox), transact across those instead
(S,O) Q
and then just pretend that you have a transaction across D and Q.
The bridge between inbox/outbox and queue is not perfect. But it derisks the process a lot. It is much saver to insert a (ideally idempotent) message into the database and then (without transaction) confirm it to the queue than running the whole business process. The likelihood the process will fail is much higher than the inbox / outbox. These patterns also keep your brokers queue empty and allows you to gracefully shutdown your systems.
One of my favorite pieces of technical writing is Brandur Leach’s “Transactionally Staged Job Drains in Postgres” where he reasons through the outbox pattern from first principles. I remember reading it for the first time and feeling like I had been let in on a big secret. Clever, simple, powerful. I still use the pattern all the time.
Is there a reason why two-phase commit can't work with a DB and a message queue? DB2 and MQ Series used to support this (though they called it "XA" transactions and you had to compile support into the drivers which felt a bit sketchy - late 90s I think). Should I have been suspicious of this?
It's an odd question but it's not impossible. Some database products have a full transactional message queue product built in, at which point it's easy. This might sound like "cheating" but why? The assumption that MQs and databases are necessarily different or run in different transactional domains is one you can void by just spending some money.
[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.
OK. I've read it a few times and still don't understand. Where is the distributed part? You store data in a single transaction into postgres. What/who is notifying the message queue?
The article is ridden with misconception. Have you guys ever heard of the CAP theorem ?
Disturbed system suck let's implement a non distributed one. The title is also misleading: Postgres transactions are not distributed.
This. It's easy to forget that Postgres is fundamentally a single-node database without distributed transactions. It won't pass the Jepsen test suite with multiple nodes. DBOS, Temporal and friends inherit this limitation.
Something like Restate actually implements distributed transactions.
The part about durable workflows is technically correct, but it's focusing on different things than what I've ever run into in practice. Any mildly complex system will have side effects outside your DB, then you want idempotency. If you have no side effects, you probably don't need a durable workflow in the first place? Maybe there's a more concrete example.
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?
The coolest thing about using Postgres for everything is when the database works everything works and when the database goes down it all goes down, so you get to fix nothing most days then everything all at once.
This is the trick that kills the dual-write bug in money-movement systems: co-locate the checkpoint with the write so a mid-workflow crach can't leave you half-commited.
29 comments
[ 3.0 ms ] story [ 55.1 ms ] threadIs it really a distributed system or just a bunch of services with a central database?
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?
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.
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.
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:
is to split D into two parts (the State and the Outbox), transact across those instead and then just pretend that you have a transaction across D and Q.[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.
Something like Restate actually implements distributed transactions.
https://www.postgresql.org/docs/current/two-phase.html
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?
In the OP's case, pg down but luckily workflow works??