Subscribe

Postgres job queues in golang with FOR UPDATE SKIP LOCKED and advisory locks

How we share background jobs between several ECS tasks with only Postgres: a SKIP LOCKED batch claim with a retry window, and a transaction advisory lock that keeps a per-user weekly cap honest. With tests that show the naive versions failing.

Postgres job queues in golang with FOR UPDATE SKIP LOCKED and advisory locks

Our golang backend runs as a few ECS tasks and we don't have Redis. Background work still has to be shared between those tasks: reconcile some rows, rebuild a user's numbers, send an alert. Every task runs the same scheduler, so every task wakes up at the same moment and reaches for the same rows.

Postgres already handles this well. In this blog we will look at the two tools we ended up using: FOR UPDATE SKIP LOCKED to split a batch of jobs between workers, and pg_advisory_xact_lock to stop two workers from both deciding "this user can get one more alert". I'll use pgx v5.13.0 and Postgres 18.7 for the examples. Our own code does the same through GORM's Raw, the SQL is what matters.

The table

CREATE TABLE IF NOT EXISTS jobs (
    id                bigserial PRIMARY KEY,
    kind              text        NOT NULL,
    payload           jsonb       NOT NULL DEFAULT '{}',
    status            text        NOT NULL DEFAULT 'pending',
    attempts          int         NOT NULL DEFAULT 0,
    last_attempted_at timestamptz,
    created_at        timestamptz NOT NULL DEFAULT now(),
    done_at           timestamptz
);

-- Workers only ever look at pending rows, oldest first.
CREATE INDEX IF NOT EXISTS jobs_pending_idx ON jobs (created_at) WHERE status = 'pending';

The partial index keeps the claim query fast even when the table has millions of finished rows. last_attempted_at does two jobs: it is the claim marker and the retry backoff.

The naive claim, and what it did

The first query anyone writes looks like this. Pick some pending rows, stamp them, return them:

UPDATE jobs SET last_attempted_at = now(), attempts = attempts + 1
 WHERE id IN (SELECT id FROM jobs
               WHERE status = 'pending' AND last_attempted_at IS NULL
               ORDER BY created_at LIMIT 25)
RETURNING id;

I knew it was racy, but I wanted a number. So I put 2,000 jobs in table and ran 10 goroutines claiming batches of 25 until nothing was left. Result: all 2,000 jobs were claimed, and 1,975 of them were claimed more than once. Honestly I expected a few duplicates. Almost all of them was a surprise.

The reason is that every worker runs the subquery on the same snapshot, gets the same 25 oldest IDs, and then queues up to update the same rows. When a worker finally gets the row lock, postgres re-checks the outer WHERE id IN (...), which is still true. The last_attempted_at IS NULL check lives in the subquery, and that is not evaluated again.

Adding FOR UPDATE to the subquery fixed the duplicates (zero in the same test). But now the workers wait for each other. Worker two blocks on the rows worker one locked, and you've built a queue where only one consumer works at a time.

SKIP LOCKED

SKIP LOCKED tells postgres to skip rows another transaction has locked and take the next ones instead. Here is the claim we use:

// Claim takes up to limit pending jobs that no other worker holds right now
// and that were not tried in the last retryAfter. It stamps last_attempted_at
// in the same statement, so the claim survives after the row lock is gone.
func (q *Queue) Claim(ctx context.Context, limit int, retryAfter time.Duration) ([]Job, error) {
    rows, err := q.db.Query(ctx, `
        UPDATE jobs
           SET last_attempted_at = now(), attempts = attempts + 1
         WHERE id IN (
               SELECT id FROM jobs
                WHERE status = 'pending'
                  AND (last_attempted_at IS NULL
                       OR last_attempted_at < now() - make_interval(secs => $2))
                ORDER BY created_at
                LIMIT $1
                FOR UPDATE SKIP LOCKED)
        RETURNING id, kind, payload, attempts`,
        limit, retryAfter.Seconds())
    if err != nil {
        return nil, err
    }
    return pgx.CollectRows(rows, func(r pgx.CollectableRow) (Job, error) {
        var j Job
        err := r.Scan(&j.ID, &j.Kind, &j.Payload, &j.Attempts)
        return j, err
    })
}

It is one statement, so it runs in its own short transaction. The row locks last only as long as that statement. After commit, the thing that keeps other workers away is the last_attempted_at stamp together with the retryAfter window.

Finishing and giving up are plain updates:

func (q *Queue) Done(ctx context.Context, id int64) error {
    _, err := q.db.Exec(ctx, `
        UPDATE jobs SET status = 'done', done_at = now()
         WHERE id = $1 AND status = 'pending'`, id)
    return err
}

func (q *Queue) Fail(ctx context.Context, id int64, maxAttempts int) error {
    _, err := q.db.Exec(ctx, `
        UPDATE jobs SET status = 'failed'
         WHERE id = $1 AND status = 'pending' AND attempts >= $2`, id, maxAttempts)
    return err
}

A job that fails and has attempts left simply stays pending. It comes back after retryAfter, which is the backoff. If you want it to grow with each attempt, multiply the interval by attempts in the query; I wrote about the idea in general in exponential backoff in go.

Testing that every job runs once

This is the test I trust. Same setup as the naive run, 2,000 jobs and 10 workers, but with the real Claim:

var wg sync.WaitGroup
for w := 0; w < 10; w++ {
    wg.Go(func() {
        for {
            jobs, err := q.Claim(ctx, 25, time.Minute)
            if err != nil {
                t.Error(err)
                return
            }
            if len(jobs) == 0 {
                return
            }
            for _, j := range jobs {
                mu.Lock()
                seen[j.ID]++
                mu.Unlock()
                if err := q.Done(ctx, j.ID); err != nil {
                    t.Error(err)
                }
            }
        }
    })
}
wg.Wait()

Then it checks len(seen) == 2000 and that every count is exactly 1. It passes, in under a second on a postgres 18 container on my Mac. sync.WaitGroup.Go needs Go 1.25 or newer; I ran it on 1.27.3.

There's a second small test for the backoff: claim a job, don't finish it, claim again and expect nothing, then move last_attempted_at two minutes back and expect the job with attempts = 2.

The lease is only as good as retryAfter

One thing to be clear about. After the claim commits, the job is not locked any more. If a job runs longer than retryAfter, another worker will pick it up and you'll run it twice. So either pick a retryAfter well above your slowest job (one of ours waits six hours before a retry), or make the job safe to run twice. Ideally both.

The AND status = 'pending' in Done helps a little. A slow, late worker can't flip a row that someone else already finished into some other state.

Per-user caps need a different lock

The second problem looks similar but SKIP LOCKED doesn't help. We send a user at most N alerts a week. The check is count-then-insert:

SELECT count(*) FROM alert_sends WHERE user_id = $1 AND iso_week = $2;
-- if count < cap:
INSERT INTO alert_sends (user_id, iso_week, ref_key) VALUES ($1, $2, $3);

Two workers can both count 2, both see that it's under the cap of 3, both insert. A unique constraint on (user_id, ref_key) stops the same alert going twice, but it can't stop two different alerts from going over the cap. There is no row to lock yet either, because the row we care about doesn't exist.

To see it, I fired 20 goroutines at the same user with a cap of 3, without any lock. Twenty alerts went in. Every single run.

An advisory lock fixes it. It is a lock on a number that you choose, with no table behind it:

func (q *Queue) claimAlert(ctx context.Context, userID, isoWeek, refKey string, weeklyCap int, lock bool) (Outcome, error) {
    out := Claimed
    err := pgx.BeginFunc(ctx, q.db, func(tx pgx.Tx) error {
        if lock {
            // Released by itself at COMMIT or ROLLBACK.
            if _, err := tx.Exec(ctx,
                `SELECT pg_advisory_xact_lock(hashtextextended('alert:' || $1, 0))`, userID); err != nil {
                return err
            }
        }

        var same, week int
        if err := tx.QueryRow(ctx, `
            SELECT count(*) FILTER (WHERE ref_key = $2),
                   count(*) FILTER (WHERE iso_week = $3)
              FROM alert_sends
             WHERE user_id = $1`, userID, refKey, isoWeek).Scan(&same, &week); err != nil {
            return err
        }

        switch {
        case same > 0:
            out = Duplicate
            return nil
        case week >= weeklyCap:
            out = Capped
            return nil
        }

        _, err := tx.Exec(ctx, `
            INSERT INTO alert_sends (user_id, iso_week, ref_key) VALUES ($1, $2, $3)`,
            userID, isoWeek, refKey)
        return err
    })
    return out, err
}

The lock flag only exists so the test can show the broken version; the public ClaimAlert always passes true. With the lock, the same 20 goroutines produce exactly 3 rows.

A few details I'd point out:

  • Use the _xact_ variant. It is released at commit or rollback. The session variant (pg_advisory_lock) stays with the connection, and with a pool that connection goes back to the pool still holding the lock if you forget to unlock.
  • Advisory locks take a bigint, so the user ID has to be hashed. hashtextextended(text, seed) returns a bigint; the older hashtext returns a 32-bit int, which collides more often. Neither is in the main docs as a public API, but both have been there for years. A collision only means two unrelated users wait for each other for a few milliseconds, which is harmless.
  • Prefix the key ('alert:') so a lock for alerts never blocks a lock for something else about the same user.
  • Take the lock first, before you read anything. Then the count you read is the real one.

You could also run the transaction as SERIALIZABLE and retry on serialization errors. That works too. I find the advisory lock easier to read six months later, and there is no retry loop to get wrong.

Shutting down the workers

Workers like these should watch the same context as the HTTP server, so a deploy stops them between batches instead of mid job. I covered the server side in graceful shutdown of a golang HTTP server on ECS Fargate behind an ALB. Even if a worker gets killed mid job, nothing is lost: the job stays pending and comes back after retryAfter.

That's it. One table, one UPDATE ... SKIP LOCKED and one advisory lock cover a lot of what people install a queue server for.