Short answer: protect the renewal deadline first, then let cron open a durable cleanup run and let queue workers drain that run in bounded, idempotent Postgres transactions. The scheduler starts work; it does not prove completion.
For an e-commerce system, a delayed renewal reminder and a large retention purge have opposite operational shapes. The reminder has a business deadline and little tolerance for queue age. Cleanup can usually take longer, but it can consume every database connection and worker slot if nobody gives it a boundary. Putting both on one unconstrained execution path turns an ordinary backlog into a customer-facing incident.
I've been paged by missed jobs and duplicate deliveries. Those pages taught the same lesson — recovery has to be designed before the first trigger fires. A schedule timestamp in a log isn't recoverable state, and a second invocation isn't a recovery plan.
Protect the deadline.
Operational recovery begins at the renewal deadline
Treat the cron trigger as a thin control-plane event. It calculates the intended cleanup window and attempts to create one durable run record identified by that window and policy version. A uniqueness constraint makes two trigger deliveries converge on the same logical run. After the run exists, a dispatcher places bounded batch work on a cleanup queue; workers repeatedly claim, mutate, and commit small sets of eligible rows until the run is complete.
The important split is between starting, delivering, and committing. Node.js can host the cron handler and dispatcher, but the process memory must not be the only record that a run was accepted. The queue provides retryable delivery and backpressure, while Postgres provides the transaction boundary for row selection, cleanup, and durable progress. None of those components can silently inherit another component's guarantee.
The renewal reminder gets a separate queue or reserved worker capacity. Its lateness alarm is measured against the business deadline, not against cleanup throughput. Cleanup gets its own concurrency ceiling, chosen from foreground database latency, lock wait, transaction duration, replication pressure, and queue age. I'm not sure what that ceiling should be for your dataset; row width, index coverage, traffic shape, and the deadline margin are the evidence needed to set it.
Here is the operational contract I would put in the runbook:
| Boundary | Durable evidence | Recovery action |
|---|---|---|
| Schedule to run | Unique run key, cutoff, and policy version | Reconcile the missing window and request the same run key |
| Run to batch | Pending batch or transactional outbox record | Republish work for the existing run |
| Batch to rows | Committed progress in Postgres | Redeliver the batch; eligibility excludes completed rows |
| Reminder to customer | Deadline and delivery state on an isolated path | Alert on lateness without waiting for cleanup |
This is an at-least-once design with harmless repetition, not an exactly-once claim. That distinction matters at 02:00 when an operator needs to decide whether replaying work will repair the gap or double an external side effect.
Should scheduled Postgres cleanup use Node.js cron and queue workers?
Imagine the bounded incident the architecture must survive. The nightly cleanup trigger is accepted, several batches commit, and the control process exits before the next batch is published. Meanwhile, a renewal reminder is due at its promised business time. If both workloads share one saturated queue, the operator has two unclear problems: whether cleanup can resume without repeating effects, and whether the reminder can get capacity before its deadline.
The invariant is more useful than the incident timeline: every accepted cleanup window must remain discoverable after the trigger process disappears, and repeating any delivery must be safe. Store the intended cutoff rather than recalculating it on retry. Keep the policy version with the run. Record progress where an operator can query it without reconstructing state from application logs. If cleanup also emits an external effect, write an outbox record in the same transaction as the database mutation and deliver that record independently.
This changes the recovery sequence. First, verify that the reminder lane still has capacity. Second, inspect durable cleanup runs for the missing schedule window. Third, compare the last committed batch with pending outbox or queue work. Finally, request work for the existing run key. Don't create a fresh cutoff merely because the retry happened later; doing so changes the eligible set during recovery.
Duplicates become routine.
A dead-letter queue is useful as a diagnostic boundary for deliveries that exhausted their configured attempts. It isn't proof that the cleanup run is complete or permanently failed. Preserve the run key, policy version, and attempt metadata so the runbook can connect a dead-lettered message back to database progress. The AWS SQS documentation describes the dead-letter queue concept and configuration considerations; the same operational question applies regardless of which queue carries the work.
The scheduler also needs to be treated according to its documented behavior. Cloudflare's Cron Triggers documentation, for example, describes scheduled invocation of a handler. Application-owned run state is still what answers whether a large cleanup finished. During deployment, verify the chosen scheduler's timing and concurrency behavior instead of assuming that every managed trigger behaves like a local Unix cron process.
Transaction design for bounded large-dataset deletion
The code below is deliberately language-neutral at the interfaces even though the sketch is Go, as required for a compact copyable example. A Node.js control process can implement the same protocol. The storage implementation is responsible for the unique run key and for atomically claiming eligible rows, applying the cleanup, and advancing progress.
package cleanup
import (
"context"
"errors"
"time"
)
var ErrRunExists = errors.New("cleanup run already exists")
type Run struct {
Key string
PolicyVersion string
Cutoff time.Time
}
type Batch struct {
RunKey string
Limit int
}
type Store interface {
CreateRun(ctx context.Context, run Run) error
CommitBatch(ctx context.Context, batch Batch) (more bool, err error)
CompleteRun(ctx context.Context, runKey string) error
}
type Queue interface {
Publish(ctx context.Context, batch Batch) error
}
type Service struct {
store Store
queue Queue
batchSize int
}
func (s *Service) Trigger(ctx context.Context, run Run) error {
err := s.store.CreateRun(ctx, run)
if errors.Is(err, ErrRunExists) {
return nil
}
if err != nil {
return err
}
return s.queue.Publish(ctx, Batch{RunKey: run.Key, Limit: s.batchSize})
}
func (s *Service) Work(ctx context.Context, batch Batch) error {
more, err := s.store.CommitBatch(ctx, batch)
if err != nil {
return err
}
if more {
return s.queue.Publish(ctx, batch)
}
return s.store.CompleteRun(ctx, batch.RunKey)
}
CommitBatch must do the hard work within one Postgres transaction: select no more than Limit eligible rows, prevent concurrent workers from claiming the same rows, apply the retention action, and advance durable progress. Use an indexed retention predicate with a stable tie-breaker such as the primary key. A repeated delivery then encounters rows that no longer satisfy the predicate and continues safely.
There is a handoff after a batch commits and before its successor is published. If losing that handoff would leave a run stranded, use a transactional outbox: commit the next-work record beside the cleanup mutation, then have a dispatcher publish unsent records. A periodic reconciler can also find nonterminal runs with no recent progress and request the next batch. Both mechanisms depend on idempotency; neither should create a different logical run.
Keep batch size configurable. A huge transaction may hold locks and compete with foreground traffic, while a tiny batch increases queue and transaction overhead. The useful test isn't “does 1,000 rows sound reasonable?” It is whether the chosen limit maintains the renewal deadline and foreground latency while making measurable progress through the eligible backlog. Your mileage may vary, so record the observed transaction duration and adjust from production-like load tests rather than folklore.
Deployment tests for missed triggers and duplicate delivery
A green “cron fired” metric is weak evidence. The dashboard needs intended run age, time since last committed batch, eligible-row backlog, queue age, retry count, transaction duration, lock wait, and foreground database latency. Track renewal reminder lateness independently. One graph cannot represent both a throughput job and a deadline job honestly.
Test the recovery claims directly. Deliver the same trigger twice and confirm there is one run. Deliver the same batch twice and confirm the retention result is unchanged. Stop a worker before and after the transaction boundary. Leave a run between commit and successor publication, then confirm the outbox dispatcher or reconciler advances it. Saturate the cleanup lane and verify that renewal reminder capacity remains available. Roll policy versions while old and new workers overlap, and require workers to reject an unknown version before mutating data.
The deployment gate should be an operator exercise, not merely a unit-test count. Give the responder only the alert and normal production tooling. They should be able to identify the schedule window, find the run, locate its last committed progress, determine whether a delivery is pending or dead-lettered, and resume the same run without guessing. If that path depends on searching unstructured logs for a process that no longer exists, the system is not ready.
This architecture has costs. It adds a run table, queue operations, reconciliation, and more states for the team to understand. It is not suitable when cleanup is small, completes comfortably in one bounded database operation, and can safely wait for the next schedule after a miss; a database-native scheduled task or a single job guarded against overlap is easier to operate there. Stick with partition lifecycle management when retention aligns cleanly with a partition boundary and dropping an expired partition fits the schema and dependency model. A queue is appropriate when work spans many bounded transactions, must survive process interruption, or includes per-record effects that need explicit retry state.
The decision rule is plain: choose the least complex mechanism whose worst credible interruption can be recovered without violating the renewal deadline. For large e-commerce cleanup, that usually means isolating deadline traffic, persisting run identity, and making queue delivery repeatable. Cron remains useful. It just doesn't get to certify the outcome.
Top comments (0)