PostgreSQL prerequisites

QueryPilot observes your database. It never writes to it.

The agent opens every connection with default_transaction_read_only=on, so the server itself rejects any write the collector could attempt. This is enforced by PostgreSQL, not by good intentions.

Supported versions

PostgreSQL	Support
12 and newer	Supported. Older versions fail the connection test’s version check.
13 and newer	WAL metrics from pg_stat_statements are collected.
16 and newer	Execution plan capture (EXPLAIN (GENERIC_PLAN)) is available.

Least-privilege role

Create a dedicated role. Do not use a superuser.

CREATE ROLE querypilot WITH LOGIN PASSWORD 'choose-a-strong-password';

-- Read the statistics views, including other users' queries.
-- Available from PostgreSQL 10 onward.
GRANT pg_read_all_stats TO querypilot;

-- Allow connecting to the database you want observed.
GRANT CONNECT ON DATABASE your_database TO querypilot;

-- Needed to resolve table and index names.
GRANT USAGE ON SCHEMA public TO querypilot;

pg_read_all_stats is the important one. Without it, pg_stat_activity shows only the monitoring role’s own queries and pg_stat_statements hides other roles’ statements: the collectors run but see an almost empty database.

Plan capture

Plan capture runs EXPLAIN on the captured statements, and PostgreSQL requires the planning role to be able to read the tables a query references. Grant SELECT on the schemas whose queries you want planned (for example GRANT SELECT ON ALL TABLES IN SCHEMA public TO querypilot;), or leave it out and those captures will fail individually with a permission error while everything else keeps working. Capture always runs inside a READ ONLY transaction that is rolled back.

Optional: relation sizes

Table and index sizes make sequential-scan and index analysis far more meaningful, because “this table is scanned constantly” means something very different at 10 thousand rows than at 50 million.

GRANT SELECT ON pg_catalog.pg_class TO querypilot;

Without it QueryPilot reports sizes as unknown rather than guessing.

Enabling pg_stat_statements

This extension is the single most important input to QueryPilot. Without it, there is no query workload data at all: no top queries, no query history and no plan capture.

It ships with PostgreSQL but is not enabled by default, and turning it on requires a restart, because it must be loaded before the server starts.

1. Add it to postgresql.conf:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all
   pg_stat_statements.max = 10000

2. Restart PostgreSQL.

3. Create the extension in the database you want observed:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The PostgreSQL bundled with the Docker Compose stack already starts with shared_preload_libraries=pg_stat_statements and creates the extension when its data volume is first initialized. An existing volume needs CREATE EXTENSION by hand.

Managed providers

Provider	How to enable
Amazon RDS / Aurora	Add pg_stat_statements to shared_preload_libraries in the parameter group, then reboot the instance.
Google Cloud SQL	Set the cloudsql.enable_pg_stat_statements flag.
Azure Database	Set shared_preload_libraries in server parameters, then restart.

On managed providers, pg_read_all_stats is often granted through the provider’s own admin role instead of directly.

Verifying

The Test connection button reports exactly what is available and what is not, with a remediation for each gap. Run it after granting permissions; it is the fastest way to confirm the role works, and it never writes anything.

It checks the server version and probes each source with a single-row read, distinguishing missing (the view or extension does not exist) from not permitted (it exists but this role cannot read it). They need different fixes.

A missing capability disables one collector; it never disables monitoring altogether:

Capability	Collectors it enables
pg_stat_statements readable	query-stats (and plan capture on PostgreSQL 16+)
pg_stat_activity	activity, connections
pg_locks + pg_stat_activity	locks
pg_stat_user_tables	tables
pg_stat_user_indexes	indexes

What QueryPilot reads

Source	Used for
pg_stat_statements	Query workload: calls, execution time, rows, shared/temp blocks, WAL
pg_stat_activity	Active queries, long transactions, connection counts
pg_locks	Lock waits and blocked sessions
pg_stat_user_tables	Table size, live/dead tuples, scan counts, vacuum/analyze times
pg_stat_user_indexes	Index usage

Query text and privacy

Query text is collected by default; query parameters are never stored. Statement text from pg_stat_statements is already normalized by PostgreSQL, so literal values are replaced with placeholders before QueryPilot ever sees them.

To disable text collection entirely, set the environment variable on the API:

QUERYPILOT_STORE_QUERY_TEXT=false

or, in a configuration file (see Configuration):

privacy:
  storeQueryText: false

The API passes this setting to agents at registration, and agents then never send query text. QueryPilot keeps working: fingerprints, metrics, history and plans still function. You lose the ability to read the query itself in the UI.