How to Schedule a Database Cleanup Cronjob

Why cleanup jobs exist

Every app accumulates data that's safe to delete: expired sessions, used password-reset tokens, soft-deleted rows past their grace period, old audit logs, orphaned uploads. Left alone, these bloat tables, slow queries, and inflate backups. A scheduled cleanup job keeps the database lean.

The danger is doing it badly — a single DELETE of a million rows can lock a table and take your app down. This tutorial covers writing a *safe* cleanup and scheduling it.

What to clean (and what not to)

Good candidates:

• Expired sessions / tokens.
• Soft-deleted records past retention (e.g., deleted_at < now() - 30 days).
• Old logs/events beyond your retention window.
• Orphaned rows (children whose parent is gone).

Be careful with:

• Anything under legal/compliance retention.
• Data referenced by foreign keys (delete children first or use cascades deliberately).
• Financial records (usually archive, don't delete).

Always prefer a dry run that counts what *would* be deleted before you delete it.

Delete in batches, not all at once

The golden rule: never delete a huge range in one statement. Batch it so each transaction is small, locks are brief, and the job is interruptible.

-- Postgres: delete in batches of 5000
DELETE FROM sessions
WHERE id IN (
  SELECT id FROM sessions
  WHERE expires_at < now()
  ORDER BY id
  LIMIT 5000
);

Run this repeatedly until zero rows are affected. In a script:

// cleanup.js
import { pool } from "./db.js";

async function cleanupSessions() {
  let total = 0;
  for (;;) {
    const { rowCount } = await pool.query(`
      DELETE FROM sessions
      WHERE id IN (
        SELECT id FROM sessions
        WHERE expires_at < now()
        ORDER BY id LIMIT 5000
      )
    `);
    total += rowCount;
    if (rowCount === 0) break;
    // small pause so we don't hammer the DB
    await new Promise((r) => setTimeout(r, 200));
  }
  console.log(`deleted ${total} expired sessions`);
}

cleanupSessions().then(() => process.exit(0)).catch((e) => {
  console.error(e);
  process.exit(1);
});

The setTimeout pause between batches keeps cleanup from starving live traffic.

Make it idempotent and safe

• The job must be safe to run twice — deleting already-deleted rows is a no-op, which is exactly what you want.
• Wrap each batch in its own transaction (a single DELETE is already atomic).
• Log how many rows were removed so you can spot anomalies (a sudden 10x spike means something's wrong).
• Exit non-zero on failure so the platform records the run as failed.

A dry-run first

Before scheduling destructive deletes, run a counting version:

SELECT count(*) FROM sessions WHERE expires_at < now();
SELECT count(*) FROM users WHERE deleted_at < now() - interval '30 days';

Eyeball the numbers. If "expired sessions" is 90% of your table, investigate before deleting.

Scheduling on PandaStack

PandaStack cronjobs are first-class objects with their own schedule, logs, and execution history. To schedule the cleanup:

1. 1Create a Cronjob in the dashboard.
2. 2Point it at your repo/image and set the command: node cleanup.js (or python cleanup.py).
3. 3Set the schedule in cron syntax — e.g., 0 3 * * * for 3 a.m. daily (low-traffic hours).
4. 4Attach the database so DATABASE_URL is injected.
5. 5Save. Watch execution logs after the first run to confirm row counts.

Cron syntax reference for the schedule field:

┌ minute (0-59)
│ ┌ hour (0-23)
│ │ ┌ day of month (1-31)
│ │ │ ┌ month (1-12)
│ │ │ │ ┌ day of week (0-6, Sun=0)
0 3 * * *   -> every day at 03:00
0 4 * * 0   -> every Sunday at 04:00
*/15 * * * * -> every 15 minutes

Schedule cleanups for off-peak hours (e.g., 3–4 a.m. in your users' timezone) so batch deletes don't compete with peak traffic.

After deletion: reclaim space

In Postgres, DELETE marks rows dead but doesn't immediately return disk to the OS. Autovacuum handles reuse, but for big one-time cleanups you may want to run VACUUM (ANALYZE) afterward. Avoid VACUUM FULL on a live table — it takes an exclusive lock. Let routine autovacuum do the steady-state work.

Monitoring the job

• Alert if the job fails (non-zero exit).
• Alert if it doesn't run (missed schedule).
• Watch the deleted-row count trend — a sudden jump means a bug upstream is creating garbage.

References

• PostgreSQL DELETE: https://www.postgresql.org/docs/current/sql-delete.html
• PostgreSQL VACUUM: https://www.postgresql.org/docs/current/sql-vacuum.html
• Batched deletes pattern (Postgres wiki): https://wiki.postgresql.org/wiki/Deleting_duplicates
• crontab syntax (crontab.guru): https://crontab.guru/
• Autovacuum tuning: https://www.postgresql.org/docs/current/routine-vacuuming.html

---

Need a scheduled cleanup job with logs, history, and your database wired in? PandaStack cronjobs run on a cron schedule with DATABASE_URL injected. Set one up free at https://dashboard.pandastack.io