Concepts

This page walks through the building blocks of QueryInsights one at a time. If you have read the Architecture overview, this fills in what each piece actually does.

Poller

The poller captures queries from databases that expose their workload as statistics tables. It is intentionally a thin data collector: it does not parse, normalize, or store anything. It reads, filters, samples, and forwards.

• PostgreSQL — reads pg_stat_statements, joining pg_database to resolve each query's database name. Because pg_stat_statements is cluster-wide, the poller can fall back to the maintenance postgres database if the DSN's own database doesn't have the extension installed.
• CockroachDB — reads crdb_internal.statement_statistics, skipping internal application names and DDL statements.

Each configured instance gets its own goroutine and poll interval (default 60 seconds).

Pollers do not sample. All three statistics views are already aggregated: one row covers every execution of a normalized statement (per database and user on PostgreSQL, per schema on MySQL, per app/plan/hour on CockroachDB) and carries its execution count. A query run a million times is one row with calls = 1000000, so there are no individual executions to sample.

Instead each poller reads the cumulative counters (calls, total execution time, rows returned, rows examined, executions without a usable index) and sends only the change since the previous poll. The first poll after start sets a baseline; a counter that goes backwards means the statistics were reset and the current value is the delta. Each execution is therefore counted exactly once regardless of the poll interval, and a quiet database sends nothing.

Pollers read the union of the top statements by total time, by calls, and by rows examined, so a rare but expensive query is not cut off by a busy cheap one. Internal and utility statements (system catalogs, SET, SHOW, transaction control, DDL, and known admin tools) are filtered out before anything is forwarded.

Deltas are sent to the engine in batches via the SubmitQueryStats gRPC call, tagged with the engine, source instance, database, user (PostgreSQL) or application name (CockroachDB).

The legacy sampling_rate field is still accepted in instance configs but ignored, with a warning in the poller log.

Webhook sampling

The Trino webhook is different: Trino posts one event per query, so sampling does reduce volume there. Set QUERYINSIGHTS_WEBHOOK_SAMPLING_RATE (0–1). Each kept event is weighted by 1/rate so call counts and time totals stay unbiased. Only completed events are ingested, because they carry wall time and rows scanned; created events carry no cost and would double-count.

Engine

The engine is the centralized brain. It is a gRPC server that owns all processing and all writes to the metastore. For every query it receives it:

1. Parses the SQL into a structured analysis (query type, tables, columns, joins, clause presence, subquery count).
2. Normalizes it into a fingerprint by replacing literals with placeholders.
3. Upserts the normalized pattern, deduplicating by (normalized_query, engine), and records a raw-query sample.
4. Recommends — for a new pattern with resolvable tables, it kicks off a background per-query recommendation.

The engine also runs the workload-insight ticker, which periodically produces holistic, cross-pattern advice per engine/instance (see Recommendations below).

Internally the engine keeps a map of recommendation services keyed by engine name (trino, cockroachdb, postgresql), each wired to the matching metadata provider, and selects the right one based on the query's engine label.

Parsing and normalization

Parsing turns raw SQL into a structured Analysis, and normalization turns it into a stable fingerprint. The engine routes by engine type:

• Trino — the official Trino SQL grammar compiled with ANTLR; a visitor walks the parse tree to extract metadata, and a token-stream normalizer replaces literals.
• CockroachDB — a yacc-style lexer and structural analyzer.
• PostgreSQL / others — a regex-based normalizer that strips comments, replaces string/numeric/dollar-quoted literals, and collapses whitespace.

Normalization is what makes the product tractable: a thousand variants of the same statement collapse into one pattern, so you optimize shapes, not noise.

-- Original
SELECT name FROM users WHERE id = 42 AND status = 'active'

-- Normalized
SELECT name FROM users WHERE id = ? AND status = ?

Metadata providers (schema/DDL)

Recommendations are only useful if they are grounded in your real schema. The SchemaProvider interface fetches the DDL for the tables a query touches:

Providers are multi-instance: the engine keeps one provider per configured instance and connects lazily to the specific database a query came from. Table names are validated before being interpolated into any metadata query.

LLM provider

The LLM provider is the abstraction over the model backend that writes the actual recommendations. It exposes two operations — one for per-query rewrites and one for workload insights — behind a single interface, so support for a new backend is a matter of implementing that interface.

Two backends ship today:

• OpenAI — chat completions using OpenAI's Structured Outputs, so the model is forced to return a strict { summary, details } JSON envelope.
• Anthropic — Claude models.

Which backend runs is decided from the configured model name:

• If the model name contains claude and an Anthropic API key is set → Anthropic.
• If the model name contains claude but only an OpenAI key is set → the OpenAI-compatible client (this is the expected path when routing Claude through an OpenAI-compatible gateway such as LiteLLM or Portkey).
• Otherwise → OpenAI.

A custom base URL lets you point either client at a gateway instead of the vendor's public API. Generation uses a low temperature and a hard token cap so output stays focused and per-call cost is bounded. If no API key is configured, the engine simply starts without recommendation features — ingestion and the dashboard still work.

See Configuration for the exact variables.

Recommendations: two scopes, one feed

QueryInsights produces two deliberately non-overlapping kinds of advice, surfaced together in the dashboard with scope and kind chips:

Keeping the two scopes non-overlapping means the advice stays complementary instead of contradictory.

Idempotency and safety

Because recommendation generation calls an LLM, the engine takes care to never do it twice for the same pattern:

• An is_recommendation_generated flag is checked before any LLM call.
• SELECT ... FOR UPDATE inside a transaction serializes concurrent workers racing on the same pattern.
• In-flight claims and per-table caps prevent a burst of new patterns from spawning unbounded concurrent LLM calls.
• Failed attempts are counted and permanently skipped after a configurable limit.

These controls make ingestion safe to run continuously against a live, high-volume workload.