In the previous post, I mentioned
that ReleaseFlow has no message broker: all background work runs on PostgreSQL
itself, using FOR UPDATE SKIP LOCKED. This post goes into the details: how it
works, where it is easy to get wrong, and the limits of the approach.
The code below is taken as-is from the ReleaseFlow repository, shortened only where noted.
The problem: work that cannot happen in the request
When a pull request is merged, GitHub sends a pull_request webhook to
ReleaseFlow. That webhook says how many files the pull request changed, but not
which ones. And the file list matters: a pull request titled chore: tidy that
edits a database migration, the security configuration or a CI workflow must
not be classified by the rules and released without anyone looking at it.
Listing the files takes a separate, authenticated call to GitHub. ADR-0008 sets three conditions for that call:
-
it must not run inside the webhook request — GitHub gives up after ten seconds;
-
it must not run inside a database transaction;
-
it must survive restarts and transient GitHub failures.
Later, the same spot also gained calls to GitLab, Linear, Jira and the AI. In other words, ReleaseFlow needed a queue.
Why not a message broker
The usual answer is RabbitMQ, Kafka or Redis. But ReleaseFlow already has PostgreSQL, and everything a job needs — the change, its source, the encrypted token — is already there.
The deciding point for me was atomicity. When a webhook arrives, ChangeIntake
records the change and its processing job in the same transaction:
// The change and its processing job are recorded together; changed files are
// collected and the change classified later, outside this call.
transactionTemplate.executeWithoutResult(status -> {
Instant receivedAt = clock.instant();
Change change = changeRepository.saveAndFlush(Change.received(/* ... */));
jobRepository.save(new ChangeProcessingJob(UUID.randomUUID(), change, receivedAt));
});
Either both are committed, or neither is. With the queue in a broker, I would have to handle a change that was saved while its message was never sent, or the other way round. The usual cure for that is an outbox table — which lives in PostgreSQL anyway.
The second reason is that fewer moving parts are easier to run. The quickstart bundle has just two services: the application and one PostgreSQL. For a self-hosted tool that I want anyone to try with a few commands, every service removed is one less reason to give up.
One table per kind of work
ReleaseFlow has no shared jobs table. Each kind of work has its own table,
with its own columns and constraints. Here is the table for change processing
(shortened, combining migrations V10 and V11):
CREATE TABLE change_processing_jobs (
id UUID PRIMARY KEY,
change_id UUID NOT NULL,
status VARCHAR(20) NOT NULL,
attempts INTEGER NOT NULL,
next_attempt_at TIMESTAMP WITH TIME ZONE NOT NULL,
claimed_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE,
last_error VARCHAR(100),
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
CONSTRAINT change_processing_jobs_change_unique UNIQUE (change_id),
CONSTRAINT change_processing_jobs_status_known
CHECK (status IN ('PENDING', 'ENRICHING', 'CLASSIFYING', 'FALLBACK_REQUIRED', 'COMPLETED')),
CONSTRAINT change_processing_jobs_state_consistent CHECK (
(status = 'COMPLETED') = (completed_at IS NOT NULL)
AND (status NOT IN ('ENRICHING', 'CLASSIFYING') OR claimed_at IS NOT NULL)
)
);
Each column answers one question: next_attempt_at — when the job is due;
claimed_at — when someone took it; attempts — how many tries so far;
last_error — why the last one failed. The CHECK constraints make sure the
database never holds a job that is "done without a completion time", or one
that is "running without anyone having claimed it".
Alongside it is a partial index holding only the jobs still waiting:
CREATE INDEX change_processing_jobs_due_idx
ON change_processing_jobs (next_attempt_at, created_at)
WHERE status IN ('PENDING', 'FALLBACK_REQUIRED');
Finished jobs — over time, nearly the whole table — are not in the index, so finding the next job stays fast as the table grows.
The same pattern is reused by five workers:
| Worker | Runs every | Table | Work |
|---|---|---|---|
|
second |
|
Enrich and classify a change |
|
second |
|
History imports, and Jira polls |
|
second |
|
DeepL translations |
|
second |
|
Turn a publication into runs, then deliver one action at a time |
|
15 seconds (default) |
|
Book runs for cron schedules and release reminders; never calls a provider |
The rest of this post uses ChangeProcessingWorker as the example.
Claiming a job: FOR UPDATE SKIP LOCKED
This is the whole claim query:
// SKIP LOCKED lets several workers claim different jobs without waiting on each other.
@Query(value = """
SELECT * FROM change_processing_jobs
WHERE status IN ('PENDING', 'FALLBACK_REQUIRED') AND next_attempt_at <= :now
ORDER BY next_attempt_at, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED
""", nativeQuery = true)
Optional<ChangeProcessingJob> lockNextDue(@Param("now") Instant now);
Piece by piece:
-
FOR UPDATElocks the selected row until the transaction ends. Without it, two workers could both read the samePENDINGjob and both process it. -
FOR UPDATEalone is not enough. The second worker would wait on exactly the row the first one has locked. When the first commits, that row no longer matches theWHEREclause, and the second worker gets back… nothing — even though other jobs may still be waiting. -
SKIP LOCKEDtells PostgreSQL to skip a locked row and take the next one. Each worker gets a different job, and nobody waits for anybody. -
ORDER BY next_attempt_at, created_attakes the job that has been due the longest first, in the same order as the partial index above.
The worker is a @Scheduled method that runs every second. On each tick, it
claims one job at a time until none are due:
@Scheduled(fixedDelayString = "PT1S")
void processDueJobs() {
if (!enabled) {
return;
}
try {
while (processOne()) {
// Keep draining due jobs before sleeping again.
}
} catch (RuntimeException exception) {
log.error("Change processing stopped unexpectedly; it resumes on the next tick.", exception);
}
}
Because SKIP LOCKED already shares the jobs out, the worker can run in every
instance of the application without electing a leader.
Short transactions, with the network in between
The easiest trap is keeping a transaction open while calling GitHub. That call can take a few seconds — GitHub returns 100 files per page, and ReleaseFlow reads up to five pages. All that time, the transaction holds a connection from the pool and a lock on the row. A few slow calls at once and the pool runs dry, and user requests end up queuing behind an API somewhere far away.
So every job goes through three steps, and a network call never sits inside a transaction:
-
Transaction 1 — claim. Lock the job with the query above, move it to
ENRICHING, incrementattempts, recordclaimed_at, and commit. The lock is released right away. From here on, what keeps the job for this worker is no longer a lock but a status: anENRICHINGjob no longer matches theWHEREclause, so no other worker will claim it. -
No transaction — call out. Fetch the file list from the provider, then call the AI if it is enabled.
-
Transaction 2 — record the result, but only after checking that the job still carries this worker’s claim.
With AI enabled, there is one more short transaction between the two calls: it
records the files just fetched and moves the job to CLASSIFYING. That small
detail becomes important in the next section.
When a worker dies halfway
Holding a job with a status instead of a lock comes at a price. If the process
is killed during step 2, there is no transaction to roll back for me: the job
stays ENRICHING forever.
So on every tick, before claiming anything, the worker recovers jobs that were claimed more than ten minutes ago:
// A worker that died mid-call leaves its claim behind. Collecting files is safe to
// repeat, but an AI call is not: a stale CLASSIFYING job is completed without it.
@Modifying
@Query(value = """
UPDATE change_processing_jobs
SET status = CASE status WHEN 'CLASSIFYING' THEN 'FALLBACK_REQUIRED' ELSE 'PENDING' END,
next_attempt_at = :now
WHERE status IN ('ENRICHING', 'CLASSIFYING') AND claimed_at < :staleBefore
""", nativeQuery = true)
int recoverStale(@Param("staleBefore") Instant staleBefore, @Param("now") Instant now);
The interesting part is the CASE. An ENRICHING job goes back to PENDING,
because fetching a file list is only a read — doing it again is harmless. A
CLASSIFYING job is different: the AI call may already have reached the
provider, already cost money, maybe already been answered. ReleaseFlow asks the
AI exactly once per change, so that job becomes FALLBACK_REQUIRED. The next
time it is claimed, it is completed at once from the files recorded in the
middle transaction, by the rules alone, with a CLASSIFIER_FALLBACK trigger
that forces a human review — and the AI is never asked again.
There is a harder case than a dead worker: a worker that is not dead. It is just slow, stalls for eleven minutes, then wakes up. Meanwhile the job has been recovered and another worker has claimed it. If the old worker simply writes its result, the two results overwrite each other.
claimed_at is the ticket that prevents this:
// True only for the worker holding the current claim; a claim that went stale and was
// taken over by another worker no longer belongs to it.
boolean isClaimedAt(Instant claim) {
return (status == Status.ENRICHING || status == Status.CLASSIFYING) && claim.equals(claimedAt);
}
Every write of a result checks the ticket first:
ChangeProcessingJob job = jobRepository.findById(claim.jobId()).orElseThrow();
if (!job.isClaimedAt(claim.claimedAt())) {
// The claim went stale and another worker took the job over.
return null;
}
The new worker records a new claimed_at when it claims the job, so the old
worker’s ticket no longer matches and its result is thrown away.
One small detail I almost missed: Java’s Instant is precise to the
nanosecond, while PostgreSQL stores microseconds. Without truncating the time to
microseconds before writing it, the ticket read back from the database would
never match the one the worker holds — even with nobody competing for the job:
// PostgreSQL keeps microseconds, so a claim time must survive a round trip unchanged.
private Instant now() {
return clock.instant().truncatedTo(ChronoUnit.MICROS);
}
Bounded retries, and nothing silently skipped
Retrying needs no separate delay queue. A retry just puts the job back to
PENDING with a later next_attempt_at, and the same claim query picks it up
again when it is due.
-
Transient failures — timeouts, network errors, rate limits, 5xx responses — are retried after 2 and then 4 seconds, up to three attempts.
-
Out of attempts, or a failure not worth retrying — no token, a token GitHub refuses, an invalid response, or five pages of 100 files that are all full: the change is still classified, but with a
CHANGED_FILES_UNAVAILABLEtrigger that forces a review. A truncated list is never treated as complete. -
An unexpected failure — an exception nobody planned for — reschedules the job, and the change keeps showing as Processing in the Change Inbox instead of quietly disappearing.
The database protects itself meanwhile too. The changes_processing_unsettled
constraint keeps a PROCESSING change from being released, reviewed or sent to
the AI — even if the service layer has a bug.
Testing on a real PostgreSQL
In the previous post I wrote that mocks only answer the questions you thought
to ask. With locks it is even clearer: a fake database has no SKIP LOCKED. So
the test for this part runs on a real PostgreSQL through Testcontainers, with
the actual production migrations:
@Test
void concurrentWorkersClaimEachJobOnce() throws Exception {
Repository repository = connect(true);
GITHUB.respondWithFiles("src/main/java/Export.java");
GITHUB.delay(Duration.ofMillis(300));
for (int number = 1; number <= 3; number++) {
deliver(repository, number, "feat: change " + number);
}
ExecutorService executor = Executors.newFixedThreadPool(4);
// ... four threads call worker.processOne() until no job is left ...
assertThat(jobRepository.findAll()).allSatisfy(job -> assertThat(job.getAttempts()).isOne());
}
Four workers compete for three jobs, and the GitHub stub deliberately answers 300 ms late so the workers are sure to overlap. The test checks that all three changes are processed, that the stub receives exactly three file requests — one per pull request — and that each job is claimed exactly once.
The limits, plainly
-
Polling every second. A new job waits up to about a second, and when idle, each worker still asks the database every second. At the pace of release notes, that does not matter.
LISTEN/NOTIFYcould remove the delay, but I have not needed it yet. -
Several instances. The workers are written to run in parallel without a leader, but the operations guide still recommends a single instance: sessions live in memory, and CI has never run more than one. "Designed for" is not yet "tested with".
-
When would I use a broker? When several other systems need to hear the same event, when the workload grows beyond what one database can carry, or when the team already runs a broker. ReleaseFlow is in none of those three cases.
What’s next
The next post is about AI: how to let
it run automatically while a person still makes the decisions. That is also why this post has CLASSIFYING,
FALLBACK_REQUIRED and the "ask the AI only once" rule.
All the code is in the ReleaseFlow repository, and the original decision is recorded in ADR-0008. If you have built a queue on PostgreSQL a different way, or spot something in this post that is not right, open a Discussion on GitHub.