I don't think it's true that distributed and decentralized mean the same thing. A hub and spoke rail system is centralized, but it's still a distributed system, if it has multiple trains running concurrently.* A distributed system has to coordinate somehow, and a single central DB is one way of doing it.
*: edit, maybe a better example here is a rail system with a single central dispatcher is centralized but may still be distributed
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.
The key is that the UDF's enqueue is transactional with the database update. Let's say the database update is inserting a new order. This provides the guarantee that if a new order is inserted, a job to process the order is also enqueued. It's impossible for a new order to be inserted without its processing job also being enqueued. Then the durable workflow/queue system is responsible for making sure the processing job, once enqueued, actually executes.
The job will run the next time a worker runs (in both cases).
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).
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.
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?
Yes you can - usually I think it's advisable to wrap postgres in a shim application to provide a consistently defined surface you can control but postgres can absolutely serve as the authority node on data correctness.
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.
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.
I'm glad you mentioned DBOS. I personally think DB based OS or more accurately data-centric OS is the way to go to simplify all the distributed applications (I'm looking at you Kafka!).
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].
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.
That's what I generally choose. You don't need to worry about distributed system semantics, if you choose to not make the system distributed.
However 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 1: identify that you and at least one other node are separated by distance, and some lossy communication channel, and therefore form a distributed system.
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.
eventual consistency as generally used doesn't guarantee that events are presented in the same order. I use 'monotonic consistency' for that, but idk how common that is.
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.
But that's what the outbox pattern is. You take the problem of transacting between more than one system, and by "just using the db", you declare the problem solved, leaving the communication with other systems as an exercise for the reader.
From the end of the article:
The enqueue_workflow UDF creates this row in the same transaction as the user database update, guaranteeing atomicity
Your right! But the outbox pattern is good enough for a lot of purposes. The outbox pattern works if the only reason the write to the 2nd system can fail is because of transient issues. It will keep trying until the system is back up.
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.
The point is that the "outbox pattern" is not an atomic transaction. You fundamentally don't have those in the distributed world, via the CAP theorem, and if you want anything close to the guarantees a local transactional database gives you in a distributed system you have to design your schema for it.
Distributed coherency is not something you can abstract away, the abstractions all leak.
With an inbox/outbox pattern it's possible. The incoming message might be processed more than once, and an outgoing message might be sent more than once. That's the limitation, and the system needs to be able to handle it.
If you can't de-duplicate messages it's not possible, that's true.
I'm not following. Doesn't the outbox pattern just pass the buck?
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.
Outbox's power is that it turns an atomicity problem into an idempotency problem. You atomically write to the outbox, then you have an idempotent "workflow" that processes events from the outbox. This turns "at most once" semantics (where an event could be dropped entirely) to "at least once" semantics (where the event processing could run multiple times). For many systems, that's a big improvement.
That's true for duplicates, but even if a message is processed once, that doesn't tell you whether the validation step for that message actually finished before the commit that made it happen.
No, you're right. It basically just passes the buck. But the general idea is that if your transaction succeeds, you KNOW that there is a durable record that some external thing needs to end up in a message bus. And then something else can sit there and spin retries until it happens. It gives you the opportunity for retrying getting it onto the message bus, out of band of the process that is trying to initiate the enqueue.
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.
The message is written to the outbox table in the same transaction as the database changes. Only if the transaction completes, the message is actually created, and other tables are updated.
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.
Just post to the database then asynch send to message queue. Messages should still be idempotent by the consumer but at least this follows rest and is transactional.
It’s simple and easy to follow. At scale use multi tenancy.
One major trick in distributed systems is to always attempt things in the same order. And then locally, you just store what you’ve seen, for “a long time”. That takes care of a lot of transactional issues — idempotency, retries, exactly-once delivery with no distributed locks, etc.
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.
The way they worded that question is bad and, as you say, the outbox pattern does not transactionally update the queue itself. The outbox pattern is nevertheless very useful.
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.
At that point why not just keep the broker shutdown and have the parties read from the DB and update an ownership column? Personally, I find the whole message queue thing the typical waste of time, absurdly complex and pointlessly corrupting component you get when you pretend you are Twitter and have the problem of maybe delivering a lot of stuff single entry style instead of provable having handled a small amount of stuff that needs to be double entry handled because however small a professional gig is it still uses real accounting.
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?
Yeah I once saw a system designed about 2000 or so that pulled messages from an MQ queue and updated a database all within a single transaction managed by COM+. To be honest the distributed transaction side of it seemed more bother than it was worth...
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.
Despite the person you responded to playing the fool, I found this discussion very interesting. I had never thought about a built in queue as a solution here.
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?
You build a distributed system on top of this! For example, you may have many distributed workers durably executing workflows from the Postgres-backed task queue. The Postgres transactions allow you to atomically perform operations spanning both your task queue and your business data.
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?
You still need idempotency for side effects outside your database, that's true (and fundamental to durability). But now you get exactly-once semantics for operations on your database, which can be quite valuable if your workflow performs many such updates or they're particularly complicated or stateful.
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.
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.
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.
89 comments
[ 3.3 ms ] story [ 70.5 ms ] threadIs 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
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.
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).
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.
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'
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:
https://d4m.mit.edu/
[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:
https://lwn.net/Articles/1062286/
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.However 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.
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:
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.
https://linuxblog.io/the-two-generals-problem/
Distributed coherency is not something you can abstract away, the abstractions all leak.
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.
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.
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.
Scenario: The system turns out to have a data dependent bug that prevents that message from being received by the message broker.
It’s simple and easy to follow. At scale use multi tenancy.
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.
If you invoke 2PC, you want the states to progress thus:
A strictly harder problem, right?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.
[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.
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.
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?
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??