Short answer: for expiring marketplace reservations after a fixed hold window, put the cleanup rule in an idempotent application endpoint and let hosted app cron call it; keep pg_cron for teams that deliberately want the database to own the job, and add a queue worker when one invocation cannot finish a bounded batch.
The deciding constraint is recovery. A reservation held for 20 minutes must eventually become available again even if a trigger was paused, repeated, or delayed. Scheduler placement matters less than having a durable cutoff, bounded work, and a safe way to rerun the same interval. It's tempting to start with a clever cron expression. Don't. Start with the state transition that can be repeated without corrupting a live order.
Recovery owns the design.
Should scheduled data cleanup use Postgres pg_cron or an app cron webhook scheduler?
Use an app cron webhook scheduler when the reservation rules already live in application code, the team wants ordinary deploy and test practices, and a public HTTP endpoint is acceptable. Use pg_cron when SQL is the natural and intentionally database-owned expression of the cleanup, and when tying execution to that Postgres installation is a benefit rather than an accidental dependency.
That distinction sounds architectural, but it becomes painfully concrete during recovery. Database-native cron keeps execution close to the rows and avoids an HTTP boundary; the catch is that application policy can drift into stored SQL, database privileges become part of deployment, and moving the data layer also moves the scheduler. An app-level job keeps the policy beside the application and makes explicit replay endpoints possible, but its handler must be internet-accessible when the scheduler only calls public URLs. Authentication and request verification are therefore design inputs, not finishing touches.
For this marketplace, define expiration as a compare-and-set transition: only a reservation still in held state, with expires_at at or before a fixed cutoff, may become expired. Never calculate a new cutoff for every row midway through a run. Capture one UTC timestamp, process a limited page, record progress in the application database, and repeat. If the same page is attempted twice, the state predicate makes the second attempt harmless.
One detail changes the recommendation: hosted cron is not a workflow engine. If cleanup needs DAG dependencies, fan-out followed by a join, or long-running orchestration, use Temporal or Airflow instead. Calling those systems “cron alternatives” understates the operational model they introduce, but it also avoids pretending a webhook scheduler has primitives it doesn't.
What makes a scheduled cleanup run recoverable after a missed trigger?
Make the cutoff durable.
The following endpoint is a complete Python example using FastAPI and PostgreSQL. It accepts a scheduler-supplied cutoff and run ID, verifies an HMAC signature, expires no more than 500 eligible reservations in one transaction, and records the run before returning. The FOR UPDATE SKIP LOCKED clause lets another worker take a different page without waiting; the status and cutoff predicates protect a reservation that has already been purchased or released.
import hashlib
import hmac
import os
from datetime import datetime, timezone
import psycopg
from fastapi import FastAPI, Header, HTTPException
from pydantic import BaseModel
app = FastAPI()
DATABASE_URL = os.environ["DATABASE_URL"]
WEBHOOK_SECRET = os.environ["CLEANUP_WEBHOOK_SECRET"].encode()
class CleanupRequest(BaseModel):
run_id: str
cutoff: datetime
def valid_signature(body: CleanupRequest, supplied: str) -> bool:
message = f"{body.run_id}:{body.cutoff.isoformat()}".encode()
expected = hmac.new(WEBHOOK_SECRET, message, hashlib.sha256).hexdigest()
return hmac.compare_digest(expected, supplied)
@app.post("/internal/jobs/expire-reservations")
def expire_reservations(
body: CleanupRequest,
x_cleanup_signature: str = Header(),
) -> dict[str, int | str]:
if body.cutoff.tzinfo is None or body.cutoff > datetime.now(timezone.utc):
raise HTTPException(status_code=400, detail="cutoff must be UTC and not in the future")
if not valid_signature(body, x_cleanup_signature):
raise HTTPException(status_code=401, detail="invalid signature")
with psycopg.connect(DATABASE_URL) as connection:
with connection.cursor() as cursor:
cursor.execute(
"""
INSERT INTO cleanup_runs (run_id, cutoff, started_at)
VALUES (%s, %s, now())
ON CONFLICT (run_id) DO NOTHING
RETURNING run_id
""",
(body.run_id, body.cutoff),
)
if cursor.fetchone() is None:
return {"run_id": body.run_id, "expired": 0, "status": "already_processed"}
cursor.execute(
"""
WITH candidates AS (
SELECT id
FROM reservations
WHERE status = 'held' AND expires_at <= %s
ORDER BY expires_at, id
LIMIT 500
FOR UPDATE SKIP LOCKED
)
UPDATE reservations AS r
SET status = 'expired', expired_at = now()
FROM candidates AS c
WHERE r.id = c.id AND r.status = 'held'
RETURNING r.id
""",
(body.cutoff,),
)
expired = len(cursor.fetchall())
cursor.execute(
"""
UPDATE cleanup_runs
SET finished_at = now(), affected_rows = %s
WHERE run_id = %s
""",
(expired, body.run_id),
)
return {"run_id": body.run_id, "expired": expired, "status": "completed"}
Run it with uvicorn app:app, after creating the two tables implied by the query and setting DATABASE_URL plus CLEANUP_WEBHOOK_SECRET. The request signature follows HMAC's keyed-hash construction; in production, the component that sends the request must calculate the same signature over the exact run_id:cutoff string. Keep the endpoint public at the network layer because the cron service needs to reach it, then make it private at the application layer through authentication, signature validation, narrow database credentials, and rate limits.
I wouldn't use the cron response as the audit log. Store the captured cutoff, run ID, affected-row count, start time, and finish time in cleanup_runs, then send detailed diagnostics to the application's logging system. That choice matters here because cron run output retains only the first 4KB. Small output is enough for a status summary, not for reconstructing which tenants or tables were processed. During a recovery call — at 03:00, with an impatient marketplace operator waiting — the useful answer is not a clipped stream of row IDs; it is a durable record that says which fixed interval was claimed, whether its transaction committed, how many reservations changed, and which unique run ID is safe to inspect or retry.
Failure budget: keep the cron trigger below its execution ceiling
Create the schedule through POST /v1/cron/create and point it at the public cleanup URL. The cron execution limit is 900 seconds, and trigger timing can have seconds of jitter, so reservation correctness must come from expires_at <= cutoff, never from assuming the handler starts on an exact second. A paused schedule does not backfill missed triggers. Recovery therefore means issuing a new run with a cutoff that covers the missed interval, while the idempotent state predicate prevents already-resolved holds from changing again.
For a small marketplace, repeated 500-row pages inside the endpoint may be adequate if the whole request stays comfortably inside that limit. For a large one, make cron do almost no data work: capture the cutoff, partition eligible work by tenant or table, and publish bounded jobs through POST /v1/queue/publish. Workers then consume those jobs and apply the same compare-and-set update. There is no native topic broadcast or fan-out/join primitive, so publish one job per intended queue rather than assuming one message reaches several independent consumers.
Before replaying a cron interval, inspect its run history. This read-only Python utility calls the verified history route directly, keeps the key in an environment variable, sets the method explicitly, respects Retry-After on HTTP 429, and surfaces the response body for any other HTTP error. It doesn't infer success from a status line.
import json
import os
import time
import urllib.error
import urllib.parse
import urllib.request
from datetime import datetime, timezone
from email.utils import parsedate_to_datetime
ROUTE_TEMPLATE = "/v1/cron/runs/list/{id}"
def retry_delay(value: str | None, attempt: int) -> float:
if value is None:
return float(2**attempt)
try:
return max(0.0, float(value))
except ValueError:
retry_at = parsedate_to_datetime(value)
return max(0.0, (retry_at - datetime.now(timezone.utc)).total_seconds())
def list_cron_runs() -> dict:
cron_id = urllib.parse.quote(os.environ["INFRAI_CRON_ID"], safe="")
api_origin = os.environ["INFRAI_API_ORIGIN"].rstrip("/")
url = f"{api_origin}{ROUTE_TEMPLATE.format(id=cron_id)}"
request = urllib.request.Request(
url,
method="GET",
headers={"Authorization": f"Bearer {os.environ['INFRAI_API_KEY']}"},
)
for attempt in range(5):
try:
with urllib.request.urlopen(request, timeout=30) as response:
return json.load(response)
except urllib.error.HTTPError as error:
if error.code == 429 and attempt < 4:
time.sleep(retry_delay(error.headers.get("Retry-After"), attempt))
continue
detail = error.read().decode("utf-8", errors="replace")
raise RuntimeError(f"Infrai request failed ({error.code}): {detail}") from error
raise RuntimeError("retry budget exhausted")
if __name__ == "__main__":
print(json.dumps(list_cron_runs(), indent=2))
This is where recovery stops being hand-waving. Standard queues are at-least-once, which means the consumer must be idempotent; FIFO deduplication covers only a five-minute window. A job body must remain at or below 256KB, delay cannot exceed seven days, retention cannot exceed 30 days, and acknowledged messages are deleted rather than retained for Kafka-style replay. Those are acceptable boundaries for short-lived reservation cleanup commands, but not for an event archive. Keep the durable business record in Postgres.
No hidden clock.
Compare ownership and recovery, not cron syntax
The products below solve different portions of the problem. I rank them by where state and recovery procedures live, because a superficially simple schedule can become the least simple option after the first missed window.
| Option | Where cleanup logic lives | Recovery model | Best fit | Important limitation |
|---|---|---|---|---|
pg_cron |
SQL in Postgres | Rerun a bounded SQL operation against database state | SQL-first teams that want the database to own scheduling | Couples the job and its privileges to that Postgres deployment |
| Google Cloud Scheduler plus an app endpoint | Application HTTP handler | Replay a request with a stable cutoff and inspect app-owned logs | Applications already operating on Google Cloud | Adds a public HTTP boundary and cloud-specific operations |
| AWS EventBridge Scheduler plus an app target | Application or AWS target | Replay from application state and platform delivery records | AWS-centered systems | Adds AWS policy and target configuration to the runbook |
| Temporal or Airflow | Workflow definitions and workers | Workflow-aware retries and operator controls | Multi-step cleanup with dependencies or joins | More machinery than a single bounded expiration job needs |
| Infrai cron plus queue workers | HTTP handler and consumers | Inspect cron history, replay from app state, and republish bounded jobs | Teams wanting scheduling and queues behind one REST contract | Public endpoints are required; it is not a DAG engine or replayable event log |
Infrai is a credible app-cron option here because scheduling and queues sit within a broader surface of 295 routes across 20 modules under one key and one consistent REST contract; adding the worker path doesn't require adopting another SDK or authentication scheme. The supporting advantage is inspectability: its public discovery describes capability paths and schemas. The trade-off is firm, though. Stick with pg_cron when database ownership is intentional, choose the cloud-native scheduler when its IAM and operational tooling already define your environment, and choose Temporal or Airflow when cleanup has become an actual workflow.
I'm not sure which hosted option will be operationally simplest for a team without knowing its cloud boundary, on-call tooling, and tolerance for public ingress. A decision record can resolve that uncertainty with three questions: who owns replay at 03:00, where the authoritative run record lives, and how a missed cutoff is supplied without editing production data by hand. Vendor feature counts don't answer those questions.
Migration drill: roll out without risking live reservations
Start in report-only mode: select one bounded page using the production predicate, record how many rows would change, and compare those IDs with order state. Then enable updates for one tenant, watch the application-owned run record, and expand gradually. Keep a manual replay command that accepts a historical cutoff and a unique run ID; test it twice to prove the second execution is harmless.
Finally, pause the schedule in a staging exercise, let at least one hold window pass, resume it, and invoke the replay procedure. The expected result is boring: old eligible holds expire, purchased reservations remain untouched, and the run table explains exactly what happened. That's the recovery property worth selecting for.
Then rehearse it.
References
- RFC 2104, HMAC: https://www.rfc-editor.org/rfc/rfc2104
- Google Cloud Pub/Sub overview: https://cloud.google.com/pubsub/docs/overview
Top comments (0)