How We Built a Reliable Job Queue Entirely in Postgres

Smuves fetches content from HubSpot portals that can have tens of thousands of pages, blog posts, landing pages, and HubDB rows. That work cannot happen in a single API call. It needs to be broken into jobs, distributed across workers, retried on failure, and tracked so users can see progress in real time.

The standard advice is to use Redis, or SQS, or a dedicated queue service. We did not. We built the entire job queue in Postgres using row-level locking, stored procedures, and a few patterns that turned out to be surprisingly robust.

This post explains the design, the edge cases that almost broke it, and why Postgres was the right choice for our stage.

Why not Redis or a managed queue

We are a 4 person team. Every piece of infrastructure we add is something we have to monitor, debug, and keep running. Supabase already gives us Postgres. Adding Redis or SQS would mean another service to provision, another set of credentials to manage, another thing that can go down at 2am.

The workload also did not require the throughput that dedicated queue systems are designed for. We are not processing millions of jobs per second. A busy portal might generate 8 to 16 fetch jobs at a time, one per content type. Export jobs are similarly bounded. The total concurrency at any given moment is low.

Postgres can handle this volume without breaking a sweat. The question was whether it could handle it reliably, with proper locking, retries, and failure recovery. It can, but you have to be deliberate about how you design the queries.

Claiming jobs with FOR UPDATE SKIP LOCKED

The core of the system is a stored procedure called claim_next_job. When a worker is ready for work, it calls this function. The function finds the next available job and atomically locks it so no other worker can claim it at the same time.

The SQL uses FOR UPDATE SKIP LOCKED, which is the key to making this work. FOR UPDATE locks the selected row so no other transaction can modify it. SKIP LOCKED tells Postgres to silently skip any row that is already locked by another transaction instead of waiting for it.

This means two workers calling claim_next_job at the exact same moment will never get the same job. The first one locks the row. The second one skips it and moves to the next available job. No deadlocks, no retries, no race conditions.

The function filters for jobs that are in a pending state and orders them by creation time so older jobs get picked up first. When it finds one, it updates the status to running, sets a locked_at timestamp, records which worker claimed it, and returns the job details to the caller.

Recovering from stale locks

Workers crash. Edge functions get killed mid-execution. Network connections drop. When any of that happens, a job can end up stuck in a running state with a lock that will never be released because the worker that claimed it is gone.

The claim function handles this by also looking for jobs where the locked_at timestamp is older than a threshold. For fetch jobs, that threshold is 5 minutes. For export jobs, it is 2 minutes. If a job has been locked for longer than that, the claim function treats it as abandoned and re-claims it.

This is essentially a lease-based locking model. A worker does not own a job forever. It owns it for a window of time. If it does not complete the job within that window, the system assumes it is dead and makes the job available again.

Each time a job is reclaimed, its retry count is incremented. The claim function also enforces a maximum retry count. If a job has been retried 3 times and is still not completing, something is fundamentally wrong with that job, and the system stops trying. The job gets marked as failed, and the user is notified.

Self-healing stuck runs

Jobs in Smuves are grouped into runs. When a user clicks fetch, the system creates one run and then creates individual jobs for each content type under that run. The run tracks overall progress. When all jobs in a run are complete, the run is marked complete and the user gets a notification.

But what happens when some jobs in a run finish successfully, some fail, and the run itself gets stuck in a running state because the last job to finish crashed before it could update the parent run?

We wrote a stored procedure called reconcile_stuck_runs that runs at the start of every worker invocation. It looks for runs that are still in a pending or running state but where every child job has already reached a terminal state (completed, failed, or stopped). When it finds one, it computes what the run status should be based on the child jobs and updates it.

The logic uses COUNT FILTER to tally how many jobs completed versus failed. If all succeeded, the run is marked completed. If any failed, the run is marked as partially failed. If all failed, it is marked as failed.

This function runs as SECURITY DEFINER, which means it executes with elevated permissions regardless of who called it. That matters because row-level security policies on the jobs table would otherwise prevent a worker from seeing jobs that belong to a different user. The reconciliation function needs to see all runs across all users to find and fix stuck ones.

This pattern turned out to be one of the most valuable things in the entire system. Before we added it, stuck runs would sit in the database silently, and users would see a perpetual loading state with no way to resolve it. Now the system heals itself every time a worker wakes up.

Orchestrating job creation with deduplication

When a user triggers a fetch, the orchestrator function creates a run and inserts one job per content type. But what if the user clicks fetch twice in quick succession? Or what if a previous fetch is still running?

The orchestrator checks for in-flight jobs before creating new ones. It queries for any jobs in that portal that are still pending or running for the same content types. If all the requested content types already have active jobs, the orchestrator returns a 409 status with an ALL_DUPLICATE_JOBS code. If some are duplicates and some are new, it creates only the new ones.

This prevents the system from piling up redundant work. It also prevents a subtle bug where two workers could be fetching the same content type for the same portal simultaneously, which would result in duplicate records in the database.

The orchestrator also does something useful before creating jobs. It hits the HubSpot API with a limit=1 request for each content type to get the total count. That total is stored on the job record so the frontend can show a progress bar. It is a cheap API call that makes the user experience significantly better.

Why this worked for us

This system has been running in production for months without any queue-related incidents. Jobs get claimed, executed, retried, and resolved without human intervention.

The reason it works is that our concurrency requirements are modest. We do not have thousands of workers competing for jobs. We have a handful of edge function invocations running at any given time, and the job volume is bounded by the number of content types per portal.

If we were processing millions of jobs per day, we would need a real queue. But for a product at our stage with our workload profile, Postgres is more than enough. The SQL is straightforward, the behavior is predictable, and the whole thing lives in the same database as our application data, which means no sync issues, no separate monitoring, and no additional infrastructure to maintain.

The biggest risk with this approach is that it only works if you handle the edge cases properly. FOR UPDATE SKIP LOCKED handles concurrency. Stale lock recovery handles worker crashes. Reconcile handles orphaned parent records. Remove any one of those pieces and the system falls apart. Keep all three, and it is remarkably solid.