QueryPilotQueryPilot watches your queries, plans, locks and tables. When something changes, it finds the cause, gives you the SQL to fix it, and then checks that the fix actually worked.
Mean latency
1.8 ms38 ms 21×1.7 ms
SELECT … FROM orders WHERE customer_id = $1Index Scan → Seq Scan on orders
Cause: statistics are stale. 4.1M rows changed since the last ANALYZE.
ANALYZE orders;
p95 went from 84 ms to 5 ms. The before/after difference is statistically significant.
The problem
pg_stat_statements adds everything up since the last reset. It cannot tell you what got slower this afternoon, and nobody saved the plan that used to work.
pg_stat_statements on its own
total_exec_time since the last reset
With QueryPilot
Execution time per minute
Inside the app
Throughput, latency, cache and connections at a glance. The queries that cost the most rise to the top, and new findings arrive as they are detected.
orders-primary
productioncheckoutLast 1 hour · liveSELECT … FROM orders WHERE customer_id = $130%UPDATE inventory SET reserved = reserved + $1 …27%SELECT … FROM order_items WHERE order_id = $123%INSERT INTO events (type, payload, …) VALUES …11%SELECT count(*) FROM invoices WHERE account_id = $110%Plan regression on orders by customer
Index Scan → Seq Scan after 4.1M rows changed.
What it catches
Five things that take production Postgres down every day, and what QueryPilot shows you for each.
Reads: pg_stat_statements · EXPLAIN (GENERIC_PLAN) · pg_stat_user_tables
Detect
Mean latency is 19× its baseline since 14:32. Calls per minute did not change, so this is not extra traffic.
Explain
The planner switched from Index Scan to Seq Scan on orders. 4.1M rows changed since the last ANALYZE.
Fix
ANALYZE orders;Outcome
p95 went from 84 ms to 5 ms, measured over matching windows before and after.
Reads: pg_locks · pg_stat_activity · wait event samples
Detect
38 sessions are waiting on Lock:relation. Lock waits are 64% of all active time.
Explain
PID 4121 runs ALTER TABLE orders and holds an AccessExclusiveLock. It is itself waiting behind a transaction that has been idle for 2 minutes.
Fix
SET lock_timeout = '5s'; -- before running DDLOutcome
Blocking chain, waiting queries and the idle-in-transaction session, all on one screen.
Reads: pg_stat_statements call ratios · correlation over time
Detect
order_items by order_id runs 50.2 times for every orders by customer_id call, and the two rise and fall together.
Explain
The child query runs once per parent row. That is 20k round trips a minute for 400 page loads.
Fix
SELECT … FROM order_items WHERE order_id = ANY($1);Outcome
One batched query per page instead of 50, with 98% fewer calls.
Reads: pg_stat_user_tables · reloptions · age(relfrozenxid)
Detect
events has 12.4M dead rows, 6× its effective autovacuum threshold. It was last vacuumed 11 days ago.
Explain
A scale factor of 0.2 on an 18 GB table means autovacuum waits for 20% churn. Meanwhile sequential scans read the dead rows too.
Fix
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);Outcome
Dead rows and freeze age tracked per table, with the setting that caused the lag.
Reads: plan predicates · rows read vs returned · validation
Detect
p95 latency has grown every day for three weeks. Each call reads 2.3M rows to return 12.
Explain
Seq Scan on invoices with a selective filter on account_id, and no index covers it.
Fix
CREATE INDEX CONCURRENTLY ON invoices (account_id);Outcome
Validated as improved, with p95 98% lower. Dropping the index would have been flagged as a regression.
Sample data. Each scenario comes from a detection rule QueryPilot runs today.
How it works
Every step is deterministic and inspectable. No model guesses at your data, and nothing runs against your database without you.
Snapshots of query stats, activity, locks, wait events and table health every few seconds.
pg_stat_statements · pg_stat_activity · pg_locks · catalogs
Detection rules compare each window with its history: regressions, lock storms, bloat, N+1, invalid indexes.
deterministic rules, no black box
Every finding carries its evidence: the plan diff, the blocking chain, the stale statistics.
plan fingerprints · wait profiles
A concrete fix with the SQL to run: an index, ANALYZE, a vacuum setting, a batched query.
you apply it, QueryPilot never writes
Before and after windows are compared statistically, so "it feels faster" becomes "improved" or "regressed".
Welch's t-test on latency
Features
01 · See
Everything PostgreSQL knows, kept over time.
Query history
Per-query latency, calls, rows and I/O rebuilt from pg_stat_statements deltas, for any window.
Plan capture & diff
Plans fingerprinted by shape. See the exact node that changed, like Index Scan to Seq Scan.
Waits & locks
Sampled wait events per query, plus blocking chains with the session at the root.
Table & index health
Dead rows against the real autovacuum threshold, freeze age, unused and invalid indexes.
02 · Understand
Findings with evidence, not dashboards to stare at.
Regression detection
Latency compared against each query’s own history, tied to the plan change that caused it.
N+1 detection
Child queries whose call rate tracks a parent query’s rows, found from statistics alone.
Severity & evidence
Every finding explains itself: which numbers, which window, which rule, and why it matters.
Environments & tags
Group databases by production, staging or your own tags, and filter everything by them.
03 · Fix
The SQL to run, and proof it worked.
Index advice
Missing, duplicate and unused indexes, with CREATE INDEX CONCURRENTLY or DROP INDEX ready to copy.
Maintenance advice
ANALYZE for stale statistics, autovacuum settings for tables that fall behind.
Query rewrites
Batching suggestions for N+1 patterns, with the query shape to use instead.
Fix validation
Before and after windows compared with a significance test: improved, no change or regressed.
Built for production
A small agent next to Postgres reads statistics and pushes them out over HTTPS. Everything else runs on your servers, on a plain PostgreSQL you already know how to operate.
Read the architecture guide →Read-only sessions
default_transaction_read_only=on, so PostgreSQL itself rejects any write.
Plans never execute
EXPLAIN (GENERIC_PLAN) inside a transaction that is rolled back.
Bounded by design
Two connections, row caps and statement timeouts on every collector.
Outbound-only agent
No inbound ports to open next to your database.
Credentials encrypted
AES-256-GCM at rest and never returned by the API.
No parameters stored
Query text is normalized, and you can turn text collection off entirely.
Quickstart
Run QueryPilot
One Docker Compose file: web, API, worker and storage.
Connect a database
A least-privilege role with pg_read_all_stats.
Start the agent
Next to your database. Data arrives within seconds.
Needs Docker and PostgreSQL 12+ with pg_stat_statements. Plan capture needs 16+.
QueryPilot is in early preview. The repository and these commands become available when the source is published.
# The repository becomes available at launch
cd querypilot && cp .env.example .env
sed -i.bak "s|^QUERYPILOT_ENCRYPTION_KEY=.*|QUERYPILOT_ENCRYPTION_KEY=$(openssl rand -base64 32)|" .env
docker compose up -d --build
# open http://localhost:3000Roadmap
Detection, recommendations and validation are built. Next, QueryPilot comes to where your team already works: your AI tools, your alerts, your dashboards and your pull requests.
Plans, not promises. QueryPilot will never apply changes to your database on its own.Full roadmap →
No. Every agent session is opened with default_transaction_read_only=on, so PostgreSQL itself rejects any write. Plan capture uses EXPLAIN (GENERIC_PLAN), which plans a query without running it, inside a read-only transaction that is rolled back. EXPLAIN ANALYZE is never run automatically.
Security model →Very little, by design. It uses two connections at most, caps the rows of every collector query and sets a 5 second statement timeout. Collectors run every 5 to 60 seconds, and each run is scheduled from the end of the previous one, so a slow database gets longer gaps instead of a pile-up. The agent reports its own collection time on /metrics.
Installing the agent →PostgreSQL 12 and newer. WAL metrics need 13+, and automatic plan capture needs 16+ because it relies on EXPLAIN (GENERIC_PLAN). On older versions you can still upload plans you captured yourself.
Yes. QueryPilot only needs pg_stat_statements enabled and a role that can read the statistics views. On RDS and Aurora, add pg_stat_statements to the parameter group. On Cloud SQL, set the cloudsql.enable_pg_stat_statements flag. On Azure, set shared_preload_libraries. Run the agent anywhere that can reach both the database and the QueryPilot API.
PostgreSQL prerequisites →No. A dedicated role with pg_read_all_stats and CONNECT covers everything. Plan capture also needs SELECT on the tables a query touches, because PostgreSQL requires it to plan the query. Without it, only those captures fail.
QueryPilot stores statistics, normalized query text and sanitized plans in its own PostgreSQL. Query parameters are never stored, and literal values are stripped from plans on your host before they are sent. You can turn query text off entirely. There is no hosted service and no phone-home telemetry.
No. Detection and recommendations come from deterministic rules you can inspect, and every finding shows the numbers behind it. Optional AI explanations are on the roadmap and will be off by default.
Yes. QueryPilot will be released as open source under the Apache-2.0 license, and it is self-hosted, so you run it on your own infrastructure at no cost. The repository is published at launch.
Not yet. It is on the roadmap. Until then, run QueryPilot on a trusted network, behind a VPN or behind an authenticating reverse proxy.
Production checklist →QueryPilot is PostgreSQL only. Focusing on one database is what lets it read plans, locks and table health in depth.
The live demo runs the real interface on sample data. Follow a regression from the first finding to the verified fix.