A continuous PostgreSQL improvement platform#
pgAssistant turns PostgreSQL evidence into prioritized action. Analyze a database and its workload, decide what to improve, organize the work, collect new evidence, and measure the result.
Use it for one database or extend the same approach across a fleet of hundreds or thousands.
Observe. Diagnose. Prioritize. Plan. Implement. Measure. Repeat.
The continuous improvement loop#
pgAssistant connects diagnosis, implementation planning, and historical evidence in one repeatable process:
Observe → Diagnose → Prioritize → Plan → Implement → Collect again → Measure
↑ │
└────────────────────────── Repeat continuously ────────────────────┘
- Observe — collect repeatable database, workload, and environment evidence.
- Diagnose — use deterministic advisors to identify SQL, schema, index, configuration, and maintenance issues.
- Prioritize — rank findings by urgency, confidence, effort, and workload impact.
- Plan — turn related recommendations into ordered work packages owned by DEV, OPS, or both.
- Implement — apply each change with its dependencies, SQL guidance, and maintenance requirements in view.
- Measure — compare the next collection with the previous one to understand what changed and whether the workload improved.
pgAssistant is not a real-time monitoring or alerting system. Monitoring tells you what is happening now; pgAssistant helps you decide what to improve next and verify the result over time.
From findings to an Executive Plan#
The Global, Index, Parameter, Autovacuum, and Fillfactor advisors produce reproducible findings from PostgreSQL catalogs, statistics, settings, and execution plans.
The Executive Plan consolidates those findings into an ordered remediation plan instead of leaving teams with a disconnected list of recommendations. It provides:
- priorities, dependencies, confidence, impact, and estimated effort;
- work packages assigned to DEV, OPS, or DEV/OPS;
- implementation guidance, maintenance constraints, and executable SQL;
- PDF reports filtered for development and operations audiences;
- historical comparison of new, persistent, and no-longer-detected findings.
A finding that disappears from a later collection is reported as no longer detected—not automatically treated as proof that a change was deployed.
Measure results with Workload Insights#
Introduced in pgAssistant 3.8.0, Workload Insights compares consecutive Collector measurements and shows:
- execution time, call volume, and query-mix changes;
- the queries driving the largest gains or regressions;
- changes in PostgreSQL versions and important tuning settings;
- the evolution of Executive Plan recommendations;
- workload impact before and after an intervention.
This historical view closes the improvement loop: teams can connect a decision to observed PostgreSQL changes, evaluate the outcome, and choose the next action using fresh evidence.
Read what is new in pgAssistant 3.8.0
One database or a PostgreSQL fleet#
One database#
Use the pgAssistant web interface for a deep analysis of:
- SQL statements and execution plans;
- workload impact and expensive-query ranking;
- schema design, relationships, indexes, and table health;
- PostgreSQL configuration and pgTune baselines;
- autovacuum, statistics, bloat indicators, and maintenance;
- prioritized remediation for developers and DBAs.
A fleet of hundreds or thousands#
Two companion projects automate and centralize the same improvement process:
| Project | Role |
|---|
| pgAssistant Collector | Runs selected jobs across declared databases and stores historical workload, environment, and advisor snapshots in a central PostgreSQL repository. |
| pgAssistant Grafana | Displays fleet-wide priorities and trends so teams can identify the databases, queries, and recommendations requiring attention first. |
PostgreSQL fleet → Collector → Repository → pgAssistant → Executive Plan
↑ ↓ ↓ ↓
└──────────── Collect again ← Measure results ← Implement changes
↓
Grafana fleet overview
Deterministic first, AI when useful#
The core pgAssistant advisors are deterministic and work without an LLM. They query PostgreSQL system catalogs, statistics, configuration, and execution plans to produce reproducible recommendations.
AI is an optional augmentation layer for tasks such as:
- SQL rewrite suggestions;
- contextual execution-plan explanations;
- schema-design feedback;
- naming convention and RFC checks.
pgAssistant remains fully usable without AI.
Try pgAssistant#
Open the pgAssistant live demo and use:
postgresql://postgres:demo@demo-db:5432/northwind
Explore fleet-wide priorities in the pgAssistant Grafana 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.
Get started#
Managing more than 1,000 PostgreSQL databases with pgAssistant Managing a PostgreSQL fleet at scale is not simply a matter of running the same diagnostic tool 1,000 times. Development, staging, and production have different access rules, risk profiles, owners, and workloads. At the same time, DEV, DBA, SRE, and OPS teams need one place to decide which databases require attention first.
A practical pgAssistant architecture separates analysis by environment while centralizing collection, history, and fleet-wide visibility:
...
pgAssistant 3.8.0 is released pgAssistant 3.8.0 marks an important step in the product’s evolution into a continuous PostgreSQL improvement platform.
The release introduces Workload Insights, improves Executive Plan history, and records PostgreSQL environment changes alongside workload measurements. pgAssistant can now help teams move beyond producing recommendations: it connects what was observed, what changed, and what happened to the workload afterward.
Observe → Diagnose → Prioritize → Plan → Implement → Collect again → Measure ↑ │ └────────────────────────── Repeat continuously ────────────────────┘ Workload Insights Workload Insights explores the periodic measurements stored by pgAssistant Collector and compares each collection with its predecessor.
...
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.