Postgres data freshness check: queries you can copy

The most common way a dashboard fails is not a wrong number. It’s a right number from last Thursday. The pipeline broke, the table stopped getting rows, and every chart kept rendering without complaint.

A freshness check is the cheapest fix there is: one query, run on a schedule, that says “the newest row is older than it should be.”

The single-table check

This returns a row only when the table is stale. A healthy table returns nothing.

SELECT
  now() - max(loaded_at) AS lag,
  max(loaded_at)         AS last_row
FROM events
HAVING coalesce(max(loaded_at), '-infinity')
       < now() - interval '2 hours';

Two details matter here. An aggregate with no GROUP BY always produces exactly one row, even over an empty table, so HAVING is what decides whether you see it. And max() over zero rows is NULL, and NULL < anything is NULL, which is not true. Without the coalesce, an empty table passes the check silently, which is the worst possible outcome. Coalescing to -infinity makes “no rows at all” count as stale.

If your alerting tool wants a number to compare against a threshold instead of “did a row come back,” return the lag in seconds and put the threshold in the tool:

SELECT extract(epoch FROM now() - max(loaded_at)) AS seconds_since_last_row
FROM events;

Many tables at once

Writing twenty of these by hand gets old. Put the tables, their timestamp columns, and their allowed lag in a VALUES list and UNION ALL the checks:

WITH latest AS (
  SELECT 'events' AS tbl, max(loaded_at) AS last_row FROM events
  UNION ALL
  SELECT 'orders', max(loaded_at) FROM orders
  UNION ALL
  SELECT 'customers', max(updated_at) FROM customers
),
limits(tbl, col, max_lag) AS (
  VALUES
    ('events',    'loaded_at',  interval '2 hours'),
    ('orders',    'loaded_at',  interval '26 hours'),
    ('customers', 'updated_at', interval '26 hours')
)
SELECT l.tbl, l.col, x.last_row, now() - x.last_row AS lag, l.max_lag
FROM limits l
JOIN latest x USING (tbl)
WHERE coalesce(x.last_row, '-infinity') < now() - l.max_lag;

Plain SQL can’t take a table name from a value, so each table still needs its own max() line in latest. The VALUES list keeps the tolerances in one place where you can read and review them. Adding a table is two lines.

Every table in the result is one that’s late, along with how late. No rows means everything is fine.

Pick the window from how the table loads

The threshold is the part people get wrong, and it’s where false alarms come from.

A table loaded by a nightly batch at 02:00 should not have a two-hour limit. It will be “stale” 22 hours a day. Give it the load interval plus slack: for a daily job, 26 or 27 hours. That tolerates a late run but still catches a skipped one.

A table fed by streaming or a frequent sync can have a tight limit, but not tighter than its quietest period. A table that takes events from a B2B product is going to have quiet Sunday nights. If the check pages someone every Sunday at 3am for a table that is working fine, the check gets muted, and a muted check is worse than none.

Say an events table usually gets a row every few seconds on weekdays and one every few minutes at night. A limit of 30 minutes would cover both. A limit of 5 minutes would fire every night.

The trap to avoid is the page-every-morning one: a daily-batch table checked hourly with a limit shorter than the batch gap. Match the check to the load, not to how fresh you wish the data were.

created_at, updated_at, or loaded_at

Which column you take the max() of changes what you’re measuring.

• created_at is when the event happened in the source system. If the source is quiet, it looks stale even when your pipeline is fine. If a backfill loads old rows, it looks stale even though data just arrived.
• updated_at only moves when the source row is edited. It’s right for dimension tables like customers, and wrong for append-only event tables.
• loaded_at is when your pipeline wrote the row. This is the one you want, because the question is “is my pipeline delivering,” not “did something happen in the business.”

If your tables don’t have a loaded_at, add one with DEFAULT now(). It costs one column and makes every freshness check honest. If you can’t, updated_at or created_at works, but widen the window to cover quiet periods in the source.

Timezone pitfalls

Most bad freshness alerts at some point turn out to be a timezone bug.

timestamptz stores an instant, and now() returns one, so now() - max(ts) is correct no matter what the session timezone is. timestamp (without time zone) stores a wall-clock reading with no zone attached. Compare it to now() and Postgres converts now() to the session’s timezone first, so the answer depends on how the connection is configured.

If a pipeline writes UTC into a plain timestamp column and your session is set to America/New_York, the lag comes out four or five hours off (negative, in this case) depending on the season. You can check what your session does:

SHOW timezone;

The fix for a plain timestamp column that holds UTC is to say so explicitly:

SELECT now() - max(loaded_at AT TIME ZONE 'UTC') AS lag
FROM events;

Better, migrate the column to timestamptz so the question never comes up. A lag that’s off by a whole number of hours is almost always this.

The cheap signal: pg_stat_user_tables

Postgres tracks activity per table without you adding anything:

SELECT relname, n_tup_ins, n_tup_upd, last_autoanalyze, last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('events', 'orders', 'customers');

n_tup_ins is a running count of inserted rows. Sample it twice on a schedule and a flat number means nothing was inserted in between. last_autoanalyze moves when autovacuum decides enough rows have changed.

It’s handy when you have no usable timestamp column. But it’s a weak check:

• The counters reset on a crash recovery or a stats reset, so you can only compare deltas, which means storing the previous sample somewhere.
• last_autoanalyze only moves after a threshold of changes, so on a big table it can lag days behind reality, and on a small one it can stay still while data flows.
• It counts rows inserted, not whether they’re the right rows. A job that loads the same stale file every night looks perfectly alive.
• On a read replica it reflects the replica, not the primary.

Use it to find which tables are dead, then put a real max(loaded_at) check on the ones that matter.

Running it on a schedule

A query nobody runs is a script in a drawer. Whatever you use, cron plus a Slack webhook, a scheduled job in your orchestrator, or a monitoring tool, the check has to run without anyone opening it, and the alert has to go somewhere people look.

This is the shape of check arcodash runs as a scheduled freshness monitor. You write the query, set the threshold, and pick Slack, email or PagerDuty. You can also do this with a cron job and psql. The queries above work the same either way.

If you want to skip the typing, the Postgres data check generator fills in your table and timestamp column and writes the freshness query for you, plus row count, null and duplicate checks.