Data retention jobs: safely delete old records and prove they ran
For developers who need a retention job in production without guessing whether it worked. This shows a concrete Postgres-based pattern for deleting old records in batches, wiring it to cron, and producing logs and database evidence you can hand to auditors or teammates.
TL;DR — Build the retention job as a repeatable SQL delete in small batches, log every run to a
retention_job_runstable, and run it from cron with a fixed cutoff timestamp. The most common failure is deleting too much because the predicate is wrong or usingNOW()repeatedly; compute one cutoff once per run, dry-run withSELECT count(*), then delete by primary key in batches. Reading time: ~5 min
Goal
When you are done, your app has a retention job that deletes records older than a defined cutoff in bounded batches, records exactly what it deleted and when in a database audit table, exits non-zero on failure, and can be verified end-to-end from both cron logs and SQL.
Prerequisites
- Postgres access with permission to
SELECT,DELETE,CREATE TABLE, andCREATE INDEX - Shell access on the worker or app host that will run the job
psqlclient installed — check with:
psql --version
bashandcronavailable — check with:
bash --version | head -1
crontab -l >/dev/null 2>&1; echo $?
- A connection string in
DATABASE_URL, for examplepostgres://app_user:***@db.example.internal:5432/appdb - The table name, timestamp column, and retention period you actually want, for example:
events,created_at,90 days - A primary key column on the target table, assumed below as
id
Steps
Step 1: Create a table to prove each run happened
Run this once in the target database:
CREATE TABLE IF NOT EXISTS retention_job_runs (
id bigserial PRIMARY KEY,
job_name text NOT NULL,
started_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz,
cutoff timestamptz NOT NULL,
batch_size integer NOT NULL,
rows_deleted bigint NOT NULL DEFAULT 0,
status text NOT NULL CHECK (status IN ('running','success','failed')),
error_message text,
host_name text,
pid integer
);
CREATE INDEX IF NOT EXISTS retention_job_runs_job_name_started_at_idx
ON retention_job_runs (job_name, started_at DESC);
You should see CREATE TABLE and CREATE INDEX or NOTICE: relation ... already exists, skipping.
Step 2: Add the index the delete query needs
For a table named events with id and created_at:
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_created_at_id_idx
ON events (created_at, id);
You should see CREATE INDEX after it finishes; on large tables this can take time but should not block normal reads/writes the way a plain CREATE INDEX can.
⚠️ This job deletes data permanently. Before continuing, run the dry-run count in Step 3 against production and compare it to what your policy actually allows. If the number is unexpectedly high, stop and fix the predicate before any delete runs.
Step 3: Dry-run the retention predicate with a fixed cutoff
Set one cutoff timestamp and count the rows that would be deleted:
export DATABASE_URL='postgres://app_user:***@db.example.internal:5432/appdb'
export CUTOFF_UTC="$(date -u -d '90 days ago' '+%Y-%m-%dT%H:%M:%SZ')"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -v cutoff="$CUTOFF_UTC" -c "SELECT count(*) AS rows_to_delete FROM events WHERE created_at < :'cutoff';"
You should see a single count, for example:
rows_to_delete
----------------
184233
(1 row)
Step 4: Create the batch delete script with run logging
Create /usr/local/bin/retention-events.sh:
cat >/usr/local/bin/retention-events.sh <<'EOF'
#!/usr/bin/env bash
set -euo pipefail
: "${DATABASE_URL:?DATABASE_URL is required}"
JOB_NAME="events_retention_90d"
BATCH_SIZE="${BATCH_SIZE:-5000}"
CUTOFF_UTC="${CUTOFF_UTC:-$(date -u -d '90 days ago' '+%Y-%m-%dT%H:%M:%SZ')}"
HOST_NAME="$(hostname)"
PID="$$"
RUN_ID="$(psql "$DATABASE_URL" -X -A -t -v ON_ERROR_STOP=1 \
-v job_name="$JOB_NAME" -v cutoff="$CUTOFF_UTC" -v batch_size="$BATCH_SIZE" \
-v host_name="$HOST_NAME" -v pid="$PID" <<'SQL'
INSERT INTO retention_job_runs (job_name, cutoff, batch_size, status, host_name, pid)
VALUES (:'job_name', :'cutoff', :'batch_size', 'running', :'host_name', :'pid')
RETURNING id;
SQL
)"
TOTAL_DELETED=0
cleanup_failed() {
local msg=${1:-"unknown error"}
psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -v run_id="$RUN_ID" -v msg="$msg" <<'SQL'
UPDATE retention_job_runs
SET finished_at = now(), status = 'failed', error_message = :'msg', rows_deleted = rows_deleted
WHERE id = :'run_id';
SQL
}
trap 'cleanup_failed "script failed on line $LINENO"' ERR
while true; do
DELETED="$(psql "$DATABASE_URL" -X -A -t -v ON_ERROR_STOP=1 \
-v cutoff="$CUTOFF_UTC" -v batch_size="$BATCH_SIZE" <<'SQL'
WITH doomed AS (
SELECT id
FROM events
WHERE created_at < :'cutoff'
ORDER BY created_at, id
LIMIT :'batch_size'
), deleted AS (
DELETE FROM events e
USING doomed d
WHERE e.id = d.id
RETURNING e.id
)
SELECT count(*) FROM deleted;
SQL
)"
DELETED="${DELETED//[[:space:]]/}"
TOTAL_DELETED=$((TOTAL_DELETED + DELETED))
echo "cutoff=$CUTOFF_UTC batch_deleted=$DELETED total_deleted=$TOTAL_DELETED"
if [ "$DELETED" -eq 0 ]; then
break
fi
sleep 1
done
psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -v run_id="$RUN_ID" -v total_deleted="$TOTAL_DELETED" <<'SQL'
UPDATE retention_job_runs
SET finished_at = now(), status = 'success', rows_deleted = :'total_deleted', error_message = NULL
WHERE id = :'run_id';
SQL
EOF
chmod 0750 /usr/local/bin/retention-events.sh
You should see no output from cat; verify the file exists with:
ls -l /usr/local/bin/retention-events.sh
Step 5: Run it once manually and inspect the exit code
Run the job:
export DATABASE_URL='postgres://app_user:***@db.example.internal:5432/appdb'
/usr/local/bin/retention-events.sh; echo "exit_code=$?"
You should see output shaped like this and exit_code=0:
cutoff=2026-08-01T00:00:00Z batch_deleted=5000 total_deleted=5000
cutoff=2026-08-01T00:00:00Z batch_deleted=5000 total_deleted=10000
cutoff=2026-08-01T00:00:00Z batch_deleted=4233 total_deleted=14233
cutoff=2026-08-01T00:00:00Z batch_deleted=0 total_deleted=14233
exit_code=0
Step 6: Schedule it in cron with log capture
Install a dedicated cron entry:
( crontab -l 2>/dev/null; echo '17 2 * * * DATABASE_URL="postgres://app_user:***@db.example.internal:5432/appdb" BATCH_SIZE=5000 /usr/local/bin/retention-events.sh >> /var/log/retention-events.log 2>&1' ) | crontab -
crontab -l
You should see the exact cron line in crontab -l output.
Step 7: Add a simple query for recent proof
Use this query any time you need evidence of execution:
psql "$DATABASE_URL" -X -c "SELECT job_name, started_at, finished_at, cutoff, rows_deleted, status, error_message, host_name FROM retention_job_runs WHERE job_name = 'events_retention_90d' ORDER BY started_at DESC LIMIT 10;"
You should see recent rows with status = success and a non-null finished_at.
Verify it works
Run all three checks after the first scheduled run time passes.
- Confirm cron produced log lines:
tail -n 20 /var/log/retention-events.log
Expected shape:
cutoff=2026-08-01T02:17:00Z batch_deleted=5000 total_deleted=5000
cutoff=2026-08-01T02:17:00Z batch_deleted=0 total_deleted=5000
- Confirm the database recorded a successful run:
psql "$DATABASE_URL" -X -c "SELECT status, rows_deleted, started_at, finished_at FROM retention_job_runs WHERE job_name = 'events_retention_90d' ORDER BY started_at DESC LIMIT 1;"
Expected shape:
status | rows_deleted | started_at | finished_at
---------+--------------+-------------------------------+-------------------------------
success | 5000 | 2026-10-30 02:17:00.123456+00 | 2026-10-30 02:17:03.456789+00
(1 row)
- Confirm no rows older than the cutoff remain:
export CUTOFF_UTC="$(date -u -d '90 days ago' '+%Y-%m-%dT%H:%M:%SZ')"
psql "$DATABASE_URL" -X -v cutoff="$CUTOFF_UTC" -c "SELECT count(*) AS remaining_old_rows FROM events WHERE created_at < :'cutoff';"
Expected result after the job has caught up:
remaining_old_rows
--------------------
0
(1 row)
Common pitfalls
Wrong predicate deletes the wrong rows
Mistake: using created_at > cutoff instead of created_at < cutoff, or using the wrong timestamp column.
Symptom: the dry-run count is tiny when it should be large, or huge when it should be near zero; recent records disappear.
Fix: run this exact dry-run before any delete and verify the count:
psql "$DATABASE_URL" -X -v cutoff="$CUTOFF_UTC" -c "SELECT min(created_at), max(created_at), count(*) FROM events WHERE created_at < :'cutoff';"
Recomputing now() inside each batch causes inconsistent boundaries
Mistake: putting WHERE created_at < now() - interval '90 days' directly in every loop iteration.
Symptom: rows near the boundary move in and out during a long run; repeated runs delete slightly different totals with no clear explanation.
Fix: compute one cutoff once per run and pass it as CUTOFF_UTC, exactly as in the script.
Large single-statement deletes bloat the table and block work
Mistake: DELETE FROM events WHERE created_at < ...; on a large table.
Symptom: long transactions, lock contention, replication lag, autovacuum pressure, or timeouts.
Fix: delete by primary key in batches with LIMIT 5000 as shown, then tune BATCH_SIZE up or down.
Missing index makes every batch scan the whole table
Mistake: running the batch query without an index on (created_at, id).
Symptom: each batch is slow, CPU spikes, and EXPLAIN shows a sequential scan.
Fix: create this index:
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_created_at_id_idx ON events (created_at, id);
Cron runs with a different environment than your shell
Mistake: the script works interactively but cron cannot see DATABASE_URL or psql.
Symptom: log file contains errors like:
/usr/local/bin/retention-events.sh: line 4: DATABASE_URL: DATABASE_URL is required
or
/usr/local/bin/retention-events.sh: line 14: psql: command not found
Fix: put DATABASE_URL="..." directly in the crontab line and use full paths if needed, for example /usr/bin/psql.
Assuming deletion alone is enough proof
Mistake: relying only on row counts in the target table.
Symptom: after a quiet period, you cannot tell whether the job ran successfully or there was simply nothing to delete.
Fix: keep the retention_job_runs table and treat status, started_at, finished_at, and rows_deleted as the source of truth for evidence.
This article was written by an AI system and published pending human review. Verify anything you intend to act on.
Have a project in mind?
Get an instant AI price estimate for it, or talk directly to our team.
One email a month on what we learn building with AI