pg-checkup
Workspace
HomeReports
GitHub
Run on GitHub
Learn
OverviewQuickstartChecks referenceTables that never clean themselvesConnectingRun it on your machineMigration checksTools that helpSecurity & privacyFAQ
Help
File a ticketRequest a featurePricing
PrivacyTerms
Docs/Tables that never clean themselves
Sign inNew checkupC

Tables that never clean themselves

Some tables only ever grow, and nothing in Postgres removes their old rows for you. They are a common cause of a disk that fills for no obvious reason, of backups that keep getting slower, and of jobs that quietly stopped. pg-checkup looks for four of them.

Where Check Flags
pg_cron run history extensions.cron_history cron.job_run_details over 100 MB (info), over 1 GB (warning)
pg_cron failing jobs extensions.cron_failures any job with a failed run in the last 7 days (warning)
pg_net responses extensions.pg_net_responses net._http_response over 100 MB, or a request queue of 1,000+
Any log-like table tables.unbounded_growth over 256 MB of inserts with no deletes: 256 MB info, 5 GB warning, 50 GB critical

pg_cron

cron.job_run_details records every run of every job, and nothing deletes the rows. A job that runs each minute adds over half a million rows a year.

Trim it once, then keep it trimmed with a job of its own:

delete from cron.job_run_details where end_time < now() - interval '7 days';
select cron.schedule('purge-cron-history', '0 3 * * *',
  $$delete from cron.job_run_details where end_time < now() - interval '7 days'$$);

To stop logging for one very busy job, set cron.log_run = off for it.

A failing job is worse than a big table. If a cleanup, a rollup or a materialized view refresh fails, nothing tells you: the work just stops. The failed-jobs check lists each job with failures this week, how many runs failed, when it last failed and the error message. Read the message, fix the job's SQL or its permissions, and look at the history with:

select * from cron.job_run_details where jobid = 1 order by start_time desc limit 20;

pg_cron shows each role only its own jobs, so the failed-jobs check needs the scanning role to own the jobs or to be a superuser. The history-size check works with any login.

pg_net

pg_net keeps every response in net._http_response and deletes rows older than pg_net.ttl (6 hours by default). Heavy webhook or trigger traffic makes the deletes outrun autovacuum, so the table fills with dead rows. Shorten the TTL and make autovacuum aggressive on the table:

alter role postgres set pg_net.ttl = '1 hour';
select net.worker_restart();
alter table net._http_response set (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 0);
vacuum (verbose, analyze) net._http_response;

A growing net.http_request_queue means the worker is behind or the destination is slow or unreachable.

Your own log, audit and event tables

tables.unbounded_growth finds tables named like logs, audit trails, history, events, messages, responses, runs or webhooks that have taken 100,000+ inserts and not one delete since the statistics were last reset. It also checks tables that extensions and Supabase create: pgmq archives (pgmq.a_*), supabase_functions.hooks and auth.audit_log_entries.

Decide how long the data is useful, then pick one:

  • Delete on a schedule. Run a delete ... where created_at < now() - interval '90 days' from pg_cron, in batches if the table is large.
  • Partition by date and drop old partitions. Dropping a partition is instant and leaves no dead rows. See partition checks.

Because it is a signal from statistics, a table that is cleaned by a job running less often than the statistics were reset can show up. If you already trim it, ignore the finding or reset the statistics after the next cleanup.

PreviousChecks referenceNextConnecting