Postgres data freshness check and dashboards

Connect PostgreSQL

Add a PostgreSQL connector and fill in host, port (5432 by default), user, password, database name and SSL mode. The field-by-field walkthrough, including how a connection test works, is in Connectors overview.

Make a dedicated role. arcodash sends your SQL to the database unchanged, so privileges are your only guardrail. This was run on Postgres 18:

CREATE ROLE arcodash_ro LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE app TO arcodash_ro;
GRANT USAGE ON SCHEMA public TO arcodash_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO arcodash_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO arcodash_ro;
ALTER ROLE arcodash_ro SET default_transaction_read_only = on;

The ALTER DEFAULT PRIVILEGES line covers tables created later by the role that ran it; repeat it for each role that creates tables. default_transaction_read_only makes writes fail even if someone grants more than intended. If you want a check on pg_stat_activity to see other sessions’ queries, also run GRANT pg_read_all_stats TO arcodash_ro;.

Network and SSL, from how arcodash Cloud works today: the database must be reachable from the internet, and arcodash does not publish fixed outbound IP addresses, so a tight IP allowlist will not work. The SSL mode options are disable, prefer (the default) and require. prefer silently falls back to plaintext when the server offers no TLS, so choose require for anything real. Each query also runs with a statement_timeout of 300 seconds, and a result keeps at most 10,000 rows (see Limits). A check returns one row, so neither matters for monitoring.

A Postgres data freshness check

The queries for a stale table, several tables and the timezone traps are in Postgres data freshness check: queries you can copy, and the concept is in data freshness. I won’t repeat them. What changes when you run it in arcodash is the shape: a monitor reads the first row of the result, takes one column and compares it with a threshold. So return a number, not a filtered row set:

SELECT coalesce(extract(epoch FROM now() - max(loaded_at)), 31536000)::int
       AS lag_seconds
FROM events;

Create the monitor on lag_seconds with > and 7200 for a two hour limit. The coalesce turns an empty table into a year of lag, so it triggers instead of landing in the unknown state. Put the query on a schedule (every 5, 15, 30 minutes or hourly) so it runs without anyone opening it. See scheduled refresh and data observability monitors.

A volume check

Freshness says the newest row is recent. It does not say the load was complete. Count what landed in the last hour:

SELECT count(*) AS rows_last_hour
FROM events
WHERE loaded_at >= now() - interval '1 hour';

Monitor rows_last_hour with < and a floor well under a normal hour. If your volume has a daily rhythm, a fixed floor either misses a quiet-hour collapse or cries wolf overnight. A baseline monitor learns the normal level from the last 30 observations instead, with the caveat that it has no weekly seasonality model. More on the pattern in row count anomaly SQL.

A metric alert: failed payment rate

Business metrics fit the same shape. This one returns the share of payments that failed in the last hour:

SELECT coalesce(
  round(100.0 * count(*) FILTER (WHERE status = 'failed')
        / nullif(count(*), 0), 1), 0) AS failed_pct
FROM payments
WHERE created_at >= now() - interval '1 hour';

Alert on failed_pct > 5. FILTER (WHERE ...) is Postgres syntax and keeps the query short. nullif avoids a divide by zero in a quiet hour, and coalesce returns 0 so the monitor reads a number rather than unknown. Send it to Slack (how) or PagerDuty depending on how loud it should be.

Gotchas when monitoring Postgres

• timestamp vs timestamptz. now() returns timestamptz. Comparing it to a plain timestamp column casts using the session time zone, which can shift your lag by hours. Store timestamptz, or compare against now()::timestamp knowing the column is UTC.
• Replicas lie by omission. On a streaming replica, replay lag looks like stale data. SELECT coalesce(extract(epoch FROM now() - pg_last_xact_replay_timestamp()), 0)::int returns seconds behind the primary, and 0 on a primary. It is also the number that explains a freshness alert on a replica.
• Long checks hold resources. A check that scans a large table every five minutes is a load on production. Filter on an indexed timestamp, as the volume check does, or use pg_stat_user_tables as the post on freshness explains.

To generate freshness, row count, null and duplicate checks for a Postgres table, use the Postgres data check generator.

What a dashboard on Postgres looks like

Three tiles that need nothing but SQL:

SELECT date_trunc('day', created_at) AS day,
       count(*) AS payments,
       sum(amount) AS paid
FROM payments
WHERE status = 'paid' AND created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

A daily line chart of paid volume. Next, a loading rhythm bar chart by hour, which makes a skipped batch visible at a glance:

SELECT date_trunc('hour', loaded_at) AS hour, count(*) AS rows_loaded
FROM events
WHERE loaded_at >= now() - interval '24 hours'
GROUP BY 1 ORDER BY 1;

Last, a table-health tile from the stats collector:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

If you would rather not write the SQL, Suggest checks with AI on the connector page proposes freshness and row-count checks from the schema, and Ask with AI drafts queries. You bring your own provider key. Free for 14 days, no card, then from $59 a month with unlimited seats.

docs

PostgreSQL setup in the docs

Connection fields, an example configuration and caveats.