[mike_hearn]

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 opinions are my own. [/boilerplate]

This feature is one big reason so many companies use Oracle, it offers this out of the box. It has AQ (Advanced Queuing) and the more modern TxEQ which is all built on the same underlying mechanisms as the relational database engine, so queue pushes and pops are atomic with other transactions.

Postgres has an extension that claims to add an MQ too but I don't consider it safe to use personally, because it doesn't implement proper locking/dequeuing. Instead you get a visibility timeout, so you have to choose how long a message remains dequeued before it goes back onto the queue automatically. That's a harsh choice - in the case of unexpectedly slow message processing a second worker might start processing a message that's already in flight, causing data corruption or business correctness problems (e.g. double charging a customer).

A proper MQ product like TxEQ doesn't have this problem because dequeueing is implemented as you'd expect, so a message that's dq'd into a transaction remains invisible to other workers until either the transaction commits, rolls back or the session is terminated due to abandonment (client no longer responds to pings). You can't get multiple workers processing a message simultaneously unless there's a split brain scenario (really rare in practice and a fundamental limit).

Also useful: AQ/TxEQ are full spec-compliant message queue brokers that support the standard feature sets and semantics you normally need, like exception queues. PGMQ lacks these.

And finally Oracle DB scales horizontally as does the integrated MQ, so it's reasonable to have very high traffic apps that use integrated MQ/DB transactions. The newer TxEQ feature uses a similar scaling design as Kafka.

So it's interesting that this is being used as a technical interview question when the answer would seem trivial to any bank DBA.

[mrkeen]

I hadn't heard of throwing money at it before.

Maybe "Two Generals" doesn't work, but "Two Rich Generals" does.

[mike_hearn]

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 prices to rent an Oracle DB and see how it compares, especially because they flex well so on the smaller end it can end up being cheaper as you're renting only part of a machine. Plus a lot of times people will tell you that Postgres can do this or that, but then it requires some custom extension that is not necessarily available in your cloud's managed product. A lot of stuff that's extensions in Postgres are out of the box features in Oracle e.g. message queues or JavaScript support.

[mrkeen]

How much does it cost to rent a db that can do CAP?

[mike_hearn]

What do you mean by do CAP?

If you mean the CAP theorem then that's an impossibility result, so...

[lonely_wanderer]

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.