A measured SQL retention job: 5,000-row batches, a successful purge log, and PostgreSQL and MySQL partition-drop timings

A retention job has two jobs. It must delete rows older than the policy, and it must leave evidence that it finished. event_scheduler = ON, an enabled Event, or a row in cron.job_run_details is not that evidence. In our failure test, MySQL kept the broken Event enabled and advanced LAST_EXECUTED every minute. When either database or scheduler was stopped, neither pg_cron nor MySQL caught up the missed minutes after it returned.

The reliable shape is: delete in measured transactions, write one durable row per run, prevent overlap, and alert when the last successful row is too old. We tested that shape on 6 November 2026 with PostgreSQL 18.6 plus pg_cron 1.6.8 and MySQL Community 8.4.13. Each database held 4,000,000 made-up log rows on the same 2 vCPU, 4 GB machine.

The result first: start at 5,000, then measure your database

text
delete about 100,000 expired rows; three rounds

              PostgreSQL                         MySQL 8.4
batch         median statement     WAL           median statement    binlog + redo
1,000         17-39 ms              32 MB         49 ms               19 + 33 MB
5,000         24-85 ms              31 MB         291 ms              19 + 30 MB
20,000        104-107 ms            30 MB         656 ms              19 + 27 MB
100,000       1.2-1.5 s             28-30 MB      1.1 s               19 + 16 MB

Those numbers are not a sizing table for another server. They show the trade-off to measure. Larger batches finished the whole purge sooner, but made one transaction and its locks last longer. On MySQL, a concurrent update to an old row waited as long as 2.2 seconds in the 100,000-row test. PostgreSQL wrote about 30 MB of WAL per 100,000 deleted rows at every batch size; MySQL wrote about 19 MB of row-based binlog plus 16–33 MB of redo. Small commits did not make replication traffic disappear.

Use 5,000 as a cautious first measurement, not a rule. Record each batch's rows and elapsed time. Reduce it if a batch regularly exceeds your writer-latency budget; increase it if statements are short and commit overhead dominates. Test while the real writers and replicas are busy. Our test had three rounds on one small host, with clients sharing its CPUs, and did not include replicas.

The proof table

sql
-- PostgreSQL
create table purge_log (
  run_id bigserial primary key, job_name text not null, started_at timestamptz not null default now(),
  finished_at timestamptz, status text not null default 'running',
  rows_deleted bigint not null default 0, batches integer not null default 0, error_text text
);
create index purge_log_last_ok on purge_log (job_name, finished_at desc)
  where status = 'succeeded';

-- MySQL: use bigint auto_increment, varchar(64), datetime(6), longtext,
-- and an index on (job_name, status, finished_at).

Insert running before the first delete. After every committed batch, update rows_deleted and batches in its own transaction. On success, set status = 'succeeded' and finished_at. In an exception handler set failed, the error text and finished_at, then re-raise the error so an outside scheduler also sees a non-zero exit. In our injected failures, five committed batches—25,000 rows—stayed deleted and the log correctly said failed, rows_deleted = 25000, batches = 5. A retry simply continued from the cutoff.

PostgreSQL procedure and pg_cron

sql
create or replace procedure purge_app_logs(p_days int, p_batch int) language plpgsql as $$
declare v_run bigint; v_n int; v_error text;
begin
  if not pg_try_advisory_lock(74700) then
    insert into purge_log(job_name,status,finished_at) values ('app_logs_90d','skipped_overlap',now());
    commit; return;
  end if;
  insert into purge_log(job_name) values ('app_logs_90d') returning run_id into v_run; commit;
  loop
    v_error := null;
    begin
      delete from app_logs where id in (select id from app_logs
        where created_at < now() - make_interval(days => p_days)
        order by created_at, id limit p_batch);
      get diagnostics v_n = row_count;
    exception when others then v_error := sqlerrm;
    end;
    if v_error is not null then
      update purge_log set status='failed',error_text=v_error,finished_at=now() where run_id=v_run; commit;
      perform pg_advisory_unlock(74700); raise exception 'purge run % failed: %',v_run,v_error;
    end if;
    update purge_log set rows_deleted=rows_deleted+v_n,batches=batches+1 where run_id=v_run; commit;
    exit when v_n < p_batch;
  end loop;
  update purge_log set status='succeeded',finished_at=now() where run_id=v_run; commit;
  perform pg_advisory_unlock(74700);
end $$;

select cron.schedule('purge-app-logs', '5 1 * * *', 'CALL purge_app_logs(90, 5000)');

A procedure that commits must be the only statement in pg_cron's command: CALL ...; SELECT 1 failed in our test. pg_cron does not overlap two instances of the same job; a due run waits for the current one. Check cron.job_run_details, but treat it as scheduler diagnostics, not business proof. It is never cleaned automatically, so schedule a second job to delete old rows from it. We also found that changing cron.host fixed new jobs but an existing job kept its stored connection setting and had to be recreated.

MySQL procedure and Event

sql
-- Put the same batched DELETE and purge_log updates in purge_app_logs(days, batch).
-- Its EXIT HANDLER must mark the run failed and RESIGNAL. Guard the body:
select get_lock('purge_app_logs', 0) into @got_lock;
-- if @got_lock = 1: run the purge, then do_release_lock('purge_app_logs')
-- otherwise: write a 'skipped_overlap' run and return.

create event purge_app_logs_daily
  on schedule every 1 day starts (current_date + interval 1 day + interval 1 hour + interval 5 minute)
  do call purge_app_logs(90, 5000);

select @@event_scheduler;
select event_name, status, last_executed
from information_schema.events where event_schema = database();

MySQL Events can overlap: our 90-second Event started another copy each minute. With GET_LOCK(name, 0), the second invocation logged a skip and left. MySQL 8.4 had event_scheduler = ON by default, but it can be OFF or startup-only DISABLED, and super_read_only stops it. A failed Event stayed ENABLED; its error existed only in the server error log. That is why the proof query below reads purge_log, never LAST_EXECUTED. Release the named lock in both the normal and exception paths.

Outside scheduling: the most observable option

text
# systemd service: the database client exits non-zero when CALL fails
ExecStart=/usr/bin/flock -n /run/purge-app-logs.lock /usr/bin/psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "CALL purge_app_logs(90,5000)"

# timer
OnCalendar=*-*-* 01:05:00 UTC
Persistent=true

Use an outside scheduler when the database service does not offer pg_cron/Events, when operations already watches systemd or Kubernetes Jobs, or when you want credential rotation and failure alerts outside the database. In our systemd test, Persistent=true caused one catch-up immediately after a three-minute outage—not one run for every missed minute—and flock -n prevented overlap. The database procedure and purge_log stay the same, so changing schedulers does not change the safety model. A plain cron line works too, but add a lock and alert on its exit code.

Alert on a missing success, not merely on a failure

sql
-- Run outside the purge schedule. One row means alert.
select 'app_logs_90d has no successful run in 26 hours' as problem
where not exists (
  select 1 from purge_log
  where job_name = 'app_logs_90d' and status = 'succeeded'
    and finished_at >= current_timestamp - interval '26 hours'
);

-- MySQL: replace the last expression with
-- finished_at >= current_timestamp - interval 26 hour

A failure alert alone misses a scheduler that never started. Run this query from a different scheduler or monitoring system, after the expected finish time plus a realistic grace period. Our two-minute lab check was healthy after a successful run and raised no good purge in 2 minutes after the schedule was removed. Also alert on running rows older than your maximum expected duration, and keep failed/skipped rows long enough to explain gaps.

For large append-only tables, rotate partitions

text
remove one week, about 155,555 rows

PostgreSQL 18.6     DELETE: 467-2,816 ms, about 43 MB WAL
                    DROP partition: 19-188 ms, 113-132 KB WAL
                    DETACH CONCURRENTLY + drop: 73-150 ms, 195-208 KB WAL

MySQL 8.4.13        DELETE: 2,009-3,247 ms, about 30 MB binlog
                    DROP PARTITION: 112-229 ms, 214 bytes binlog

Partition rotation avoids touching every expired row and releases that partition's storage. It is not lock-free. A long PostgreSQL reader made DROP TABLE wait 13 seconds and stalled inserts for 13 seconds; lock_timeout = '2s' cancelled it after two. DETACH PARTITION ... CONCURRENTLY also waited for the reader, but inserts kept moving (339 ms worst in this small test). It must run outside a transaction block and cannot be used with a default partition. MySQL DROP PARTITION waited 14 seconds for a metadata lock and blocked inserts behind it; lock_wait_timeout = 2 bounded that wait. MySQL has no equivalent concurrent detach.

sql
-- PostgreSQL: bounded attempt, or detach concurrently where its restrictions fit
set lock_timeout = '2s';
drop table app_logs_2026_w20;
-- ALTER TABLE app_logs DETACH PARTITION app_logs_2026_w20 CONCURRENTLY;

-- MySQL: prepare future RANGE partitions ahead of time
set session lock_wait_timeout = 2;
alter table app_logs drop partition w202620;

Create future partitions before they are needed. Splitting an empty MySQL pmax during our rotation took 3.2 seconds, while creating the next PostgreSQL partition took 14 ms. Retry a timed-out rotation later and alert from the same run log. Do not fall back to an unbounded wait in the busiest hour.

The production checklist

text
[ ] retention cutoff uses the business timezone and an indexed timestamp
[ ] first run is capped; old backlog cannot delete the whole table at once
[ ] one committed purge_log row proves every run's outcome
[ ] overlap is prevented (pg_cron same-job rule, GET_LOCK, or flock)
[ ] missing-success check runs somewhere independent
[ ] batch duration, writer waits, WAL/binlog and replica lag were measured
[ ] partition DDL has a short lock timeout and a retry
[ ] cron.job_run_details and purge_log have their own retention
[ ] restore and legal-hold requirements agree with the 90-day policy

Deleting rows does not necessarily shrink the file. PostgreSQL's heap and indexes stayed 845 MB and 292 MB after deleting roughly half the table and running ordinary VACUUM; the space became reusable. MySQL's .ibd stayed about 1,145 MB until OPTIMIZE TABLE rebuilt it in 14.1 seconds and reduced it to 642 MB. Do not add a weekly rebuild by reflex: it is a separate, locking maintenance decision.

Open the purge_log beside the table, save the proof and skipped-run queries, and inspect each batch while you tune it. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Before adding the procedure, Event or partition DDL, paste it into sqlInspector for PostgreSQL or MySQL migration and lock warnings.

Open sqlInspector

Related: Deleting millions of rows without hurting production explains the delete mechanics in more depth; A daily database health table records size, dead rows and key runway around this job.

Primary sources: the pg_cron README for scheduling, connection settings, one-instance behaviour and cron.job_run_details; PostgreSQL 18 declarative partitioning and ALTER TABLE for detach locks and restrictions; MySQL 8.4 CREATE EVENT, Event Scheduler configuration, and partition maintenance. Measurements are ours; exact versions, schema, raw results and limitations were recorded with the test on 6 November 2026.