Two Pods, One Job Table: A Safe Postgres Queue with SKIP LOCKED in Spring Boot

It's 10:20 AM on a Monday and support has forwarded three screenshots of the same "Your invoice is ready" email, received three times by one customer. The dashboards look fine. CPU is low, the error rate is zero. Your logs show this:

10:15:00.112 [scheduling-1] INFO c.a.n.NotificationWorker - picked 50 jobs (pod notif-7c9d-a) 10:15:00.115 [scheduling-1] INFO c.a.n.NotificationWorker - picked 50 jobs (pod notif-7c9d-b) 10:15:00.118 [scheduling-1] INFO c.a.n.NotificationWorker - picked 50 jobs (pod notif-7c9d-c) 

Three pods, one notification_job table, the same 50 rows. The deployment went from one replica to three last week, and nobody asked what @Scheduled does when it lives in three JVMs at once.

If you already run PostgreSQL, a job table with FOR UPDATE SKIP LOCKED is a reasonable queue for moderate volumes, and you don't need a broker for it. There are a few ways to get it subtly wrong, so this post goes through them.

Table of Contents

• The innocent worker
• Why a plain SELECT hands out the same rows
• What FOR UPDATE alone does
• SKIP LOCKED
• Pattern 1: claim in a short transaction, work outside it
• Pattern 2: leases for crashed workers
• Keeping the table healthy
• Gotchas and when not to use this
• Checklist

The innocent worker

This is roughly what the first version looks like in most codebases:

@Component public class NotificationWorker { private final NotificationJobRepository jobs; private final EmailClient emailClient; public NotificationWorker(NotificationJobRepository jobs, EmailClient emailClient) { this.jobs = jobs; this.emailClient = emailClient; } @Scheduled(fixedDelay = 5000) @Transactional public void run() { List<NotificationJob> batch = jobs.findTop50ByStatusOrderByIdAsc(JobStatus.PENDING); for (NotificationJob job : batch) { emailClient.send(job.getRecipient(), job.getPayload()); job.setStatus(JobStatus.DONE); } } } 

With one pod this works. @Scheduled is per JVM, so three pods mean three schedulers, and nothing in this code coordinates them.

Why a plain SELECT hands out the same rows

PostgreSQL defaults to READ COMMITTED. A plain SELECT takes no row locks, so every pod sees the same committed rows with status = 'PENDING'. Pod A hasn't committed its update to DONE when pod B reads, because pod A is still calling the email provider inside the transaction. All three pods read the same 50 rows.

sequenceDiagram participant A as Pod A participant B as Pod B participant DB as PostgreSQL A->>DB: SELECT ... WHERE status='PENDING' LIMIT 50 B->>DB: SELECT ... WHERE status='PENDING' LIMIT 50 DB-->>A: rows 1..50 DB-->>B: rows 1..50 A->>A: send 50 emails B->>B: send 50 emails A->>DB: UPDATE status='DONE', COMMIT B->>DB: UPDATE status='DONE', COMMIT

Both updates succeed, because setting a row to DONE twice is not a conflict. There is no error anywhere, only duplicate emails.

What FOR UPDATE alone does

The obvious fix is a pessimistic lock. In Spring Data JPA that is @Lock(LockModeType.PESSIMISTIC_WRITE) on the repository method, which Hibernate turns into SELECT ... FOR UPDATE. Pod B's query now tries to lock the same rows and waits for pod A to commit.

The duplicates are gone, but the pods now run one after another. Pod B holds a pooled connection and waits for as long as pod A's batch takes. If 50 emails take 10 seconds, pod B waits 10 seconds, then re-checks status = 'PENDING' against the updated rows and finds nothing. You pay for three workers and get one.

SKIP LOCKED

The PostgreSQL SELECT docs say that with SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. The same page warns that this gives an inconsistent view of the data and isn't suited to general-purpose work, but that it can be used to avoid lock contention with multiple consumers of a queue-like table. That is our case.

SELECT id, recipient, payload FROM notification_job WHERE status = 'PENDING' ORDER BY id LIMIT 50 FOR UPDATE SKIP LOCKED; 

With a LIMIT, locking stops once enough rows have been returned. Pod A locks rows 1 to 50, pod B skips them and locks 51 to 100, pod C gets 101 to 150. They run in parallel without waiting.

Now look at the transaction boundary. A row lock lasts until the transaction ends. If you keep the original @Transactional around the email calls, you hold 50 row locks and one HikariCP connection for the whole batch. With the default maximumPoolSize of 10, ten slow batches use every connection, which is the same trap as a transaction wrapped around a third-party API call. The claim has to be short.

Pattern 1: claim in a short transaction, work outside it

Mark the rows as taken in the same statement that selects them, and commit at once. The status column then does the job of the lock.

CREATE TABLE notification_job ( id BIGSERIAL PRIMARY KEY, recipient TEXT NOT NULL, payload TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'PENDING', attempts INT NOT NULL DEFAULT 0, locked_until TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_notification_job_pending ON notification_job (id) WHERE status = 'PENDING'; 

The partial index holds only pending rows, so it stays small however many finished rows pile up. The claim is one UPDATE ... RETURNING:

import java.time.Duration; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @Repository public class NotificationJobStore { private static final String CLAIM_SQL = """ UPDATE notification_job SET status = 'RUNNING', attempts = attempts + 1, locked_until = now() + make_interval(secs => ?) WHERE id IN ( SELECT id FROM notification_job WHERE status = 'PENDING' ORDER BY id LIMIT ? FOR UPDATE SKIP LOCKED ) RETURNING id, recipient, payload, attempts """; private final JdbcTemplate jdbc; public NotificationJobStore(JdbcTemplate jdbc) { this.jdbc = jdbc; } public record Job(long id, String recipient, String payload, int attempts) {} @Transactional public List<Job> claim(int batchSize, Duration lease) { return jdbc.query( CLAIM_SQL, (rs, i) -> new Job( rs.getLong("id"), rs.getString("recipient"), rs.getString("payload"), rs.getInt("attempts")), lease.toSeconds(), batchSize); } @Transactional public void markDone(long id) { jdbc.update("UPDATE notification_job SET status = 'DONE', locked_until = NULL WHERE id = ?", id); } @Transactional public void markFailed(long id, boolean giveUp) { jdbc.update( "UPDATE notification_job SET status = ?, locked_until = NULL WHERE id = ?", giveUp ? "FAILED" : "PENDING", id); } } 

claim is a transaction of a few milliseconds. The subquery's row locks are released at commit, but by then the rows are RUNNING, so the next poller's status = 'PENDING' filter no longer matches them. The worker:

import java.time.Duration; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; @Component public class NotificationWorker { private static final int MAX_ATTEMPTS = 5; private final NotificationJobStore store; private final EmailClient emailClient; public NotificationWorker(NotificationJobStore store, EmailClient emailClient) { this.store = store; this.emailClient = emailClient; } @Scheduled(fixedDelay = 2000) public void poll() { for (var job : store.claim(50, Duration.ofMinutes(2))) { try { emailClient.send(job.recipient(), job.payload()); store.markDone(job.id()); } catch (Exception e) { store.markFailed(job.id(), job.attempts() >= MAX_ATTEMPTS); } } } } 

poll() has no @Transactional. Each store call is its own short transaction, and I like that the boundaries are visible by reading the store class.

Pattern 2: leases for crashed workers

Once the lock is gone, a pod killed mid-batch leaves its rows as RUNNING forever. locked_until handles that: a second scheduled method puts expired jobs back.

@Scheduled(fixedDelay = 30000) @Transactional public void reclaimExpired() { jdbc.update(""" UPDATE notification_job SET status = 'PENDING', locked_until = NULL WHERE status = 'RUNNING' AND locked_until < now() """); } 

Put this in NotificationJobStore (or inject the JdbcTemplate where you use it), and keep the lease longer than your slowest job. If a job takes 3 minutes and the lease is 2, another pod picks it up while the first is still working and the duplicates are back.

Even with a sound lease, delivery is at least once. A pod can send the email, die before markDone, and the job runs again. Pass the job id as an idempotency key if your email provider supports one, or record sent ids in a table with a unique constraint.

Keeping the table healthy

A queue table is written constantly, and every UPDATE in PostgreSQL leaves a dead row version behind. Here a job is updated two or three times (claim, done, sometimes a retry). Autovacuum starts when dead tuples exceed autovacuum_vacuum_threshold (default 50) plus autovacuum_vacuum_scale_factor (default 0.2) times the row count. With 5 million rows in the table, that is about a million dead tuples before vacuum even starts, and your pollers wade through them meanwhile.

Both settings can be overridden per table:

ALTER TABLE notification_job SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000 ); 

Deleting or archiving DONE rows on a schedule helps more, since a small table needs little tuning. Long-running transactions elsewhere in the database also stop vacuum from removing recent dead rows, and a queue table is the first place you'll feel it.

Gotchas and when not to use this

When the jobs come from a database write anyway, I prefer this to a new broker, because you can insert the job in the same transaction as the business change. That gives you outbox behaviour with no extra infrastructure.

Checklist