pgAssistant helps developers, DBAs, and operations teams understand and improve PostgreSQL databases.
It can be used interactively to analyze a single database, or combined with pgAssistant Collector and pgAssistant Grafana to identify and prioritize actions across hundreds or thousands of databases.
Analyze one PostgreSQL database. Prioritize a fleet of thousands.
One Database or an Entire PostgreSQL Fleet#
Analyze a Single Database#
The main pgAssistant application provides an interactive interface for:
- understanding a database schema and its workload;
- detecting structural and configuration issues;
- analyzing expensive SQL queries and execution plans;
- identifying indexing opportunities;
- reviewing autovacuum, statistics, and maintenance;
- producing prioritized recommendations and an implementation plan.
This is useful for a developer investigating one application database, as well as for a DBA performing a detailed PostgreSQL assessment.
Manage Hundreds or Thousands of Databases#
For larger PostgreSQL estates, two companion projects automate and centralize pgAssistant analyses:
| Project | Role |
|---|
| pgAssistant Collector | Runs selected pgAssistant analyses across declared databases and stores historical results in a central PostgreSQL repository. |
| pgAssistant Grafana | Provides fleet-wide dashboards showing priorities, trends, advisor findings, and the databases requiring attention first. |
Together, the three projects make it possible to:
- collect consistent diagnostics across applications and environments;
- identify which databases require action first;
- drill down from a fleet overview to a database, query, or recommendation;
- assign remediation work to development and operations teams;
- track whether priority findings are resolved over time.
PostgreSQL fleet → Collector → pgAssistant analyses → Central repository → Grafana
↓
Diagnosis and action plan
Deterministic First, AI When Needed#
pgAssistant is built on a simple principle:
Start with deterministic database analysis.
Use AI only when it adds value.
Deterministic Analysis#
pgAssistant queries PostgreSQL system catalogs and statistics to produce reliable and reproducible diagnostics.
The Global Advisor provides:
- one-click database analysis;
- recommendations ranked by priority, confidence, impact, and effort;
- detection of missing indexes on foreign keys;
- datatype inconsistencies;
- redundant, invalid, or unused indexes;
- tables without primary keys;
- stale statistics and maintenance issues;
- table bloat and dead-tuple pressure;
- long-running transactions;
- unsupported PostgreSQL versions and available upgrades;
- important configuration issues.
The Executive Plan consolidates findings from the Global, Index, Parameter, Autovacuum, and Fillfactor advisors into ordered work packages for DEV and OPS teams.
No AI is required for these analyses.
Query and Workload Analysis#
pgAssistant can analyze an individual SQL statement or the workload collected by pg_stat_statements:
EXPLAIN ANALYZE insights;- expensive-query ranking;
- join, scan, sort, and aggregate analysis;
- index recommendations;
- row-estimation and statistics issues;
- query rewrite opportunities;
- PostgreSQL parameter recommendations;
- relational visualization of the tables involved.
EXPLAIN ANALYZE executes the statement. Review queries carefully and use a suitable database role.
Optional AI Assistance#
AI is an augmentation layer, not a dependency.
When an LLM is configured, pgAssistant can provide:
- SQL rewrite suggestions;
- contextual explanations of execution plans;
- advanced optimization reasoning;
- database-design feedback based on schema and workload;
- naming convention and RFC checks.
pgAssistant remains fully usable without AI.
Try It Online#
Database analysis interface:
https://ov-004f8b.infomaniak.ch/
Demo connection:
postgresql://postgres:demo@demo-db:5432/northwind
Fleet dashboards:
pgAssistant Grafana demo
The Grafana demo credentials are documented in the
pgAssistant Grafana repository.
The public demo does not use an LLM.
Do not provide personal API keys.
Why pgAssistant?#
- Open source and built specifically for PostgreSQL;
- useful for both a single database and a large PostgreSQL estate;
- deterministic and reproducible analysis;
- optional AI assistance;
- recommendations prioritized by impact and effort;
- focused on actionable improvements, not just metrics;
- implementation planning for development and operations teams;
- historical fleet-level visibility with Collector and Grafana.
Explore the documentation, blog posts, and project repositories:
Before You Begin#
Monitoring and pgAssistant#
pgAssistant is not intended to replace a real-time monitoring and alerting platform.
For PostgreSQL observability, pgWatch provides:
- real-time metric collection;
- Grafana dashboards;
- Prometheus integration;
- visualization and alerting.
Monitoring shows what is happening.
pgAssistant helps decide what to improve next and how to implement it.
Purpose pgAssistant inspects PostgreSQL configuration, system catalogs, statistics, schemas, relations, sequences, and query plans. Some optional features can also run maintenance operations or reset statistics.
This document recommends separating these capabilities between two login roles:
pgassistant_analyze: the default, read-only account used for dashboards, reports, advisors, query ranking, query plans, and database design analysis. pgassistant_maintain: a privileged account used only when an operator explicitly requests maintenance, such as VACUUM, ANALYZE, or a statistics reset. The maintenance role inherits the analysis role, so it can perform the same diagnostic work in addition to its explicitly granted maintenance operations.
...
What’s New in pgAssistant Since Version 2.8 Since version 2.8, pgAssistant has evolved significantly.
The initial goal was to introduce a Global Advisor capable of combining multiple PostgreSQL signals—schema design, indexes, maintenance statistics, configuration, and workload activity—to provide higher-level recommendations.
Several releases later, this experimental feature has become a much more mature expert system. At the same time, the Query Advisor, Index Advisor, ranking engine, collector integrations, and developer-facing maintenance views have also improved.
...
When investigating PostgreSQL performance, the usual starting point is pg_stat_statements. From there, many teams sort queries by mean_exec_time or total_exec_time and start optimizing the first rows in the list.
That approach is simple, but it often leads to the wrong priorities.
A query that takes five seconds but runs twice a day is not necessarily more important than a query that takes five milliseconds and runs millions of times. Conversely, a query with a high total execution time may simply be a normal core workload query, not necessarily the best optimization target.
...
PostgreSQL index design is often misunderstood.
Many developers think that creating a good index simply means:
“Create an index containing the columns from the WHERE clause.”
In reality, efficient index design is far more nuanced.
The order of columns inside a composite index matters enormously, and the best choice depends on:
Predicate types (=, >=, BETWEEN, LIKE, etc.) Column selectivity Table size PostgreSQL planner statistics Actual execution plans This article explains the core principles behind efficient PostgreSQL index design before showing how pgAssistant automates this process using execution plans and database statistics.
...
Detecting PostgreSQL optimization issues with deterministic analysis For years, I kept seeing the same PostgreSQL problems in production:
missing foreign key indexes stale statistics unused indexes datatype mismatches sequences approaching exhaustion postgres configuration Most of these issues are not difficult to detect.
They already exist inside PostgreSQL catalogs, statistics, …
The difficult part is usually connecting the signals together and understanding their operational impact.
For the past months, I have been working on a deterministic PostgreSQL analysis approach inside pgAssistant 2.8.
...
Features Introduced Global Advisor (initial version)
A first implementation of a global analysis engine that aggregates multiple database signals (queries, schema, statistics) to provide higher-level recommendations.
This feature is experimental and will evolve in future releases with more advanced diagnostics and prioritization logic. New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:latest Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
...
Features Introduced SQL Advisor, a new safe-by-design advisor focused on SQL query analysis and guidance. It is designed to provide helpful recommendations while staying conservative and avoiding risky automated actions. Improved Top queries are now displayed as cards, making them easier to scan, compare, and review visually. New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:latest Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
...
Features UI improvement of tables, minor changes of icons, colors and sizes Bug fixes UI : issues with text and icons alignment in Dashboard, generic header and left menu New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:latest Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
UI UX
Many changes on UI UX - Sidebar menu refactoring, new color schema.
Postgresql 18 Postgresql 18 top queries : adjust column orders
New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:latest Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
Code Suggestions Section
When pgAssistant generates recommendations, the form now includes a dedicated “Code suggestions” section grouping all actionable statements.
A convenient “Copy” button lets users export them instantly.
(Thank you Manon for the great idea!)
Bgwriter & Checkpointer Insights
The reporting API now exposes pg_stat_bgwriter (and pg_stat_checkpointer for PG17+) metrics along with detailed recommendations to improve checkpoint and background writer performance.
Database Uptime on Dashboard
The main dashboard now displays the database uptime, with a clean human-readable format.
...