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

51–60 of 108 posts

Re: Postgres transactions are a distributed systems superpower

#51

Post by koverstreet »

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

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.

Re: Postgres transactions are a distributed systems superpower

#52

Post by AlotOfReading »

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.

Order independence/monotonicity is strong EC rather than regular EC.

Re: Postgres transactions are a distributed systems superpower

#53

Post by zadikian »

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?

Re: Postgres transactions are a distributed systems superpower

#54

Post by KraftyOne »

zadikian wrote: post #53

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

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.

Re: Postgres transactions are a distributed systems superpower

#56

Post by nickpeterson »

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.

Only a Sith deals in absolutes

Re: Postgres transactions are a distributed systems superpower

#57

Post by orbisvicis »

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.

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.

Re: Postgres transactions are a distributed systems superpower

#58

Post by EGreg »

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

Re: Postgres transactions are a distributed systems superpower

#59

Post by akoboldfrying »

munk-a wrote: post #16
brentjanderson wrote: post #13

Earlier quoted context omitted.

I think Ducklake[1] is a terrific example of this. They said "look, let's build a lake house over S3, but for the bit that needs strong consistency (the manifest of which S3 blobs are in play), let's use Postgres". Postgres as a metadata catalog or control plane is brilliant for this, since you get strong consistency and the scaling story around a metadata catalog is far different than the volume of data you need to…

I have built and maintain a system that uses a very similar system - we register artifacts with UUIDs into S3 in a specifically write-once, never edit, never remove approach and then store those UUIDs in a postgres system. We simply juggle around the connection of other model objects to UUIDs as needed allowing us to achieve safe guarantees without burdening the centralized system with the massive volume (these artif…

If you use hashes of the content itself for your UUIDs, you'll (a) get deduplication and data consistency checking for free and (b) have basically implemented (a subset of) git that uses S3 backing instead of a local filesystem directory :)

Re: Postgres transactions are a distributed systems superpower

#60

Post by KraftyOne »

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…

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.
Post reply on HN