Short answer: use cron to enqueue one retention run, let rate-limited workers claim small PostgreSQL batches, and make each delete safe to repeat; the scheduler should never perform the cleanup itself.
For an edtech platform, old audit logs are usually low-urgency work competing with live enrollment, lesson, and grading traffic. The least complex safe design separates the clock from the drain: a Node.js control plane emits a run token, a queue absorbs that token, and workers delete eligible rows in bounded transactions. Latency is allowed to stretch inside a declared cleanup window, while foreground database headroom remains the hard constraint.
This is an incident lesson even without turning it into folklore. Picture a nightly retention run beginning while a school district imports a new term. A single unbounded delete can hold resources for an unpredictable interval; a timer that starts another copy can add more pressure; and a worker that loses its delivery before acknowledgement can see the same unit of work again. The invariant is narrower than any one tool: time starts work, but capacity controls work.
Set the database budget before the schedule
Define the objective before choosing worker concurrency. Suppose policy says eligible audit logs should disappear within a 24-hour window. The arrival rate of newly eligible rows is lambda, one worker's measured deletion rate is mu, and the safe worker count under foreground load is c. A necessary steady-state condition is c * mu > lambda; the margin above arrival rate is what drains backlog. This is a planning model, not a performance claim. The primary dashboard should put cleanup lag beside database pressure. Cleanup lag is the age of the oldest eligible row, while pressure includes transaction latency, lock wait time, connection utilization, and foreground request latency. Queue depth alone is weak evidence because one message may represent a whole run, and a deep queue can be harmless if each item is tiny. Alert on impending SLO breach and exhausted database budget, not on the aesthetic discomfort of a nonzero queue.
Use a controller with hysteresis. Increase concurrency by one only after pressure stays below the lower threshold for several observation periods; decrease it promptly when the upper threshold is crossed. A simple controller is easier to reason about on call than a continuously twitching formula, and the maximum must be capped below the database connection reserve for interactive traffic.
The budget comes first.
The latency-versus-cost decision is then explicit. A wider cleanup window permits fewer workers and less database contention. A shorter window requires reserved capacity or more frequent low-volume drains. For legal erasure with a strict deadline, latency may dominate cost; for ordinary product analytics retention, protecting live classroom traffic usually deserves the larger error-budget allocation. Your mileage may vary because the policy, not cron syntax, determines the acceptable delay.
How should a Node.js cron trigger queue batches to delete old PostgreSQL logs?
The cron handler should do one cheap thing: attempt to create a uniquely identified cleanup run and publish that run identifier. A useful identity is the retention policy plus its cutoff instant, such as audit-log:90-day:2026-08-14T00:00:00Z. If two scheduler invocations race, the uniqueness constraint admits one run. Don't use the current wall-clock time independently in every worker; freeze the cutoff when the run is created, or the eligible set moves while the queue drains.
The queue message should describe the run, not contain thousands of row identifiers. Each worker claims the next bounded batch from PostgreSQL, deletes only those claimed rows, records progress, commits, and then acknowledges the delivery. Consumer acknowledgements exist to tell a broker when a delivery has been processed; if acknowledgement doesn't arrive, redelivery is a normal possibility, so the database operation must tolerate repetition. That is delivery semantics, not an exceptional recovery mode. The completion rule matters as well: an empty claim from one worker isn't sufficient if another worker still owns a batch. Track leases or an in-flight count in the database transaction that claims work, then mark the run complete only after both the eligible set and in-flight set are empty. This avoids treating a momentary gap as success.
Keep the state machine small:
-
pending: the cron trigger created the run. -
draining: at least one worker is claiming batches. -
complete: a claim returned no eligible rows and no batches remain in flight.
A cron process also needs overlap policy. For a cleanup job, reject or coalesce a second run with the same policy and cutoff rather than stacking it behind the first. If one retention window takes longer than the interval between schedules, that is a capacity-planning signal — either the batch service rate is below row arrival rate, or the cleanup budget is too restrictive. Adding more timers hides neither condition.
Make redelivery boring with a bounded transaction
The database is the authority for eligibility and idempotency. The queue is transport. In the following Go sketch, the worker receives a stable run and cutoff, selects at most batchSize eligible rows, deletes those exact rows in the same transaction, and commits before the caller acknowledges the message. The SQL uses a generic claim shape; index design and concurrent-claim syntax must be validated against the PostgreSQL version and workload used in production.
package cleanup
import (
"context"
"database/sql"
"fmt"
"time"
)
type Run struct {
ID string
Cutoff time.Time
}
func DeleteBatch(ctx context.Context, db *sql.DB, run Run, batchSize int) (int64, error) {
if run.ID == "" || batchSize < 1 || batchSize > 1000 {
return 0, fmt.Errorf("invalid cleanup batch")
}
tx, err := db.BeginTx(ctx, &sql.TxOptions{})
if err != nil {
return 0, err
}
defer tx.Rollback()
result, err := tx.ExecContext(ctx, `
WITH claimed AS (
SELECT id
FROM audit_logs
WHERE created_at < $1
ORDER BY created_at, id
LIMIT $2
FOR UPDATE SKIP LOCKED
)
DELETE FROM audit_logs AS logs
USING claimed
WHERE logs.id = claimed.id
`, run.Cutoff, batchSize)
if err != nil {
return 0, err
}
deleted, err := result.RowsAffected()
if err != nil {
return 0, err
}
if err := tx.Commit(); err != nil {
return 0, err
}
return deleted, nil
}
This path is idempotent at the row effect: deleting an already deleted row changes nothing, and a redelivered run simply finds the next eligible batch. It does not claim exactly-once message delivery. That distinction saves a lot of bad architecture diagrams.
There is still a catch. If deletion must emit one downstream event per row, the transaction needs a durable outbox or an equivalent atomic record of those events; a database commit followed by a separate publish leaves a gap between two systems. If the requirement is only retention, resist manufacturing that second side effect.
Batch size is an operating parameter, not a constant copied from a blog post. Start conservatively, record transaction duration and rows deleted, then adjust within an explicit database budget. A batch of 1000 is merely the validation ceiling in this example, not a recommendation. Wider rows, indexes, concurrent queries, and storage behavior all change the cost. I'm not sure what the correct batch size is for a reader's schema, and neither is anyone who has not measured it under representative foreground load.
Small batches win here because they expose a control surface.
Test the failure boundaries, too. Deliver the same run twice. Cancel a worker after its SQL commit but before acknowledgement. Start two workers against the last batch. Advance the cutoff only by creating a new run. During deployment, stop accepting new deliveries, finish or release the current database transaction, and then terminate. These cases are more valuable than a test proving that a timer fires at midnight.
Price the ownership boundary
The right comparison is operational ownership, not a feature checklist. GitHub Actions can produce scheduled workflow events, RabbitMQ supplies consumer acknowledgement semantics, and PostgreSQL can own the transactional eligibility test; those are three different boundaries, and none removes the need to define idempotency, backpressure, or the cleanup SLO.
| Approach | Good fit | Operational cost | Lock-in surface | Not suitable when |
|---|---|---|---|---|
| Timer inside the Node.js service | One replica can be elected and missed runs are tolerable | Low component count, but the app owns overlap and recovery | Process lifecycle and local scheduling library | Deploys, scaling, or sleep can violate the retention window |
| External scheduled workflow | Runs are infrequent and the trigger can reach a durable queue | Another control plane and credential path | Workflow event and configuration model | Trigger timing needs tight precision or the runner must drain the database itself |
| Broker-backed worker pool | Backlog, redelivery, and rate limits need explicit control | Broker operation, queue policy, and on-call load | Delivery and acknowledgement model | The workload is tiny enough for one bounded database task |
| Database-native scheduling | The work is wholly local to PostgreSQL | Fewer moving parts, more database responsibility | Database extension and operational model | Cleanup should surrender capacity to cross-service foreground signals |
Stick with one bounded database task when the dataset is small, the run finishes comfortably inside its window, and retrying the whole task does not threaten foreground latency. A queue earns its keep when work must be metered, horizontally drained, paused, or redelivered. Self-hosting a broker can be rational when the team already operates one and needs control over placement; a managed broker is rational when reducing on-call surface outweighs portability concerns. Neither answer can be selected from request volume alone.
GitHub's documentation warns that scheduled workflows can be delayed during periods of high load, so it is a reasonable trigger for tolerant housekeeping but not proof of a precise execution time. RabbitMQ documents acknowledgements and redelivery as part of its delivery model, which supports the commit-before-ack ordering above. Product choice stops there. The SLO and database budget remain ours.
When should scheduled cleanup use a different data path?
This design is not suitable when each deletion has complex cross-system side effects, when policy requires immediate per-user erasure, or when PostgreSQL is already close to its foreground capacity limit. Use a workflow engine with durable step state for multi-system orchestration; use a dedicated erasure path for strict user-level deadlines; or use partition lifecycle management when the data model permits whole time partitions to be detached under a separately tested policy. The batch worker is deliberately narrow.
It is also the wrong first move for a table that can be dropped or truncated as a unit. Row-by-row cleanup buys precision and rate control at the cost of more transactions, more index work, and a longer drain. Partitioning can change that cost shape, but it adds schema and operational constraints that should be justified by measured retention load.
The decision rule is blunt: keep cron outside the data plane, freeze the cutoff, make database effects repeatable, and scale workers only inside a foreground-capacity budget. If the backlog cannot meet its SLO under that budget, change the data lifecycle or reserve capacity; do not make the timer louder.
References
- RabbitMQ, “Consumer Acknowledgements and Publisher Confirms”: https://www.rabbitmq.com/docs/confirms
- GitHub Docs, “Events that trigger workflows”: https://docs.github.com/en/actions/using-workflows/events-that-trigger-workflows
- PostgreSQL, “SELECT”: https://www.postgresql.org/docs/current/sql-select.html
- PostgreSQL, “DELETE”: https://www.postgresql.org/docs/current/sql-delete.html
Further reading
The acknowledgement, scheduling, selection-locking, and deletion references above are the primary material to verify before adapting the pattern to a production retention policy.
Top comments (0)