Postgres Media Retention: 3-State Scheduled API Cleanup with Queue Backpressure

The best option for scheduled media cleanup is a thin scheduler feeding a durable queue, with a Postgres ledger controlling idempotency and worker admission. Webhooks can accelerate discovery, but they shouldn't own deletion correctness. That split keeps a rate-limited worker pool busy without letting retries turn one expired asset into several destructive API calls.

Start with the bill. For N expired assets, the dominant variable term is usually work attempted, not cron ticks: N * A outbound cleanup attempts, where A is the mean number of attempts per asset. Retaining a payload of B bytes for D days adds N * B * D byte-days, while retaining a compact ledger adds only the identifiers, timestamps, state, and attempt metadata needed to prove what happened. The design change that matters is therefore reducing duplicate attempts before tuning the schedule. Keep the ledger; stop keeping full media payloads after confirmed deletion. The catch is that a later investigation can reconstruct control flow, but not the deleted object itself.

That's the answer. The rest is failure accounting.

What does cleanup actually cost to retain?

A cleanup system has at least four cost buckets: discovery scans, queued storage, outbound API attempts, and retained evidence. Teams often stare at the schedule frequency because it's visible. A cron expression running every minute feels expensive, yet an indexed query that claims a bounded batch can be a small part of the work. A retry fan-out is different. If two schedulers discover the same 80,000 expired rows and each publishes them, the downstream attempt count can approach twice the intended count before rate limiting even starts.

Use a symbolic model before inserting provider prices:

This exposes the uncomfortable part: a cheap queue doesn't rescue an expensive retry policy. A worker that immediately retries HTTP 429 responses can consume its own capacity, extend queue age, and repeat calls that were never eligible to run. It can also crowd out first attempts. Keep one attempt counter for transport delivery and another for the cleanup operation; otherwise a redelivery after a worker exit looks identical to a deliberate API retry.

Retention needs an explicit purpose. Keep asset_id, the upstream idempotency key, state, attempt count, not_before, lease owner, and outcome timestamp long enough for the team's audit and dispute window. Don't keep the original media merely because the worker once needed its URL. For deletion work, less retained content reduces the material available during an incident — but it also means recovery must come from a backup or the system of record, not the cleanup queue. Pick that trade-off before launch, especially when the same media can appear in email or SMS notifications and deletion timing affects consent or suppression evidence.

How should scheduled cleanup API calls use queue backpressure?

Cron should discover eligible work and stop. It shouldn't loop over remote calls. The queue should absorb bursts and lease work, while a shared admission controller decides when another cleanup call may start. The Postgres ledger is the authority for state transitions: pending, in_flight, and done. Those three states are enough for the core path; retry timing and terminal failure can be represented with columns rather than an ever-growing collection of ambiguous statuses.

Claim a small batch in one transaction. PostgreSQL documents SKIP LOCKED as producing an inconsistent view that can be useful for multiple consumers accessing a queue-like table. That is precisely the boundary here: it is appropriate for distributing claims, not for a report that needs a consistent snapshot.

CLAIM_SQL = """
WITH claimed AS (
    SELECT asset_id
    FROM cleanup_ledger
    WHERE state = 'pending'
      AND not_before <= CURRENT_TIMESTAMP
    ORDER BY not_before, asset_id
    FOR UPDATE SKIP LOCKED
    LIMIT %(batch_size)s
)
UPDATE cleanup_ledger AS ledger
SET state = 'in_flight',
    lease_owner = %(worker_id)s,
    lease_expires_at = CURRENT_TIMESTAMP + %(lease_seconds)s * INTERVAL '1 second'
FROM claimed
WHERE ledger.asset_id = claimed.asset_id
RETURNING ledger.asset_id, ledger.idempotency_key, ledger.attempt_count;
"""

The transaction that runs this statement should commit before workers make network calls. Holding database locks across an outbound request couples database concurrency to remote latency, which is exactly the wrong backpressure boundary. A worker receives only a compact command: asset identifier, operation identifier, and lease metadata. It then asks the admission controller for a token. No token, no call.

Keep it boring.

Admission should account for both rate and concurrency. A token bucket can pace starts, while a semaphore caps simultaneous requests. If the remote side returns 429 with retry guidance, move the ledger row back to pending, set not_before from that guidance, and release the worker slot. Don't sleep inside the worker while holding the message or a database lease; delayed work belongs in durable state. Add bounded random jitter when many rows receive the same retry time so they don't all become eligible on the same millisecond.

Webhooks fit before this pipeline, not around it. A webhook indicating that an asset changed can upsert a candidate ledger row and shorten discovery latency. The periodic scan remains the reconciliation path for missed, delayed, or duplicated delivery. Both inputs must converge on the same unique operation key. If either path can directly issue deletion, backpressure becomes local to that path and the global limit is fiction.

Retry safety needs two identities

An asset identifier names the object. An operation identifier names one intended deletion under a particular retention decision. They aren't interchangeable. An asset may be restored and later expire again, so permanent deduplication on asset_id alone can suppress legitimate future cleanup. Derive or assign an operation key when the retention decision is recorded, enforce uniqueness on that key, and send it with every repeated attempt when the target API accepts an idempotency key.

The worker must classify outcomes before changing state. A confirmed success moves to done. A rate-limit response schedules a later attempt. A transport outcome with uncertain delivery remains retryable under the same operation key because the caller doesn't know whether the remote side acted. A permanent policy rejection moves out of the active queue for review; retrying it faster cannot make the policy true. This is where compliance and deliverability habits help: a suppression decision, an OTP expiry, and a media deletion all need a durable distinction between “not attempted,” “attempted with unknown outcome,” and “confirmed.” Collapsing those categories creates confident dashboards and weak evidence.

Here is the worker boundary in Python. The queue, limiter, ledger, and remote client are injected interfaces, so the control flow doesn't depend on a commercial service.

from dataclasses import dataclass
from datetime import datetime, timedelta, timezone
from enum import Enum
from random import uniform


class ResultKind(Enum):
    CONFIRMED = "confirmed"
    RATE_LIMITED = "rate_limited"
    UNCERTAIN = "uncertain"
    REJECTED = "rejected"


@dataclass(frozen=True)
class CleanupJob:
    asset_id: str
    operation_key: str
    lease_owner: str


async def process(job, limiter, ledger, cleanup_client):
    await limiter.acquire()
    result = await cleanup_client.delete(
        asset_id=job.asset_id,
        idempotency_key=job.operation_key,
    )

    if result.kind is ResultKind.CONFIRMED:
        await ledger.complete(job.operation_key, job.lease_owner)
        return

    if result.kind is ResultKind.REJECTED:
        await ledger.reject(job.operation_key, result.reason)
        return

    delay = result.retry_after or timedelta(seconds=30)
    if result.kind is ResultKind.UNCERTAIN:
        delay = timedelta(seconds=10)

    retry_at = datetime.now(timezone.utc) + delay + timedelta(
        seconds=uniform(0, 3)
    )
    await ledger.reschedule(job.operation_key, retry_at)

The 30-second and 10-second values above are policy examples, not universal defaults. Tune them from the target API's published limits and observed completion latency. I'm not sure any fixed retry interval survives a large media purge unchanged; queue-age and outcome metrics are what resolve that uncertainty. The important invariant is stable: every retry carries the same operation key, and only the ledger decides whether the operation is still eligible.

Lease expiry adds one more edge. If a worker disappears after the remote call but before recording success, another worker will eventually reclaim the row. The repeated call must use the same idempotency key. Set the lease longer than the normal request deadline, renew it only while genuine work is active, and require the lease owner when completing the row. That last comparison prevents an old worker from overwriting a newer claim.

Operate queue age, not just queue depth

Depth is a count; it doesn't say whether the oldest deletion is ten seconds or ten hours late. Track age of the oldest eligible item, age of the oldest leased item, attempt count by outcome class, claim rate, admitted request rate, and completion rate. Alert on a growing gap between eligibility and completion. A flat depth can still hide starvation if new work finishes while one poisonous item cycles forever.

Deployment should be reversible at the admission layer. Start with a low concurrency cap, observe 429 frequency and completion latency, then raise the cap in controlled steps. During a large retention boundary, pause new admissions without deleting ledger rows. The schedule can continue discovering work because discovery is no longer allowed to flood the remote API. This separation also makes testing sharper: property tests can assert that one operation key never has two simultaneous leases, integration tests can kill a worker between call and commit, and load tests can vary arrival rate independently from permitted departure rate.

There are limits. This pattern is not suitable when every cleanup must finish in a single database transaction with the source row; a remote API cannot participate in that local atomic boundary, so use an outbox plus reconciliation or keep the deletion local. Stick with a database-only worker when volume is modest and one operational store can meet the recovery requirement. Use a dedicated broker when backlog size, delayed delivery, or independent scaling would make the primary database a poor queue host. Webhook-only cleanup is reasonable only when the sender provides a delivery contract that meets the retention requirement and reconciliation is unnecessary — a narrow case, and one that should be documented rather than assumed.

The deliberate retention decision remains: preserve the small operation ledger and metrics, expire bulky request material, and rely on the system of record for restoration. That lowers stored data volume, but post-incident inspection loses the exact deleted payload. For regulated or disputed media, the required evidence window may justify encrypted snapshots with separate access controls. Your mileage may vary because that choice is policy, not queue mechanics.

Decision rule

Choose the queue-plus-ledger design when arrivals can burst, the API enforces a shared rate limit, and retries must be idempotent. Let cron and webhooks propose work; let neither execute it. Use Postgres claims when the team can operate the ledger at the expected backlog and contention, and move queue transport to a dedicated broker when those demands need an independent failure or scaling boundary.

Don't choose by cron syntax.

Choose by the location of durable truth, the scope of the rate limiter, and the evidence you need after the media is gone. A system that can answer “which operation was attempted, under which lease, with which key, and when may it run again?” can drain a constrained worker pool predictably. One that can only answer “the job ran” will eventually confuse delivery with completion.

References

• Celery introduction documentation
• PostgreSQL SELECT documentation

Further reading

The Celery introduction is useful background on task-queue vocabulary and worker distribution. PostgreSQL's SELECT reference defines the locking behavior behind the claim query, including the trade-off attached to SKIP LOCKED.

• https://docs.celeryq.dev/en/stable/getting-started/introduction.html
• https://www.postgresql.org/docs/current/sql-select.html