Pruning Expired Push Subscriptions From Your Database

Deleting a dead subscription is easy; deleting it without destroying your consent audit trail, your unsubscribe analytics or your database’s write throughput is the actual problem.

Quick Answer

Never hard-delete on the first failure. Classify the push service response, mark the row soft-deleted with a timestamp, and let a separate scheduled job hard-delete tombstones once they clear a retention window. Do the deletion in keyset-paginated batches of a few thousand rows with a pause between them, so no single statement holds a lock long enough to stall dispatch or blow up replica lag.

404 and 410 Carry Different Guarantees

RFC 8030 gives 410 Gone a specific meaning for push: the subscription no longer exists and will never exist again at that URL. The push service is asserting a fact about its own state, and no retry can change it. The correct handling at volume — batching the revocations, keeping them out of the retry path — is covered in handling 410 Gone responses at scale.

404 Not Found asserts nothing so strong. In practice it appears in three situations: the registration really is gone but the service returns 404 instead of 410 (Mozilla autopush has historically done this for some record states), a request landed on a node that had not yet replicated the registration, or a malformed path made the URL unroutable. The first is permanent, the second is transient, the third is your bug. Because you cannot distinguish them from a single response, 404 must be a vote rather than a verdict. Three consecutive 404s across separate dispatch attempts, ideally separated by hours, is a reasonable threshold. Any 2xx in between resets the counter to zero.

The failure mode worth guarding against is treating 429 or 503 as evidence of a dead endpoint. During a push service incident, every request fails at once. A pruning rule that keys on “the last send failed” will empty a healthy subscriber table in a single evening. Rate limiting and server errors belong entirely to the retry and backoff layer and must never touch the lifecycle columns described in the subscription storage and lifecycle guide.

Soft Delete First, Hard Delete Later

A hard delete on the dispatch path is tempting because it is one statement. It is also irreversible, it happens inside a latency-sensitive request, and it destroys the only record that the subscription ever existed.

Soft deletion means setting deleted_at and removing the row from every send query, while leaving the tombstone in place. Partial indexes with WHERE deleted_at IS NULL keep the working set the same size it would have been after a hard delete, so there is no query-performance argument for deleting immediately.

What an Immediate Hard Delete Actually Costs

The consent audit trail disappears. If a regulator or a customer asks when a person opted in and when the subscription ended, a deleted row answers neither question. The tombstone — endpoint hash, user ID, created_at, revoked_at, and nothing else — answers both while holding no credential material. The record-keeping obligations are set out in more detail in GDPR-compliant push unsubscribe logging.

Unsubscribe analytics become uncomputable. Churn rate needs a denominator. If revoked rows vanish the instant they fail, your subscriber count only ever reflects survivors, and week-over-week churn silently reads as zero. You also lose the ability to attribute churn to a campaign: the question “did Tuesday’s send cause the revocations?” requires knowing that a row existed on Monday and was revoked on Tuesday.

Re-subscription looks like acquisition. A user who clears site data and opts back in the next day produces a fresh endpoint. With tombstones you can see that the same user_id had a subscription two days ago and label it a reconnection. Without them, every returning user inflates your new-subscriber metric.

Cascades fire too early. Delivery ledgers, preference rows and campaign membership tables usually reference the subscription. A hard delete either cascades and destroys delivery history, or it fails on a foreign key inside the dispatch worker — neither is a good outcome at three in the morning.

Batched Pruning That Does Not Lock the Table

The naive purge is a single DELETE ... WHERE deleted_at < now() - interval '90 days'. On a table with two million tombstones this acquires row locks on every matched row, holds them for the whole statement, generates one enormous WAL record, and blocks autovacuum from reclaiming anything until it commits. Replica lag spikes, dispatch queries queue behind it, and the whole thing rolls back if it times out — leaving you exactly where you started, only slower.

Batch it with keyset pagination on the primary key. Each batch is its own transaction, so locks are released every few milliseconds.

-- One batch. $1 = last id from the previous batch, $2 = retention cutoff.
WITH doomed AS (
  SELECT id
  FROM push_subscription
  WHERE deleted_at IS NOT NULL
    AND deleted_at < $2
    AND id > $1
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
DELETE FROM push_subscription s
USING doomed d
WHERE s.id = d.id
RETURNING s.id;

FOR UPDATE SKIP LOCKED means a dispatch worker holding a row lock never blocks the purge, and the purge never blocks the dispatcher. The batch simply skips that row and picks it up on the next pass.

The driver is a loop with three safety valves: a keyset cursor so no batch re-scans the previous one, a pause so autovacuum and replication get airtime, and a lag check that stops the job rather than degrading the read replicas.

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const BATCH = Number(process.env.PRUNE_BATCH_ROWS ?? 5000);
const PAUSE_MS = Number(process.env.PRUNE_PAUSE_MS ?? 100);
const MAX_LAG_BYTES = 64 * 1024 * 1024;

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

async function replicaLagBytes(client) {
  const { rows } = await client.query(
    `SELECT coalesce(max(pg_wal_lsn_diff(sent_lsn, replay_lsn)), 0)::bigint AS lag
     FROM pg_stat_replication`
  );
  return Number(rows[0].lag);
}

export async function prunePass({ retentionDays = 90, budgetMs = 240000 } = {}) {
  const deadline = Date.now() + budgetMs;
  const cutoff = new Date(Date.now() - retentionDays * 86400000);
  let cursor = '00000000-0000-0000-0000-000000000000';
  let removed = 0;

  const client = await pool.connect();
  try {
    while (Date.now() < deadline) {
      if (await replicaLagBytes(client) > MAX_LAG_BYTES) {
        await sleep(5000);
        continue;
      }
      const { rows } = await client.query(PRUNE_BATCH_SQL, [cursor, cutoff]);
      if (rows.length === 0) break;
      cursor = rows[rows.length - 1].id;
      removed += rows.length;
      await sleep(PAUSE_MS);
    }
  } finally {
    client.release();
  }
  return { removed, resumeCursor: cursor, exhausted: removed < BATCH };
}

Note the returned resumeCursor. A purge that runs on a four-minute budget inside a five-minute cron slot will not finish a large backlog in one pass, and restarting from the beginning every time means the tail never gets deleted. Persist the cursor.

MySQL has no RETURNING, so run a SELECT ... FOR UPDATE SKIP LOCKED to collect IDs, then DELETE ... WHERE id IN (...) in the same transaction. Keep batches smaller — 1000 rows — because InnoDB gap locks are wider than PostgreSQL’s row locks.

Idempotency When the Endpoint Comes Back

Endpoints reappear more often than intuition suggests. A user disables notifications, changes their mind an hour later, and the browser hands out a subscription whose endpoint string is byte-identical to the one you just pruned. Push services reuse registration IDs within a session more readily than they mint new ones.

Three rules make this safe.

1. Prune by endpoint hash, never by row ID captured earlier. A worker that queued “delete row 4471” at 09:00 and executes it at 09:05 may delete a row that was resurrected at 09:03. Delete conditionally: WHERE endpoint_hash = $1 AND revoked_at IS NOT NULL AND revoked_at < $2.
2. Make the resurrection path an upsert, not an insert. The ON CONFLICT (endpoint_hash) DO UPDATE branch must clear deleted_at, clear revoked_at, and zero the failure counter. If your registration handler inserts blindly, a returning endpoint raises a unique violation against a tombstone and the user silently fails to re-subscribe.
3. Keep the tombstone’s created_at. Resurrecting a row preserves the original subscription date, which is what makes the reconnection visible in analytics. Overwriting it turns a returning user into a new one.

Rule 2 is where most implementations break, and the symptom is distinctive: registration returns 500 only for users who previously unsubscribed, and only until the purge job finally removes the tombstone.

Metrics to Watch

Alert on the prune rate first. It is the metric that catches a classification bug before it has deleted a meaningful fraction of your list, and it is cheap to compute from the same counters the dispatcher already emits.

Gotchas & Edge Cases

• A purge job that also handles staleness will double-count. Keep “mark expired” and “hard delete tombstones” as separate jobs with separate schedules and separate metrics; merging them makes the prune rate impossible to interpret.
• Foreign keys from the delivery ledger will block the delete. Use ON DELETE SET NULL on the ledger side and retain the endpoint hash there, so delivery history survives the subscription.
• autovacuum will not reclaim space during a long-running read. An analytics query holding a snapshot for twenty minutes pins every dead tuple the purge created. Check pg_stat_activity for old transactions before blaming the job.
• Deleting a row does not unsubscribe the browser. The client still holds a live PushSubscription and will re-register it on the next visit. If the intent is genuine removal, the client must call unsubscribe() as well.
• A vendor outage can look like mass revocation. Add a circuit breaker: if terminal classifications exceed a threshold per minute, stop pruning and page someone. It is always cheaper to prune late than to prune wrongly.

Related

• Deduplicating push subscriptions across devices — why the same user keeps many rows and how the unique constraint interacts with pruning.
• Handling 410 Gone responses at scale — the dispatch-side routing that feeds this job.
• Retry Logic & Backoff Strategies — where 429 and 5xx responses go instead of the prune queue.

Back to Push Subscription Storage & Lifecycle

FAQ