Implementing a Task Queue in SQL
Software systems often periodically execute collections of similar or identical tasks. Whether it’s computing daily account analytics, or running background computations asynchronously like GitHub Actions, it’s common to structure and complete this computation using a queue of tasks and the concept of a generic “worker” which executes the task. In the naive approach, a single worker sequentially executes each task, avoiding locks or other coordination.
The alternative to the single worker is to use a pool of workers, each pulling a task from the queue when they are ready to execute one. In exchange for the reduced queueing delay and the reduced overall wall-clock execution time, the programmer must manage the complexity of assigning and executing tasks. Some cloud services, such as Amazon Simple Queue Service (SQS), offer a managed abstraction for queueing tasks and assigning them to workers. However, they can be difficult to debug or impose undesirable properties, such as SQS’s default policy of at least once delivery (rather than exactly once). Lastly, it might be the case that you just don’t want the third-party dependency!
A queue provides some elasticity in your system when your message (or, in our case, task) volume varies or is unpredictable. It also allows you to impose smoothing to a computational workload, which would otherwise be bursty, by using a fixed set of resources to consume the queue and process the messages.
