Originally published on kuryzhev.cloud
Context
Our PostgreSQL archive old rows script started as a five-line cron job and ended up as the reason our on-call engineer got paged at 2 a.m. three separate times in one month. The setup was simple enough on paper: an events table on Postgres 14.9 (RDS, db.r5.xlarge) was growing roughly 40 million rows a month, dashboards were timing out, and query planning on that table had gotten noticeably slower. The fix seemed obvious — archive anything older than 90 days into a separate table, then delete it from the live one.
The first version was a single Python script using psycopg2 2.9.9, run via cron once a day. It copied matching rows into events_archive, then deleted them from events, all inside one big transaction. It ran fine in staging against a 200k-row test dataset. It ran fine in production too — for about two weeks, while the volume of "old" rows was still manageable. Then the backlog caught up, the batch sizes ballooned into the millions, and everything we hadn't tested for showed up at once.
I want to be upfront: none of these mistakes were exotic. They're the kind of thing you'd catch in a code review if you'd been burned by them before. We hadn't been. This is the retrospective on what broke and what we changed.
Mistake 1: One giant transaction to delete everything
The original script did an INSERT INTO events_archive SELECT * FROM events WHERE created_at < now() - interval '90 days', followed by the matching DELETE, both inside a single transaction, no LIMIT, no batching. On a day where the backlog had grown to 6 million qualifying rows, that transaction held a lock on events for 18 minutes straight.
During those 18 minutes, every app write against that table queued up behind it. The app logs filled with canceling statement due to statement timeout — connections from the pool were dying waiting for a lock that never released in time. Worse, the WAL volume from deleting 6M rows in one shot spiked hard enough that replication lag on our read replica jumped past 40 minutes. Nothing errored on the replica side — it just silently served stale data to whatever was reading from it, which in our case included a reporting pipeline and an ETL job. Nobody noticed until someone asked why a dashboard showed numbers from an hour ago.
Root cause, in hindsight, was obvious: no LIMIT, no batching, and no lock_timeout or statement_timeout set on the connection. A single unbatched DELETE against millions of rows is going to hold locks for as long as it takes, and Postgres will happily let you do it. Watch out for this if you're writing a "simple" cleanup script against any table that also takes live writes — the size of the row count is the only variable that decides whether your script is invisible or an incident.
Mistake 2: No protection against overlapping cron runs
The job was scheduled hourly. Under normal conditions each run finished in a couple of minutes, so overlap was never a concern — until Mistake 1 made a run take 18 minutes, which meant the next scheduled run started while the first one was still working. Cron doesn't check whether the previous invocation finished. It just fires on the interval.
Two processes ended up selecting the same "old" rows before either had deleted them. Both inserted the same rows into events_archive, and the second insert hit a primary key violation. That would have been a loud, obvious failure — except the exception handling in that section of the script was, and I'm not proud of this, a bare except Exception: pass. It was swallowing duplicate-key errors silently. We found it during a code review months later, after someone noticed archive row counts didn't reconcile with the source table's delete count.
This is a classic gotcha: cron does not guarantee non-overlapping execution. A fixed interval plus a variable-duration job will eventually overlap — it's not a matter of if, only when. The permanent fix was a Postgres advisory lock (pg_try_advisory_lock), which we'll cover below. At the time, we had literally nothing preventing two copies of the script from running at once, and the silent exception handler meant we had no visibility into it happening for weeks.
Mistake 3: Trusting archive-then-delete as atomic across two systems
Even after fixing the lock and the overlap issue, the script logic was still: copy rows to events_archive, commit, then delete from events, commit. Two separate transactions. That gap between them is where things went wrong.
A network blip between the insert-commit and the delete-commit left around 12,000 rows sitting in both tables — archived and not yet deleted — for several days before anyone caught it, because nothing failed loudly. We only noticed when a monthly reconciliation script found the archive count didn't match the delete count.
The worse near-miss came from an earlier version of the script, before a code review comment made us reorder the steps. That version deleted from events first, then archived second. If the process had crashed between those two steps — a deploy, an OOM kill, an RDS failover — those rows would have been gone with zero recovery path. We got lucky that this version never actually crashed at the wrong moment in production. It could have, and there was no way to tell from the logs afterward that it almost had.
The lesson here is that "archive then delete" only sounds atomic. Unless both operations commit as one transaction, you're one interrupted process away from either duplicated data or permanently lost data, and which failure mode you get depends entirely on which write you did first.
What we do differently now
The current version batches everything into small, short transactions instead of one long one. Each batch selects up to 5,000 rows with FOR UPDATE SKIP LOCKED so we never fight with concurrent app writers touching the same rows, and the insert-plus-delete for that batch happens in a single transaction — so a crash mid-batch just means re-running that batch, which is safe because the archive table has ON CONFLICT (id) DO NOTHING on its primary key.
# archive_old_rows.py
# Batched, lock-safe archival of rows older than N days from `events` -> `events_archive`
import os
import sys
import time
import logging
import psycopg2
logging.basicConfig(level=logging.INFO)
log = logging.getLogger("archiver")
DSN = os.environ["ARCHIVE_DB_DSN"] # pulled from Secrets Manager at deploy time, never hardcoded
BATCH_SIZE = 5000
RETENTION_DAYS = 90
LOCK_KEY = "archive_events_job"
def acquire_lock(conn):
with conn.cursor() as cur:
cur.execute("SELECT pg_try_advisory_lock(hashtext(%s))", (LOCK_KEY,))
return cur.fetchone()[0]
def archive_batch(conn):
"""Move a single batch. Returns rows moved (0 means done)."""
with conn.cursor() as cur:
# session-level guard: never let one batch block writers for long
cur.execute("SET lock_timeout = '5s'")
cur.execute("SET statement_timeout = '30s'")
cur.execute("""
WITH batch AS (
SELECT id FROM events
WHERE created_at < now() - interval '%s days'
ORDER BY id
LIMIT %s
FOR UPDATE SKIP LOCKED
),
moved AS (
INSERT INTO events_archive
SELECT e.* FROM events e JOIN batch b ON e.id = b.id
ON CONFLICT (id) DO NOTHING
RETURNING id
)
DELETE FROM events
WHERE id IN (SELECT id FROM batch)
RETURNING id;
""", (RETENTION_DAYS, BATCH_SIZE))
rows = cur.rowcount
conn.commit() # commit per-batch, not per-run — keeps locks short
return rows
def main():
conn = psycopg2.connect(DSN)
conn.autocommit = False
if not acquire_lock(conn):
log.info("Another archive run holds the lock, exiting cleanly.")
return
total = 0
try:
while True:
try:
moved = archive_batch(conn)
except psycopg2.errors.LockNotAvailable:
log.warning("Batch skipped due to lock contention, retrying in 5s")
time.sleep(5)
continue
total += moved
if moved == 0:
break
time.sleep(0.1) # brief pause to avoid saturating IOPS
log.info(f"Archived {total} rows.")
finally:
conn.close()
if __name__ == "__main__":
main()
The advisory lock wraps the whole run so an overlapping cron or systemd-timer execution just exits cleanly instead of racing — pg_try_advisory_lock returns false immediately if another run already holds it, no polling required. We also moved scheduling off plain crontab to a systemd timer with Persistent=true, so a missed run after a reboot still fires once instead of silently never happening again.
# /etc/systemd/system/archive-events.timer
# Runs every hour, catches up on missed runs if host was down
[Unit]
Description=Timer for events archival job
[Timer]
OnCalendar=hourly
Persistent=true
RandomizedDelaySec=60
[Install]
WantedBy=timers.target
# /etc/systemd/system/archive-events.service
[Unit]
Description=Archive old rows from events table
[Service]
Type=oneshot
EnvironmentFile=/etc/archiver/env
ExecStart=/usr/bin/python3 /opt/archiver/archive_old_rows.py
User=archiver_svc
# --- observed output during the Mistake 1 incident (for comparison) ---
# ERROR: canceling statement due to statement timeout
# app-server-3 | psycopg2.errors.QueryCanceled: canceling statement due to lock timeout
# pg_stat_replication.replay_lag: 00:41:12
#
# --- after fix, typical run log ---
# INFO:archiver:Archived 6023841 rows.
# real 9m14.221s
The numbers speak for themselves: the same 6M-row backlog that used to take 18 minutes and block every writer now takes about 9 minutes total, with each batch holding a lock for roughly 200ms. Zero write blocking, zero timeout errors in the app logs.
A few other things changed alongside the code. The archiver now runs under a dedicated archiver Postgres role with only INSERT on events_archive and SELECT/DELETE on events — no DROP or ALTER, and credentials come from AWS Secrets Manager instead of being hardcoded in a config file. We also dropped three unused indexes on events_archive since it's append-only and rarely queried; that alone cut insert time noticeably. And after any large archive run, we manually kick off VACUUM (ANALYZE) events; during a low-traffic window, because autovacuum can fall behind on a table with heavy write traffic, and dead tuples pile up fast after a mass delete. We track n_dead_tup from pg_stat_user_tables in Grafana and alert if a run takes more than 10 minutes.
One quiet gotcha worth calling out separately: created_at < now() - interval '90 days' is evaluated in the database's timezone, not the application's. If your RDS instance isn't set to UTC, "90 days old" can be off by hours depending on daylight saving — easy to miss, annoying to debug after the fact. We double-checked this against the Postgres date/time function docs and the RDS parameter group settings and normalized everything to UTC explicitly instead of trusting defaults.
If you're running a similar cleanup job today, I'd rather you learn this from a retrospective than from a 2 a.m. page. We wrote up more of our production database patterns over on kuryzhev.cloud if you want the broader context on how we run Postgres on RDS day to day.
Top comments (0)