Project Walkthrough: Building a Queue System from Scratch
Build a minimal but complete job queue from first principles — a producer, a storage-backed broker, a worker loop, retry logic with exponential backoff, a failed-jobs table, and basic queue-depth monitoring — to understand the mechanics that libraries like the one covered in php-background-queues-job-processing wrap for you.
Most PHP projects reach for an existing queue library, and for good reason — but understanding what that library is actually doing underneath is what lets you debug it when a job mysteriously never runs, reason about at-least-once delivery guarantees, or build a custom worker when the off-the-shelf tool doesn't fit. This project builds a queue system from scratch: a producer that enqueues jobs, a simple database-table broker (the same mechanics apply almost unchanged if you swap in a Redis list), a worker process that claims and executes jobs, retry with exponential backoff for transient failures, a failed-jobs table for jobs that give up, and monitoring hooks that tie back into the metrics vocabulary from php-metrics-catalog-for-php-apps.
Project Overview and Requirements
A queue solves a specific problem: some unit of work is too slow, too unreliable, or too bursty to do inline during an HTTP request, so instead of doing it directly you record "please do this later" and let a separate process handle it at its own pace. Sending a welcome email, resizing an uploaded image, calling a flaky third-party API, or — as in the previous lesson — recording click analytics are all classic candidates: work that the user doesn't need to wait for, and that benefits from being retried if it fails transiently.
The core requirements for a minimal but honest queue system are: a way to enqueue a job with its type and payload; a way for one or more worker processes to claim a job without two workers grabbing the same one (this is the concurrency-critical part); execution of the job's handler with the payload; automatic retry with backoff when a job's handler throws; and a permanent record of jobs that exhausted their retries, so a human can inspect and potentially replay them rather than having failures vanish silently.
We'll build this on top of a plain database table rather than Redis, specifically because a relational table makes the concurrency-safe "claim a job" step easiest to reason about using ordinary SQL transactions and row locking — the same approach generalizes directly to a Redis-backed broker using BRPOPLPUSH or Redis Streams' consumer groups, which give you the same claim/acknowledge/retry shape with different primitives.
• Producer: enqueues a job (type + JSON payload) without blocking on its execution. • Broker: durable storage for pending, in-progress, and failed jobs — here, a database table. • Worker: a long-running process that claims one job at a time, executes it, and marks the outcome. • Retry with exponential backoff for transient failures; a failed_jobs table once retries are exhausted. • Monitoring: queue depth (pending count) and processed/failed counters, exported the way php-metrics-catalog-for-php-apps describes.
Architecture and File Structure
Two tables carry the whole system. The jobs table holds every job's lifecycle: id, queue (a name, so multiple logical queues can share one table), payload (JSON-encoded), attempts (how many times we've tried), status (pending, reserved, or done), available_at (a timestamp — a job isn't eligible to be claimed until this time, which is exactly how we implement backoff: pushing this timestamp into the future delays a retry), and reserved_at (set when a worker claims it, used to detect a worker that crashed mid-job and never marked it done). The failed_jobs table is simpler and append-only: id, queue, payload, exception, and failed_at, written once a job's attempts are exhausted.
On the code side, a JobQueue class owns push() (the producer's only method) and the claim/complete/fail/release primitives the worker needs; a Worker class runs the main claim-execute-ack loop; and a small registry maps a job's type string (e.g. 'send_welcome_email') to the callable that actually handles it, so the worker itself never needs to know about specific job types — it just claims a row, looks up the handler by type, and invokes it with the decoded payload. This registry pattern is the same dependency-inversion idea used throughout the course: the generic infrastructure (the worker loop) depends on an abstraction (a callable), and the specific behavior (sending an email) is plugged in from outside.
Using available_at for backoff is a small design choice worth calling out because it means we never need a separate scheduler or timer process — the exact same SELECT ... WHERE status = 'pending' AND available_at <= NOW() query that claims a fresh job also naturally skips a job that's mid-backoff, and picks it back up automatically once its delay has elapsed.
• jobs(id, queue, payload, attempts, status, available_at, reserved_at) — the live queue. • failed_jobs(id, queue, payload, exception, failed_at) — permanent record of exhausted jobs. • JobQueue — push/claim/complete/fail/release; the only thing that touches the jobs table directly. • Worker — the claim-execute-ack loop; delegates actual work via a type-to-handler registry. • available_at doubles as the backoff mechanism — no separate scheduler needed.
Core Implementation: Claiming a Job Safely
The single trickiest part of a database-backed queue is making sure two worker processes never claim and execute the same pending job simultaneously — without that guarantee, you'd process a job twice, which is disastrous for something like charging a payment and merely wasteful for something like resizing an image. The standard solution is to combine a transaction with row-level locking: SELECT ... FOR UPDATE SKIP LOCKED selects the oldest eligible pending job while telling the database to skip any row another transaction already has locked, rather than blocking and waiting for it. This means N workers running the same query concurrently each get a different row (or no row, once the queue is empty) without any of them ever waiting on another.
Once a row is selected under that lock, the same transaction immediately updates its status to 'reserved' and sets reserved_at to now, then commits. Only after that commit does the worker decode the payload and actually run the handler — the claim itself is a short, fast transaction, and the (potentially slow, potentially failing) job execution happens entirely outside of it, so a slow job never holds a database lock open.
reserved_at earns its keep when a worker process crashes mid-job — say the machine loses power right after claiming a row but before marking it done. Without a check for this, that job would sit reserved forever, invisible to new claims, quietly lost. A periodic sweep (either in the same worker loop or a separate small script) looks for jobs WHERE status = 'reserved' AND reserved_at < NOW() - INTERVAL 5 MINUTE and resets them back to pending, so a crashed worker's in-flight jobs eventually get picked up again by someone else — at the cost of possibly running that job twice if the first worker actually did finish and just failed to report it, which is why job handlers should generally be written to be idempotent, tying back to the php-idempotency-keys lesson.
• SELECT ... FOR UPDATE SKIP LOCKED lets N workers claim different jobs concurrently with no blocking. • Claim is a short transaction: select + mark reserved + commit; the actual job execution happens outside it. • reserved_at lets a sweep detect and requeue jobs abandoned by a crashed worker. • Because a requeued job might have partially run already, handlers should be idempotent (see php-idempotency-keys).
Retry Logic with Exponential Backoff
Not every failure deserves the same response. A job that fails because a third-party API returned a transient 503 is worth retrying — the same request will probably succeed a minute later. A job that fails because the payload is malformed JSON will never succeed no matter how many times you retry it, and hammering it repeatedly just wastes worker time. Our system doesn't try to distinguish these automatically (that would require the handler to signal "this is retryable" vs. "this is permanent", an enhancement left as a natural extension) — instead it retries every failure up to a fixed attempt limit, which is simple and handles the common transient case well, while the failed-jobs table catches the permanent-failure case after that limit is reached.
Exponential backoff means each successive retry waits longer than the last, rather than retrying immediately in a tight loop that would hammer a struggling downstream service right when it's least able to handle it. A common formula is delay = base * 2^(attempt - 1), optionally with a small random jitter added so that if many jobs fail at once (say, because a downstream API had a brief outage), their retries don't all land in the same instant and cause a thundering-herd retry spike. With a 10-second base, attempts back off as 10s, 20s, 40s, 80s, and so on.
Implementing this is just a matter of computing the delay when a handler throws, and setting available_at = NOW() + delay along with incrementing attempts and flipping status back to pending — exactly the same eligibility query that claims fresh jobs will naturally leave this one alone until its backoff window has passed, with zero extra machinery.
• Retry every failure up to a max attempt count; a fixed limit is simple and handles common transient failures well. • Exponential backoff: delay = base × 2^(attempt − 1), often with jitter to avoid synchronized retry storms. • On failure: increment attempts, compute delay, set available_at into the future, set status back to pending. • Once attempts exceeds the max, move the job to failed_jobs instead of retrying again.
The Worker Loop and Failed-Job Handling
The worker itself is a long-running PHP CLI process — the kind of long-lived script discussed in php-long-running-applications — that loops forever: claim a job (or sleep briefly and try again if none is available), look up the handler for the job's type, run it inside a try/catch, and route the outcome. On success, delete the job (or mark it done, if you want a completed-jobs audit trail). On failure, decide between scheduling a backoff-delayed retry and, if attempts are exhausted, writing a row into failed_jobs with the exception message and stack trace before removing it from the live jobs table.
The failed_jobs table exists because silently dropping a job that never succeeded is almost always the wrong choice — someone needs to know that a welcome email never sent, or that a payment webhook never got processed, so they can investigate and, if the underlying cause gets fixed, manually re-enqueue the job from its saved payload. A small CLI command like php artisan-style queue:retry-failed 42 that reads a failed_jobs row and re-inserts it into jobs closes that loop cleanly.
Because a real worker process needs supervision (something to restart it if it crashes, and a graceful way to stop it mid-job for deploys), production setups typically run workers under a process manager and have the loop check a shutdown signal (pcntl_signal for SIGTERM) between jobs so a deploy can ask a worker to finish its current job and then exit cleanly rather than being killed mid-execution.
• Loop: claim → look up handler by type → execute in try/catch → success deletes job, failure schedules retry or fails permanently. • failed_jobs preserves the payload and exception so a human can diagnose and manually replay the job later. • Never silently drop a job — a failed job that vanishes with no record is a debugging nightmare weeks later. • Handle SIGTERM between jobs so a deploy can stop a worker gracefully instead of killing it mid-job.
Monitoring Queue Depth and Throughput
A queue that's silently backing up is one of the most common causes of "why did this email arrive three hours late" incidents, and it's invisible unless you're actually measuring it. Queue depth — a simple SELECT COUNT(*) FROM jobs WHERE status = 'pending' — tells you how much work is waiting, and a growing trend in that number over time (rather than its absolute value at any one instant) is the real signal that workers can't keep up with producers, whether because there aren't enough worker processes or because individual jobs have gotten slower.
Alongside depth, counting jobs processed and jobs failed per unit time gives you a throughput and error-rate picture, following the metrics vocabulary from php-metrics-catalog-for-php-apps: a counter incremented on every successful completion, a counter incremented on every move to failed_jobs, and ideally a histogram of job execution duration so you can see whether a particular job type has started taking unusually long. Exporting these as Prometheus-style metrics (or even just logging them periodically) turns "the queue feels slow today" into a graph you can actually point at.
A simple but effective alert to set up once monitoring exists is on queue depth exceeding some threshold for a sustained period — say, more than 10,000 pending jobs for more than five minutes — which catches the "workers all crashed and nobody noticed" failure mode long before a human happens to check manually.
• Queue depth: COUNT(*) of pending jobs — watch the trend, not just the instantaneous value. • Counters: jobs processed, jobs failed — exported the way php-metrics-catalog-for-php-apps recommends. • A duration histogram per job type reveals which job has started running slower than usual. • Alert on sustained high queue depth — it's the clearest signal that workers have stopped keeping up or crashed.
Code Examples
claim() uses FOR UPDATE SKIP LOCKED inside a short transaction so concurrent workers never grab the same row and never block waiting on each other. fail() is where the backoff math lives: it computes an exponentially growing delay with jitter and pushes available_at into the future rather than retrying immediately, and once attempts are exhausted it archives the job to failed_jobs instead of retrying forever.
The worker never knows what a 'send_welcome_email' job actually does — it just looks up a callable by type name and invokes it, catching any Throwable so one bad job can never crash the whole worker process. Listening for SIGTERM and checking a flag between jobs lets a deploy stop the worker after its current job finishes instead of killing it mid-execution.
This is deliberately the simplest possible version of monitoring — two counting queries — but it's enough to wire into a periodic cron check (see php-cron-scheduled-jobs) or a metrics exporter, and it already answers the two questions that matter most: is work piling up, and is it failing at an unusual rate.
Claiming a job with a plain SELECT ... WHERE status = 'pending' LIMIT 1 followed by a separate UPDATE to mark it reserved, with no locking in between.
Wrap the select and the reservation update in a transaction using SELECT ... FOR UPDATE SKIP LOCKED.
Without locking, two workers running the select at nearly the same moment can both see the same pending row before either has updated its status, and both will proceed to execute the same job. FOR UPDATE SKIP LOCKED makes the claim atomic and lock-aware, so concurrent workers never double-claim a row.
Retrying every failed job immediately, in a tight loop, with no backoff delay.
Apply exponential backoff (with jitter) so each retry waits longer than the last before the job becomes eligible again.
Retrying instantly against a struggling downstream service (say, a third-party API that's rate-limiting you or briefly down) just adds more load exactly when it's least able to handle it, often making the outage worse and burning through the job's attempt count in seconds. Backoff gives the failing dependency room to recover between attempts.
Letting a job that exhausts all its retries simply get deleted with no record of what it was or why it failed.
Move exhausted jobs into a failed_jobs table that preserves the payload and the exception message.
A silently vanished job is nearly impossible to debug after the fact — nobody can tell whether a welcome email never sent, or investigate why, if there's no trace of it. The failed_jobs table turns an invisible failure into an inspectable, and often replayable, record.
What does SELECT ... FOR UPDATE SKIP LOCKED accomplish in the claim() method? A. It deletes the job row immediately after selecting it B. It lets multiple workers each claim a different pending job concurrently without blocking on rows another worker already has locked C. It automatically retries the job on failure D. It sorts jobs by priority instead of by id
B. It lets multiple workers each claim a different pending job concurrently without blocking on rows another worker already has locked — FOR UPDATE locks the selected row within the transaction, and SKIP LOCKED tells the database to skip rows already locked by another transaction rather than waiting — so concurrent workers each get a distinct job with no double-claiming and no blocking.
Why does fail() push available_at into the future instead of retrying the job immediately? A. Because MySQL requires a delay before any UPDATE statement B. To implement exponential backoff, giving a struggling dependency time to recover before the next attempt C. Because available_at is only used for the initial job creation, not retries D. To make the job permanently ineligible for claiming
B. To implement exponential backoff, giving a struggling dependency time to recover before the next attempt — Setting available_at into the future is exactly how backoff is implemented without a separate scheduler — the same eligibility query used to claim fresh jobs naturally skips this job until its delay has elapsed.
What is the purpose of the failed_jobs table? A. To temporarily hold jobs while they wait for their backoff delay to elapse B. To permanently record jobs that exhausted their retry attempts, preserving the payload and exception for later inspection or replay C. To store jobs that succeeded, for auditing D. It duplicates the jobs table for backup purposes only
B. To permanently record jobs that exhausted their retry attempts, preserving the payload and exception for later inspection or replay — Once a job's attempts are exhausted, it's moved out of the live queue and into failed_jobs so the failure isn't silently lost — a human can inspect the exception and payload and decide whether to fix the underlying issue and manually re-enqueue it.
Why should job handlers generally be written to be idempotent? A. Idempotency is required by PHP's Throwable interface B. Because a worker crash after claiming but before completing a job can cause that job to be requeued and executed again C. Because JSON payloads cannot be decoded more than once D. Idempotency only matters for jobs pushed from a URL shortener
B. Because a worker crash after claiming but before completing a job can cause that job to be requeued and executed again — A reserved_at sweep requeues jobs abandoned by a crashed worker, but that worker might have actually finished the work before crashing on the acknowledgment step — so the job can run twice. Idempotent handlers make that safe (see php-idempotency-keys).
<?php
declare(strict_types=1);
final class JobQueue
{
public function __construct(private readonly PDO $db) {}
public function push(string $queue, string $type, array $payload): int
{
$stmt = $this->db->prepare(
'INSERT INTO jobs (queue, type, payload, attempts, status, available_at)
VALUES (:queue, :type, :payload, 0, \'pending\', NOW())'
);
$stmt->execute([
'queue' => $queue,
'type' => $type,
'payload' => json_encode($payload, JSON_THROW_ON_ERROR),
]);
return (int) $this->db->lastInsertId();
}
/** Atomically claim the oldest eligible job, or null if none is ready. */
public function claim(string $queue): ?array
{
$this->db->beginTransaction();
$stmt = $this->db->prepare(
'SELECT id, type, payload, attempts FROM jobs
WHERE queue = :queue AND status = \'pending\' AND available_at <= NOW()
ORDER BY id ASC
LIMIT 1
FOR UPDATE SKIP LOCKED'
);
$stmt->execute(['queue' => $queue]);
$job = $stmt->fetch(PDO::FETCH_ASSOC);
if ($job === false) {
…<?php
declare(strict_types=1);
final class Worker
{
private bool $shouldStop = false;
/** @param array<string, callable> $handlers job type => handler callable */
public function __construct(
private readonly JobQueue $queue,
private readonly string $queueName,
private readonly array $handlers,
) {
pcntl_signal(SIGTERM, function () { $this->shouldStop = true; });
}
public function run(): void
{
while (!$this->shouldStop) {
pcntl_signal_dispatch();
$job = $this->queue->claim($this->queueName);
if ($job === null) {
usleep(500_000); // Nothing to do — avoid a tight busy loop.
continue;
}
$this->process($job);
}
echo "Worker shutting down gracefully after SIGTERM\n";
}
private function process(array $job): void
{
$handler = $this->handlers[$job['type']] ?? null;
if ($handler === null) {
$this->queue->fail((int) $job['id'], $this->queueName, $job['payload'], (int) $job['attempts'], "No handler registered for type '{$job['type']}'");
return;
}
…<?php
declare(strict_types=1);
function reportQueueHealth(PDO $db, string $queue): array
{
$depth = (int) $db->prepare(
"SELECT COUNT(*) FROM jobs WHERE queue = :q AND status = 'pending'"
)->execute(['q' => $queue]);
$depthStmt = $db->prepare("SELECT COUNT(*) FROM jobs WHERE queue = :q AND status = 'pending'");
$depthStmt->execute(['q' => $queue]);
$pendingCount = (int) $depthStmt->fetchColumn();
$failedStmt = $db->prepare(
"SELECT COUNT(*) FROM failed_jobs WHERE queue = :q AND failed_at > NOW() - INTERVAL 1 HOUR"
);
$failedStmt->execute(['q' => $queue]);
$failedLastHour = (int) $failedStmt->fetchColumn();
return [
'queue' => $queue,
'pending_depth' => $pendingCount,
'failed_last_hour' => $failedLastHour,
];
}