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#
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.
...
I’m excited to announce the release of pgAssistant 2.0, a major milestone for the project. This new version marks a strategic shift toward API-first integration, making it easier to embed pgAssistant into existing workflows — from code review pipelines to production compliance checks.
The first available API in this release focuses on automated database health reporting. It generates a Markdown report that provides :
• A global health overview of your PostgreSQL database • Key performance and metrics • Identified issues and improvement suggestions Since its beginning, pgAssistant’s mission has been to help developers design better, faster, and more maintainable databases — while also providing educational insights, references, and explanations for every metric.
...
Lightweight SQL style guide Overview SQL style guide by Simon Holywell is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at https://www.sqlstyle.guide/
General Do Use consistent and descriptive identifiers and names. Make judicious use of white space and indentation to make code easier to read. Store [ISO 8601][iso-8601] compliant time and date information (YYYY-MM-DDTHH:MM:SS.SSSSS). Try to only use standard SQL functions instead of vendor-specific functions for reasons of portability. Keep code succinct and devoid of redundant SQL—such as unnecessary quoting or parentheses or WHERE clauses that can otherwise be derived. Include comments in SQL code where necessary. Use the C style opening /* and closing */ where possible otherwise precede comments with -- and finish them with a new line. Avoid camelCase—it is difficult to scan quickly. Descriptive prefixes or Hungarian notation such as sp_ or tbl. Plurals—use the more natural collective term where possible instead. For example staff instead of employees or people instead of individuals. Quoted identifiers—if you must use them then stick to SQL-92 double quotes for portability (you may need to configure your SQL server to support this depending on vendor). Object-oriented design principles should not be applied to SQL or database structures. Naming conventions General Ensure the name is unique and does not exist as a Postgresql keyword. Keep the length to a maximum of 30 bytes—in practice this is 30 characters unless you are using a multi-byte character set. Names must begin with a letter and may not end with an underscore. Only use letters, numbers and underscores in names. Avoid the use of multiple consecutive underscores—these can be hard to read. Use underscores where you would naturally include a space in the name (first name becomes first_name). Avoid abbreviations and if you have to use them make sure they are commonly understood. Tables Use a collective name or, less ideally, a plural form. For example (in order of preference) staff and employees. Do not prefix with tbl or any other such descriptive prefix or Hungarian notation. Never give a table the same name as one of its columns and vice versa. Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services. Columns Always use the singular name. Where possible avoid simply using id as the primary identifier for the table. Do not add a column with the same name as its table and vice versa. Always use lowercase except where it may make sense not to such as proper nouns. Aliasing or correlations Should relate in some way to the object or expression they are aliasing. As a rule of thumb the correlation name should be the first letter of each word in the object’s name. If there is already a correlation with the same name then append a number. Always include the AS keyword—makes it easier to read as it is explicit. For computed data (SUM() or AVG()) use the name you would give it were it a column defined in the schema. Uniform suffixes The following suffixes have a universal meaning ensuring the columns can be read and understood easily from SQL code. Use the correct suffix where appropriate.
...
This release is coming with this a new AI feature : Use AI to verify table compliance with your SQL guide lines (like naming conventions) - Just provide a valid URL to your guidelines.
It is strongly recommanded to use the Markdown format for SQL guide lines.
This new functionnality is added on the Table definition helper menu.
Enjoy !
New docker image is available on dockerhub Take a look at DockerHub image tag
...
This release is coming with this news features :
pgTune : docker-compose yaml file is using a healthcheck and the shm_size parameter pgTune : a Kubernetes deployment descriptor has been added New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:1.9.8 Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
This release is coming with this news features :
Re-factoring LLM API Calls to detect the use of ollama. Tested with the lastest ollama version With Lighthouse, optimize the UI Re-factoring the LLM settings form New docker image is available on dockerhub Take a look at DockerHub image tag
docker pull bertrand73/pgassistant:1.9.7 Enjoy !
Docker image security advices No any security advice from github or docker scouts or Grype.
Following the release of OpenAI’s open-source model gpt-oss, I set out to benchmark its performance in conjunction with PostgreSQL, focusing specifically on integration with pgAssistant. All evaluations were conducted locally on a MacBook Pro M4 Pro (24 GB RAM), using Ollama as the model runtime environment.
gpt-oss installation To install this new model, first download the latest version of ollama, then run this :
ollama pull gpt-oss:20b Ollama should now serve the API at: http://localhost:11434
...
Object This post provides a SQL query that analyzes PostgreSQL tables and suggests optimized autovacuum settings on a per-table basis. It aims to improve database health and performance by adapting vacuum/analyze thresholds according to table size and usage patterns.
Goal PostgreSQL uses autovacuum to automatically clean up dead tuples and refresh planner statistics. However, the default settings may be too aggressive for small tables or too lax for large ones, leading to:
...
Privacy Data privacy is no longer a luxury — it’s a necessity.
Today, we’ll show you how to use pgAssistant with Infomaniak Cloud — Infomaniak’s privacy-focused, sovereign cloud solution based in Switzerland🇨.
Why Data Sovereignty Matters When using AI tools in the cloud, where your data is processed and stored has a major impact:
Legal compliance (e.g. GDPR) Data residency requirements Protection from mass surveillance Infomaniak’s Swiss Cloud is one of the few solutions that:
...