This is something worth recording. The original article Transactionally Staged Job Drains in Postgres, which is informative by itself, was linked from Hacker News, which has even more supporting information.
Building a robust background worker system that leverages transactional isolation to never work a job too early, and transactional durabiity to never let one drop.
With this pattern, jobs aren’t immediately sent to the job queue. Instead, they’re staged in a table within the relational database itself, and the ACID properties of the running transaction keep them invisible until they’re ready to be worked. A secondary enqueuer process reads the table and sends any jobs it finds to the job queue before removing their rows.
Jobs are only removed after they’re successfully transmitted to the queue, so even if the worker dies partway through, it will pick back up again and send along any jobs that it missed. At least once delivery semantics are guaranteed.