Short answer: for scheduled cleanup of a large marketplace dataset, use a short cron trigger to create bounded queue work, then let a rate-limited worker pool drain those chunks with idempotent commits and an audit trail. The important choice is the delivery guarantee you can actually prove, not the scheduler brand.
This is an architecture decision record for deleting expired marketplace data while orders, refunds, and seller records continue to arrive. The invariants are deliberately boring: a cleanup unit has a stable identity, its database effect can be replayed safely, and its audit row describes the same commit as the deletion. The failure boundaries are equally explicit: a trigger may run twice, a queue may redeliver, a worker may stop between database commit and acknowledgement, and a database may slow down under competing traffic.
Those events are normal. Design for them.
No global delete.
What should a Node.js Postgres cleanup use for scheduled queue workers?
The cron handler should enqueue work and return. It should not scan and delete the entire table inside the scheduled request. For a marketplace, one message might contain a tenant identifier, a fixed retention cutoff, and an ID interval such as [from_id, to_id). A worker claims that unit, deletes rows matching the immutable predicate, writes one audit record keyed by the same interval, and acknowledges only after the transaction commits.
This gives the system an exactly-once mindset without pretending that the transport provides exactly-once delivery. At-least-once delivery is acceptable when the business operation is idempotent. On a replay, the same cutoff and interval produce the same logical result; the audit key prevents a second completion record, and the delete finds no already-removed rows. Do not recalculate the cutoff with now() during a retry. A retry should mean the same work, not a moving target.
The worker pool needs a database-aware limit. A queue concurrency of 50 is not useful if Postgres can sustain only 12 cleanup transactions alongside checkout traffic. Start with a small per-tenant and global limit, measure lock waits and query latency, and make backoff part of the worker rather than a property left to hope. The objective is to drain old work without turning retention into an availability incident. Consider a marketplace with one unusually busy tenant and many quiet ones: a single FIFO stream can let that tenant consume every slot, while an unbounded fan-out can create the opposite problem by opening more database sessions than the primary can tolerate. A practical admission loop therefore keeps a global ceiling, reserves enough capacity for foreground requests, and applies a separate tenant ceiling; it records the reason for a delayed job so operators can distinguish intentional throttling from a stuck consumer. That policy is less glamorous than changing the cron cadence, but it is the part that protects checkout during a retention run. If the queue redelivers after a worker has committed, the system should not try to infer whether the prior attempt “probably” succeeded from a timeout. It should inspect the stable audit key and rerun the bounded predicate safely.
For compliance-sensitive data, the audit trail should record the cleanup unit, cutoff, actor or scheduler identity, start and completion timestamps, affected-row count, and outcome. The count is operational evidence, not proof that every eligible row was found; a later verification query or watermark is still needed when completeness matters.
The decision record: boundaries, options, and consequences
The central boundary is between orchestration and mutation. Cron decides when to create work. The queue provides a retryable unit and absorbs bursts. Node.js runs the consumer and applies backpressure. Postgres owns the transaction and the predicate. Keeping those responsibilities separate makes a failed run diagnosable instead of turning one long request into an ambiguous partial state.
| Option | Useful when | Delivery and operational trade-off |
|---|---|---|
| In-process cron plus direct delete | The dataset is small and a missed run is harmless | Simple, but replicas can duplicate schedules and one request becomes the retry unit |
| Database-native scheduler plus chunked SQL | The database is the natural owner of the job | Fewer moving parts, but cleanup competes directly with primary database capacity |
| Cron trigger plus queue and workers | Runtime, retries, tenant isolation, or auditability matter | Strongest separation of concerns, at the cost of queue operations, poison-message handling, and observability |
| Workflow engine | Cleanup has dependent stages, joins, approvals, or long-lived state | Expressive orchestration, but additional infrastructure and concepts are hard to justify for one bounded pipeline |
There is no universal best practice detached from failure cost. If a duplicate cleanup can alter a financial ledger, the predicate and audit key deserve more scrutiny than the cron expression. If the work is merely deleting derived cache rows, a simpler schedule may be entirely reasonable.
Queue dead-letter handling is part of the design, not an afterthought. A message that repeatedly fails because of malformed data should leave the normal delivery path after a bounded number of attempts, while transient database pressure should remain retryable. The dead-letter record needs enough context to identify the tenant, cutoff, interval, and failure class without reconstructing the original request from logs. A dead-letter queue documents that delivery was exhausted; it does not prove that cleanup was complete.
The critical path in Go
The following example keeps provider details out of the decision. The scheduler publishes a deterministic work item; the worker then places the delete and audit insert in one transaction. The queue acknowledgement is intentionally outside that transaction.
package main
import (
"context"
"database/sql"
"fmt"
)
type CleanupJob struct {
TenantID string
Cutoff string
FromID int64
ToID int64
}
type Queue interface {
Publish(context.Context, CleanupJob, string) error
Ack(context.Context, string) error
}
func process(ctx context.Context, db *sql.DB, q Queue, receipt string, job CleanupJob) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
result, err := tx.ExecContext(ctx, `
DELETE FROM marketplace_events
WHERE tenant_id = $1
AND event_id >= $2
AND event_id < $3
AND occurred_at < $4`,
job.TenantID, job.FromID, job.ToID, job.Cutoff)
if err != nil {
return err
}
count, err := result.RowsAffected()
if err != nil {
return err
}
_, err = tx.ExecContext(ctx, `
INSERT INTO cleanup_audit (tenant_id, from_id, to_id, cutoff, rows_deleted)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (tenant_id, from_id, to_id, cutoff) DO NOTHING`,
job.TenantID, job.FromID, job.ToID, job.Cutoff, count)
if err != nil {
return err
}
if err := tx.Commit(); err != nil {
return err
}
if err := q.Ack(ctx, receipt); err != nil {
return fmt.Errorf("commit succeeded; acknowledgement needs retry: %w", err)
}
return nil
}
The important ordering is easy to miss — and it is where delivery language becomes an implementation detail rather than a slogan. If the process dies before Commit, the queue can redeliver the job and the database has no audit record for that attempt. If it dies after Commit but before Ack, redelivery is expected; the deterministic delete is harmless and the unique audit key keeps the record singular. An acknowledgement failure must therefore be observable as a retry state, not reported as evidence that the database transaction rolled back. The application does not need to know which side of the network timeout occurred before it can make progress; it needs a transaction whose replay has the same business meaning. That is the useful form of an exactly-once mindset.
Use indexes that match the bounded predicate, but verify the plan against production-shaped data. A range chosen only by timestamp can still touch a large fraction of a hot table; a tenant-plus-ID interval often gives the worker a better operational boundary, although the correct index depends on the schema and write pattern. I am not sure one chunk size can be prescribed across marketplaces: row width, vacuum behavior, lock duration, and replica lag change the answer. Measure, then tune.
Testing and operating the drain
The test plan should exercise the boundaries rather than only the happy path. Run the same job twice and assert one audit record. Kill the worker before commit and after commit. Delay acknowledgement. Deliver two jobs for the same tenant and overlapping intervals. Add malformed input and verify that it reaches a dead-letter path with useful context. These tests expose the difference between at-least-once transport and idempotent business effects.
Production telemetry should answer four questions quickly: how old is the oldest pending cleanup unit, how many attempts does each unit require, how much database capacity does cleanup consume, and which audit intervals are missing? Track queue age, retry count, dead-letter count, transaction duration, lock waits, rows per second, and replica lag. A dashboard showing only successful cron invocations can look healthy while the queue quietly accumulates work.
The schedule itself should create a bounded set of jobs. A paused or missed trigger should not silently imply that every historical row will be deleted at once on the next run. Store the cutoff used for each run, generate intervals from a durable watermark or retention calendar, and cap the number of jobs admitted at one time. This also makes a replay deliberate: operators can reissue a known run instead of guessing which date a moving query used.
Three checks matter most: can the unit be replayed, can its completion be audited, and can the pool stop before it harms foreground traffic? If any answer is no, the cleanup is not ready for a large dataset.
When this pattern is the wrong choice
The catch is that a queue pipeline is not a universal workflow engine. It is not suitable when cleanup requires many dependent stages, human approval, or a final join whose state must be reconstructed across weeks; use a workflow-oriented system when those requirements are real. It is also a poor fit when the database is tiny, the job completes comfortably in one bounded transaction, and duplicate execution has no material consequence.
Stick with a database-native scheduler or a small in-process job when the private-network boundary makes an external trigger awkward and the operational risk is understood. Choose the queue pattern when the work must be paused, retried, isolated by tenant, and drained under an explicit rate limit. The decision should follow the delivery guarantee and failure cost, not the novelty of the scheduling component.
Top comments (0)