CockroachDB data freshness check and alerts

Connect CockroachDB

Add a CockroachDB connector and enter host, port (26257 by default), user, password, database name and SSL mode. The connector reuses arcodash’s PostgreSQL implementation, since CockroachDB speaks the Postgres wire protocol. The form and the connection test are described in Connectors overview.

Give arcodash a role that can only read. Syntax from the CockroachDB GRANT, ALTER DEFAULT PRIVILEGES and CREATE USER pages:

CREATE USER arcodash_ro WITH LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE app TO arcodash_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO arcodash_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner GRANT SELECT ON TABLES TO arcodash_ro;

Passwords exist only on secure clusters; the CREATE USER docs say password creation is unsupported in insecure mode. ALTER DEFAULT PRIVILEGES FOR ROLE must be run by a member of the creating role (app_owner here) and covers tables that role creates later.

Network and SSL: arcodash Cloud has no published outbound IP list, so the cluster must accept connections from the internet and an exact-IP allowlist will not work. Use SSL mode require. The default, prefer, falls back to plaintext if the server offers no TLS, and CockroachDB’s own docs describe prefer as accepting an insecure connection. Copy host, port, user and database name from your cluster’s connection details rather than composing them by hand.

A CockroachDB data freshness check

The concept and the plain-Postgres queries live in data freshness and Postgres data freshness check: queries you can copy. Here is the CockroachDB shape. It returns one number, which is what a monitor needs (it reads the first row and compares one column with a threshold):

SELECT COALESCE(extract_duration('second', now() - max(loaded_at)), 31536000)
       AS lag_seconds
FROM events AS OF SYSTEM TIME follower_read_timestamp();

Two CockroachDB specifics. AS OF SYSTEM TIME sits at the end of the FROM clause, and the whole statement then runs at that past timestamp. follower_read_timestamp() is statement_timestamp() minus about 4.2 seconds per the docs, which lets any replica serve the read. Your lag is therefore overstated by a few seconds, harmless against a threshold of minutes. And extract_duration turns the interval into a number of seconds; now() - max(...) alone is an interval, which a monitor can’t compare.

Monitor lag_seconds with > and a threshold such as 1800. The COALESCE makes an empty table read as a year of lag instead of landing in the unknown state. Schedule it from scheduled refresh.

A volume check

SELECT count(*) AS rows_last_hour
FROM events AS OF SYSTEM TIME follower_read_timestamp()
WHERE loaded_at >= now() - INTERVAL '1 hour';

Alert with < and a floor, or use a baseline monitor that learns normal volume, which is described in the alerts overview. A drop to zero is the clearest failure; see row count anomaly SQL for choosing a floor.

A metric alert

SELECT COALESCE(
  ROUND(100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0), 1), 0) AS failed_pct
FROM payments AS OF SYSTEM TIME follower_read_timestamp()
WHERE created_at >= now() - INTERVAL '1 hour';

Alert on failed_pct > 5. Here staleness matters more: if you want the page to fire the moment a burst of failures lands, drop the AS OF clause and accept the contention cost. Slack delivery is covered in alert to Slack with SQL.

Gotchas when monitoring CockroachDB

• Follower reads trade freshness for cost. They avoid waiting on the leaseholder and on contended writes. They also cannot show a row committed four seconds ago.
• Not for writes or locking reads. The docs say the clause can’t be combined with FOR UPDATE or FOR SHARE, or with mutation statements. Checks are SELECTs, so this only bites if you paste something else.
• Multi-region latency. A query from arcodash to a leaseholder in another region adds a round trip. Follower reads let the nearest replica answer.
• Wide time windows cost scans. Filter on an indexed timestamp column. A check that scans a billion-row table every five minutes is real work for the cluster even on followers.

What a dashboard on CockroachDB looks like

A daily paid volume line, using a cast to DATE:

SELECT created_at::DATE AS day, count(*) AS payments, sum(amount) AS paid
FROM payments AS OF SYSTEM TIME follower_read_timestamp()
WHERE status = 'paid' AND created_at >= now() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 1;

A table for failed background jobs, which CockroachDB exposes through SHOW JOBS. The docs document it as the source of a SELECT in a WITH clause:

WITH x AS (SHOW JOBS)
SELECT job_id, job_type, status, created, error
FROM x
WHERE status = 'failed'
ORDER BY created DESC
LIMIT 20;

Change the select list to count(*) AS failed_jobs and the same query becomes a monitor that fires on any failed schema change or backup. Your own tables follow the same shape as the Postgres dashboards in the freshness post.

You can also let the connector page suggest checks with AI from the schema, using your own provider key. Free for 14 days, no card, then from $59 a month with unlimited seats.

docs

CockroachDB setup in the docs

Connection fields, an example configuration and caveats.