pgAssistant

A continuous PostgreSQL improvement platform.
Analyze PostgreSQL, turn findings into an actionable remediation plan, track what changed, and measure whether the workload actually improved.
Designed for one database or a fleet of thousands.

⭐ Star pgAssistant on GitHub if it helps you improve your PostgreSQL databases.

---

What is pgAssistant?

pgAssistant is an open-source platform that turns PostgreSQL evidence into prioritized action. It combines database introspection, workload analysis, specialized advisors, implementation planning, historical evidence, and result measurement in a single web interface.

pgAssistant Collector extends this loop with historical workload and environment measurements, connecting recommendations to observed outcomes. Together, they help teams answer four recurring questions:

1. What should be improved? Detect and prioritize PostgreSQL risks, inefficiencies, and tuning opportunities.
2. What did we decide to do? Consolidate recommendations into an ordered Executive Plan owned by DEV, OPS, or both.
3. What did we actually fix? Track the recommendation lifecycle—new, persistent, and no-longer-detected findings—without falsely assuming that disappearance proves deployment.
4. What was the result? Correlate changes with execution time, call volume, query mix, PostgreSQL environment changes, and workload impact.

AI assistance is optional. The core advisors remain deterministic and can be used without an LLM.

See pgAssistant in action

The Executive Plan transforms technical findings into an ordered remediation plan with priorities, ownership, dependencies, and implementation guidance.

The continuous improvement loop

pgAssistant is not a real-time monitoring system. It builds a continuous improvement loop from periodic database and workload collections; monitoring platforms remain the source for what is happening right now.

Observe → Diagnose → Prioritize → Plan → Implement → Collect again → Measure
   ↑                                                                  │
   └────────────────────────── Repeat continuously ────────────────────┘

pgAssistant supports the full loop:

• Observe: Collector captures repeatable workload, environment, and advisor snapshots.
• Diagnose: deterministic advisors explain SQL, schema, configuration, maintenance, and lifecycle issues.
• Prioritize: findings are ranked by urgency, confidence, effort, and real workload impact.
• Plan: Executive Plan turns related recommendations into ordered, team-owned work packages.
• Verify: history identifies new and no-longer-detected recommendations while keeping deployment confirmation explicit.
• Measure: Workload Insights compares consecutive collections and exposes performance, query mix, environment, and recommendation changes.

The product components feed the same loop rather than producing disconnected reports:

                 PostgreSQL evidence
                         │
       ┌─────────────────┼─────────────────┐
       │                 │                 │
 Global Advisor     Index Advisor   Parameter & maintenance
       │                 │                 │
       └─────────────────┼─────────────────┘
                         ↓
                  Executive Plan
                         ↓
                    Implementation
                         ↓
                  Workload Insights
                         ↓
                    New evidence

From findings to action

The Executive Plan groups related findings into ordered, team-owned work packages. It makes dependencies and responsibilities explicit, so teams can move from diagnosis to execution instead of working through an unstructured list of recommendations.

P1 — DEV
  Rewrite query Q42
  Add the missing FK index on orders(customer_id)

P2 — OPS
  Tune autovacuum for the affected high-churn tables
  Review the workload-related PostgreSQL settings

P3 — DEV/OPS
  Drop unsed index, Review index opportunity on public.orders 

Highlights

• Multi-database support — connect to a PostgreSQL instance, list its databases, and select the database to analyze.
• Global Advisor — deterministic database-wide checks ranked by priority, confidence, impact, and effort.
• Executive Plan — combines Global, Index, Parameter, and Autovacuum recommendations into ordered work packages assigned to DEV, OPS, or DEV/OPS.
• Workload Insights — compares Collector measurements over time, highlights recommendation and PostgreSQL environment changes, and ranks queries by workload impact.
• Continuous verification — tracks new, persistent, and no-longer-detected recommendations without confusing observed disappearance with confirmed implementation.
• PDF reporting — generate a styled Executive Plan report filtered for DEV and/or OPS audiences, including a table of contents, maintenance requirements, sources, and SQL commands.
• Workload prioritization — identify the queries consuming the largest share of database workload by combining execution frequency and total execution impact.
• Index Advisor — inspect workload plans and identify actionable index opportunities, redundant indexes, and foreign-key coverage issues.
• Parameter Advisor — review workload signals and propose PostgreSQL parameter changes.
• Autovacuum Tuning — analyze cluster and per-table settings, stale statistics, maintenance activity, and table-specific tuning opportunities.
• Schema and table analysis — inspect DDL, relationships, table health, indexes, bloat indicators, and partitioned tables.
• pgTune — calculate a PostgreSQL configuration baseline through a guided interface.
• Optional LLM assistance — request SQL rewrites, schema-design feedback, naming checks, and contextual explanations.

One database or a PostgreSQL fleet

pgAssistant supports two complementary operating modes:

For a large PostgreSQL estate, two companion projects automate and centralize the same analyses:

Together, the three projects let teams:

1. collect consistent diagnostics across environments, applications, groups, and owners;
2. identify which databases should be corrected first;
3. drill down from the fleet overview to a database, query, or recommendation;
4. assign and plan remediation for DEV and OPS;
5. compare each collection with its predecessor, including workload and PostgreSQL environment changes;
6. track whether priority findings remain active or are no longer detected;
7. measure the workload result and decide what to improve next.

PostgreSQL fleet → Collector → Repository → pgAssistant → Executive Plan
       ↑                           ↓             ↓              ↓
       └──────────── Collect again ← Measure results ← Implement changes
                                   ↓
                          Grafana fleet overview

Live demo

Try the database analysis interface at https://ov-004f8b.infomaniak.ch/.

postgresql://postgres:demo@demo-db:5432/northwind

Explore the fleet dashboards in the Grafana demo. The demo credentials are documented in the pgAssistant Grafana repository.

The demo database is reset daily. AI features are disabled: do not enter personal API keys.

Advisor coverage and implementation plan

The Global, Index, Parameter, Autovacuum, and Fillfactor advisors produce reproducible recommendations. The Executive Plan consolidates related findings by objective or affected object into ordered work packages instead of a disconnected list of checks and SQL commands.

View the complete advisor coverage

The currently available advisors cover:

• Global Advisor — data model and schema: foreign-key columns using different data types; tables without a primary key; low or missing foreign-key coverage; and sequences approaching their maximum value.
• Global Advisor — indexes: missing useful indexes on foreign keys; non-unique indexes covered by a unique index; strictly duplicate unused indexes; partially duplicate low-usage indexes; unused non-constraint indexes; invalid or unusable indexes; and tables with a high index-to-table size ratio.
• Global Advisor — statistics, storage, and maintenance: potentially stale table statistics; estimated table bloat and high dead-tuple volume; tables never vacuumed or autovacuumed; urgent dead-tuple cleanup; tables flagged for autovacuum maintenance; cluster-wide autovacuum load; tables responsible for significant PostgreSQL buffer-cache misses; and abnormally long-running transactions.
• Global Advisor — configuration and lifecycle: important PostgreSQL settings that are disabled or suboptimal; unsupported PostgreSQL major versions; and available minor-version upgrades.
• Index Advisor — query plans: index opportunities for selective sequential scans and residual filters; safer single-column or composite index candidates; indexes supporting joins; indexes supporting ORDER BY, including ORDER BY ... LIMIT; indexes supporting GROUP BY; existing equivalent-index detection; and row-estimation or statistics observations that make an automatic recommendation unsafe.
• Parameter Advisor — workload configuration: reviews of work_mem, effective_cache_size, random_page_cost, effective_io_concurrency, max_parallel_workers_per_gather, and max_wal_size based on workload and generic-plan signals.
• Autovacuum Advisor — per-table actions: ANALYZE, VACUUM, and table-specific autovacuum tuning for never-analyzed, stale-analysis, never-vacuumed, stale-vacuum, modified-row, and dead-tuple pressure conditions.
• Autovacuum Advisor — cluster settings: reviews of autovacuum, autovacuum_max_workers, autovacuum_naptime, vacuum and analyze scale factors and thresholds, autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, and log_autovacuum_min_duration.
• Fillfactor Advisor: identifies tables that may benefit from a controlled fillfactor experiment, validates the signal against HOT-update efficiency, indexed-column updates, vacuum pressure, and long-running transactions, and highlights partitioned tables that require leaf-by-leaf review.

Query and workload analysis

pgAssistant can analyze an individual SQL statement or the workload collected by pg_stat_statements:

• real plans with EXPLAIN ANALYZE;
• PostgreSQL 16+ generic plans for parameterized queries;
• joins, scans, sorts, aggregates, buffers, WAL, and row-estimation insights;
• index suggestions supported by schema and column statistics;
• query-parameter mapping and configuration review;
• relational visualization of the tables involved.

[!CAUTION] EXPLAIN ANALYZE executes the statement. Review queries carefully and use a suitable database role, especially outside a development environment.

Screenshots

Dashboard

Global Advisor summary

Global Advisor recommendations

Executive Plan PDF reporting

Workload prioritization

Index Advisor

Autovacuum tuning

Quick start

Docker (recommended)

Use the published Docker image and expose the application on port 8080:

docker run --name pgassistant --rm -p 8080:5005 bertrand73/pgassistant:latest

Then open http://localhost:8080.

For persistent settings, LLM configuration, Docker Compose, and database connectivity examples, see the Docker installation guide.

Python

For a local source installation, see the Python installation guide.

Integration API

pgAssistant provides a versioned API for integrating its analysis and planning capabilities with automation platforms, database portals, CI/CD workflows, and other operational tools. These endpoints do not depend on a browser session and do not change the historical /api/v1 routes.

Authentication is optional and controlled by PGA_API_TOKEN. When configured, clients must send Authorization: Bearer <token>; when it is unset or empty, authentication is disabled.

• API v2 integration guide
• Swagger UI: /api/v2/docs
• Machine-readable Swagger specification: /api/v2/swagger.json

PostgreSQL access

pgAssistant accepts standard libpq-compatible PostgreSQL connection URIs, including additional connection options:

postgresql://user:password@host:5432/database

For the best workload analysis, enable pg_stat_statements. Some features degrade gracefully when the extension is unavailable, and generic-plan advisors require PostgreSQL 16 or newer.

Use a dedicated database account with only the permissions required for the analyses you intend to run. Multi-database mode also requires the account to be able to connect to each selected database.

PostgreSQL release metadata is cached for 30 days in postgresql_versions_cache.json. The Docker image stores it in /home/pgassistant/data/postgresql_versions_cache.json. Set PGA_POSTGRESQL_VERSIONS_CACHE_FILE to use another location or mount /home/pgassistant/data to preserve the cache when containers are replaced.

Continuous improvement with pgAssistant Collector

Set the optional COLLECTOR_URI environment variable to connect pgAssistant to a pgAssistant Collector repository:

COLLECTOR_URI=postgresql://collector_reader:password@collector-host:5432/pga_collector

When configured, the Database connection page displays a Collector tab where the user searches for and selects the target associated with the active database. The selection is stored in the session and shared by Executive Plan history, Query activity history, and Workload Insights. The Executive Plan History tab provides 7, 15, 30-day and complete-history comparisons, DEV/OPS team filters, and work-package-grouped changes. The latest collector snapshot is also compared with a freshly generated Executive Plan, so a correction appears immediately; the next complete collection confirms it. Partial live plans never confirm a correction.

Workload Insights compares consecutive periodic measurements—not a real-time monitoring stream. It correlates recommendation changes with execution time, call volume, query type, PostgreSQL version and settings changes, and the queries with the largest workload impact. This provides evidence about what changed after a decision while preserving the distinction between correlation, a no-longer-detected finding, and a confirmed deployment.

The collector connection is opened in read-only mode. A dedicated read-only PostgreSQL role is recommended.

Documentation and releases

• Documentation
• Full changelog
• Docker Hub
• Issue tracker
• Collector repository
• Grafana dashboards repository

Who is it for?

License

pgAssistant is released under the MIT License.