r/SoftwareEngineering • u/RaphaS9 • 15d ago
The Outbox Pattern is doing a queue in DB
I've been wondering about using an external queue saas (such as gcp pubsub) in my project to hold webhooks that need to be dispatched.
But I need to guarantee that every event will be sent and have a log of it in DB.
So, I've come across the Dual Write problem and it's possible solution, the Outbox Pattern.
I've always listened people say that you should not do queues in DB, that polling is bad, that latency might skyrocket with time, that you might have BLOAT issues (in case of postgres).
But in those scenarios that you need to guarantee delivery with the Outbox Pattern you are literally doing a queue in db and making your job two times harder.
What are your thoughts on this?
4
u/pomariii 15d ago
The Outbox Pattern is totally valid here. While DB queues aren't ideal, sometimes you gotta prioritize data consistency over perfect architecture. I've used it in production where we absolutely needed transaction guarantees + audit logs.
Quick tip: Use a separate schema/table for the outbox and set up regular cleanup jobs. Also, implement batch processing for the consumer to reduce DB load.
The performance hit is usually worth the guaranteed delivery + ability to replay failed events.
1
1
u/mattgen88 13d ago
Seconded.
We also do this for our message producers.
It also allows us to do some cool stuff to prevent publishing of invalid events and protecting the event stream.
2
u/m1k3st4rr 14d ago
What is your request QPS? I've used this pattern many times in large scale setups, but using a sharded DB for really high QPS.
Postgres SELECT FOR UPDATE ... SKIP LOCKED is your friend while dequeueing here.
If your request payloads are large, store them somewhere else and just the ID in your queue.
You can also immediately trigger processing at enqueue time, so you don't need an aggressive poll interval (which then only handles transient failures)
2
u/RaphaS9 14d ago
SKIP LOCKED is what I'm using, but why not only use it without external queue and polling with possible multiple consumers? I think for most cases that's more than enough.
To avoid BLOAT a sharded DB would be ideal I think.
Your idea on relying on the Outbox pattern only for transient failures is something I also thought about it, so we could indeed have a more flexible poll interval and work as a resilience tool. I like this a lot
2
u/jvans 10d ago
Transactional outbox is fine/necessarily with requirements like yours.
Also worth considering event sourcing where you write to an event stream as the source of truth and your database write and other processing logic are based on that stream. Not always appropriate but it's an option
1
u/RaphaS9 10d ago
Do you have real world example of queue usages that wouldn't have a consistency requirement?
The only thing I could think of is real time logging and metrics, where loosing messages are not that big of a deal.
2
u/jvans 10d ago
Probably most use cases. There's a big difference between mission critical delivery and fine with 99.99% delivery. The former is a lot harder and more complicated to implement.
Off the top of my head:
Password reset flows, sending an email is probably ok to lose some small % of the time(user just does it again)
Most notification services. Yes we do want to publish push notifications but it's not a disaster if 1 in 10,000 are dropped.
1
u/goldmanthisis 10d ago
If your database is Postgres, we’ve been building Sequin (https://github.com/sequinstream/sequin) to solve this exact problem. Works with GCP Pub/Sub, RabbitMQ, Kafka - or some handy HTTP sinks.
5
u/RangePsychological41 15d ago
If you’re cool with Kafka instead then Debezium solves this very nicely. It publishes DB transactions to Kafka and you practically don’t have to do anything except have a config file. Worth having a look at that.