Postgres transactions are a distributed systems superpower • Hacker News Board

Re: Postgres transactions are a distributed systems superpower

#81

Post by mike_hearn » Fri, Jul 03, 2026, 9:09 AM UTC

mrkeen wrote: post #18

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/even…

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.

Re: Postgres transactions are a distributed systems superpower

#82

Post by gr__or » Fri, Jul 03, 2026, 9:10 AM UTC

dylanlacom wrote: post #71

mrkeen wrote: post #18

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/even…

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.

sauce: https://brandur.org/job-drain

Re: Postgres transactions are a distributed systems superpower

#83

Post by mrkeen » Fri, Jul 03, 2026, 9:27 AM UTC

dcminter wrote: post #77

mrkeen wrote: post #18

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/even…

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?

I have the starting state:

  DB={}    Q={}

I would like to either remain in the starting state, or enter a new state:

  DB={Bob paid $15}  Q={Bob paid $15}

But this is Two Generals, which is impossible.

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}

A strictly harder problem, right?

Re: Postgres transactions are a distributed systems superpower

#84

Post by mrkeen » Fri, Jul 03, 2026, 9:44 AM UTC

kokonuts wrote: post #72

oaiey wrote: post #68

Earlier quoted context omitted.

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…

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…

I somewhat agree with 'just use the db' over 'use the db as the state and the outbox'.

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.

Re: Postgres transactions are a distributed systems superpower

#85

Post by mrkeen » Fri, Jul 03, 2026, 9:47 AM UTC

mike_hearn wrote: post #81

mrkeen wrote: post #18

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/even…

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 o…

I hadn't heard of throwing money at it before.

Maybe "Two Generals" doesn't work, but "Two Rich Generals" does.

Re: Postgres transactions are a distributed systems superpower

#86

Post by mike_hearn » Fri, Jul 03, 2026, 9:54 AM UTC

mrkeen wrote: post #85

mike_hearn wrote: post #81

Earlier quoted context omitted.

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 o…

I hadn't heard of throwing money at it before. Maybe "Two Generals" doesn't work, but "Two Rich Generals" does.

If you compare prices vs cloud managed Postgres it's not that expensive.

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.

Re: Postgres transactions are a distributed systems superpower

#87

Post by mrkeen » Fri, Jul 03, 2026, 9:56 AM UTC

mike_hearn wrote: post #86

mrkeen wrote: post #85

Earlier quoted context omitted.

I hadn't heard of throwing money at it before. Maybe "Two Generals" doesn't work, but "Two Rich Generals" does.

If you compare prices vs cloud managed Postgres it's not that expensive. 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 pric…

How much does it cost to rent a db that can do CAP?

Re: Postgres transactions are a distributed systems superpower

#88

Post by oaiey » Fri, Jul 03, 2026, 10:05 AM UTC

kokonuts wrote: post #72

oaiey wrote: post #68

Earlier quoted context omitted.

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…

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…

If you write a good monolith, you would do exactly that. You message between the subsystems and you are good with just adjust the "ownership" within a transaction.

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)

Re: Postgres transactions are a distributed systems superpower

#89

Post by oaiey » Fri, Jul 03, 2026, 10:13 AM UTC

mrkeen wrote: post #84

kokonuts wrote: post #72

Earlier quoted context omitted.

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…

I somewhat agree with 'just use the db' over 'use the db as the state and the outbox'. 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 agree with you. For one, if you are small, build a good monolith and call it a day. So much more efficient than anything else.

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.

Re: Postgres transactions are a distributed systems superpower

#90

Post by lima » Fri, Jul 03, 2026, 11:31 AM UTC

zyngaro wrote: post #44

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.