Pruning 40M Postgres rows on a schedule with a self-feeding queue

A chunked cleanup pattern for large Postgres tables: the Infrai cron tick enqueues one chunk, and each worker re-enqueues the next until the table is clean.

Deleting 40 million rows in one statement is how a nightly cleanup takes a production database down: a single transaction holds locks for minutes, the WAL balloons, replicas fall behind, and if anything times out you roll all of it back and start again tomorrow. The fix is boring and reliable — delete in bounded chunks, one chunk per message, and let an Infrai queue hold the work so the schedule never has to finish it in one go.

The pattern below has a property that’s easy to miss: the worker enqueues its own successor, so the tick doesn’t need to know how big the backlog is.

One statement, and why it bites

-- The version that pages someone at 03:40.
DELETE FROM events WHERE created_at < now() - interval '90 days';

-- The version that runs in 200ms and can be repeated.
DELETE FROM events
WHERE id IN (
  SELECT id FROM events
  WHERE created_at < now() - interval '90 days'
  ORDER BY id
  LIMIT 5000
)
RETURNING id;

The second form takes a small, predictable number of row locks and commits. Run it in a loop until it returns fewer than 5,000 rows and you’ve deleted the same data with none of the drama. Postgres will also thank you afterwards — a chunked delete lets autovacuum reclaim space as it goes instead of leaving one enormous dead-tuple pile behind.

Each iteration is independent. That’s what makes it a queue message.

The chunk descriptor

A message shouldn’t carry row ids; by the time a worker reads it the set has moved. It carries the rule for finding the next chunk:

export INFRAI_API_KEY="your_infrai_api_key"

curl -sS -X POST "https://api.infrai.cc/v1/queue/create" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"name":"row-prune","type":"standard","dlq":"row-prune-dlq"}'

curl -sS -X POST "https://api.infrai.cc/v1/queue/publish" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"queue":"row-prune","body":{"table":"events","before":"2026-04-27","batch":5000,"cursor":0}}'

{
  "ok": true,
  "data": {
    "message_id": "qmsg_gur1TSchsydPAXeYU69cd10P",
    "queue": "row-prune",
    "payload": { "table": "events", "before": "2026-04-27", "batch": 5000, "cursor": 0 },
    "status": "available",
    "delivery_count": 0,
    "published_at": "2026-07-26T01:27:54Z"
  }
}

A scheduled job with POST /v1/cron/create publishes exactly one of these per night, pointed at your own enqueue endpoint over task_type: "http_url". Read the field names off the cron reference rather than off a cron.list response — the request key for the target URL differs from the key you get back, and a job created from the wrong one never fires.

The worker that feeds itself

import process from "node:process";
import pg from "pg";

const KEY = process.env.INFRAI_API_KEY;
if (!KEY) throw new Error("INFRAI_API_KEY is missing");
const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const headers = { Authorization: `Bearer ${KEY}`, "Content-Type": "application/json" };

async function nextChunk() {
  const res = await fetch("https://api.infrai.cc/v1/queue/consume", {
    method: "POST",
    headers,
    body: JSON.stringify({ queue: "row-prune", max_messages: 1 }),
  });
  const out = await res.json();
  if (!out.ok) throw new Error(`consume: ${out.error.code}`);
  return out.data.items[0] ?? null;
}

async function pruneChunk(spec) {
  const { rowCount } = await db.query(
    `DELETE FROM ${spec.table}
       WHERE id IN (SELECT id FROM ${spec.table}
                    WHERE created_at < $1 ORDER BY id LIMIT $2)`,
    [spec.before, spec.batch],
  );
  return rowCount;
}

async function queueNext(spec, deleted) {
  const followUp = { queue: "row-prune", body: { ...spec, cursor: spec.cursor + deleted } };
  const res = await fetch("https://api.infrai.cc/v1/queue/publish", {
    method: "POST",
    headers,
    body: JSON.stringify(followUp),
  });
  const out = await res.json();
  if (!out.ok) throw new Error(`publish follow-up: ${out.error.code}`);
}

const message = await nextChunk();
if (!message) {
  console.log("nothing to prune");
} else {
  const spec = message.payload;
  const deleted = await pruneChunk(spec);
  if (deleted === spec.batch) await queueNext(spec, deleted);
  else console.log(`prune of ${spec.table} finished at cursor ${spec.cursor + deleted}`);
  const ack = await fetch("https://api.infrai.cc/v1/queue/ack", {
    method: "POST",
    headers,
    body: JSON.stringify({ queue: "row-prune", receipt_handle: message.message_id }),
  });
  const acked = await ack.json();
  if (!acked.data.acked) console.warn("ack was refused — the lease had probably expired");
  console.log(`deleted ${deleted} rows from ${spec.table}`);
}
await db.end();

Order matters in that block. Publish the successor before the ack, and a crash between the two costs you a duplicate chunk — which is harmless, because deleting rows that are already gone deletes nothing. Ack first and a crash loses the chain, and the table stays half-pruned until tomorrow. Prefer the harmless failure.

max_messages is capped at 10, and here 1 is deliberate: a chunk is a database transaction, and pulling ten of them into one worker just serialises them behind each other with a 300-second lease ticking.

Knowing when it’s done

curl -sS "https://api.infrai.cc/v1/queue/stats/row-prune" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}"

{
  "ok": true,
  "data": {
    "queue": "row-prune",
    "message_count": 1,
    "available_count": 1,
    "in_flight_count": 0,
    "delayed_count": 0,
    "dlq_count": 0,
    "oldest_message_age_seconds": 1
  }
}

An empty queue means the chain terminated. A non-zero dlq_count means a chunk failed three deliveries — statement timeout, deadlock, a table that no longer exists — and the chain broke there, so nothing after it was ever enqueued. That’s the failure mode to alert on; the dead-lettered message is readable by consuming row-prune-dlq directly, since the dedicated DLQ listing route currently returns an empty array.

Chunked delete isn’t always the right tool

If your table is already partitioned by month, dropping a partition is instant and beats every row-by-row scheme in this article — say so to yourself before building the queue. Celery is the better fit for a Python data stack, and SQS plus Lambda is natural inside AWS. What none of those give you is the second half of the task on the same credential: the same key that drains this queue writes the pruning report to storage, emails it, and records the exception when a chunk deadlocks.

Cost, and the caveats

Publishing is metered at $0.00002 per message, verified 2026-07-26; consume, ack and stats are free and rate-limited. A 40-million-row prune at 5,000 rows per chunk is 8,000 messages — $0.16 for the whole campaign, once. New accounts get $2 in credit, and these rates trend downward, so check the live number before you plan:

curl -sS "https://api.infrai.cc/v1/discovery" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  | jq '.data.capabilities[] | select(.id == "queue.publish") | .billing'

The limitations worth stating plainly: messages cap at 256 KB, so a chunk descriptor must stay a descriptor; retries are fixed at three deliveries with no backoff between them, which is unkind to a database that’s temporarily overloaded; and there’s no fairness between queues, so a runaway prune chain competes with your other consumers for worker time. If your cleanup has to hold a lock for an hour, no queue helps — that’s a schema problem, and partitioning is the answer.

References

• Infrai Message Queue API reference
• Infrai Cron API reference
• PostgreSQL: DELETE
• PostgreSQL: table partitioning