pgQueryOps user guide

Installation

Use the supplied Docker Compose setup for a fresh deployment. You need Docker Engine with Compose, the pgQueryOps source checkout, a TLS certificate and matching key, and access to a PostgreSQL superuser for setup. Run the Compose commands from the source directory.

1. Prepare configuration and secret files

From packaging/config, copy service.json.example and pg_service.conf.example into /srv/pg-queryops/config and copy auth-state.json.example into /srv/pg-queryops/secrets. Drop the .example suffix. Create a strong PostgreSQL password in postgres-password and place the TLS certificate and key at the paths below.

Keep the password, auth state, passfile and private key at mode 0600 in a protected 0700 directory. The gateway runs as UID/GID 10001 and needs to own and read its auth state, passfile, certificate and key. Give its non-secret config directory and files modes 0755 and 0644 respectively.

Save these settings in /srv/pg-queryops/.env. Update the paths for your host and set PG_MAJOR and POSTGRES_DATA_PATH together.

COMPOSE_PROJECT_NAME=pg-queryops
PG_MAJOR=17
POSTGRES_DATA_PATH=/var/lib/postgresql/data
QUERYOPS_DATABASE=advisor
QUERYOPS_ENABLED=on
QUERYOPS_HTTP_PORT=8443
QUERYOPS_CONFIG_DIR=/srv/pg-queryops/config
POSTGRES_PASSWORD_FILE=/srv/pg-queryops/secrets/postgres-password
QUERYOPS_AUTH_STATE_FILE=/srv/pg-queryops/secrets/auth-state.json
QUERYOPS_PGPASS_FILE=/srv/pg-queryops/secrets/pgpass
QUERYOPS_TLS_CERT_FILE=/srv/pg-queryops/secrets/gateway.crt
QUERYOPS_TLS_KEY_FILE=/srv/pg-queryops/secrets/gateway.key

2. Start PostgreSQL

docker compose --env-file /srv/pg-queryops/.env up --build -d postgres
docker compose --env-file /srv/pg-queryops/.env ps postgres

Wait for healthy. A fresh volume runs scripts/bootstrap-roles.sql to create the extension’s service roles. To connect as the setup superuser, use the following command and enter the PostgreSQL password at the prompt.

docker compose --env-file /srv/pg-queryops/.env exec postgres \
  psql -X -W -h /run/pg-queryops -U postgres -d postgres

3. Install the extension in the target database

Run this example in the psql session for a new target named advisor. If using an existing target database, connect to it instead of creating one. Set a distinct password for the viewer when prompted.

CREATE DATABASE advisor;
\connect advisor
CREATE EXTENSION pg_queryops;

CREATE ROLE advisor_viewer LOGIN INHERIT NOSUPERUSER NOCREATEDB
  NOCREATEROLE NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 16;
\password advisor_viewer

SELECT 'GRANT queryops_viewer TO advisor_viewer' ||
  CASE WHEN current_setting('server_version_num')::integer >= 160000
       THEN ' WITH INHERIT TRUE, SET FALSE, ADMIN FALSE'
       ELSE '' END
\gexec
GRANT CONNECT ON DATABASE advisor TO advisor_viewer, queryops_job;

For an existing PostgreSQL cluster, install the native library and SQL files built for its PostgreSQL major. Preserve existing shared_preload_libraries entries when adding pg_queryops. Configure pg_queryops.control_database, set pg_queryops.control_role to queryops_job, and list the target databases in pg_queryops.database_allowlist. Set pg_queryops.enabled = on to enable capture at startup. Plan a server restart and run scripts/bootstrap-roles.sql as a superuser before creating the extension. Match the gateway build to the extension release and configure a protected direct database connection.

Targets and access

A target is a registered database. After installation, give the worker up to one 30-second cycle to register it. Run this in the target database and save the returned UUID.

SELECT target_id
FROM queryops.target_state
WHERE database_oid = (
  SELECT oid FROM pg_catalog.pg_database
  WHERE datname = current_database()
);

As the setup superuser, replace <target-uuid> with the returned value and bind the viewer login to this target.

INSERT INTO queryops.target_role_bindings
  (target_id, session_role_oid, access_level)
SELECT '<target-uuid>'::uuid, oid, 'viewer'
FROM pg_catalog.pg_roles
WHERE rolname = 'advisor_viewer'
ON CONFLICT (target_id, session_role_oid)
DO UPDATE SET access_level = EXCLUDED.access_level;

1. In service.json, set the target’s id to this UUID, database to advisor, and role_services.viewer.expected_session_user to advisor_viewer. Keep the advisor-viewer service name matched to pg_service.conf. Set listen.base_url to the HTTPS origin you use to open the dashboard.
2. Add a passfile entry in the form /run/pg-queryops:5432:advisor:advisor_viewer:<viewer-password>. Escape colons and backslashes in the password with a backslash as required by libpq.
3. Generate a bearer token from 32 random bytes encoded as base64url without padding. Store it in your secret manager. Put only the lowercase SHA-256 hex digest of the encoded token in auth-state.json as token_hash. Give the token its own UUID, the viewer role, the target UUID in target_ids, and a future UTC RFC 3339 expiry.
4. With PostgreSQL healthy, start the gateway. The --no-deps option starts only the gateway service.

docker compose --env-file /srv/pg-queryops/.env up --build -d --no-deps gateway

Open https://localhost:8443 using a certificate valid for that hostname and trusted by your browser. Enter the bearer token in the dashboard’s connection settings and choose the target. Compose exposes the gateway on loopback and shares a Unix socket with PostgreSQL. For remote access, use a controlled HTTPS ingress.

For each additional access level, add a separate LOGIN role, grant its corresponding queryops_* membership, bind it to the target, and configure its libpq service, passfile and role_services entry. Grant only the table and schema permissions the operation needs.

First checks

From the target database, verify the extension version and inspect the instance status.

SELECT extversion
FROM pg_catalog.pg_extension
WHERE extname = 'pg_queryops';

SELECT queryops.pg_queryops_status();

Check the gateway with your TLS trust configuration, then sign in to the dashboard. The readiness endpoint returns HTTP 200 when its configuration, authentication, metadata and job queue checks pass.

curl --fail-with-body --cacert /path/to/trusted-ca.pem \
  https://localhost:8443/health/ready

Open the workload overview after representative queries have run. An empty new target needs time to capture and analyze workload samples.

Workload monitoring

1. Select a target and open its workload overview.
2. Compare execution time and call count. A moderately slow statement run often may account for more total time than an isolated slow call.
3. Review capture coverage and the query shape, then open recommendations for a candidate index or live diagnostics for a wait or scan.

Recommendations reflect captured workload and refill after a server restart. Check capture status when a familiar query or recommendation is missing.

Performance history and plans

Enable performance history with a target-bound admin token and a reason. The running gateway persists sampled sessions, waits and captured execution plans. Capture must also be enabled on the target. Operators and admins can inspect history.

Choose a time window, inspect waiting sessions and blockers, then compare plans for the same query and role context. In plan protection, an admin can approve a reference after at least eight complete, error-free executions. A different plan in the same context becomes a regression candidate when its average is at least twice the reference and at least 10 ms slower. The reference is used for comparison and does not force PostgreSQL’s planner.

Incident center

With performance history enabled, a target admin can enable incident recording with a reason. Review the saved evidence for a plan regression candidate, a long lock wait or sustained connection pressure. Operators and admins can read incidents. An admin can mark an incident for investigation or close it with a reason.

By default, a lock wait qualifies after 30 seconds and connection pressure qualifies at 85% for 15 seconds. Plan regression detection uses a recent window with complete, error-free executions. Use the saved evidence to decide how to respond.

Live diagnostics

Use a target-bound operator or admin to inspect current activity.

Only a superuser enables scan progress or statement history for a session. If you must signal a blocker, verify the session and enter a reason. Canceling an active query requires operator or admin access. Terminating the session requires admin access. The gateway rechecks the transaction identity and records the action.

Index analysis

Review the advisor’s index candidates alongside the queries that produced them. Candidate order prioritizes the accumulated execution time of source queries. Check table write activity, existing indexes and a what-if comparison before approving a build.

Use index findings to review invalid, duplicate, prefix-covered and potentially unused indexes. An unused candidate needs at least 14 days of continuous observation with no increase in scans. Restarts, statistics resets and sampling gaps can restart the observation window. Check replicas and infrequent reports before deciding to remove an index.

For B-tree bloat, review the estimate and verify a candidate with the bounded page sample. The dashboard displays applicable REINDEX INDEX CONCURRENTLY and index removal statements for review. The operator manages any cleanup separately.

What-if simulation

1. Open the what-if view with analyst, operator or admin access. Enter one eligible SELECT and use representative concrete values in place of bind parameters.
2. Compare the current plan with a hypothetical index, or choose an existing index to hide for the comparison.
3. Review the plan and estimated cost change before deciding whether to measure a real change.

Simulation plans the statement without executing it or modifying physical indexes. It covers supported filters, sorts, joins, views and non-recursive CTEs. User-defined functions and operators, aggregates and window functions are outside the supported simulation scope. PostgreSQL planner cost is a relative estimate. Use measured timing for latency comparisons.

Before and after measurements

Configure a writable, persistent measurement_runs_dir outside the gateway state directory. For Compose, mount it as a writable volume for UID 10001. Use an operator or admin token and configure separate analyst and admin database services. The analyst needs SELECT access to the measured query’s tables. The admin worker needs table ownership and CREATE on the schema.

1. From Recommendations or Measurements, choose a candidate and enter one SELECT with fixed values.
2. Run the baseline. pgQueryOps uses five warm-up runs and 50 timed executions.
3. Review the proposed DDL and give a reason and explicit approval to create the physical index.
4. After the build, compare the recorded median and p95 from the same query. Review whether background load and cache conditions were comparable.

Space reclamation

After your application’s retention or cleanup process deletes rows, open Space Reclamation and preview the target table. Review reclaimable space, free disk, WAL and index workspace. Choose a maintenance window for index rebuilds, which can make writes wait.

1. Use a target-bound admin login with table ownership or its inherited privileges. Give the gateway a direct database connection for that role.
2. Start the job with a reason and maintenance confirmation. The worker uses existing free space in the table and TOAST files and rebuilds indexes individually.
3. Watch actual file sizes and the job status. If the job pauses, resolve the reported condition and resume. Pause or cancel preserves completed batches.

In 2.4.2, the table owner can also queue a job with SQL. Use the same admin LOGIN for the caller and the target’s gateway admin service. In this example, public.events is the table you have already reviewed. The call accepts write waits during index maintenance and the gateway picks up the job after commit.

SELECT queryops.compact_table('public.events');

Use permanent heap tables, materialized views or individual partition leaves. A repeat call from the same role returns its existing open job. Resume a paused job explicitly.

Query rules

Rules can redirect eligible SELECTs to another relation or add a predicate for selected roles. The module starts disabled. A PostgreSQL superuser controls the server setting. A bound admin manages rules for owned source tables and needs the applicable schema and relation privileges.

1. Choose the source relation and application roles in the Rules view.
2. Select a compatible destination or provide a predicate, then validate and confirm the rule.
3. Test representative reads under the intended roles. Keep PostgreSQL grants and row-level security in place.

Rules is a SELECT rewrite feature. Reads within writes have additional restrictions. Plan rule changes alongside application tests and review data equivalence before enabling them on a production target.

HTTP API

Use the gateway’s HTTPS origin. Health checks are unauthenticated. Target routes require Authorization: Bearer <token> and apply the token’s role and target scope.

The source checkout includes the full API contracts in docs/contracts/http-api.json and docs/contracts/sql-api.json.

Troubleshooting

Upgrades and rollback

Rehearse on a same-major copy and verify a restorable pre-upgrade backup. Pause active maintenance and verify the worker has stopped. Deploy the matching library, SQL files and gateway together, restart PostgreSQL in a maintenance window, then update the extension in each target.

ALTER EXTENSION pg_queryops UPDATE TO '2.4.2';

SELECT extversion
FROM pg_catalog.pg_extension
WHERE extname = 'pg_queryops';

SELECT extension_version FROM queryops.instance_state;

Confirm both versions are 2.4.2 and repeat the first checks. Rollback uses the pre-upgrade backup in a separate data directory with the previous matching package. There is no SQL downgrade for 2.4.2. Older extension identities such as pg_hypo_advisor and select_rules require a separate migration rehearsal.

For release-specific steps, see docs/installation.md and docs/rollback.md in the source checkout.