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

101–108 of 108 posts

Re: Postgres transactions are a distributed systems superpower

#101

Post by nextaccountic »

belinder wrote: post #21
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…

Why not just put the message queue in the same db

Yep. That's why I will always recommend https://github.com/pgmq/pgmq/

Re: Postgres transactions are a distributed systems superpower

#102

Post by johnthescott »

akoboldfrying wrote: post #59
munk-a wrote: post #16

Earlier quoted context omitted.

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

for smallish databases, where uuid is surogate id, i would trust dbos/postgres replication. replication is just too hard to role your own.

tangentially, for dbs with large blobs, lot's of easy tricks when uuids are immutable digests.

syncing, say, two blob stores, A and B, boils down to jaccard metric, as a first order approximation

   |(A ∩ B)| / |(A ∪ B)|
diffing the two digest sets at point in time is second order approximation.

and don't forget logical replication ...

Re: Postgres transactions are a distributed systems superpower

#103

Post by johnthescott »

akoboldfrying wrote: post #62
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…

amen.

Re: Postgres transactions are a distributed systems superpower

#104

Post by JackSlateur »

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.

Are they not ?

https://www.postgresql.org/docs/current/two-phase.html

Re: Postgres transactions are a distributed systems superpower

#105

Post by zyngaro »

JackSlateur wrote: post #104
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.

Are they not ? https://www.postgresql.org/docs/current/two-phase.html

No they are not. Read the docs.

Re: Postgres transactions are a distributed systems superpower

#107

Post by HenriTEL »

akoboldfrying wrote: post #62
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…

Your DB runs more heterogeneous workloads and migrations so it's more likely to blow up.

Re: Postgres transactions are a distributed systems superpower

#108

Post by oludeleop »

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