Short answer: keep scheduled data cleanup beside the database when it is one bounded, idempotent SQL operation; use an app cron or signed webhook scheduler when cleanup depends on application rules, cross-service coordination, or tighter operational control.
| Choice | Best fit | Latency profile | Cost shape | Main catch |
|---|---|---|---|---|
| Database-local schedule | Expiring digest staging rows in one database | No network hop before work starts | Reuses database capacity | Cleanup competes with customer queries |
| App cron plus worker | Rules already live in application code | One scheduler-to-worker handoff | App compute, queue, and database work | Deployment and scheduler ownership can blur |
| External webhook scheduler | Several services need one control plane | Adds HTTP delivery before execution | Scheduler calls plus worker runtime | Authentication and duplicate delivery need design |
For a marketplace weekly digest, use a production-shaped replay as the decision gate: run retention cleanup near the data if a delayed run won't affect the next digest and each batch stays below the database latency budget. Move the trigger outward once the job must ask another service who is active, coordinate delivery state, or expose independent retry and audit controls. “Simplest” means the fewest failure boundaries that survive the replay. It doesn't mean the fewest lines in the cron expression.
Audit the glue before benchmarking the clock
First, separate the clock from the work. A schedule should create an execution attempt. It should not imply that the cleanup ran exactly once, finished, or even started on time. Database-local cron, an application timer, and a webhook control plane all need the same worker properties: idempotency, bounded batches, observable progress, and safe retries.
This matters in a weekly digest pipeline because “active customer” is usually a changing business predicate, while expired staging rows are plain retention data. Put those concerns in different jobs. The digest planner can resolve active recipients through application logic. A cleanup worker can delete sent or expired staging records by stable identifiers and a cutoff timestamp. Combining both into one heroic Friday-night transaction makes latency harder to attribute and retries harder to reason about.
The mechanisms differ mainly in ownership. The pg_cron extension runs cron-based jobs inside PostgreSQL and can execute SQL directly. Google Cloud Scheduler can target HTTP endpoints or publish through Pub/Sub. Amazon EventBridge Scheduler can invoke AWS targets on one-time or recurring schedules. Kubernetes CronJob creates Jobs on a repeating schedule, while GitHub Actions supports scheduled workflows with documented timing constraints. Those are concrete implementation choices, not proof that any control plane is automatically more reliable or cheaper for a particular workload.
Keep the contract dull. An execution attempt carries a job name, scheduled timestamp, and idempotency key. The worker reports rows examined, rows changed, elapsed time, and the last processed key. If a webhook crosses a trust boundary, authenticate the exact request bytes with HMAC, reject stale timestamps, and store the idempotency key before starting work. RFC 2104 defines HMAC; the scheduler brand doesn't change that requirement.
Duplicates are normal.
Exactly-once scheduling is the wrong target because side effects span boundaries. A sender can time out after the worker commits, leaving the sender unsure whether another attempt is safe. Make another attempt safe. For cleanup, that usually means selecting only rows whose state and retention cutoff still qualify, then deleting or marking a limited set inside one transaction. For digest delivery, it means a separate delivery key such as (campaign_week, customer_id) with a uniqueness constraint.
Benchmark customer traffic before benchmarking the timer
Measure two clocks: trigger lag and work duration. Trigger lag is worker_started_at - scheduled_at. Work duration is worker_finished_at - worker_started_at. Their sum is completion lag, the number the next digest stage actually cares about. A low-latency scheduler cannot rescue a deletion query that waits behind a lock for twenty minutes, and a fast query cannot meet a five-minute objective if the application process that owns its timer is asleep.
Set the objective from the marketplace workflow. If stale staging rows only need to disappear before the next weekly planning run, an hour of trigger lag may be harmless. If cleanup releases a uniqueness key required by the next customer action, the tolerated lag could be seconds. These are examples of decision inputs, not universal service-level objectives. I'm not sure what the right threshold is without the arrival rate, table growth, and customer-facing dependency; a seven-day replay of production-shaped metadata would resolve most of that uncertainty.
Benchmark the whole path. Record p50, p95, and maximum trigger lag, but also collect deleted rows per second, transaction duration, lock wait time, dead tuples or equivalent storage churn, queue age, attempts per execution, and application request latency during each batch. Run the benchmark against a dataset with the same index selectivity and row width as production. A million narrow synthetic rows can flatter a plan that falls apart on wide marketplace payloads.
One practical trap is a cleanup query that finds every expired row and deletes it in a single transaction. It looks cheap in a local run. Under load, it holds locks longer, creates a large write burst, and makes cancellation expensive. A bounded batch of 500 rows is a reasonable starting hypothesis, not a magic number. Test 100, 500, and 2,000 against the latency budget, then tune batch size and pause duration together. If the worker uses non-blocking row claims, count skipped rows; a rising count can reveal contention even while total runtime still looks acceptable.
Fast enough wins.
Count ownership handoffs before compute
Scheduler price is rarely the useful comparison. Count the database CPU and I/O, worker runtime, queue operations, outbound requests, log volume, metrics cardinality, and engineer time needed to own the path. The database work exists in every design. Moving the clock to an external service does not move the delete off the database, so a cheap trigger can still produce an expensive full-table scan.
Use a per-run cost worksheet instead of a vendor price screenshot:
| Cost input | What to measure | Common distortion |
|---|---|---|
| Trigger | Attempts and redeliveries | Counting schedules but not retries |
| Compute | Worker duration and concurrency | Ignoring cold or idle capacity |
| Database | Rows scanned, writes, vacuum or compaction work | Pricing only the successful delete |
| Operations | Alerts, dashboards, key rotation, deploy ownership | Treating glue as free |
For a single database and one retention rule, database-local scheduling often has the smallest control-plane footprint. The catch is resource isolation: its query shares the database with checkout, search, and digest preparation. An app worker adds a deployment and usually a queue, but it can reuse application validation, tracing, and concurrency controls. A signed webhook adds a network boundary and secret lifecycle; it pays for itself when central scheduling, independent releases, or cross-service visibility are requirements rather than aspirations.
Don't compare steady-state spend alone. Model a missed run, a duplicate attempt, and a seven-day backlog. The cheapest design on a quiet Tuesday can become the costly one when it has no rate control and replays millions of expired rows beside customer traffic. Cost and latency meet at the batch limiter.
Carry one execution envelope through every trigger
The following TypeScript sketch keeps vendor details behind two small interfaces. The scheduler submits an attempt; the worker owns database semantics. The values are intentionally visible because config bloat hides the decision being tested.
type CleanupAttempt = {
idempotencyKey: string;
scheduledAt: string;
cutoff: string;
};
type CleanupResult = {
deleted: number;
elapsedMs: number;
status: "completed" | "duplicate";
};
interface CleanupStore {
reserveAttempt(key: string, scheduledAt: Date): Promise<boolean>;
deleteExpiredDigestRows(cutoff: Date, limit: number): Promise<number>;
finishAttempt(key: string, result: CleanupResult): Promise<void>;
}
const BATCH_SIZE = 500;
const MAX_BATCHES = 20;
export async function runCleanup(
store: CleanupStore,
attempt: CleanupAttempt,
): Promise<CleanupResult> {
const started = performance.now();
const reserved = await store.reserveAttempt(
attempt.idempotencyKey,
new Date(attempt.scheduledAt),
);
if (!reserved) {
return { deleted: 0, elapsedMs: 0, status: "duplicate" };
}
let deleted = 0;
for (let batch = 0; batch < MAX_BATCHES; batch += 1) {
const batchDeleted = await store.deleteExpiredDigestRows(
new Date(attempt.cutoff),
BATCH_SIZE,
);
deleted += batchDeleted;
if (batchDeleted < BATCH_SIZE) break;
}
const result: CleanupResult = {
deleted,
elapsedMs: Math.round(performance.now() - started),
status: "completed",
};
await store.finishAttempt(attempt.idempotencyKey, result);
return result;
}
reserveAttempt needs a unique constraint on the idempotency key. deleteExpiredDigestRows needs deterministic qualification, a supporting index, and one transaction per call. The cap prevents one late execution from monopolizing database capacity; if it hits MAX_BATCHES, emit backlog depth and let the next controlled attempt continue. Don't spin forever.
For an external webhook, sign the raw body and include a timestamp. Compare digests in constant time, enforce a narrow clock-skew window, and rotate secrets without an all-at-once cutover. Authentication proves who sent an attempt. It does not make the attempt unique.
import { createHmac, timingSafeEqual } from "node:crypto";
export function verifyScheduleSignature(
rawBody: Buffer,
timestamp: string,
suppliedHex: string,
secret: string,
nowMs = Date.now(),
): boolean {
const sentAtMs = Date.parse(timestamp);
if (!Number.isFinite(sentAtMs) || Math.abs(nowMs - sentAtMs) > 300_000) {
return false;
}
const expected = createHmac("sha256", secret)
.update(timestamp)
.update(".")
.update(rawBody)
.digest();
const supplied = Buffer.from(suppliedHex, "hex");
return supplied.length === expected.length && timingSafeEqual(supplied, expected);
}
Return quickly after durable acceptance when cleanup can exceed the sender's timeout. A queue separates webhook delivery latency from database work and provides an explicit backlog signal. Google Cloud Pub/Sub is one example of this publish-subscribe pattern, but the architectural requirement is generic: acknowledge only after the attempt is durably recorded, then let a rate-limited consumer execute it.
What should app cron and webhook schedulers prove before scheduled data cleanup ships?
Deployment deserves the same skepticism as code. Run the worker with the production query plan on a restored, scrubbed dataset. Exercise duplicate keys, a stale signature, empty batches, the batch cap, cancellation between batches, and a backlog replay. Alert on completion lag and oldest unprocessed cutoff, not merely “cron fired.” A green trigger with a stuck worker is useless.
Use the same fixture for all three placements. Start with one normal weekly run, then submit the same idempotency key twice. Delay the next attempt by seven days so it sees a backlog. Finally, run digest recipient selection and ordinary marketplace reads beside cleanup. Keep the cleanup query, batch limit, indexes, dataset, and acceptance criteria fixed; otherwise the comparison measures different workers and calls the result a scheduler benchmark. The failure assertions are more useful than a happy-path stopwatch: the duplicate changes zero extra rows, cancellation loses no committed progress, the backlog drains without crossing the customer-request latency budget, and a stale webhook fails authentication before reservation. Inspect the execution record after every phase rather than trusting a success response. If a candidate cannot expose enough timestamps to prove those statements, record that gap as an observability cost rather than guessing or adding another dashboard that watches only the trigger.
No pass, no comparison.
This is also where configuration earns suspicion. Each lease duration, retry policy, timeout, concurrency limit, and secret expands the state space. Keep the values that a failure test proves necessary. Delete ornamental knobs.
Delete every knob the replay cannot justify
Stick with database-local scheduling when all qualifying data sits in one database, the operation is naturally expressed as bounded SQL, and the database team owns capacity and observability. It is not suitable when cleanup needs remote APIs, application-only authorization logic, or isolation from customer query load. In those cases, an app worker is the cleaner boundary even though it adds runtime machinery.
Choose app cron when the application already has a continuously available process, one deployment owner, and a mature job queue. Avoid an in-process timer when autoscaling replicas can each fire it, releases can interrupt it, or dormant instances make trigger lag unpredictable. A singleton lease can constrain execution, but now the lease is part of the scheduler and needs its own expiry and metrics.
Choose a webhook control plane when schedules must be managed independently from application deploys or one team needs consistent audit and retry policy across services. It is the wrong default for a single SQL retention rule: HMAC verification, replay protection, network retries, and secret rotation are real glue. For a weekly marketplace digest spanning recipient selection, message generation, and cleanup, however, that boundary can keep orchestration visible while each worker remains independently idempotent.
There is no universal cheapest scheduler. Use the mechanism that meets completion-lag targets during customer load and backlog replay with the smallest owned surface. For the narrow cleanup job, start beside the data. Promote it to an app worker or signed webhook only when a measured dependency crosses that boundary.
References
- https://github.com/citusdata/pg_cron
- https://www.postgresql.org/docs/current/sql-delete.html
- https://www.rfc-editor.org/rfc/rfc2104
- https://cloud.google.com/scheduler/docs/overview
- https://cloud.google.com/pubsub/docs/overview
- https://docs.aws.amazon.com/scheduler/latest/UserGuide/what-is-scheduler.html
- https://kubernetes.io/docs/concepts/workloads/controllers/cron-jobs/
- https://docs.github.com/en/actions/reference/workflows-and-actions/events-that-trigger-workflows#schedule
Top comments (0)