PgAssistant

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. ...

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. ...

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. ...