Introduction

QueryPilot tells you which queries your PostgreSQL database spends its time on, how that changed over time, and why, down to the execution plan. It will be open source (Apache-2.0), runs entirely on your own infrastructure, and observes your database strictly read-only.

Read-only by construction

The agent connects with default_transaction_read_only=on, so PostgreSQL itself rejects any write. Plans are captured with EXPLAIN (GENERIC_PLAN): planned, never executed.

Self-hosted, nothing leaves the host

docker compose up brings up the whole stack. No cloud account, no LLM, no external telemetry service is required.

Built on pg_stat_statements

Workload data comes from the statistics PostgreSQL already keeps. Missing capabilities degrade one collector, never the whole system.

Honest numbers

Gaps are drawn as gaps, not zeroes. Estimated percentiles and estimated plans are labelled as estimates everywhere they appear.

What works today

QueryPilot is at version 0.1.0. These features are implemented in the current codebase:

Area	What you get
Databases	Register a PostgreSQL database (credentials encrypted at rest with AES-256-GCM), test the connection before saving, and see a capability checklist with remediation for anything missing.
Agents	Create an agent per database, get a one-time token and a ready-to-run `docker run` command, rotate or revoke tokens, and see each agent’s status, version and collector errors.
Collection	Query statistics (`pg_stat_statements`), activity, locks, connections, table and index statistics, each on its own interval, bounded and timeout-protected.
Database overview	Calls/min, mean latency, load, cache hit ratio, connections, blocked sessions, time by statement type and the largest tables over a selectable window.
Query explorer	Every normalized query with calls, mean, estimated p95, total time, rows/call, cache hit ratio and blocks read. Searchable, filterable, sortable and paginated.
Query history	Per-query latency (mean + estimated p50/p95/p99), throughput and I/O charts with stats-reset and plan-change markers.
Execution plans	Automatic and on-demand plan capture (PostgreSQL 16+), EXPLAIN JSON upload (including `ANALYZE`), structural plan fingerprints, an annotated plan tree and a semantic plan comparison.
Findings	16 deterministic rules run every minute: query regressions, sequential scans, row-estimate mismatches, expensive sorts and nested loops, I/O-heavy queries, N+1 queries, lock contention, long transactions, connection saturation, table bloat, stale statistics, wraparound risk and unused, duplicate or invalid indexes.
Recommendations	The SQL to fix a finding (`CREATE INDEX CONCURRENTLY`, `ANALYZE`, `VACUUM`, autovacuum settings, `DROP INDEX CONCURRENTLY`, batched queries) with risk and safety notes. You run it; QueryPilot never writes.
Validation	Mark a recommendation applied and QueryPilot compares before and after windows statistically: improved, no change, regressed or inconclusive.
Locks & waits	Sampled wait events per query, blocking chains, and the queries that wait, block and get blocked.
Tables & indexes	Dead rows against each table’s real autovacuum threshold, analyze lag, freeze age, index usage and size history.
Organization	Environments (production, staging, development, testing) and tags, with filters and grouping.
Settings	Per-database plan-capture settings applied to running agents, plus per-browser preferences: theme, time zone, relative times, default time range and auto-refresh.
Operations	Liveness/readiness probes, Prometheus `/metrics` on every service, an OpenAPI description at `/api/docs`.

AI explanations, an MCP server, alerting, Prometheus and OpenTelemetry export, and CI regression checks come next; see the Roadmap.

Where to next

QuickstartRun QueryPilot with Docker Compose in a few minutes.

ArchitectureHow the agent, API, worker and web app fit together.

PostgreSQL prerequisitespg_stat_statements and a least-privilege role.

Live demoClick through the real UI with sample data, no install needed.