Postgres: delete old rows in batches without locking

Most apps have a table that only ever grows: notifications, events, logs, sessions. Sooner or later you need to delete old rows in Postgres, and one big DELETE over millions of rows runs as a single long transaction that locks every row it touches while the app keeps writing to the same table. This is the data retention cron I set up for the notifications table on Buffdudes: a daily job that deletes in small batches, skips rows the app is busy with, and keeps the app's own queries away from the rows being removed.

Why two thirds of the table was invisible

The Buffdudes app shows the last 7 days of notifications. The unread badge counts only those 7 days as well. Anything older is never rendered on any screen.

The table kept growing anyway. About 115k notifications are written a week, including one for every follower when someone posts (that write path has its own story in replying first and fanning out notifications later). When I looked, production held 2.8M notification rows taking about 1 GB, and 1.86M of them were older than 8 days. Two thirds of the table was data no user would see again.

Rows nobody reads still cost something. They make every index on the table bigger, they slow down any query that ends up scanning, and they make backups and snapshots heavier.

Take the retention window from what the product shows

I start from the product. The app shows 7 days, so the job deletes rows older than 8 days. I like a day of margin past what any screen displays, so nothing at the edge of the window disappears while someone is scrolling through it.

Before choosing a window, check every reader of the table. A notifications table can quietly feed an analytics export, an admin screen or an email digest. If something reads older rows, the retention rule belongs to that feature too, and the conversation happens before the cron ships.

Postgres delete old rows in batches of 10,000

The job runs once a day and deletes in batches of 10,000. Each batch is its own short transaction, so locks are held for one batch at a time and the work commits as it goes. If the job dies halfway, everything it already deleted stays deleted and the next run picks up the rest.

The loop is small. Here is a minimal version with node-postgres (table and column names are illustrative):

import { Pool } from 'pg';

const pool = new Pool();
const BATCH_SIZE = 10_000;

export async function deleteOldNotifications(): Promise<number> {
  let deleted = 0;
  for (;;) {
    const { rowCount } = await pool.query(
      `DELETE FROM notifications
        WHERE id IN (
          SELECT id FROM notifications
           WHERE created_at < now() - interval '8 days'
           LIMIT $1
           FOR UPDATE SKIP LOCKED
        )`,
      [BATCH_SIZE],
    );
    deleted += rowCount ?? 0;
    if ((rowCount ?? 0) < BATCH_SIZE) return deleted;
  }
}

Each pool.query call outside an explicit transaction commits on its own, which is what keeps the batches independent. The inner SELECT needs an index that starts with the timestamp column, or every batch scans the table to find its 10,000 rows. If you are not sure your indexes match your queries, the method in auditing Postgres indexes on a production snapshot is how I check.

The batch size is a trade. Smaller batches mean shorter locks and more round trips; larger ones finish sooner and hold more locks per transaction. For a backlog of 1.86M rows, 10,000 works out to roughly 186 batches on the first run. After that, a week's 115k rows means around two batches a day.

Skip the rows the app has locked

The detail that matters most is FOR UPDATE SKIP LOCKED. When the app is in the middle of updating a notification, say a user is marking it as read at the moment the cron runs, that row is locked by the app's transaction. A plain delete would wait for that lock. Worse, while it waits it is holding locks on the other rows in its batch, so the app's next update on any of them waits on the cron.

With SKIP LOCKED, the inner select leaves locked rows out of the batch. The row being marked read is left alone and gets deleted by the next day's run. The cleanup job never queues behind a user, and a user never queues behind the cleanup job. The PostgreSQL docs on the locking clause cover the exact semantics, including how it behaves with LIMIT.

The trade is that a skipped row survives a little longer. For a retention job that is fine. A row that lives 9 days instead of 8 harms nobody.

Keep the app away from rows it never shows

The cron was half the fix. The other half was in the app.

"Mark all as read" used to update every unseen notification a user had, going back to the start of their account. Updating those older rows changed nothing anyone could see, since the app only shows 7 days. It also meant a single tap could touch thousands of rows, some of them in the cron's current batch, and wait on its row locks.

So "mark all as read" now touches only the last 7 days. The update got smaller, it matches what the user sees, and it stays out of the range the cleanup job is working in. I look for this pattern in any app with a retention job: if an endpoint writes to rows outside the window the UI shows, scope it to the window.

Schedule it for the quietest hour

The job runs at 08:00 UTC, which is the quietest hour for notifications on this app. To find yours, group the table's created_at by hour over a few weeks and pick the lowest. Running the cleanup when writes are lowest means fewer locked rows to skip and less competition for I/O.

Two more things I check on any cron like this:

• One run at a time. If a run can take longer than the gap between runs, two copies will fight over the same rows. I hit that with heavy cron jobs once already, as described in scaling a fitness platform to 60,000 users. A daily job with a backlog that drains in one run is unlikely to overlap, but a lock or a guard costs little.
• What happens to the disk. Deleted rows become dead tuples. Autovacuum marks that space as reusable, so new inserts fill it, but the table file does not shrink on its own. For a table that keeps receiving rows at a steady rate, that is what you want. VACUUM FULL would return the space to the operating system, but it rewrites the table under an exclusive lock, so I keep it out of normal operation. The routine vacuuming chapter explains why.

Should you partition the table instead?

Partitioning by time lets you drop a whole old partition, which is close to instant and leaves no dead rows behind, and it is the better fit for very high volume event tables. Crunchy Data's write-up on data retention with pg_partman walks through that setup, including the choice between detaching and dropping old partitions. For a table that takes about 115k rows a week, batched deletes are simpler: no partition maintenance, and no changes to primary keys or unique constraints. I would revisit partitioning if the daily delete ever became a noticeable load.

The short version

• Take the retention window from what the product shows, plus a margin, and check every reader of the table first.
• Delete in fixed-size batches, each in its own transaction, driven by an indexed timestamp.
• Use FOR UPDATE SKIP LOCKED so the cleanup never waits on the app and the app never waits on the cleanup.
• Scope the app's own bulk updates to the rows it displays, and schedule the job for your quietest hour.

If you have a table like this growing in your own database, it is the kind of backend work I take on through my services.