Short answer: use a tiny cron trigger to enqueue bounded cleanup jobs, then let idempotent Node.js workers drain the large Postgres dataset with checkpoints, leases, and observable recovery.
That split matters in a game. A match can end in a burst, a promotion can create millions of expired rows, and the cleanup window often overlaps a release. A cron process that scans and deletes everything in one transaction turns routine maintenance into an availability event. A queue gives the work a speed limit and a place to resume.
Before: one scheduled process selects old records, deletes them, and hopes the database finishes before the next tick. Lock contention grows with the scan. A deploy can stop the process. A retry then repeats a half-finished range.
After: the scheduler creates a run record and publishes small ranges. Workers claim one range, process it in a short transaction, record a checkpoint, and acknowledge the message. Recovery means finding expired leases and publishing those ranges again.
Think of the scheduler as a doorbell, not a truck. It should ring at 02:00 UTC and leave quickly.
Define recovery before choosing the machinery
The queue does not make deletion safe by itself. Safety comes from the job contract: a deterministic key range, an immutable expiry predicate, an attempt count, and a deadline. A job might say "delete rows with created_at < cutoff and id in (400000, 410000]." Repeating that predicate should reach the same database state, so redelivery after an interrupted acknowledgement doesn't expand the scope.
Five controls make that promise observable. First, alert on age, not just queue depth: 10,000 tiny jobs may be healthy while 10 jobs leased for 45 minutes are not. Track oldest job age, completed ranges per minute, delete duration, dead-letter count, and Postgres lock waits. Second, freeze the cutoff when the run starts; recalculating "older than 30 days" during every retry silently moves the boundary. Third, cap attempts and quarantine poison ranges with their range, error class, and last checkpoint. Fourth, rehearse worker loss after the delete but before acknowledgement, then confirm redelivery deletes no additional rows. Fifth, stop at the database boundary during deployment: stop claiming work, finish the current short transaction, and leave every unclaimed range visible. A quiet queue isn't enough evidence that a run completed.
Keep it visible.
A dead-letter queue is a pressure valve, not a trash can. Page on a non-zero count, and retain enough context to replay one bounded range without rerunning the whole night. AWS documents an important ordering trade-off: a dead-letter queue is not appropriate when an SQS FIFO workload must preserve exact order. Cleanup ranges should therefore be independent; if they are not, the architecture is making a promise the recovery path can't keep.
How should a cron trigger Node.js queue workers for large Postgres cleanup?
Start with a control table. It is the audit trail for operators and the coordination point for overlapping triggers.
type CleanupRun = {
runId: string;
cutoffIso: string;
nextId: bigint;
state: "running" | "draining" | "complete" | "paused";
};
async function triggerCleanup(db: Db, queue: Queue) {
const cutoffIso = new Date(
Date.now() - 30 * 24 * 60 * 60 * 1000,
).toISOString();
const run = await db.one<CleanupRun>(
`insert into cleanup_runs (cutoff, state, next_id)
values ($1, 'running', 0)
on conflict (cutoff) do update set state = cleanup_runs.state
returning id as "runId", cutoff as "cutoffIso",
next_id as "nextId", state`,
[cutoffIso],
);
await queue.publish({
runId: run.runId,
cutoffIso: run.cutoffIso,
startId: run.nextId.toString(),
endId: (run.nextId + 10_000n).toString(),
attempt: 0,
});
}
The unique constraint on the cutoff makes the trigger repeatable. The worker still needs a lease because a process can disappear after deleting rows but before acknowledging the message. A locked_until value and an attempt counter let another worker reclaim stale work without assigning the same range to two active workers.
Keep each transaction boring: select a bounded set by an indexed key, delete it, and commit. Don't use OFFSET; rows disappear while the drain runs, so offsets can skip data. Keyset pagination with id > last_id keeps the checkpoint attached to the key space. If foreign keys or audit writes make a range expensive, lower the batch size instead of extending the lock timeout. I'm not sure what batch size your schema can absorb, because row width, indexes, write traffic, and retention rules change the answer; a production-shaped load test resolves that uncertainty.
Match the scheduler to the recovery boundary
The scheduler's job ends when one durable run exists. The worker pool owns throughput, retries, and completion. That boundary keeps a missed schedule separate from a slow drain — two alerts with two different operator actions.
| Option | Integration surface | Setup cost | Best fit | Main limitation |
|---|---|---|---|---|
| Cloudflare Cron Triggers plus a durable queue | Worker event plus queue API | Two runtime components | Edge-hosted trigger with separately controlled workers | The trigger invokes a Worker; it does not replace the job queue |
| AWS EventBridge Scheduler plus SQS | Scheduled event plus queue API | Two managed services | Workloads that need scheduled delivery and dead-letter handling | Exact ordering constrains dead-letter queue use for FIFO workloads |
PostgreSQL pg_cron plus an external queue |
SQL extension plus queue client | Database extension and worker | Teams that want scheduling close to the data | Scheduler activity shares the database capacity boundary |
| Node.js timer plus an external queue | Application timer plus queue client | Fewest initial components | One continuously running process with simple deployment ownership | Process restarts and duplicate replicas require explicit coordination |
These are operating models, not rankings. Cloudflare documents that configuration changes can take several minutes to propagate, so instant schedule edits should not be part of a recovery assumption. AWS documents dead-letter queue behavior, while PostgreSQL partitioning offers another path when retention aligns with partition boundaries. Test the properties you depend on: duplicate trigger delivery, credential rotation, pause, replay, and visibility long enough to span a maintenance window.
The catch is that a cron-to-queue drain is not suitable when deletion must be globally ordered across tables, when each row needs a human decision, or when the database cannot support an index aligned with the cutoff and key range. Stick with partition detach/drop when retention maps cleanly to partitions; use an offline archival workflow when review or cross-table ordering matters. Those alternatives trade finer-grained replay for a different, often simpler, recovery boundary.
Answer the two production objections
"Why not run one SQL statement?" For a small table, that can be the right answer. For a large gaming event table, one statement concentrates locks and write-ahead-log activity and gives the operator no range-level checkpoint. Bounded transactions trade peak throughput for a recovery point that can be inspected and replayed.
"Won't a queue add cost?" Yes. It adds moving parts and another service to operate or buy. The reason to accept that cost is operational recovery: visible backlog age, bounded concurrency, and isolated failed ranges. Use measured delete latency, lock waits, and time-to-drain in the decision; don't use an untested savings claim.
Run the trigger manually in staging with production-shaped row counts. Kill a worker at the acknowledgement boundary, verify the five controls, and put pause and replay steps beside the alert. The useful best practice is concrete: a completed run means every range is checkpointed, not merely that the queue looks quiet at 03:17.
Top comments (0)