Erik Darling has created a series on queue tables in the database. Part one builds out a table:
A little explanation of the table: this is good for a reusable, ordered queue, that’ll give us some feedback on how long things took. It could also be used is there were a pending element, but we’d probably wanna change the indexing so that we could find either the last start time, or the last end time efficiently.
The thing that probably needs the most explanation here is the indexing and constraints. Because I know you, and you’re staring at the way I have my primary key, and you’re getting an itch and a twitch. That’s okay, it’s you’ve been conditioned for years to put the most selective column first. Unfortunately, that won’t work here.
Part two takes us through querying the queue:
For the table, our main priorities were indexing to make sure we can find work easily, and not allowing duplicate items.
For our worker proc, the main goals are going to be
– Looping until we run out of work
– Finding and reserving work with minimal locking
– Making that process atomicThankfully, this is a lot easier than it sounds. The table design does a lot of the work for us.
Erik’s design is not one I typically reach for, though my constraints are a bit different from his—typically, I’m using queue tables to run on periodic schedules and grab batches of records which finish processing before the next timed batch begins, and processes are idempotent, so if a queued item re-runs on occasion, it’s okay. But this is a really good technique if you need a more robust solution.