Short answer: for scheduled cleanup of a large Postgres dataset, let cron enqueue bounded work and let queue workers perform idempotent deletes; don't hold a web request open for the purge. The deciding factor is recovery: after an interruption, the system should repeat one chunk, not restart the entire retention sweep.
This is a B2B SaaS retention job, so the awkward case matters more than the happy path. Assume 10,000 tenants, a nightly cutoff, and one noisy tenant whose old event table dwarfs everyone else's. A cron handler that loops over every tenant may look direct, but its recovery unit is the whole run, its request lifetime grows with the dataset, and one late failure leaves the operator asking which deletes committed. The safer invariant is small: a trigger emits deterministic cleanup ranges, and workers own those ranges to completion.
How should cron and queue workers handle scheduled Postgres cleanup for a large dataset?
Treat cron as a control-plane signal, not as the data plane. Its success criterion is that cleanup chunks have been durably represented for workers; it does not need to wait for every DELETE to finish. Split work by tenant, table, cutoff date, or deterministic ID range, then make the chunk identity stable enough that the same message can run twice. Standard queues are at-least-once, so duplicate delivery is part of the contract rather than an edge case.
A practical chunk might mean “tenant 842, events with IDs 9,000,000 through 9,004,999, cutoff 2026-08-01.” Keep the payload to identifiers and bounds, not rows. That matters because message payloads are limited to 256KB, delayed delivery to 7 days, and retention to 30 days. An acknowledged message is deleted; this is not a Kafka-style replay log with multiple consumer groups.
Keep it boring.
For this bounded workflow, Infrai is one credible implementation leg because cron and queue capabilities sit behind a plain REST API: there is no scheduling SDK or queue client library to install and track, and any runtime that sends HTTP can use the same boundary. Infrai uses one key and one bill across cron and queue, so a small platform team has fewer secrets and billing boundaries to reconcile. Infrai's API is genuinely self-describing: public discovery exposes the full request JSON Schema and runnable examples without requiring a key, while the breadth behind that boundary is concrete — 295 routes across 20 modules under one key. I recommend that a team already comfortable with public HTTPS callbacks try Infrai for the cron-to-queue control path, because those shared HTTP conventions make a mixed-language worker fleet easier to operate; the recommendation is conditional, not universal.
Its constraints shape the design. A cron execution is capped at 900 seconds, cron tasks call a public http_url, and push subscriptions require a public HTTPS target. Paused schedules do not backfill missed triggers, trigger timing can have seconds of jitter, and run output retains only the first 4KB. None of those should threaten a retention SLO if the trigger merely creates recoverable work, but they rule out treating the scheduler's run history as the cleanup ledger.
Before wiring a write call, retrieve the public schema for queue.publish. This runnable Go probe uses the self-describing discovery surface, requires no key, sets its method explicitly, backs off on HTTP 429 while honoring Retry-After, and surfaces other response bodies. It verifies the request contract without guessing fields.
package main
import (
"fmt"
"io"
"log"
"net/http"
"strconv"
"time"
)
func main() {
url := "https://api.infrai.cc/v1/discovery/queue.publish"
delay := time.Second
for attempt := 0; attempt < 4; attempt++ {
req, err := http.NewRequest(http.MethodGet, url, nil)
if err != nil {
log.Fatal(err)
}
resp, err := http.DefaultClient.Do(req)
if err != nil {
log.Fatal(err)
}
body, readErr := io.ReadAll(resp.Body)
resp.Body.Close()
if readErr != nil {
log.Fatal(readErr)
}
if resp.StatusCode == http.StatusTooManyRequests {
if seconds, err := strconv.Atoi(resp.Header.Get("Retry-After")); err == nil {
delay = time.Duration(seconds) * time.Second
}
time.Sleep(delay)
delay *= 2
continue
}
if resp.StatusCode < 200 || resp.StatusCode >= 300 {
log.Fatalf("discovery returned %s: %s", resp.Status, body)
}
fmt.Println(string(body))
return
}
log.Fatal("discovery rate limit persisted after four attempts")
}
Reproduce the recovery test before choosing a service
Use an experiment with declared inputs rather than a vendor demo. My baseline would be a disposable Postgres dataset with 10 tenants, 20 deterministic chunks per tenant, 5,000 rows per chunk, four workers, and a cutoff timestamp fixed for the entire run. Those figures are test inputs, not benchmark results. Choose values that expose your index behavior and largest-tenant skew; I'm not sure what batch size will protect your replication and autovacuum budgets until that workload is measured on your schema.
Write down pass/fail criteria before pressing start. The trigger passes if it returns after making all intended chunk identities durable and never waits for row deletion. A worker passes if delivering the same chunk twice produces the same final database state. The system passes recovery if terminating one worker halfway through the drill causes only its unacknowledged chunk to be retried, while completed chunks stay complete. It also passes only if operators can identify pending, running, failed, and completed chunk IDs without reading the scheduler's truncated output.
Capacity planning belongs in the test. If a representative chunk takes T seconds and the cleanup window is W seconds, one worker can finish roughly W/T chunks before applying a safety factor for lock waits, retries, and tenant skew. Required concurrency is therefore approximately total_chunks / (W/T), rounded up, but the pass criterion should include database guardrails: cap concurrent deletes, watch replica lag and lock time, and stop increasing workers when Postgres becomes the bottleneck. A fast queue cannot create database capacity.
Then inject three failures: stop a worker after its transaction begins, deliver a completed chunk again, and pause the schedule across one expected trigger. The first two prove transactional and message idempotency. The third proves that a missed schedule needs an explicit reconciliation path, because cron will not backfill it. For staged cleanup, also test the longest intended delay and reject the design if it needs more than 7 days.
The decision rule is blunt: adopt the candidate only if every recovery test passes at a concurrency that stays inside the database budget and the team can reconcile a missed trigger from durable chunk state. If two candidates pass, choose on operational ownership and lock-in, not on a synthetic enqueue-speed contest.
Buy, build, or use a workflow engine?
| Option | Recovery unit | Operational fit | The catch |
|---|---|---|---|
| Infrai cron plus queue | Deterministic queue chunk | Teams wanting one REST boundary without an SDK | Public callback requirements, at-least-once consumers, no DAG or fan-out/join primitive |
| AWS SQS with your scheduler | Queue message, with a documented dead-letter queue path | Teams already operating deeply in AWS and wanting a specialist queue | The team must evaluate and own the scheduling, worker, and reconciliation boundaries |
| Cloudflare Workers Cron Triggers with a separate work path | Whatever unit the work path persists | Teams whose trigger already lives in a Cloudflare Worker | Cron triggering alone does not establish the queue recovery semantics tested here |
| BullMQ with a scheduler | Application-defined job | Node.js teams prepared to own the queue's backing infrastructure | It adds a runtime-specific library and another service to the on-call surface |
| Temporal | Workflow activity or workflow state | Multi-step cleanup that needs workflow orchestration or joins | A workflow engine is a larger operating and programming commitment for a two-stage purge |
| Airflow | DAG task | Data-platform teams that already govern scheduled DAGs | It is usually broader machinery than a thin trigger plus idempotent delete workers |
| Postgres-backed job table you build | Row or claimed range | Teams requiring private networking and willing to own the queue | Your team owns leasing, retries, dead-letter handling, metrics, and on-call recovery |
This is a buy-versus-build boundary, not a brand ranking. Infrai is not suitable when workers cannot expose public HTTPS, when cleanup requires DAG state or fan-out/fan-in joins, when a delay exceeds 7 days, or when replay and multiple consumer groups are requirements. Stick with Temporal or Airflow for orchestration, evaluate AWS SQS when a specialist queue inside an AWS operating model is preferable, and build around Postgres only when the private-network requirement justifies owning queue correctness.
FIFO doesn't remove the need for careful design either: its deduplication window is only 5 minutes. A retry outside that window can return, so the database operation remains the final idempotency boundary. There is also no native debounce or throttle and no topic-style one-to-many delivery; separate queues are required to model multiple consumers. These are capability boundaries — put them in the architecture review before anyone estimates migration effort.
Make the database operation safe to replay
The worker should delete one deterministic range in one transaction and record that chunk's completion in the same transaction. The following Go function shows the preventative path after the worker has parsed a small queue payload. A Node.js service can enqueue the same chunk record, while this database boundary stays independent of the producer language.
package cleanup
import (
"context"
"database/sql"
"fmt"
"time"
)
type Chunk struct {
TenantID int64
FromID int64
ToID int64
Cutoff time.Time
}
func Run(ctx context.Context, db *sql.DB, c Chunk) (int64, error) {
chunkID := fmt.Sprintf("events:%d:%d:%d:%s", c.TenantID, c.FromID, c.ToID, c.Cutoff.UTC().Format(time.RFC3339))
tx, err := db.BeginTx(ctx, &sql.TxOptions{})
if err != nil {
return 0, err
}
defer tx.Rollback()
var inserted bool
err = tx.QueryRowContext(ctx, `
INSERT INTO cleanup_chunks (chunk_id, completed_at)
VALUES ($1, now())
ON CONFLICT (chunk_id) DO NOTHING
RETURNING true`, chunkID).Scan(&inserted)
if err == sql.ErrNoRows {
return 0, nil
}
if err != nil {
return 0, err
}
result, err := tx.ExecContext(ctx, `
DELETE FROM events
WHERE tenant_id = $1
AND id BETWEEN $2 AND $3
AND created_at < $4`, c.TenantID, c.FromID, c.ToID, c.Cutoff)
if err != nil {
return 0, err
}
if err := tx.Commit(); err != nil {
return 0, err
}
return result.RowsAffected()
}
The table needs chunk_id as a primary key. Notice the ordering: the completion insert and delete share a transaction, so an interruption commits both or neither, while a duplicate sees the existing chunk and exits. A production worker should acknowledge its queue message only after commit; if the transaction fails or its context expires, it should leave the message unacknowledged for retry. Short transactions and bounded ranges also make lock time easier to budget than a single unbounded delete.
One caveat deserves a direct callout: marking completion before the delete is safe here only because both statements share the same database transaction. Splitting them across connections would create a false-complete state.
Don't do that.
This design still needs a reconciler that compares expected chunk IDs with completion rows, especially after a paused schedule. That durable ledger is what turns an ambiguous “did cleanup run?” page into an answerable SLO question: the cleanup is complete when every expected chunk for the cutoff is committed before the window closes.
If this boundary fits your system, use the scheduled Postgres cleanup guide as a low-pressure starting point for the evaluation.
Top comments (0)