Why do transactional emails get lost or duplicated in a monolith?

Most monoliths start by sending email directly in the request handler. A user signs up, and the code inserts the row and then calls the email provider's API. This works until the provider gets slow or returns errors. Then your signup endpoint inherits the provider's latency and failure rate. Worse, if the API call fails after the insert, you have to decide whether to roll back a valid signup because a welcome email bounced.

The usual next step is to send after commit. The user row is safe, and the email goes out once the transaction succeeds. But a deploy, OOM kill or crash between COMMIT and the API call loses the email silently. Nothing records that it was ever owed.

Publishing to an external broker instead swaps one problem for another. The database commit and the broker publish are two separate writes with no shared transaction. Either one can succeed while the other fails, so the two systems drift apart.

Approach Request latency tied to provider Lost on crash Duplicates or divergence
Send inline Yes Possible if rollback is skipped Retried requests resend
Send after commit Often Yes, between commit and send Rare
Publish to broker No Publish can fail after commit Broker and DB disagree
Outbox job row No No, the job commits with the data Handled by worker idempotency

The fix is the transactional outbox. You insert an email job row in the same transaction as the domain change. Then both commit or neither does, and a worker sends the email later. This fits monoliths already running Postgres with modest email volume, where adding another piece of infrastructure isn't worth it.

How does a Postgres job queue for email work?

The flow has three steps, and only the first runs inside the user's request.

  1. Enqueue. The request handler inserts the domain row (an order, a password reset token) and an email_jobs row with status pending in the same transaction. If the transaction rolls back, neither row exists, so no email can describe something that never happened.
  2. Claim. A worker runs SELECT ... FOR UPDATE SKIP LOCKED against pending rows, ordered by creation time and limited to a small batch. It marks those rows as in progress and commits. The claim transaction stays short, so the worker holds no row locks while waiting on the network.
  3. Send and record. The worker calls the email provider and writes the outcome back to the job row: sent, or pending again with a retry time, or failed.

SKIP LOCKED is what lets several workers share one table. With plain FOR UPDATE, every worker targets the same oldest rows. The first worker locks them, and the others block until it commits. Then they recheck those rows, often find them already claimed, and come back with nothing. Adding workers adds waiting, not throughput. With SKIP LOCKED, a worker passes over rows another transaction has locked and takes the next unlocked ones. Workers never wait on each other and never claim the same row at the same time.

The resulting guarantee: enqueueing is atomic with the business change, and delivery is at-least-once. A worker can crash after the provider accepts the message but before it records sent, so a retry may send the email again. You get this from the database you already run, with no separate broker to deploy, monitor, or keep consistent with Postgres.

(1) ENQUEUE: inside the user's request, one transaction
+-----------------------------------------------------+
| Request handler                                     |
|   BEGIN                                             |
|     INSERT INTO orders ...       (domain row)       |
|     INSERT INTO email_jobs ...   (status pending)   |
|   COMMIT  -> both rows exist, or neither            |
+-----------------------------------------------------+
                           |
                           v
+-----------------------------------------------------+
| Postgres: email_jobs, oldest first                  |
|   job 1   locked by A's claim tx    B skips it      |
|   job 2   locked by A's claim tx    B skips it      |
|   job 3   locked by B's claim tx    A skips it      |
|   job 4   pending, unlocked         next claim      |
+-----------------------------------------------------+
      ^             ^             ^             ^
  (2) claim   (3) record      (2) claim    (3) record
      |             |             |             |
+-------------------------+ +-------------------------+
| Worker A                | | Worker B                |
| short claim transaction:| | short claim transaction:|
|  SELECT ... FOR UPDATE  | |  SELECT ... FOR UPDATE  |
|  SKIP LOCKED LIMIT n    | |  SKIP LOCKED LIMIT n    |
|  mark in progress       | |  mark in progress       |
|  COMMIT                 | |  COMMIT                 |
+-------------------------+ +-------------------------+
             |                             |
             v  send (no locks held)       v
+-----------------------------------------------------+
| Email provider API                                  |
+-----------------------------------------------------+

The request commits the domain row and a pending email_jobs row together, workers claim disjoint batches with FOR UPDATE SKIP LOCKED in short transactions, call the provider without holding locks, and write back sent, pending with a retry time, or failed.

Designing the job table and enqueueing in the business transaction

Start with one table, email_jobs. Each column exists so a worker or an operator can answer a specific question without guessing:

  • id: a bigint identity primary key.
  • kind: the template name, such as password_reset.
  • payload: jsonb holding the template inputs. Store IDs and values, not rendered HTML, so you can fix a template and still re-render queued jobs.
  • status: pending, running, sent, or failed.
  • attempts and max_attempts: retry bookkeeping.
  • run_at: the earliest time the job may run. It handles both delayed sends and backoff.
  • locked_at and locked_by: identify a worker that crashed mid-send.
  • last_error: the most recent failure, kept for debugging.
  • created_at: when the job was enqueued.
  • idempotency_key: text with a UNIQUE constraint, built from the business event, for example order_confirmation:<order_id>.

Completed rows pile up quickly, and the claim query only cares about pending work. Add a partial index on (run_at) with WHERE status = 'pending'. The index then stays roughly the size of your backlog rather than your history. The planner can use it only when the claim query's predicate matches the index's WHERE clause.

Enqueue in the same transaction that changes domain state. Insert the order, then run INSERT INTO email_jobs … ON CONFLICT (idempotency_key) DO NOTHING. If the transaction rolls back, the job never existed. If a request handler retries and runs the same transaction twice, the unique constraint absorbs the duplicate.

To avoid polling latency, issue NOTIFY email_jobs in that transaction as well. Postgres delivers notifications only after commit, so a listening worker never wakes up to a row it can't see yet. Notifications are lost if no worker is listening at that moment, so treat them as a hint and keep polling as the fallback.

CREATE TABLE email_jobs (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind            text        NOT NULL,              -- template name
  payload         jsonb       NOT NULL,              -- template inputs (IDs/values), not rendered HTML
  status          text        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'running', 'sent', 'failed')),
  attempts        int         NOT NULL DEFAULT 0,
  max_attempts    int         NOT NULL DEFAULT 5,
  run_at          timestamptz NOT NULL DEFAULT now(), -- delayed sends and backoff
  locked_at       timestamptz,                       -- used to spot crashed workers
  locked_by       text,
  last_error      text,
  created_at      timestamptz NOT NULL DEFAULT now(),
  idempotency_key text        NOT NULL UNIQUE        -- e.g. 'order_confirmation:<order_id>'
);

-- Index covers only pending rows, so it tracks the backlog, not the history.
-- The claim query must include WHERE status = 'pending' for the planner to use it.
CREATE INDEX email_jobs_pending_run_at
  ON email_jobs (run_at)
  WHERE status = 'pending';

-- Enqueue in the same transaction as the domain change.
BEGIN;

INSERT INTO orders (id, customer_id, total_cents)
VALUES (4821, 117, 5999);

-- A retried request produces the same key; the duplicate is silently skipped.
INSERT INTO email_jobs (kind, payload, idempotency_key)
VALUES ('order_confirmation',
        jsonb_build_object('order_id', 4821),
        'order_confirmation:4821')
ON CONFLICT (idempotency_key) DO NOTHING;

-- Queued now, delivered to listeners only if the transaction commits.
NOTIFY email_jobs;

COMMIT;

Defines the email_jobs table with a unique idempotency key and a partial index on pending jobs, then enqueues an order confirmation and a NOTIFY in the same transaction as the order insert.

How should workers claim and send jobs with SKIP LOCKED?

Each worker claims a batch with one query: rows where status = 'pending' and run_at <= now(), ORDER BY run_at, LIMIT the batch size, FOR UPDATE SKIP LOCKED. SKIP LOCKED lets concurrent workers pass over rows another transaction holds instead of waiting on them, so several workers get disjoint batches without any coordinator. What a worker does after claiming determines how it fails.

Pattern A: hold the lock Pattern B: mark and release
Flow Claim, call provider, update status, commit Set processing, locked_at, locked_by, commit, send, finalize
Transaction length Spans network I/O Short, no I/O inside
Worker crash Lock released, row is pending again Row stuck until lease expires
Vacuum impact Long transactions hold back cleanup Minimal
Extra logic None Reaper for expired leases

Pattern B depends on the lease timeout. If the lease is too short, a slow but healthy send gets reclaimed and the email goes out twice. If it is too long, jobs from a crashed worker sit idle. Set the lease well above the provider's request timeout.

Polling every second or two is the baseline for waking workers. LISTEN/NOTIFY reduces latency, and polling stays as the fallback. LISTEN is session state, so it breaks behind a pooler in transaction mode: each transaction can land on a different backend, and notifications stop arriving without any error. Give listeners a dedicated direct connection.

Ordering is not strict. Locked rows get skipped and retries move jobs later, so a newer email can go out before an older one. If order matters for each recipient, claim by recipient: select the oldest pending job only for recipients that have no row in processing.

-- Pattern B, step 1: claim a batch and mark it in one short transaction.
-- SKIP LOCKED makes concurrent workers pass over rows another claim holds.
UPDATE email_jobs
SET status    = 'processing',
    locked_at = now(),
    locked_by = $1            -- worker id, e.g. 'worker-a1:4312'
WHERE id IN (
  SELECT id
  FROM email_jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at
  LIMIT 50                    -- batch size
  FOR UPDATE SKIP LOCKED
)
RETURNING id, recipient, payload;
-- Commit here, then call the provider outside any transaction.

-- Step 2: finalize. The locked_by check stops a worker whose lease was
-- reclaimed from overwriting the row now owned by another worker.
UPDATE email_jobs
SET status = 'sent', locked_at = NULL, locked_by = NULL
WHERE id = $2
  AND status = 'processing'
  AND locked_by = $1;

-- Reaper: return rows from crashed workers to the queue.
-- Keep the lease well above the provider request timeout so a slow
-- but healthy send is not reclaimed and sent twice.
UPDATE email_jobs
SET status = 'pending', locked_at = NULL, locked_by = NULL
WHERE status = 'processing'
  AND locked_at < now() - interval '5 minutes';

Pattern B in SQL: claim a batch with FOR UPDATE SKIP LOCKED and mark it processing, finalize only if this worker still owns the row, and reclaim rows whose lease has expired.

How do you handle retries, failures and duplicate emails?

Delivery is at-least-once. If a worker hands the message to the provider and then crashes before committing status = 'sent', the row lock is released. Another worker then claims the job and sends it again. You cannot get exactly-once delivery to an external SMTP or HTTP provider, because the provider's acceptance and your commit are two separate systems that cannot share a transaction. You can only make duplicates rare and detectable.

Idempotency

Derive the idempotency key from the business event, such as order_confirmation:{order_id}, instead of generating it randomly at enqueue time. Put a unique constraint on it so a retried request cannot enqueue the job twice. If the provider accepts an idempotency key, pass this value so the provider can drop the resend. Store the provider's message id on the row. That gives you evidence when someone reports a double email.

Retries and failures

Classify each error before deciding what to do:

  • Transient errors (timeouts, 5xx responses, rate limits): increment attempts and push run_at forward with exponential backoff plus random jitter. Jitter stops a provider outage from producing synchronized retry waves.
  • Permanent errors (invalid address, rejected template): retrying cannot succeed, so mark the job dead immediately.
  • Exhausted retries: also mark the job dead once it reaches max_attempts.

Operators need two queries. The first selects dead jobs grouped by error. The second requeues selected ids by setting status = 'pending', attempts = 0 and run_at = now().

Rate limiting

Throttle sending by capping worker concurrency and batch size, and keep the cap below the provider's quota.

-- Success: record the provider's message id as evidence of delivery.
UPDATE email_jobs
SET status = 'sent', provider_message_id = $2, sent_at = now()
WHERE id = $1;

-- Transient failure (timeout, 5xx, rate limit): back off exponentially
-- (30s, 60s, 120s ... capped at 1h), scaled by jitter in [0.5, 1.5).
-- Moves to 'dead' once max_attempts is reached.
-- Column references on the right-hand side read the pre-update values.
UPDATE email_jobs
SET attempts   = attempts + 1,
    last_error = $2,
    status     = CASE WHEN attempts + 1 >= max_attempts
                      THEN 'dead' ELSE 'pending' END,
    run_at     = now() + least(interval '1 hour',
                               interval '30 seconds' * power(2, attempts))
                         * (0.5 + random())
WHERE id = $1;

-- Permanent failure (invalid address, rejected template): retrying cannot succeed.
UPDATE email_jobs
SET attempts = attempts + 1, last_error = $2, status = 'dead'
WHERE id = $1;

-- Operator query 1: inspect dead jobs grouped by error.
SELECT last_error, count(*) AS jobs, min(run_at) AS oldest, max(run_at) AS newest
FROM email_jobs
WHERE status = 'dead'
GROUP BY last_error
ORDER BY jobs DESC;

-- Operator query 2: requeue selected dead jobs after fixing the cause.
UPDATE email_jobs
SET status = 'pending', attempts = 0, run_at = now(), last_error = NULL
WHERE id = ANY($1::bigint[]) AND status = 'dead';

SQL a worker runs to record success, retry transient failures with capped exponential backoff and jitter, and mark permanent or exhausted failures dead, plus two operator queries that inspect dead jobs and requeue them.

What does running the queue in Postgres cost in operations and engineering time?

Housekeeping

Every status change writes a new row version, so a busy job table produces dead tuples all the time. When autovacuum falls behind, the table and its indexes bloat, and the claim query scans dead entries before it finds live ones. Delete or archive completed rows in batches of a few thousand per transaction so each delete stays short. Lower the table's per-table autovacuum scale factor so vacuum runs after a fixed number of changes rather than a percentage of a growing table. At high volume, partition by time and drop old partitions. Dropping a partition removes the rows without creating any dead tuples.

A long-running transaction anywhere in the database holds back the cleanup horizon, and vacuum cannot reclaim the rows it might still see. Alert on transaction age, not only on queue metrics.

Watch: queue depth, age of the oldest pending job, attempt counts, and dead-letter count.

Illustrative estimate (assumed numbers, not a benchmark)

  • Polling cost. Assume 4 workers polling an idle queue every 2 s. Added latency is about interval ÷ 2, which gives 1 s. Query load is workers ÷ interval, which gives 2 queries/s. A 0.5 s interval lowers latency to 0.25 s but raises load to 8 queries/s.
  • Lock duration. If rows stay locked while they send, lock time ≈ batch size × send time. With 50 jobs at 200 ms each, rows stay locked for 10 s. Claim the batch and commit first, then send, to keep locks short.
You save You own
No broker to deploy or monitor Retry and backoff logic
One transactional boundary Dead-letter tooling
Familiar backups and access control Queue observability
Careful migrations on a hot table

When should you avoid a Postgres email queue, and what are the alternatives?

The job table puts its load on the same primary that serves your business writes. Each email produces an insert, at least one status update and an eventual delete. Every one of those writes WAL and leaves dead tuples behind. At high send volumes this churn competes with application traffic, causes table bloat, and needs aggressive autovacuum tuning on a hot table. Avoid the table in these cases:

  • Churn would load the primary. If WAL volume, replication lag or vacuum pressure from the queue becomes noticeable, the queue is costing your main workload.
  • You need ordering, priority fairness or per-tenant isolation. SKIP LOCKED gives you concurrency, not fairness. Strict per-recipient ordering, weighted priorities, or stopping one tenant's bulk send from starving password resets all need scheduling logic you would have to build yourself.
  • Email must outlive a database outage. If Postgres is down, nothing is enqueued and nothing is sent.
Option Strength Trade-off
Established Postgres job library Retries, backoff, scheduling and dashboards already built and tested Its schema and conventions become a dependency
Transactional outbox plus message broker Keeps atomic enqueue and moves fan-out and throughput off the primary A relay process and a broker to run
Send inline or after commit No queue at all Mail is lost on a crash or provider error

Sending after commit is acceptable for low-stakes mail such as digests or optional notices.

Decision checklist

Build the in-house table only if all of these are true:

  • Send volume is a small fraction of your write traffic.
  • Each email is independent, so ordering does not matter.
  • No mature job library exists for your stack, or none fits.
  • Email can stop while the database is down.
  • Someone owns vacuum health and dead-letter review for the table.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres job queue email, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.