Query-level database monitoring

The database checks answer is it healthy: replica lag, connection pressure, cache hit rate. They cannot answer the question that always follows, which query is doing this. That gap is why teams keep a second tool pointed at their database.

Off by default. Enable it on a collector that can already reach the database:

ROOTTRACE_DB_QUERY_STATS=true
ROOTTRACE_DB_QUERY_STATS_LIMIT=100   # statements per engine per sample, max 500

What is read

Each engine's own statistics view, never the literal SQL your application ran.

A missing pg_stat_statements is not an error worth alerting on (not installing it is a choice), so the check degrades to reporting no statements rather than failing.

Enabling the extension on PostgreSQL:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then, in the database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Parameters are never stored

This is the part worth understanding before you turn it on.

Every statistics view above normalises parameters out before RootTrace sees anything. What arrives is a query shape (SELECT id FROM orders WHERE tenant_id = $1), and a shape cannot leak the row it touched.

RootTrace then redacts again on arrival, because one literal the engine failed to normalise is a customer record sitting in a monitoring tool. The second pass removes quoted strings, long numbers, anything shaped like an email or a token, and long hex strings. It is deliberately aggressive: an over-redacted digest still ranks cost perfectly, and the trade is not close.

Statements are truncated at 4,000 characters and stored under a digest of (engine, database, normalised statement).

Counters, not samples

These views count since the server (or the view) was last reset. The collector ships absolute values and the server stores the delta against the previous sample.

A database restart sends the counter backwards. That is recorded as a gap, not as a delta: posting an enormous negative would be wrong, and clamping it to zero would silently hide the restart. The first sample after enabling the check is a baseline with nothing to compare against, so the response reports baseline_only: true rather than looking mysteriously empty.

Reading it

Queries in the sidebar, or GET /api/db-queries/top.

Ranked by total time by default, which is the ranking that finds the query worth fixing: a slow statement called twice matters less than a fast one called ten million times, and mean time alone hides the second entirely. Also available: calls, mean_time, rows.

Mean time is recomputed from the summed counters rather than averaged across windows, so a quiet window does not weigh the same as a busy one.

Opening a statement shows its shape, when it was first and last seen, and its cost over time.

Retention

Both collections follow the workspace's plan retention.

API

top accepts sort, engine, host, database, and either a preset time_range or an explicit start/end.