4 ms·
This was a well written article with an interesting investigation. However, storing small, ephemeral messages like jobs in a queue within Postgres is a bad ide
by wpeterson 11y ago
This was a well written article with an interesting investigation.
However, storing small, ephemeral messages like jobs in a queue within Postgres is a bad idea and the pain far outweighs the benefits of transactional rollback for jobs.
Instead, a much simpler solution is to plan for jobs to run at least once, use a more appropriate datastore like Redis or RabbitMQ, and build in idempotency and error handling at the job layer.
Postgres used as a system of record shouldn't be used for ephemeral message queues.
- anarazel 11y agoWell, it's not necessarily that simple. It can be very interesting to be able to directly enter jobs into a queue in a transactional manner, with very low latency. Say from a trigger. Edit: typo
- wpeterson 11y agoFor the sake of error handling on rollback, you're usually better delayed job enqueueing events to after commit hooks if you're concerned about failures resulting in a rollback.
- adamtj 11y agoDoing anything after committing misses the point. If you can do that, you don't need postgres. For example, suppose you mark an account closed, commit, and then enqueue an event to issue a refund from another system. It's possible that your process may crash or be killed at just the wrong time leaving you with a closed account but no refund. So what if you enqueue the event before you commit? In that case, you might crash before committing which will automatically rollback. Now you've done a refund on a non-closed account. Transactions make it trivial to guarantee that either both happen or neither do. There are other ways to get that guarantee, but they require more work and are more error prone.