Scheduled E-Commerce Cleanup: PostgreSQL Queue Backpressure for Rate-Limited API Delivery

A scheduled e-commerce cleanup that calls a rate-limited API must run outside the storefront request, but moving it to cron does not prove that every deletion survives crashes, duplicate execution, or upstream throttling.

Short answer: use cron only to release due cleanup records into a durable PostgreSQL-backed queue, let workers pace API calls, and treat webhooks as hints rather than the owner of deletion state. This gives the system explicit at-least-once delivery, so each cleanup operation must also be idempotent at the boundary.

That answer is deliberately narrower than “add a queue.” The queue is a deletion ledger with leases, attempt state, and a stable operation key. Backpressure belongs where work is claimed, not in the scheduler and not in an incoming webhook handler.

Delivery guarantees start at the remote commit gap

The system of record contains one row per intended cleanup, keyed by the resource and cleanup policy version. A row moves from pending to leased, then to succeeded or a terminal state chosen by policy. A lease has an expiry time rather than a permanent “processing” flag; otherwise, a worker that dies after claiming a row can strand it forever. The scheduler advances eligibility by time. It does not perform remote deletion.

The delivery contract is at least once, with idempotent effects. Exactly-once remote execution is not available merely because a queue has acknowledged a message: the worker can complete the API call and crash before committing success locally. On restart, the call is repeated. The remote cleanup contract therefore needs a stable idempotency key, or deletion semantics under which repeating the same operation has the same accepted result. If the API offers neither, the ledger can prevent many duplicates but cannot close that final ambiguity.

Keep the failure boundary visible. The local transaction can atomically claim local rows. It cannot atomically commit an HTTP response in the remote service. That gap is where duplicate delivery lives, and naming it is more useful than promising “exactly once.”

For an e-commerce example, suppose a merchant policy makes an export artifact eligible at due_at. The row should retain the tenant, object identifier, policy version, attempt count, next eligible time, lease owner, lease expiry, and last classified outcome. Do not put raw customer data into retry diagnostics. Cleanup systems tend to outlive the objects they remove, which makes their metadata a retention concern of its own.

The invariants are small enough to test directly:

• no row is claimed before due_at or next_attempt_at;
• one active lease owns a row at a time, while an expired lease is reclaimable;
• a rate-limit response delays new claims for the affected scope;
• success is committed only after the remote result meets the documented cleanup contract;
• permanent failures are terminal and inspectable, rather than retried forever.

That last distinction matters. Authentication failure, a malformed object identifier, and a rate-limit response do not belong in one generic retry loop. A retry counter without error classification is just a slower way to lose information.

How should cron and a queue apply backpressure to rate-limited cleanup API calls?

Cron should make work eligible in bounded batches and exit. Workers should claim only as much work as their concurrency and rate budget permit. A shared limiter then gates outbound calls by the same scope the upstream service limits, which might be an account, tenant, credential, or endpoint. I'm not sure which scope applies without that API's contract, and guessing wrong can make a globally quiet system overload one merchant partition.

The queue absorbs the mismatch between a burst of due records and a slower deletion API. It does not erase capacity math. If eligible work arrives faster than the allowed service rate for long enough, backlog age grows without bound. Watch the age of the oldest eligible row, not just queue length, because ten unusually expensive deletions may be more urgent than ten thousand records that became due a minute ago.

Use the upstream's documented rate-limit signal to reduce admission. When the contract supplies a retry delay, store the resulting next_attempt_at and stop claiming work in that limiter scope until it is eligible again. Add bounded jitter only within the contract's permitted delay so that workers do not wake simultaneously. Don't let each worker maintain an isolated “requests per second” guess; horizontal scaling would multiply the effective rate.

The architectural options differ primarily in who owns durable state:

This decision doesn't require a separate message broker. A PostgreSQL table can be the queue when throughput fits the database's operating envelope. FOR UPDATE ... SKIP LOCKED allows competing consumers to skip rows currently locked by another transaction rather than waiting; PostgreSQL's documentation also cautions that it gives an inconsistent view and is appropriate for queue-like access, not general-purpose consistent reads. That is precisely the trade here: workers need disjoint claims, while audit and reconciliation queries should use ordinary reads.

A dedicated task queue becomes attractive when routing, worker isolation, or workload volume has outgrown the database queue. Celery, for example, documents a model in which clients add messages to a broker and workers monitor queues, and it supports multiple workers and brokers. That is evidence for the component shape, not a reason to delegate business truth to broker retention. The deletion ledger still answers which resources are due, which operation key was used, and which outcome was accepted.

Proving lease recovery with a PostgreSQL state machine

The claim transaction should be short. Never hold a database lock while waiting for the cleanup API; doing so converts remote latency into lock contention and makes a rate-limit pause consume database capacity. Instead, select eligible rows with row locks, mark them leased, commit, and only then make outbound calls.

Here is the critical shape in Python. The database adapter and HTTP client are injected interfaces, and classify_cleanup_response must implement the actual upstream contract rather than assume every API treats repeated deletion identically.

from dataclasses import dataclass
from datetime import datetime, timedelta, timezone


@dataclass(frozen=True)
class CleanupJob:
    job_id: str
    tenant_id: str
    object_id: str
    operation_key: str
    attempt_count: int


def claim_jobs(db, worker_id: str, batch_size: int) -> list[CleanupJob]:
    now = datetime.now(timezone.utc)
    lease_until = now + timedelta(minutes=2)

    with db.transaction() as tx:
        rows = tx.fetch_all(
            """
            SELECT job_id, tenant_id, object_id, operation_key, attempt_count
              FROM cleanup_jobs
             WHERE (
                       status = 'pending'
                       AND due_at <= %(now)s
                       AND next_attempt_at <= %(now)s
                   )
                OR (
                       status = 'leased'
                       AND lease_expires_at <= %(now)s
                   )
             ORDER BY due_at, job_id
             FOR UPDATE SKIP LOCKED
             LIMIT %(batch_size)s
            """,
            {"now": now, "batch_size": batch_size},
        )
        job_ids = [row["job_id"] for row in rows]
        if job_ids:
            tx.execute(
                """
                UPDATE cleanup_jobs
                   SET status = 'leased',
                       lease_owner = %(worker_id)s,
                       lease_expires_at = %(lease_until)s,
                       attempt_count = attempt_count + 1
                 WHERE job_id = ANY(%(job_ids)s)
                """,
                {
                    "worker_id": worker_id,
                    "lease_until": lease_until,
                    "job_ids": job_ids,
                },
            )

    return [CleanupJob(**row) for row in rows]

The claim query deserves property tests, not just a happy-path integration test. Generate rows around the exact due_at, next_attempt_at, and lease_expires_at boundaries; run concurrent claimers; assert that an unexpired lease is never returned and that an expired one eventually is. Then terminate a worker after the API response but before its success commit. The expected result is a repeated call with the same operation key.

Processing should classify outcomes before mutating the ledger. An accepted cleanup commits succeeded. A documented throttling response returns the row to pending with a future next_attempt_at and also closes admission for its limiter scope. A permanent contract failure enters a terminal state for review. Network uncertainty leaves the outcome unknown, so the lease expires and the same operation is tried again; this is the unavoidable duplicate window.

Keep it boring.

Failure drills are the operational acceptance test

Operationally, alert on oldest eligible age, claim throughput, completion throughput, lease expiry count, attempts by outcome class, and terminal failures. A rising lease-expiry count points to workers that cannot finish inside the lease or that are losing progress. A rising oldest age with low limiter utilization suggests local capacity trouble; the same age increase with a closed limiter indicates upstream capacity is controlling the system. Those two incidents need different responses.

The rejected design still has a valid operating envelope

I would reject a webhook-only design for retention cleanup. A webhook describes an event delivery contract, while retention is a time-based policy. Even if an event usually arrives when an order is cancelled or an export is replaced, a periodic reconciliation pass still has to find eligible records that produced no usable event. Once that reconciliation ledger exists, letting the webhook merely advance due_at or insert an idempotent job keeps ownership clear.

The catch is that the PostgreSQL queue is not suitable when cleanup traffic would compete materially with checkout writes, when queue retention creates unacceptable table churn, or when teams require routing and worker controls that a database-backed loop would have to rebuild. Move execution to a task broker in those cases, but keep the ledger and idempotency boundary. Conversely, stick with direct cron execution when the dataset is strictly bounded, the whole scan finishes comfortably inside its interval, and missing one run has no customer or compliance consequence.

Webhooks remain useful for latency. They can make an item eligible immediately instead of waiting for the next scan, but they should acknowledge only after durable local acceptance, shed load through the same admission rules, and never become the sole evidence that scheduled cleanup is complete.

The resulting decision rule is plain: choose the smallest execution mechanism that preserves the required delivery guarantee. For rate-limited e-commerce cleanup with durable obligations, that means a timed releaser, a durable ledger, leased workers, shared backpressure, and an idempotent remote effect. Everything else is an implementation choice.

Sources

• Celery introduction documentation: https://docs.celeryq.dev/en/stable/getting-started/introduction.html
• PostgreSQL SELECT documentation, including FOR UPDATE SKIP LOCKED: https://www.postgresql.org/docs/current/sql-select.html