Deleting 50,000 rows inside a 60 second serverless function

We keep completed practice sessions for two years and then delete them. That promise is written on the privacy page, so a nightly job has to actually honour it.

The job runs as a Vercel cron hitting an API route with maxDuration = 60. Sixty seconds, hard stop. That single constraint shaped everything about how the deletes are written, and the first two versions of this job were wrong in ways that are worth separating, because one was a design mistake and one was a type coercion that made the whole thing a no-op.

An unbounded DELETE cannot fail gracefully

The obvious implementation:

await db.delete(gameSessionsTable).where(lt(gameSessionsTable.completed_at, twoYearsAgo));

This is fine right up until it is not. A single unbounded DELETE has no upper bound on how long it holds locks or how much WAL it generates. If the expiring set is ever large enough to exceed sixty seconds, the function is killed mid statement and the transaction rolls back.

Nothing is deleted. Tomorrow the set is one day larger, so it takes longer, so it fails again. The job gets monotonically slower and more certain to fail on every subsequent run, and there is no state in which it recovers by itself. A job that cannot make partial progress has a cliff, and past the cliff it is not "slow", it is permanently broken.

Batching converts it into incremental work. Each batch commits, so partial progress survives the timeout and tomorrow's run resumes where today's stopped:

const DELETE_BATCH_SIZE = 1_000;
const MAX_BATCHES_PER_RUN = 50;

Fifty thousand rows per run, worst case. If there are more, the remainder goes tomorrow, which is completely acceptable for a two year retention promise. The MAX_BATCHES_PER_RUN cap is the safety valve: without it, a bug that makes rowsDeleted always equal the batch size spins for the whole function lifetime.

Postgres has no DELETE ... LIMIT

The obvious way to bound a delete does not exist. DELETE ... LIMIT 1000 is not valid Postgres. You have to pick the rows in a subquery and delete exactly those:

const result = await db.execute(sql`
  DELETE FROM ${gameSessionsTable}
  WHERE ${gameSessionsTable.id} IN (
    SELECT ${gameSessionsTable.id}
    FROM ${gameSessionsTable}
    WHERE ${gameSessionsTable.completed_at} < ${twoYearsAgo.toISOString()}
    LIMIT ${DELETE_BATCH_SIZE}
  )
`);

Cascades still fire for dependent rows, which is the behaviour you want and worth confirming rather than assuming.

The loop terminates on a short batch, which is the only reliable signal that the set is exhausted:

const rowsDeleted = Number((result as unknown as { count?: number })?.count ?? 0);

deletedCount += rowsDeleted;
batches++;

if (rowsDeleted < DELETE_BATCH_SIZE) break;

The ?? 0 is not defensive noise. If the driver ever returns a shape we do not recognise, 0 terminates the loop on the next comparison. The alternative, NaN, makes NaN < 1000 false and spins the loop until the batch cap. Choose the fallback that fails toward stopping.

The thing that made all of this pointless

Here is the bug that mattered most, and it is three characters long.

The earlier version interpolated the cutoff as a JavaScript Date:

WHERE completed_at < ${twoYearsAgo}

A Date interpolated into a sql template is handed to postgres.js as a parameter. Under our pooled configuration with prepare: false, it reaches the wire serializer still a Date object, and throws:

Buffer.byteLength(...) received an instance of Date

The job failed on every single run. It had been failing for a long time, because the failure was caught, logged, and rethrown into a cron endpoint nobody was reading the output of. The retention promise was not being kept and nothing surfaced it.

The fix is .toISOString(). Pass the string, let Postgres do the comparison against a timestamptz column, and the parameter path never has to guess what a Date is.

The general lesson is not about postgres.js. It is that driver level type coercion is configuration dependent. That same Date works fine with prepared statements enabled. It works in a test against a non pooled connection. It fails in production, in a path nobody watches, in a job whose entire output is one log line.

Which is why the logging changed too:

// Progress goes through `logJob` rather than `logInfo`, because `logInfo` is
// development-only and this job deletes up to 50,000 rows a night. Without a
// production-visible line there is no way to answer "how much did last night's
// run remove?" after the fact.

Every message carries counts and batch state only, never row contents, so the detail is safe to keep in production. A destructive background job that logs nothing in production is a job you are trusting on faith.

Read the cutoff off the row, not off the clock

The session cleanup computes its own cutoff, because two years from completion is a fixed rule. The onboarding answers work differently, and the difference is a good pattern:

WHERE ${onboardingProfilesTable.expires_at} < ${now.toISOString()}

The deadline is written onto the row when the answers are saved. Which means the date shown to the user in their settings and the date actually enforced by the job are the same value, not two implementations of the same rule that can drift. It also means editing your answers correctly restarts the clock, for free, with no special handling in the job at all.

If a user can see a retention date, store that date. Do not recompute it in two places and hope.

The flag that was always true

The last thing to go was a piece of dead machinery:

// This used to be called `runDataRetentionIfNeeded` and returned { ran: boolean },
// but nothing ever set `ran` to false: no last-run timestamp was persisted
// anywhere, so the "already ran recently" branches in the route were unreachable
// and the endpoint's `skipped: true` response could never be produced.

A recency gate with nowhere durable to record the last run is not a gate. And it was not needed: the once per day guarantee comes from the cron schedule, and every delete is idempotent, so a second run in the same day simply finds nothing to remove.

Deleting the flag and renaming the function to runDataRetention removed more risk than it removed code, because the next reader no longer has to work out under what conditions the job declines to run. The answer was "never", and now the name says so.

Finally, the three cleanups are independent, so they run together rather than serially inside the sixty second budget:

const [sessionResult, expiredDeviceSessions, onboardingResult] = await Promise.all([
  deleteOldSessions(),
  cleanupExpiredDeviceSessions(),
  deleteExpiredOnboardingProfiles(),
]);

The promise this exists to keep

All of this machinery exists to honour one paragraph of plain English. The retention periods, what is kept, for how long, and what happens when you delete your account, are on the privacy page. Go and read your own equivalent page, then go and find the job that enforces it, and check when it last succeeded rather than when it last ran.

Ours had been running, and failing, every night for months.