AI-powered PostgreSQL Performance & Schema Optimization Assistant
Diagnose, understand and optimize complex PostgreSQL workloads using real execution plans and full schema context.
⭐ If pgAssistant helps you, please consider starring the repository.
pgAssistant is an open-source tool designed to help developers and DBAs understand and optimize PostgreSQL databases beyond traditional metric dashboards.
It combines:
- Deterministic database analysis
- Full schema inspection (DDL) with automatic relational graph visualization to reveal table dependencies and structural issues
- Structural issue detection (missing indexes, redundant indexes, missing foreign keys, datatype inconsistencies)
- Naming convention and RFC(s) validation
- Real
EXPLAIN ANALYZEexecution plans with index advisor - Database parameters & statistics
- Optional AI-assisted reasoning
The goal: turn raw PostgreSQL internals into actionable optimization decisions.
Stay up to date with the latest features and improvements:
View the full changelog
Most tools show metrics.
pgAssistant provides context-aware analysis.
When using AI-assisted features, pgAssistant injects:
- Table definitions (DDL) with Index definitions
- Database configuration parameters
pg_statsinsights- Real execution plans (
EXPLAIN ANALYZE) - Query text
This drastically reduces hallucination risk and enables meaningful recommendations such as:
- Composite index suggestions
- Join strategy improvements
- Parameter tuning (e.g.,
work_mem,effective_cache_size) - Schema corrections
This is not “copy-paste your query into ChatGPT”.
It is structured, contextualized database analysis.
pgAssistant does not rely solely on AI.
Many checks are fully deterministic and based directly on PostgreSQL system catalogs:
- Redundant indexes
- Missing indexes on foreign keys
- Unused indexes
- Datatype inconsistencies in relationships
- Index coverage analysis
- Cache usage diagnostics
AI is used as an optional augmentation layer for:
- Query rewrite suggestions
- Context-aware optimization reasoning
- RFC compliance checks
- Conventions recommendations
Complex 10-table join query.
Initial execution time: 3.2 seconds
pgAssistant recommendations:
- Add 2 composite indexes
- Rewrite a nested loop join
- Adjust
work_mem - Fix missing foreign key
New execution time: 420 ms
→ 7.6x improvement
(Results depend on workload, always validate in non-production environments.)
Compatible with Ollama or any OpenAI-compatible API.
- Query optimization suggestions
- Index recommendation
- SQL rewrite proposals
- RFC compliance checks
- Custom guideline validation (give a URL with your specific guidelines)
AI is optional. pgAssistant remains fully usable without it.
(See /media folder for more screenshots.)
Follow the guide:
https://beh74.github.io/pgassistant-blog/doc/startup_docker/
https://beh74.github.io/pgassistant-blog/doc/startup_python/
Try the demo:
https://ov-004f8b.infomaniak.ch/
Demo database connection: postgresql://postgres:demo@demo-db:5432/northwind
If you want to try the new database report API coming with v2.0 :
curl -X POST https://ov-004f8b.infomaniak.ch/api/v1/report \
-H "Content-Type: application/json" \
-d '{
"db_config": {
"db_host": "demo-db",
"db_port": 5432,
"db_name": "northwind",
"db_user": "postgres",
"db_password": "demo"
}
}'
The demo database is reset daily.
- Backend developers working with complex SQL
- PostgreSQL DBAs : you can add your favorites secrets queries using the MyQueries feature
- DevOps engineers diagnosing performance issues
- Teams without dedicated DBA resources
- Developers wanting to understand PostgreSQL internals more deeply
Traditional tools tell you what is slow.
pgAssistant helps you understand:
- Why it is slow
- What to change
- How to validate the change
It combines deterministic PostgreSQL introspection with optional AI reasoning to make developers more autonomous.
LLMs can make mistakes.
Always validate suggestions and test extensively before applying changes in production.
Full documentation:
https://beh74.github.io/pgassistant-blog/
RSS feed:
https://beh74.github.io/pgassistant-blog/index.xml
Contributions welcome.
MIT License
UI framework based on Volt Bootstrap 5 Dashboard:
https://github.com/themesberg/volt-bootstrap-5-dashboard