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.
Top comments (0)