Templates / Expired session cleanup

Purging expired sessions

Session tables are write-heavy and almost never pruned. Left alone they become the largest table in the database, and the cost lands on queries that have nothing to do with sessions.

The recipe

Request: POST https://{{domain}}/api/admin/sessions/purge
Schedule: At 04:15 AM (15 4 * * *)
Headers: Authorization: Bearer {{secret}}encrypted
Timeout: 30s
Attempts: 3
Counts as success: 2xx

You supply

Your domain: example.com
Host only — no https:// and no trailing slash.
Shared secret: s3cr3t-valuestored encrypted
Whatever your endpoint checks the caller against.

Why this job exists

Every sign-in writes a row. Most session stores write on refresh too, and plenty write on every request. What almost none of them do is delete — expiry is enforced by checking a timestamp at read time, so an expired session is functionally gone while still occupying a row, an index entry and a page in the buffer pool.

For a year or two nothing happens. Then the table is tens of millions of rows, its indexes no longer fit in memory, and the effects show up somewhere unrelated: backups take hours, a migration locks the table for minutes, autovacuum runs constantly, and the working set of every other query is being evicted by session data nobody will ever read.

Deleting rows that are already meaningless is one of the highest-value maintenance jobs available, and one of the least likely to have been set up.

Setting it up

Delete in bounded batches rather than one statement. A single DELETE covering millions of rows takes a long lock, produces an enormous transaction, and on the first run will often be the slowest query your database has ever executed:

Batched deletion with a budget

const deadline = Date.now() + 60_000;
let total = 0;

for (;;) {
  const { count } = await db.query(
    `delete from sessions
      where id in (
        select id from sessions where expires_at < now() limit 5000
      )`,
  );

  total += count;
  if (count === 0 || Date.now() > deadline) break;
}

return Response.json({ deleted: total });

Returning the count makes the first run legible: it will be enormous, and every run after it should settle to roughly a day of expiries. A number that keeps climbing means sessions are being created faster than they expire, which is worth understanding on its own.

Checking it actually works

Check the table size before and after the first run — this is the one time the effect is dramatic.
Watch the deleted count settle to a steady daily figure. A count of zero means the query is not matching anything, usually a column name or a timezone problem.
On Postgres, confirm autovacuum reclaims the space, or run VACUUM once after the first large purge.

Where it goes wrong

One enormous DELETE

The first run has years of accumulation to remove. Unbatched, it can lock the table for minutes, blow out the write-ahead log, and take the application down. Batch it, and let the first few nights catch up gradually.

Deleting sessions that are still valid

Rolling sessions extend their expiry on use; deleting on created_at rather than expires_at signs out active users. Test the predicate as a SELECT before it is a DELETE.

Forgetting the other tables

Password reset tokens, email verification tokens, magic links, API nonces and rate-limit counters accumulate the same way. Cover them in the same endpoint while you are here.

Questions

Does this sign anyone out?

Not if the predicate is right — these rows are already expired and would be rejected at read time. Nothing user-visible changes, which is exactly why nobody sets it up.

My session store is Redis, not SQL.

Then you likely do not need this: Redis expires keys itself. Check that a TTL is actually set on every key, because a session written without one lives forever.

Why 04:15 rather than a round hour?

Maintenance jobs that all start at 04:00 compete with each other and with backups. Offsetting by a few minutes spreads the load, and it is free to do.

Running it here

This is a preset in the product, not an illustration. Pick it in the dashboard, fill in the 2 values above, and the job is created, scheduled and enabled — with retries, a record of every run showing status, latency and response, and an alert the first time one fails. Secret values are encrypted at rest and never rendered back.

The free plan runs 3 jobs hourly and needs no card. Where a recipe wants a finer cadence than your plan allows, it is slowed to the fastest schedule you are permitted rather than rejected.

Create a free account →Check a cron expression →