Short answer: use a short cron-triggered dispatcher and idempotent queue workers when a large Postgres cleanup can run past a request budget; keep the deadline in durable data, split the work into bounded chunks, and make a duplicate delivery harmless.
For a B2B SaaS renewal reminder, the business deadline is the important input. Suppose an account should receive a reminder after its renewal window opens, while old reminder attempts and expired scheduling records need cleanup. The scheduler should not recalculate eligibility from the wall clock every time a worker retries. Capture the deadline once, record it with a run, and let each worker process a small, repeatable slice.
I have been paged by missed jobs and duplicate deliveries. They are different symptoms of the same design mistake: treating scheduling as the work instead of treating it as a signal to start work. A cron trigger can say “start the run.” It cannot prove that the database mutation finished.
Start with the deadline and dataset shape
Use four boundaries:
- A scheduler emits a run request.
- A dispatcher creates a durable run record and publishes chunk descriptions.
- A worker deletes or updates one bounded chunk in a Postgres transaction.
- The worker acknowledges only after the transaction commits.
The dispatcher should return quickly. Its message needs a run ID, a fixed business cutoff, and deterministic range information. It does not need a copy of every row. The worker can then find the current state from Postgres and apply the same predicate on every delivery.
For the renewal example, a chunk might contain a tenant ID and an interval of reminder IDs. A worker can remove expired attempts, or mark a reminder ready for delivery, without scanning every tenant in one transaction. The exact query depends on the schema; the invariant does not. The same run ID and range must identify the same unit of work.
This is where Node.js and Postgres fit together cleanly. Node.js owns dispatch, concurrency, and acknowledgements. Postgres owns the cutoff, run state, and mutation. The queue carries intent between them. A JavaScript timer inside one process is not a durable scheduler, and a cron handler that performs the entire delete couples request lifetime to database workload.
Duplicate delivery sets the latency budget
At-least-once delivery means a worker can finish its transaction and then disappear before acknowledging the message. The message can return. A publish retry can also create two messages for the same chunk. Those are normal failure paths, not reasons to rerun an unbounded cleanup.
The safe sequence is narrow:
package main
import (
"context"
"fmt"
)
type Chunk struct {
RunID string
TenantID string
StartID int64
EndID int64
Cutoff string
}
type Store interface {
ApplyChunk(context.Context, Chunk) error
}
func handle(ctx context.Context, store Store, chunk Chunk) error {
// ApplyChunk must commit the bounded mutation before this function returns.
if err := store.ApplyChunk(ctx, chunk); err != nil {
return fmt.Errorf("apply run %s: %w", chunk.RunID, err)
}
// The queue acknowledgement belongs here, after the database commit.
return acknowledge(chunk)
}
func acknowledge(chunk Chunk) error {
// Replace this with the selected queue client's acknowledgement call.
fmt.Printf("ack run=%s range=[%d,%d)\n", chunk.RunID, chunk.StartID, chunk.EndID)
return nil
}
The code deliberately leaves the queue client abstract. The property to test is more valuable than a provider-specific call: ApplyChunk must use one transaction, a fixed cutoff, and half-open bounds such as [start_id, end_id). Adjacent chunks then have no overlap and no accidental gap. If the same chunk runs twice, the second transaction should find no additional eligible rows, or should make the same already-applied state transition.
I keep three counters for every run: planned chunks, committed chunks, and acknowledged chunks. Those counters answer different questions during a page. Planned minus committed points to database or worker progress. Committed minus acknowledged identifies work that may be delivered again. A scheduler log cannot answer either question reliably because it is not the application ledger.
The tempting shortcut is to acknowledge as soon as the worker receives a message. That makes a database timeout look like success. The other tempting shortcut is to compute now() independently in every retry. That changes the cleanup boundary while the run is in flight. Both shortcuts make a postmortem harder because the system has lost the distinction between “not attempted,” “committed,” and “may be replayed.”
How should Node.js and Postgres handle a cron trigger, queue worker, and large dataset?
Start with the mutation and its failure modes. A renewal cleanup usually has a useful natural key: tenant, reminder ID, or a time interval. Pick the key that lets a worker make bounded progress while preserving the business deadline. Avoid a chunk whose size is defined only by “all rows older than X”; that set grows while the job runs.
There is no safe universal row count. Row width, indexes, foreign keys, lock contention, replication behavior, and foreground traffic all change the answer. For this choice, latency is the service-level cost and database pressure is the infrastructure cost; a smaller chunk can improve response time while increasing dispatch overhead. I’m not sure what your limit should be without query plans and production latency evidence. Measure one chunk in a production-like dataset, set a concurrency ceiling, and tune from lock time and query latency rather than from worker CPU alone. Your mileage may vary across tenants with different row widths.
A useful run record includes the immutable cutoff, the selected range scheme, the requested chunk count, and a status for each chunk. Store an idempotency key for the dispatch operation too. If the scheduler fires twice, the dispatcher should find the existing run or create a distinct run whose effects are still safe. Do not rely on a queue’s delivery behavior to provide application-level uniqueness.
The queue is a transport, not a database. Keep the message compact and reconstructable. Payloads should contain references and predicates, not a large snapshot of rows. The worker can fetch current rows under the run’s cutoff and write an outcome that operators can inspect. If a chunk repeatedly fails, route it through the selected queue’s dead-letter process and retain enough run state to replay that one chunk after diagnosis.
What should a runbook measure for scheduled cleanup?
The first dashboard should show business progress, not just trigger activity. I want to see the age of the oldest unfinished chunk, the number of committed chunks, the number waiting for acknowledgement, and the count of dead-lettered messages. For renewal reminders, add the number of accounts whose business deadline has passed but whose reminder state is still pending.
Alert on a missed schedule and on stalled work separately. A schedule can fire successfully while every worker is blocked on database locks. Conversely, the queue can be empty because the dispatcher never published the chunks. Those incidents need different owners and different first checks.
Test the unpleasant sequence before production:
- deliver one chunk twice and verify that the second pass changes no additional rows;
- stop the worker after commit but before acknowledgement and verify replay safety;
- fail the transaction and verify that the message remains eligible for retry;
- run adjacent ranges and verify coverage at both boundaries;
- advance a business deadline while a run is retrying and verify that the original cutoff remains in force;
- send a malformed or unknown chunk to the dead-letter path and verify that operators can identify its run.
The long test is worth the time. A cleanup that works only when every request completes once is a demo, not an operational design.
The trade-off: when a queue is too much machinery
The cron-to-queue pattern is a good default for a large dataset when work can be divided into independent, retryable chunks and the team needs bounded database pressure. It also gives operators a precise replay unit. That precision matters more than a faster happy path when a missed deadline has customer impact.
The catch is that the pattern adds moving parts: durable run state, a dispatcher, queue retention and retry policy, worker concurrency, and dead-letter operations. It is not suitable when an indexed mutation on a small table reliably completes within the scheduler’s request budget and a retry has negligible cost. In that case, one scheduled transaction may be easier to own.
It is also not the right primary path for immediate erasure requests. Those should be driven by an event or request for each affected record, with scheduled reconciliation as a backstop. If the process has dependencies, joins, approvals, or long waits between steps, use a workflow-oriented system instead of hiding a workflow inside queue messages. If several independent consumers need a replayable history, choose a log-oriented design.
Stick with the simpler scheduled transaction when the dataset is small and the operation is already bounded. Choose chunked workers when the database must remain responsive during a long run. That is the decision rule I would put in the runbook.
For the renewal reminder case, the implementation should be boring: persist the deadline, enqueue references, mutate one bounded range, commit, acknowledge, and expose the run state. The scheduler starts the clock. Postgres records what happened. The worker makes retries safe.
Top comments (0)