Bug Autopsy: The Cron Job That Outlived Its Feature

≈ 8 min read

If you run an app on Supabase or any Postgres with scheduled jobs, and you have ever removed a feature (payments, a sync, an integration), this one is for you. The code was gone. The feature was not. A job scheduled with pg_cron kept running every minute inside the database, and each run left a trail of log rows behind it. My app’s own data was about 1 MB. The logs were 629 MB. This article is the autopsy of that day, the SQL that finds such a job in five minutes, and the checklist I now go through before I call a feature removed.

The moment I knew

One morning, signing in to Serpent Collab with Google stopped at “Redirecting…” and stayed there. The Supabase dashboard had a banner: the Disk IO budget was depleted. Another one said the organisation was exceeding its usage limits. The database was at 668 MB, on a free plan that allows 500.

Nothing had been uploaded to the site in months. And the data that mattered, every board everyone had ever made, fit in about one megabyte.

The setting

Serpent Collab was a board for planning a project together, which I used to run. The app was static files on my Strato webspace. Behind it sat Supabase for the database and the Google login, on the free plan with the smallest machine, and PartyKit for the live collaboration, so moving a card never touched the database directly. It is frozen now: the site is still up as a portfolio piece, but sign-in and saving no longer work.

It used to have payments, through Supabase’s one-click Stripe integration, which copies your Stripe data into your own database and keeps it in sync. Then I made a choice: Serpent Collab would be free for everyone, with no board limits and no locked palettes, and it would run on its own while I put my time into new tools like Wyrd Almanach and Ember Grin. Twelve days before the login broke, I took payments out completely: the pricing section, the upgrade dialogs, the paywall, the webhook function. Four hundred lines gone in one commit. Free and finished, I thought. The database had other plans.

The hunt

When a database is full and your data is small, the question is simple: what else is in there? Postgres will tell you, table by table:

select n.nspname || '.' || c.relname as table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) as size
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
order by pg_total_relation_size(c.oid) desc
limit 10;

The top of that list was four tables I had never opened, at 265, 217, 123 and 24 MB. None of them belonged to my app:

  • cron.job_run_details, where pg_cron writes down every run of every scheduled job.
  • net._http_response, where pg_net keeps the answers to web requests made from inside the database.
  • stripe._sync_obj_runs and stripe._sync_runs, the Stripe sync’s own bookkeeping.

Together 629 MB, 94 % of the database. All of it was logs.

Then the question that solved it:

select jobid, jobname, schedule, active from cron.job;

One row. A job called stripe-sync-worker, scheduled * * * * *: every minute, active.

Cause one: the job nobody deleted

The Stripe integration does not live in your code. It lives in your database: a schema of its own, a few Edge Functions, a queue, secrets in the Vault, and a scheduled job that wakes the worker. My commit removed what my code knew about Stripe. The database had never heard of my commit.

So every minute, around the clock, the job did its work. It wrote a row into cron.job_run_details. It sent a request to the worker function, and the answer went into net._http_response. The sync recorded what it had done in its own tables. 1440 times a day, for an integration that no longer had anything to sync, writing logs nobody would ever read.

pg_cron never cleans that run log by itself. Its README says so in one sentence, and most people find out the way I did. pg_net does delete its answers after six hours, but in Postgres a deleted row does not give its space back to the disk, and a starved disk cleans up slowly.

Cause two: human error

The job had an accomplice, and it was me. The old app wrote the whole board back to the database every 300 milliseconds, for as long as someone was typing or dragging cards. I had fixed that on the day I took payments out: live changes went over PartyKit, and the database only got a save every five seconds. Then I forgot to upload it. So the app I thought was resting kept hammering the same small disk the cron job was already filling.

Why the login died

A small Supabase machine can burst above its baseline disk speed for a while, drawing on a daily Disk IO budget. Once the budget is spent, the database drops to its baseline, and on the smallest machine that is slow. A job writing every minute and an app saving every 300 ms had spent it.

The login was just the first thing to notice. The auth service could not even fetch Google’s configuration in time (“context deadline exceeded”), the API answered with 503s, and a single sign-in request took 12 to 36 seconds. Nothing in the app had changed. The database underneath it was drowning in its own diary.

The fix

Empty the logs, then stop the job:

-- the sync's two tables are tied by a foreign key, so they go in one statement
truncate stripe._sync_obj_runs, stripe._sync_runs;
truncate net._http_response;
truncate cron.job_run_details;

-- the zombie, by the jobid that cron.job showed
select cron.unschedule(1);

Then a restart of the project from the dashboard, to break the overload. A restart does not give the Disk IO budget back; that refills on its own over the next hours, once the disk goes quiet. Then I finally uploaded the save fix.

Afterwards the database was at about 40 MB, the disk was idle, and the login took under a second.

Four things that cost me time on the way:

  • truncate refuses a table that another table points at with a foreign key, unless both are in the same statement.
  • If you measure the database size in the same run as the truncate, you still see the old size. The SQL editor runs the whole paste as one transaction, and the files only go when it ends. Measure in a second run.
  • When the project is drowning, the dashboard’s SQL editor fails too, with errors like “upstream connect error”. Run it again. A failed run changes nothing, because the batch is one transaction.
  • The dashboard offered me a “free” upgrade to a bigger machine. It is free only on the Pro plan, which costs $25 a month and includes $10 of credit that pays for that machine. On the free plan, accepting it means paying for Pro.

The uninstaller that could not uninstall

That evening I wanted the rest of the integration gone too. Supabase’s own uninstaller stopped with “Setup secret not found in vault; cannot authenticate uninstall”. It needed a secret that the integration had stored and that was, it turned out, gone.

So I took it apart by hand, which is also the best map of what a feature like this leaves behind:

drop schema stripe cascade;                               -- every sync table and view
drop function check_subscription_status(text);            -- my only function that read them
select pgmq.drop_queue('stripe_sync_work');               -- the queue
delete from vault.secrets where name ilike '%stripe%';    -- the stored keys

Plus the three Edge Functions, deleted in the dashboard and checked from outside afterwards: all three answered 404. The job itself was already unscheduled.

The five-minute check

Before you call a feature removed, run these. They only read.

-- what is scheduled
select jobid, jobname, schedule, active from cron.job;

-- how much the run log already holds
select count(*), min(start_time), max(start_time) from cron.job_run_details;

-- what is biggest (the query from the hunt above)

-- what else is installed: queues and secret names, never the secrets themselves
select queue_name from pgmq.list_queues();
select name from vault.secrets;

And in the dashboard: the list of Edge Functions, and the schemas you did not create yourself.

Jobs you keep on purpose need the same care: delete old rows from cron.job_run_details on a schedule, or the log grows for them too.

The lesson

  • A feature lives in more places than your code: scheduled jobs, functions in the database, Edge Functions, queues, stored secrets. Removing the code removes none of them.
  • A job that runs every minute writes 1440 log rows a day, whether there is any work or not. On a small database that is a slow leak with a deadline.

Sources