Auto-Purge Old Database Rows on a Schedule

Schedule

Setup

1. 1

   Identify the tables and retention windows

   List every table that accumulates disposable data: sessions (expire_at column), soft-deleted records (deleted_at column), job logs, audit trails, notification history. For each, decide your retention window, for example keep sessions for 7 days after expiry, keep soft-deleted rows for 30 days, keep logs for 90 days. Write this down before touching any SQL.

2. 2

   Write and test the DELETE statements in a transaction

   Run each DELETE manually on a staging copy first. Use a LIMIT clause (e.g. DELETE ... LIMIT 5000) to avoid locking a large table in one shot. Wrap multiple statements in a transaction so a mid-run failure does not leave the database in a half-cleaned state. Confirm row counts match expectations before scheduling.

3. 3

   Create the purge script on your server

   Save the script (see the Script section) to a dedicated path such as /opt/scripts/db-purge.sh. Make it executable with chmod +x. Store database credentials in a .env file or in the system environment, never hard-coded in the script. Test a dry run by executing the script manually and verifying the expected rows are removed.

4. 4

   Register a heartbeat monitor in CronJobPro

   In CronJobPro, create a new Heartbeat monitor. Set the expected period to 24 hours and add a grace period of 15 minutes. Copy the unique ping URL you receive (https://cronjobpro.com/ping/<your-token>) and paste it into your script as shown in the Script section. Configure your alert channel (email, Slack, PagerDuty, etc.) so you are notified if the ping does not arrive on time.

5. 5

   Schedule the job in system cron

   Open the crontab with crontab -e and add the entry: 0 3 * * * /opt/scripts/db-purge.sh >> /var/log/db-purge.log 2>&1. Save and verify with crontab -l. The script will run nightly at 3 AM, perform the purge, and call the CronJobPro ping URL on success so you have confirmation the job completed without errors.

The script

bash

#!/usr/bin/env bash
set -euo pipefail

# Load credentials from environment or a .env file
# Example .env: DB_HOST=localhost DB_PORT=5432 DB_NAME=myapp DB_USER=appuser DB_PASS=secret
if [ -f /opt/scripts/.env ]; then
  # shellcheck source=/dev/null
  source /opt/scripts/.env
fi

HEARTBEAT_URL="https://cronjobpro.com/ping/${CRONJOBPRO_TOKEN:-REPLACE_WITH_YOUR_TOKEN}"

log() {
  echo "[$(date -u '+%Y-%m-%dT%H:%M:%SZ')] $*"
}

run_purge() {
  PGPASSWORD="$DB_PASS" psql \
    --host="$DB_HOST" \
    --port="${DB_PORT:-5432}" \
    --username="$DB_USER" \
    --dbname="$DB_NAME" \
    --no-password \
    --tuples-only \
    <<'SQL'
BEGIN;

-- 1. Delete expired sessions older than 7 days past expiry
DELETE FROM sessions
WHERE expire_at < NOW() - INTERVAL '7 days'
  AND id IN (
    SELECT id FROM sessions
    WHERE expire_at < NOW() - INTERVAL '7 days'
    LIMIT 5000
  );

-- 2. Hard-delete soft-deleted records older than 30 days
DELETE FROM users
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '30 days'
  AND id IN (
    SELECT id FROM users
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - INTERVAL '30 days'
    LIMIT 5000
  );

-- 3. Purge application logs older than 90 days
DELETE FROM app_logs
WHERE created_at < NOW() - INTERVAL '90 days'
  AND id IN (
    SELECT id FROM app_logs
    WHERE created_at < NOW() - INTERVAL '90 days'
    LIMIT 10000
  );

COMMIT;
SQL
}

main() {
  log "Starting database purge"

  if run_purge; then
    log "Purge completed successfully"
    # Notify CronJobPro that the job succeeded
    curl --silent --max-time 10 --retry 3 "$HEARTBEAT_URL" > /dev/null
    log "Heartbeat ping sent"
  else
    EXIT_CODE=$?
    log "Purge failed with exit code $EXIT_CODE"
    # Report failure to CronJobPro
    curl --silent --max-time 10 --retry 3 "${HEARTBEAT_URL}/fail" > /dev/null
    exit $EXIT_CODE
  fi
}

main

Frequently asked questions

More recipes