Pilotbase grid over the db_health table: the orders table growing from 103 MB to 802 MB in a week while its int key runway drops from 153 to 98 days

Most databases don't fail suddenly. A table grows a little faster each week, an int key creeps towards 2,147,483,647, dead rows pile up behind a vacuum that can't keep up, and nobody looks until an insert fails. The numbers that would have warned you are all in the catalog, but the catalog only tells you about now. It doesn't tell you what the table looked like last Tuesday.

So keep last Tuesday. One table, one row per table per day, filled by one scheduled query inside the database: pg_cron on PostgreSQL, the event scheduler on MySQL. No agent, no monitoring service. Then growth, runway and "something changed" become plain SQL over your own history. We built it on PostgreSQL 18.6 and MySQL 8.4.11 and ran a shop database through a busy week to see what the history shows, and what it gets wrong.

The lab: a week of a growing shop, on both engines

Same schema and the same daily writes on both engines: orders with an int key that starts at 2,100,000,000 (an app a few years old, already 97.8% through its key range), events with a 3-day retention delete, sessions with 50,000 rows each rewritten 4 times a day, and customers. Each day added more orders than the last, from 300,000 on day one to 540,000 on day seven, with yesterday's orders updated to shipped. Every day also wrote 600,000 events and, from the fifth day on, deleted the oldest day of them.

A real week takes a week, so we compressed it: each "day" ran its writes, waited 75 seconds so autovacuum could do what it would do between busy hours, then called the same snapshot procedure the scheduler calls, with that day's date. Separately, both schedulers ran the snapshot every minute for a few minutes to show they really fire (below). Dates are 23 to 30 October 2026; the 23rd is the state before the week.

The snapshot table and the function that fills it (PostgreSQL)

Sizes come from pg_relation_size and pg_indexes_size (exact, read from the files), row and dead-row counts from pg_stat_user_tables (estimates kept by the statistics system), and key use from the sequence behind each serial or identity column, found through pg_depend. The upper limit comes from the column's type, not the sequence: a sequence created as bigint feeding an int column still fails at 2,147,483,647.

sql
create table if not exists db_health (
  taken_on        date   not null,
  table_name      text   not null,          -- '(database)' for the whole database
  live_rows       bigint,
  dead_rows       bigint,
  table_bytes     bigint,                   -- heap only
  index_bytes     bigint,
  toast_bytes     bigint,
  key_column      text,                     -- the column fed by a sequence (serial / identity)
  key_type        text,
  key_last        bigint,                   -- last value handed out
  key_max         bigint,                   -- largest value the column's type holds
  last_vacuum     timestamptz,
  primary key (taken_on, table_name)
);

create or replace function record_health(p_day date default current_date) returns void
language sql as $$
  insert into db_health
  select p_day, c.oid::regclass::text, s.n_live_tup, s.n_dead_tup,
         pg_relation_size(c.oid), pg_indexes_size(c.oid),
         coalesce(pg_total_relation_size(nullif(c.reltoastrelid, 0)), 0),
         k.attname, k.typ, k.last_value, k.maxv,
         greatest(s.last_autovacuum, s.last_vacuum)
  from pg_class c
  join pg_stat_user_tables s on s.relid = c.oid
  left join lateral (
    select a.attname, format_type(a.atttypid, a.atttypmod) as typ, sq.last_value,
           case a.atttypid when 'int2'::regtype then 32767
                           when 'int4'::regtype then 2147483647
                           else 9223372036854775807 end as maxv
    from pg_depend d
    join pg_attribute a on a.attrelid = c.oid and a.attnum = d.refobjsubid
    join pg_class seq   on seq.oid = d.objid and seq.relkind = 'S'
    join pg_sequences sq on sq.schemaname = seq.relnamespace::regnamespace::text
                        and sq.sequencename = seq.relname
    where d.classid = 'pg_class'::regclass and d.refobjid = c.oid
      and d.deptype in ('a', 'i')            -- serial: auto, identity: internal
    order by a.attnum limit 1
  ) k on true
  where c.relkind in ('r', 'p')
  union all
  select p_day, '(database)', null, null, pg_database_size(current_database()), null, null,
         null, null, null, null, null
  on conflict (taken_on, table_name) do update set
    live_rows = excluded.live_rows, dead_rows = excluded.dead_rows,
    table_bytes = excluded.table_bytes, index_bytes = excluded.index_bytes,
    toast_bytes = excluded.toast_bytes, key_last = excluded.key_last,
    last_vacuum = excluded.last_vacuum
$$;

on conflict ... do update makes it safe to run twice on the same day: a manual run, a retry or a scheduler that fires twice leaves one row per table per day. A database-wide row ((database), from pg_database_size) gives the disk trend.

Scheduling it with pg_cron

pg_cron is a background worker that runs cron-style jobs inside PostgreSQL. It ships with most managed Postgres services (RDS, Cloud SQL, Azure, Supabase and others); self-hosted, it has to be in shared_preload_libraries, so adding it means one restart. It lives in one database per cluster (cron.database_name), and cron.schedule_in_database() runs jobs elsewhere.

text
# postgresql.conf (self-hosted), then restart
shared_preload_libraries = 'pg_cron'
cron.database_name = 'shop'          -- where the cron schema lives
cron.timezone = 'UTC'                -- the schedule's clock (GMT by default)
sql
create extension pg_cron;

-- every day at 00:05 UTC
select cron.schedule('db-health', '5 0 * * *', 'select record_health()');

-- pg_cron keeps every run in cron.job_run_details and never cleans it: do it yourself
select cron.schedule('cron-history-cleanup', '0 3 * * *',
  $$delete from cron.job_run_details where end_time < now() - interval '30 days'$$);

-- did it run?
select status, start_time, end_time - start_time as took, return_message
from cron.job_run_details where jobid = (select jobid from cron.job where jobname = 'db-health')
order by start_time desc limit 7;

We scheduled it every minute ('* * * * *') for a short test: two runs at 06:47:00 and 06:48:00, both succeeded, 13 ms and 7 ms, and still eight rows for that day afterwards because of the upsert. Inside the compressed week, the snapshot took 40 to 53 ms per run on PostgreSQL. Cheap enough to run hourly if you want finer history.

What a week of snapshots shows (PostgreSQL)

text
size per day, table + indexes + TOAST (PostgreSQL 18.6)
day     orders          events          sessions        whole database
10-23   103 MB          24 kB           15 MB           142 MB
10-24   148 MB  +45     88 MB   +88     67 MB   +53     328 MB  +186
10-25   239 MB  +91     174 MB  +86     67 MB    +0     506 MB  +178
10-26   323 MB  +84     261 MB  +87     118 MB  +50     728 MB  +222
10-27   411 MB  +87     347 MB  +86     118 MB   +0     902 MB  +174
10-28   539 MB  +129    434 MB  +86     118 MB   +0     1117 MB +215
10-29   641 MB  +102    512 MB  +78     118 MB   +0     1298 MB +181
10-30   802 MB  +161    541 MB  +29     118 MB   +0     1489 MB +191

Three patterns you only see with history. orders grew 45 MB on the first day and about twice that from the second day on, with only 13% more new rows. The difference is the update to shipped: in PostgreSQL an update writes a new row version, so a table that updates its rows grows faster than its row count. sessions never gained a row, yet went from 15 MB to 118 MB in three days and then stopped: four rewrites a day settle once autovacuum frees enough space for the next round to reuse. events added 86 MB a day until the retention delete began on the 28th, then slowed to 29 MB on the 30th as new rows moved into the space vacuum freed. If you only ever looked at today's size, all three would just be numbers.

Growth per day is a lag() over the snapshots. Disk runway uses the slope of the last seven days with regr_slope, which is less jumpy than first-minus-last:

sql
with d as (
  select regr_slope(table_bytes, taken_on - date '2000-01-01') as per_day,
         (array_agg(table_bytes order by taken_on desc))[1]    as now_bytes
  from db_health
  where table_name = '(database)' and taken_on > current_date - 7
)
select pg_size_pretty(now_bytes) as now, pg_size_pretty(per_day::bigint) as per_day,
       floor(40e9 / per_day) as days_until_40gb_more     -- your free disk here
from d;
--   now    | per_day | days_until_40gb_more
--  1489 MB | 195 MB  |                  195

Key runway: 98 days, and falling faster than it looks

sql
with k as (
  select table_name, key_column, key_type, key_max,
         (array_agg(key_last order by taken_on desc))[1]       as last_value,
         regr_slope(key_last, taken_on - date '2000-01-01')    as per_day
  from db_health
  where key_last is not null and taken_on > current_date - 7
  group by 1, 2, 3, 4
)
select table_name, key_column, key_type,
       round(100.0 * last_value / key_max, 2)                  as pct_used,
       round(per_day)                                          as per_day,
       floor((key_max - last_value) / nullif(per_day, 0))      as days_left
from k order by days_left nulls last;
-- orders    | id | integer | 97.97 | 440000 |    98
-- customers | id | integer |  0.01 |   5000 | 429449
Pilotbase query over db_health for orders: size per day from 103 MB to 802 MB, dead rows, key_last from 2,100,999,999 to 2,103,939,999, 97.84% to 97.97% of the int range used, days left from 153 to 98

"97.97% used" sounds like an emergency and "98 days left" sounds like next quarter's problem, and the history says both are too comfortable. Computed each day over the days so far, the runway went 153, 143, 133, 125, 117, 110, 98: it lost 55 days in six. The seven-day slope (440,000 ids a day) is an average of a rising line; at the last day's rate of 540,000 the real answer is 43,543,648 / 540,000 = 80 days. For an alert, use the larger of the 7-day slope and the last day's rate. And moving a busy table to bigint takes planning, so alert at 180 days, not at 30. Running out of int covers the migration itself.

Dead rows, and a live-row count that was 29% off

text
PostgreSQL, n_live_tup / n_dead_tup at each snapshot
day     sessions live  dead     events live  dead       orders live  dead
10-25      50,000   199,915      1,200,000         0     1,640,000        0
10-26      76,255         0      1,754,821         0     2,020,000        0
10-27      76,255   199,802      2,354,821         0     2,440,000  380,000
10-28      50,000         0      2,354,821   600,000     2,899,296  420,850
10-29      50,000         0      2,394,062   599,602     3,399,296  880,850
10-30      50,000   199,671      2,394,062 1,199,602     2,800,186        0

Dead rows are a sawtooth: they build until autovacuum runs, then drop to zero. A single snapshot of 80% dead in sessions means little; 80% on two snapshots in a row means vacuum isn't keeping up. That's how the alert view below treats it.

Look at orders on the 30th: 2,800,186 live rows. The table had 3,940,000. The snapshot ran between autovacuum finishing at 06:40:06 and the automatic analyze at 06:40:40; vacuum had just written its own estimate, built from the 58% of pages it scanned, and analyze corrected it to 3,923,332 a few seconds later. n_live_tup is an estimate, and it can be wrong by a lot for a few seconds or minutes after a vacuum. Use the size columns for trends (they're exact), treat row counts as rough, and if you need true row counts, count(*) the few tables that matter in the same function.

The same thing on MySQL, and the 24-hour trap

MySQL's numbers come from information_schema.TABLES: DATA_LENGTH, INDEX_LENGTH, DATA_FREE (space InnoDB holds but isn't using), TABLE_ROWS and AUTO_INCREMENT. The procedure is the same shape:

sql
create table if not exists db_health (
  taken_on     date         not null,
  table_name   varchar(64)  not null,      -- '(database)' for the schema total
  rows_est     bigint,                     -- TABLE_ROWS: an estimate for InnoDB
  data_bytes   bigint,
  index_bytes  bigint,
  free_bytes   bigint,                     -- DATA_FREE: space InnoDB holds but isn't using
  key_column   varchar(64),
  key_type     varchar(64),
  key_last     bigint,
  key_max      bigint unsigned,
  primary key (taken_on, table_name)
);

drop procedure if exists record_health;
delimiter //
create procedure record_health(p_day date)
begin
  -- information_schema caches table statistics for 24 h by default; read them fresh
  set session information_schema_stats_expiry = 0;
  insert into db_health
  select p_day, t.table_name, t.table_rows, t.data_length, t.index_length, t.data_free,
         c.column_name, c.column_type, t.auto_increment - 1,
         case c.data_type
           when 'tinyint'   then if(c.column_type like '%unsigned', 255, 127)
           when 'smallint'  then if(c.column_type like '%unsigned', 65535, 32767)
           when 'mediumint' then if(c.column_type like '%unsigned', 16777215, 8388607)
           when 'int'       then if(c.column_type like '%unsigned', 4294967295, 2147483647)
           else if(c.column_type like '%unsigned', 18446744073709551615, 9223372036854775807) end
  from information_schema.tables t
  left join information_schema.columns c
    on c.table_schema = t.table_schema and c.table_name = t.table_name and c.extra like '%auto_increment%'
  where t.table_schema = database() and t.table_type = 'BASE TABLE'
  union all
  select p_day, '(database)', null, sum(data_length), sum(index_length), sum(data_free), null, null, null, null
  from information_schema.tables where table_schema = database()
  on duplicate key update rows_est = values(rows_est), data_bytes = values(data_bytes),
    index_bytes = values(index_bytes), free_bytes = values(free_bytes), key_last = values(key_last);
end//
delimiter ;
sql
-- the event scheduler is ON by default in MySQL 8.4 (check: select @@event_scheduler)
create event db_health_daily
  on schedule every 1 day starts (current_date + interval 1 day + interval 5 minute)
  do call record_health(current_date);

The first line of the procedure matters more than anything else in this article. MySQL caches these table statistics, and information_schema_stats_expiry is 86,400 seconds by default: up to a day old. We ran the procedure twice per day, once with the default and once with the setting at 0:

text
MySQL 8.4.11, orders table      default (24 h cache)          expiry = 0                  true
day                             TABLE_ROWS   DATA_LENGTH       TABLE_ROWS   DATA_LENGTH   count(*)
10-23                              997,335       47.6 MB          997,335       47.6 MB   1,000,000
10-24                              997,335       47.6 MB        1,301,223       72.6 MB   1,300,000
10-27                              997,335       47.6 MB        2,385,323      169.7 MB   2,440,000
10-30                              997,335       47.6 MB        4,040,425      295.8 MB   3,940,000
whole schema, data, 10-30                        71.7 MB                       592.8 MB

With the default, all eight daily snapshots held the numbers of the first one: the table had grown sixfold and the history showed a flat line. Even AUTO_INCREMENT stayed frozen at the first day's value, so the key runway would never have moved. In a real daily schedule you'd get numbers that are anywhere from fresh to a day old, depending on when something last read them, which is worse than obviously wrong. Set the session to 0 in the procedure. With fresh statistics, TABLE_ROWS was within 2.5% of the true count (InnoDB samples pages for it), and the snapshot took 31 to 68 ms. The test event ran at 06:47:03 and 06:48:03, 7 ms each.

MySQL grew differently, and burned 9% more ids

text
MySQL 8.4.11, data + indexes per day (MB as pg_size_pretty counts them, 1,048,576 bytes)
day     orders          events          sessions    whole schema      DATA_FREE (schema)
10-23    90 MB            0 MB          13.5 MB      114 MB            18 MB
10-24   140 MB  +50      68 MB  +68     13.5 MB      256 MB  +142      20 MB
10-25   178 MB  +38     135 MB  +67     13.5 MB      361 MB  +105      22 MB
10-26   220 MB  +42     202 MB  +67     13.5 MB      470 MB  +109      23 MB
10-27   286 MB  +66     269 MB  +67     13.5 MB      603 MB  +133      22 MB
10-28   350 MB  +64     273 MB   +4     13.5 MB      672 MB   +69      86 MB
10-29   405 MB  +55     338 MB  +64     13.5 MB      791 MB  +119      85 MB
10-30   465 MB  +60     341 MB   +3     13.5 MB      854 MB   +63      84 MB

The same week took 854 MB on MySQL against 1,489 MB on PostgreSQL. InnoDB updates rows in place and keeps old versions in undo logs, so sessions stayed at 13.5 MB through 1.4 million rewrites, and orders didn't double its daily growth when the shipped updates began. Where MySQL shows churn is DATA_FREE: when the retention delete started on the 28th, events stopped growing for a day and the free space in the schema jumped from 22 MB to 86 MB, then new rows filled it on alternate days. Track DATA_FREE the way you track dead rows on PostgreSQL.

The surprise was in the keys. PostgreSQL's identity column used exactly one id per row: 2,940,000 orders in the week, 2,940,000 ids. MySQL used 3,211,215 ids for the same 2,940,000 rows, 9.2% more, and on customers it used 8,191 ids a day for 5,000 rows. This is innodb_autoinc_lock_mode = 2, the default since 8.0: for an INSERT ... SELECT, InnoDB doesn't know how many rows are coming, so it reserves ids in batches of 1, 2, 4, 8 and so on, and the unused end of the last batch is lost. 5,000 rows end inside the 4,096 batch, and 1 + 2 + ... + 4,096 = 8,191. So the runway must be computed from AUTO_INCREMENT, never from max(id) or a row count:

text
key runway, last 7 days     ids per day    days left
PostgreSQL orders               440,000           98
MySQL orders                    480,590           89      (same rows, 9.2% more ids)

MySQL has no regr_slope, so the MySQL version of the runway query takes the first and last value of the last seven days with first_value() / last_value() over a window; the full query is in our lab files and works on 8.0 and later.

Alerts: a view that is empty when all is well

The history is only useful if someone reads it, and nobody reads a healthy table every morning. Turn the rules into a view that returns rows only when something needs a look, and have whatever already runs on a schedule (a CI job, a cron line in your app, Pilotbase opened on Monday) check whether it's empty:

sql
create view health_alerts as
with last2 as (
  select *, row_number() over (partition by table_name order by taken_on desc) as rn
  from db_health
), runway as (
  select table_name, key_max,
         (array_agg(key_last order by taken_on desc))[1]    as last_value,
         regr_slope(key_last, taken_on - date '2000-01-01') as per_day
  from db_health where key_last is not null and taken_on > current_date - 7
  group by 1, 2
)
select table_name, 'key runs out in ' || floor((key_max - last_value) / per_day) || ' days' as problem
from runway where per_day > 0 and (key_max - last_value) / per_day < 180
union all
select a.table_name, 'dead rows ' || round(100.0 * a.dead_rows / nullif(a.live_rows + a.dead_rows, 0))
       || '% two days running'
from last2 a join last2 b on b.table_name = a.table_name and b.rn = 2
where a.rn = 1 and a.dead_rows > 0.2 * (a.live_rows + a.dead_rows)
               and b.dead_rows > 0.2 * (b.live_rows + b.dead_rows);
-- plus, in the lab files: a table that grew 3x its weekly average in one day

On the lab's last day, with the runway threshold at 90 days, the view returned one row: events | dead rows 33% two days running, the retention delete outpacing vacuum on a table that had just started deleting. With the threshold at 180, orders joins it. That's the right pair of warnings for this database: one for next week, one for next quarter.

What it costs to keep

One row per table per day. Our rows averaged 101 bytes, so a database with 200 tables adds 73,000 rows, around 10 MB with the primary key, per year. Keep it forever; the trend over a year is the most useful thing in it. The two scheduler logs need trimming (cron.job_run_details grows by one row per run and is never cleaned automatically), and on managed services check that the scheduler is on: on self-hosted MySQL 8.4 event_scheduler was ON by default; pg_cron needs the preload line and a restart once.

If the table you are watching runs out of runway, Running out of int: finding id columns near 2,147,483,647 and moving them to bigint is the fix; if dead rows stay high, Autovacuum falling behind is the tuning.

Open db_health next to your tables, run the runway and alert queries and keep them as saved snippets, on PostgreSQL, MySQL and more. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Planning the int-to-bigint change the runway warned you about? Paste the ALTER into sqlInspector to see which statements rewrite or lock the table on PostgreSQL and MySQL.

Open sqlInspector

Sources: pg_cron README (version 1.6: cron.schedule, cron.job_run_details not cleaned automatically, cron.timezone default GMT, one database per cluster); PostgreSQL 18 database object size functions, pg_stat_user_tables and aggregate functions (regr_slope); MySQL 8.4 information_schema_stats_expiry, the TABLES table, Event Scheduler configuration and AUTO_INCREMENT handling in InnoDB (lock mode 2, gaps for bulk inserts). Our test: PostgreSQL 18.6 with pg_cron 1.6 and MySQL 8.4.11 (defaults: event_scheduler ON, information_schema_stats_expiry 86400, innodb_autoinc_lock_mode 2) on an Apple-silicon Mac mini, October 2026; a compressed week of 7 days, scripts reproducible.