Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres transactions are a distributed systems superpower

dbos.dev

Post reply on HN
Print view View original

61–70 of 108 posts

Re: Postgres transactions are a distributed systems superpower

#61

Post by atomicnumber3 »

orbisvicis wrote: post #57
andix wrote: post #45

Earlier quoted context omitted.

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

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.

Re: Postgres transactions are a distributed systems superpower

#62

Post by akoboldfrying »

cadamsdotcom wrote: post #55

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.

> when the database goes down it all goes down

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.

Re: Postgres transactions are a distributed systems superpower

#63

Post by andix »

orbisvicis wrote: post #57
andix wrote: post #45

Earlier quoted context omitted.

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

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.

Re: Postgres transactions are a distributed systems superpower

#64

Post by 620gelato »

KraftyOne wrote: post #60
orbisvicis wrote: post #57

Earlier quoted context omitted.

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

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 a good tradeoff I suppose. I've been racking my brain trying to find a solution recently that solves both of these but haven't been able to.

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.

Re: Postgres transactions are a distributed systems superpower

#65

Post by foundart »

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…

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.

Re: Postgres transactions are a distributed systems superpower

#66

Post by jdw64 »

KraftyOne wrote: post #14
jdw64 wrote: post #12

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

Yes, the core design is building a workflow system on a database--essentially, replacing the central orchestrator most workflow systems use with a Postgres database. This previous blog post goes into more detail: https://www.dbos.dev/blog/postgres-is-all-you-need-for-durab... (HN discussion: https://news.ycombinator.com/item?id=48313530 )

thanks!!

Re: Postgres transactions are a distributed systems superpower

#67

Post by fulafel »

andix wrote: post #45
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…

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.

Argument against: The system can't guarantee the message queue receives it, if the transaction is considered finished after the outbox commit.

Scenario: The system turns out to have a data dependent bug that prevents that message from being received by the message broker.

Re: Postgres transactions are a distributed systems superpower

#68

Post by oaiey »

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…

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.

Re: Postgres transactions are a distributed systems superpower

#69

Post by mrkeen »

620gelato wrote: post #64
KraftyOne wrote: post #60

Earlier quoted context omitted.

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 a good tradeoff I suppose. I've been racking my brain trying to find a solution recently that solves both of these but haven't been able to. 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.

Too much work. Don't try to act as the sender yourself. The outbox pattern leaves that as an exercise for the reader. How many receivers do you have? Do they come and go while you're trying to send messages? Do they need to find out about old messages that were send before they came online?

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.

Re: Postgres transactions are a distributed systems superpower

#70

Post by rnagulapalle »

fulafel wrote: post #67
andix wrote: post #45

Earlier quoted context omitted.

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.

Argument against: The system can't guarantee the message queue receives it, if the transaction is considered finished after the outbox commit. Scenario: The system turns out to have a data dependent bug that prevents that message from being received by the message broker.

[flagged]
Post reply on HN