pgAssistant 2.8 — Deterministic PostgreSQL Analysis with the new Global Advisor

bertrand HARTWIG — Fri, 08 May 2026 06:01:15 +0000

For the past months, I have been working on a simple idea around PostgreSQL tooling:

before using AI, start with deterministic analysis.

This is the direction behind pgAssistant 2.8.

This release introduces a new component called Global Advisor, alongside many improvements around ranking, schema analysis, maintenance diagnostics, and index recommendations.

The project remains open-source and focused on practical PostgreSQL analysis.

What is pgAssistant?

pgAssistant is an open-source PostgreSQL analysis tool.

It helps developers:

inspect database structures
analyze execution plans
detect schema and maintenance issues
review indexes and foreign keys
understand PostgreSQL behavior more easily

The project combines:

deterministic analysis
execution-plan analysis (EXPLAIN ANALYZE)
optional AI-assisted reasoning

The goal is not to replace PostgreSQL expertise.
The goal is simply to make PostgreSQL diagnostics more accessible and more contextual.

The main addition in 2.8: Global Advisor

Before pgAssistant 2.8, most checks existed independently.

Now they are consolidated into a single entry point:

Global Advisor

The Global Advisor performs a database-wide deterministic analysis and aggregates findings into a unified recommendation list.

Each recommendation now includes:

a rank
a confidence score
an estimated impact
an estimated implementation effort
a suggested SQL statement when relevant

The objective is not to claim certainty.

It is to help prioritize investigations.

Deterministic first

One important design choice in pgAssistant is that the Global Advisor is intentionally deterministic.

The analysis is based directly on PostgreSQL catalogs and statistics:

pg_stat_user_tables
pg_stat_user_indexes
pg_constraint
pg_index
pg_settings
pg_stats
execution plans

This means:

same input → same output
no hallucinations
explainable findings
reproducible analysis

AI is still supported as an optional layer.

Examples of checks now included

The Global Advisor currently includes checks such as:

missing indexes on foreign keys
redundant or duplicate indexes
unused indexes
invalid indexes
datatype inconsistencies on foreign keys
tables without primary keys
stale statistics
tables never vacuumed
estimated table bloat
excessive index-to-table ratio
low foreign key coverage
PostgreSQL configuration checks
sequences approaching exhaustion

Most recommendations also include suggested SQL.

Query analysis is still there

The query advisor based on real EXPLAIN ANALYZE plans remains a core part of pgAssistant.

The idea is now:

Global Advisor → broad database analysis
Query Advisor → detailed query-level investigation

These two approaches complement each other.

About AI

AI support remains optional.

pgAssistant can work entirely without an LLM.

When enabled, AI features receive contextual PostgreSQL information:

schema definitions
indexes
execution plans
statistics
database settings

This significantly improves the relevance of generated suggestions compared to generic SQL prompting.

Supported providers currently include:

Ollama
OpenAI-compatible APIs

Why I built it this way

A lot of PostgreSQL tooling focuses on metrics dashboards.

Those tools are useful, but I often felt there was still a gap between:

seeing a metric
understanding the cause
deciding what to change

pgAssistant tries to reduce that gap.

The project is still evolving, but the Global Advisor is an important step toward a more coherent analysis workflow.

Live demo

A public demo is available here:

https://ov-004f8b.infomaniak.ch/

Demo connection:

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

The public demo intentionally runs without AI.

Project links

GitHub: https://github.com/beh74/pgassistant-community
Documentation: https://beh74.github.io/pgassistant-blog/
Docker image: https://hub.docker.com/r/bertrand73/pgassistant

Feedback welcome

The project is still evolving and many parts can certainly be improved.

If you work with PostgreSQL and have ideas, feedback, or criticisms, feel free to open an issue or discussion on GitHub.

Thanks for reading.

pgAssistant v1.7 released

bertrand HARTWIG — Sat, 01 Feb 2025 13:09:19 +0000

I'm excited to share that we just released pgAssistant v1.7.

PGAssistant is an open-source tool designed to help developers gain deeper insights into their PostgreSQL databases and optimize performance efficiently.

It analyzes database behavior, detects schema-related issues, and provides actionable recommendations to resolve them.

One of the goals of PGAssistant is to help developers optimize their database and fix potential issues on their own before needing to seek assistance from a DBA.

🚀 AI-Powered Optimization: PGAssistant leverages AI-driven language models like ChatGPT, Claude, and on-premise solutions such as Ollama to assist developers in refining complex queries and enhancing database efficiency.

🔗 GitHub Repository: PGAssistant

🚀 Easy Deployment with Docker: PGAssistant is Docker-based, making it simple to run. Get started effortlessly using the provided Docker Compose file.

I’d love to hear your feedback! If you find PGAssistant useful, feel free to contribute or suggest new features. Let’s make PostgreSQL database easy for dev Teams !

pgAssistant - postgesql tool 4 dev

bertrand HARTWIG — Mon, 12 Aug 2024 05:00:03 +0000

I wrote this small tool because I noticed that our young developers were struggling to understand the behavior of their PostgreSQL database, and therefore had even more difficulty optimizing their databases.

Gradually, developers started using it, and the tool helped the development teams become much more autonomous, and me much less solicited.

Feel free to use it and give me your feedback.

I try to enhance the application whenever time allows.

You can find it there : https://github.com/nexsol-technologies/pgassistant

DEV Community: bertrand HARTWIG

pgAssistant 2.8 — Deterministic PostgreSQL Analysis with the new Global Advisor

pgAssistant v1.7 released

pgAssistant - postgesql tool 4 dev