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

41–50 of 108 posts

Re: Postgres transactions are a distributed systems superpower

#41

Post by mrkeen »

vlovich123 wrote: post #35
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…

> Sure enough it sounded like this article 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

Re: Postgres transactions are a distributed systems superpower

#42

Post by convolvatron »

mrkeen wrote: post #33
belinder wrote: post #21

Earlier quoted context omitted.

Why not just put the message queue in the same db

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…

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.

Re: Postgres transactions are a distributed systems superpower

#43

Post by mrkeen »

convolvatron wrote: post #42
mrkeen wrote: post #33

Earlier quoted context omitted.

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…

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.

Yes, same-ordering gives you EC, not the other round.

Re: Postgres transactions are a distributed systems superpower

#45

Post by andix »

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.

Re: Postgres transactions are a distributed systems superpower

#46

Post by pylua »

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…

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.

Re: Postgres transactions are a distributed systems superpower

#47

Post by sarchertech »

mrkeen wrote: post #41
vlovich123 wrote: post #35

Earlier quoted context omitted.

> Sure enough it sounded like this article 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.

I wrote about this here a few years ago.

https://linuxblog.io/the-two-generals-problem/

Re: Postgres transactions are a distributed systems superpower

#48

Post by game_the0ry »

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…

I envy you DB + distributed systems specialists. Reminds me I still have a lot to learn.

Re: Postgres transactions are a distributed systems superpower

#49

Post by halfcat »

LgWoodenBadger wrote: post #37
KraftyOne wrote: post #8

Earlier quoted context omitted.

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

And if that job never runs? Or if that job runs and then fails to commit that it ran in postgres?

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).

Re: Postgres transactions are a distributed systems superpower

#50

Post by gopalv »

cloudie78 wrote: post #2

Congratulations, you discovered a mutex. Is it really a distributed system or just a bunch of services with a central database?

> Is it really a distributed system or just a bunch of services with a central database?

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.

Post reply on HN