Insights and X-ray | StreamQL documentation

Find slow statements, tell whether the pooler or the database is slow, and get advice that is never applied automatically.

What it collects#

A sample of statements from the data path. Sampling is bounded and non-blocking, and dropped samples are counted.
Per-template latency quantiles with 2% relative error, split into pool wait, database time and lock wait, kept in 5-minute and hourly buckets for 8 days.
A same-hour 7-day baseline with a robust regression test and change-point detection.
pg_stat_statements deltas (columns detected on PostgreSQL 13 to 19), wait-event sampling, blocking chains and table and index statistics, from a collector connection to the upstream.
Plans captured with plain EXPLAIN (GENERIC_PLAN on 16 and later, a generic prepared plan on 13 to 15), never EXPLAIN ANALYZE, plus auto_explain log ingestion with constants scrubbed.

Hints#

Hints are advice only. They cover missing, unused and duplicate indexes, stale statistics, bloat, lock contention, long transactions, N+1 patterns, pool-bound templates, work_mem spills, JIT, generic plans, plan regressions and timeouts. Index candidates are validated with HypoPG when it is installed. Each hint has a lifecycle, notes, export and audit records, and can generate pg_hint_plan hint-table SQL (generated only, never run).

Configuration#

Key	Default	Meaning
`insights.enabled`	true	Collect insights
`insights.sample_every`	1	Sample one statement in this many
`insights.retention_days`	8	How long buckets are kept
`insights.persist_file`	unset	Persist state to a file (or `persist_table` for `streamql.insights_state`)
`insights.plans.use_hypopg`	true	Validate index candidates with HypoPG
`insights.plans.min_interval_ms`	900000	Minimum time between plan captures of one template
`insights.hints.min_table_rows`, `min_template_calls`	10000, 5	Thresholds for hints
`insights.collector.enabled`	true	The database-side collector
`insights.collector.user`, `password`, `database`	unset	Collector role; it needs `pg_read_all_stats` and, for other roles' statement text, `pg_monitor`
`insights.collector.statements_every_ms`	60000	Statement statistics interval
`insights.collector.activity_every_ms`	1000	Activity sampling interval
`insights.collector.auto_explain_log`	unset	Path of an `auto_explain` log to ingest
`insights.behavior.enabled`	false	Optional slow-risk estimates

pg_stat_statements must be in shared_preload_libraries. The admin API is under /api/v1/insights/*.

Slow-risk estimates#

An optional model estimates the chance that an execution will be slow. It is off by default (insights.behavior.enabled), runs inside the StreamQL process with a bounded native evaluator, calls no external service, and is advisory only: it never changes findings, hints or execution. The console can switch it at run time.

Measured on two shifted held-out workloads, precision was 0.92 and recall 7 to 16 percent. When the model raises a flag it is usually right, but it misses most slow executions, because many are short pool-saturation bursts that history cannot foresee. Treat it as a research feature.

X-ray#

X-ray reads pg_stat_statements snapshots and table statistics and reports labelled database time by template, the coalescable and cacheable fractions, pooling pressure and recommendations, as JSON or Markdown, with ground-truth tests on PostgreSQL 13 to 19. Numbers are estimates or upper bounds and are never presented as measurements. X-ray is in progress: log ingestion is not complete, and caching itself is not part of the current data path.