Beyond Redis: Building a Postgres-Native Job Queue with Supabase pg_net and Edge Functions

The Redis Dependency Is a Serverless Anti-Pattern

Most serverless stacks bolt a Redis, ElastiCache, or BullMQ layer onto Postgres purely for background work—email delivery, webhook fan-out, media pipelines, AI inference orchestration. That adds a second source of truth, a network to manage, connection pools to tune, and a failure domain disconnected from your actual data. If your database is already Supabase Postgres, the queue can live there too: Postgres is a fully sufficient job broker when combined with pg_net for zero-poller dispatch, FOR UPDATE SKIP LOCKED for contention-free claiming, and Supabase Edge Functions as stateless workers. This is the exact pattern Picodevs deploys for AI-heavy client products—durable jobs, sub-second dispatch, one bill, zero brokers.

• Infrastructure count: a managed Redis queue requires a paid instance plus networking; pg_net ships enabled on every Supabase project. Cost delta: $0.
• Transactional integrity: enqueueing happens inside the same transaction as your domain writes—no dual-write bugs, no outbox middleware, no reconciliation jobs.
• Observability: job state is queryable with plain SQL—no separate Redis Insights dashboard or CLI spelunking.
• Latency: pg_net pushes via HTTP POST within milliseconds of commit; pollers add 1–30 seconds of queue latency by design.

System Architecture: Postgres as the Broker, Edge as the Worker

Five components, zero external services:

• jobs table — the queue itself, with status, attempt counters, and idempotency metadata.
• dispatch_job() trigger — fires on insert and calls the Edge Function via pg_net's non-blocking net.http_post.
• Edge Function worker — a stateless Deno runtime that claims a job with SKIP LOCKED and executes the handler.
• pg_cron sweeper — requeues timed-out jobs, applies exponential backoff, and promotes exhausted jobs to dead-letter.
• service_role key check — the single auth boundary between Postgres and the worker.

The official pg_net extension reference documents the underlying HTTP API and its internal delivery retries.

Step 1: The Jobs Table and Idempotency Contract

Three schema decisions matter at scale: a UNIQUE idempotency_key gives you consumer-side deduplication under at-least-once delivery; run_after turns the queue into a delayed-job scheduler for free; a partial index on queued rows keeps claim scans cheap regardless of table history:

CREATE TABLE jobs (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind            text        NOT NULL,
  payload         b       NOT NULL DEFAULT '{}',
  status          text        NOT NULL DEFAULT 'queued'
                  CHECK (status IN ('queued','running','done','failed')),
  attempts        int         NOT NULL DEFAULT 0,
  max_attempts    int         NOT NULL DEFAULT 5,
  run_after       timestamptz NOT NULL DEFAULT now(),
  locked_at       timestamptz,
  idempotency_key text UNIQUE,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX jobs_claim_idx ON jobs (id) WHERE status = 'queued';

Step 2: Contention-Free Claiming with FOR UPDATE SKIP LOCKED

The historical objection to Postgres-as-queue was lock contention under concurrent workers. SELECT ... FOR UPDATE SKIP LOCKED eliminates it: workers never block on rows held by other workers—they skip them and claim the next available job, so throughput scales linearly with worker count. Wrap the claim in a SECURITY DEFINER function so the Edge Function needs no direct table grants:

CREATE OR REPLACE FUNCTION claim_job(p_kind text DEFAULT NULL)
RETURNS jobs LANGUAGE sql SECURITY DEFINER AS $$
  WITH next_job AS (
    SELECT id FROM jobs
    WHERE status = 'queued'
      AND run_after <= now()
      AND (p_kind IS NULL OR kind = p_kind)
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
  )
  UPDATE jobs j
  SET status = 'running', locked_at = now(), attempts = attempts + 1
  WHERE j.id IN (SELECT id FROM next_job)
  RETURNING j.*;
$$;

Step 3: pg_net Dispatch — Killing the Poller

Polling is latency you pay on every single job. A trigger using pg_net pushes an HTTP POST to the Edge Function the instant a job row commits—fire-and-forget, without holding the transaction open:

CREATE OR REPLACE FUNCTION dispatch_job() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  PERFORM net.http_post(
    url     := 'https://<project-ref>.functions.supabase.co/job-worker',
    headers := b_build_object(
                 'Content-Type', 'application/',
                 'Authorization', 'Bearer ' || current_setting('app.service_role_key', true)
               ),
    body    := b_build_object('job_id', NEW.id, 'kind', NEW.kind),
    timeout_milliseconds := 5000
  );
  RETURN NEW;
END; $$;

CREATE TRIGGER trg_dispatch AFTER INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION dispatch_job();

Never hardcode the service role key in the trigger. Load it from a custom GUC (ALTER DATABASE postgres SET app.service_role_key = '...') or fetch it inside the function body. pg_net retries delivery internally and records every request and response in net._http_response, giving you a built-in audit trail for dispatch failures.

Step 4: The Edge Function Worker (Deno)

The worker verifies the service role header, claims any runnable job, executes the handler, and records the terminal state. Handlers must be idempotent because delivery is at-least-once:

Deno.serve(async (req) => {
  const auth = req.headers.get('Authorization') ?? '';
  if (auth !== `Bearer ${Deno.env.get('SERVICE_ROLE_KEY')!}`) {
    return new Response('forbidden', { status: 403 });
  }

  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SERVICE_ROLE_KEY')!
  );

  const { data: job, error } = await supabase.rpc('claim_job').maybeSingle();
  if (error || !job) return new Response('no job', { status: 204 });

  try {
    await handlers[job.kind](job.payload);
    await supabase.from('jobs').update({ status: 'done' }).eq('id', job.id);
  } catch (err) {
    const exhausted = job.attempts >= job.max_attempts;
    await supabase.from('jobs')
      .update({ status: exhausted ? 'failed' : 'queued', locked_at: null })
      .eq('id', job.id);
  }
  return new Response('ok');
});

Step 5: Self-Healing with pg_cron

Workers crash and edge runtimes get evicted mid-execution. A one-minute sweeper closes the loop—requeueing anything stuck in running and applying exponential backoff on retries:

SELECT cron.schedule('sweep-jobs', '* * * * *', $$
  UPDATE jobs
  SET status = CASE
        WHEN attempts >= max_attempts THEN 'failed'
        ELSE 'queued'
      END,
      run_after = now() + (interval '15 seconds' * power(2, attempts)),
      locked_at = NULL
  WHERE status = 'running'
    AND locked_at < now() - interval '2 minutes'
$$);

status = 'failed' is your dead-letter queue—surface it in an admin view or fan it into your alerting channel via another pg_net call.

Delivery Semantics: Design for At-Least-Once

• Duplicate execution will happen (trigger retry plus sweeper overlap). Key every side effect on idempotency_key or guard writes with conditional updates (UPDATE ... WHERE status != 'done').
• Ordering is not guaranteed under parallel workers. Sequence via run_after chaining or claim a single kind with one worker if strict ordering matters.
• Exactly-once does not exist in any distributed queue—Postgres merely makes every failure state inspectable with a SELECT.

Throughput Envelope and Cost Delta

Field numbers from Picodevs client deployments on a mid-tier Supabase compute add-on (2 vCPU / 8 GB):

• Sustained throughput: 400–800 jobs/sec for lightweight handlers (JSON writes, HTTP fan-out, embedding dispatch) before claim-index contention appears.
• Dispatch latency: p95 under 200 ms from commit to worker start via pg_net push, versus 1.5–30 s with a 5-second poller—directly user-visible in export and notification UX.
• Removed line items: Upstash/Redis Cloud or VPC-peered ElastiCache, plus the Lambda-to-VPC networking that came with them. Observed on a mid-size SaaS: one service, one bill, roughly $40–70/month and two weeks of annual ops toil deleted.

When This Pattern Is the Wrong Tool

• Sustained >1,000 jobs/sec or multi-hour CPU work — Edge Functions cap CPU per invocation. Graduate to dedicated consumers (Fly machines, ECS) that still claim via the same SKIP LOCKED function.
• High fan-out pub/sub — thousands of subscribers per event belongs in Supabase Realtime or Redis Streams, not row-per-subscriber jobs.
• Strict global FIFO — ordered IDs approximate it within a single-kind lane, but a partitioned log (Kafka, Redpanda) is the honest answer.

How Picodevs Ships This

This queue is a standard building block in our edge-native backend engineering services: AI ingestion pipelines, usage-based billing events, webhook fan-out, and document processing all run on the same Postgres-native substrate—no Redis, no SQS, no per-environment brokers, with staging and production sharing identical infrastructure. You can see shipped examples in our portfolio of production deployments where background throughput and zero-ops infrastructure were the deciding constraints. For runtime limits and pricing envelopes, the Supabase Edge Functions documentation is the canonical reference.

Bottom Line

A job queue is a solved problem inside Postgres. FOR UPDATE SKIP LOCKED for contention-free claiming, pg_net for push-based dispatch, pg_cron for self-healing, and Edge Functions for stateless execution—four primitives, one database, zero additional infrastructure. Every job is transactional with your domain data, inspectable with SQL, and recoverable by default. That is scalable infrastructure that deletes complexity instead of adding it.