[deleted]
Postgres transactions are a distributed systems superpower
91–100 of 108 posts
Re: Postgres transactions are a distributed systems superpower
#92Earlier 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.
The outbox is basically a local queue in front of the remote queue.
Re: Postgres transactions are a distributed systems superpower
#93Earlier quoted context omitted.
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?
What do you mean by do CAP?
If you mean the CAP theorem then that's an impossibility result, so...
Re: Postgres transactions are a distributed systems superpower
#94I 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…
Despite the person you responded to playing the fool, I found this discussion very interesting. I had never thought about a built in queue as a solution here.
Re: Postgres transactions are a distributed systems superpower
#95Earlier quoted context omitted.
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 probl…
Ah, you're assuming distributed, which I wasn't. We are not at home to partition tolerance :)
Makes sense in the context of the original post though.
Re: Postgres transactions are a distributed systems superpower
#96Earlier quoted context omitted.
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?
Yeah I once saw a system designed about 2000 or so that pulled messages from an MQ queue and updated a database all within a single transaction managed by COM+. To be honest the distributed transaction side of it seemed more bother than it was worth...
Yeah, for a lot of systems it really is more trouble than it's worth - I remember enjoying this article when it came out: https://www.enterpriseintegrationpatterns.com/ramblings/18_s...
Re: Postgres transactions are a distributed systems superpower
#97I 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.
Youtube. Design interviews and anything about how cloud services work etc.
Re: Postgres transactions are a distributed systems superpower
#98I 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?
[dead]
Re: Postgres transactions are a distributed systems superpower
#99Earlier quoted context omitted.
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 probl…
Ah, you're assuming distributed, which I wasn't. We are not at home to partition tolerance :) Makes sense in the context of the original post though.
But you are assuming distributed transactions as soon as you have two systems participating in the transaction.
It's a recipe for deadlocks and even live locks.
That's a reason industry moved away from this. Bc when it works it's magic. But when the problems start it's pure hell.
Re: Postgres transactions are a distributed systems superpower
#100I 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 acros…
"One major trick in distributed systems is to always attempt things in the same order"
This is inportant in DBs in general to avoid deadlocks by two requests taking locks in different order.