3 ms·
I actually wrote about this problem recently [1]. (Note that in that comment I'm not describing a job system, but rather a system for keeping one or more second
by lobster_johnson 11y ago
I actually wrote about this problem recently [1]. (Note that in that comment I'm not describing a job system, but rather a system for keeping one or more secondary stores in sync with a master database.)
If you're using an external queue such as RabbitMQ, there's basically no way to guarantee a queue message being fired without a "two-phase" transactional approach.
Let's say your use case is creating a user and sending an email verification email. You need to:
1. Start transaction.
2. Create the user.
3. Insert a tuple into an "events to fire" table.
4. Commit.
5. Fire the event.
6. Delete the tuple.
This ensures that if the transaction fails, nothing is fired; if the transaction succeeds, we guarantee that the user now exists, and that the command to fire the event also exists. Thus, if step 5 fails, we still have the "events to fire" tuple, and we can have a cron job running every minute that picks up stragglers. (In practice, you'll need to run steps 5-6 in a transaction that exclusively locks the tuple so that a concurrent cron job doesn't do it.)
This way, you're risking duplicate events, but you'll never lose the event, unless RabbitMQ falls over. Duplicate events are of course unfortunate and undesirable, but missing events are worse.
Of course, with the above solution, it could be argued that your "events to fire" table is a queue itself. The good news is that RabbitMQ is much better at scaling a queue than Postgres, so the duplication of work isn't actually that much of a problem. The "events to fire" table can be optimized in various ways; Postgres supports "unlogged" tables, for example.
Another approach which will be available soon is Bottled Water [2], which gives you pretty much the above, except without needing to maintain the "events to fire" table. But it's definitely more complicated.
[1] https://news.ycombinator.com/item?id=9490583 https://news.ycombinator.com/item?id=9490583
[2] http://blog.confluent.io/2015/04/23/bottled-water-real-time-integration-of-postgresql-and-kafka/ http://blog.confluent.io/2015/04/23/bottled-water-real-time-...
- adamtj 11y agoUnlogged tables aren't durable and might lose events even after a successful commit. If you're willing to make that trade off, then you don't need Postgres in the first place.
- lobster_johnson 11y agoSure, which is why I said it would be an optimization. But if your PostgreSQL server dies in the middle of that transaction, I would be more worried about RabbitMQ.