A PostgreSQL assistant for developers
Understand, optimize, and improve your PostgreSQL database with ease.

pgAssistant is an open-source tool designed to help developers understand and optimize PostgreSQL database performance.
It provides actionable insights into database behavior, detects schema issues, assists in query tuning, and even integrates with AI to go beyond traditional analysis.

Originally published under the MIT License, this project is now community-maintained.
The original public snapshot is archived here (Wayback Machine).

You can try the live demo here: https://ov-004f8b.infomaniak.ch/

To connect to the demo database, use the following credentials:

• Host name: demo-db
• Port: 5432
• Database name: northwind
• User: postgres
• Password: demo

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"
    }
  }'

• This demo does not use any Large Language Model (LLM).
  Do not provide any API keys or credentials in the LLM Settings form.

• If you enter your own LLM provider API key in the demo, please be aware that:

  • The key may be visible to other demo users.
  • The key could be used by others without your consent.
  • The demo administrator accepts no responsibility for any misuse, exposure, or unauthorized access resulting from providing such keys.

• The demo database is reset and reinitialized from scratch every day.
  All stored data will be permanently deleted during this process.

• In-Depth Performance Analysis – Understand exactly how your PostgreSQL instance behaves.
• Schema Optimization – Detect and fix structural issues in your database.
• Top Query Insights – Spot your heaviest queries, automatically suggest parameters , run EXPLAIN ANALYZE, and get an easy-to-understand breakdown of the results — enhanced by AI if you choose.
• PGTune Integration – Get recommended ALTER SYSTEM parameters tailored to your workload. Create a ready-to-use docker-compose file from PGTune results.
• Index & Cache Coverage – Measure index usage per table/column and identify queries with poor cache/index hit ratios.

• Query Library – Store and manage SQL queries in a JSON file for quick reuse.
• SQL Linting – Keep your SQL clean with sqlfluff.
• Common Value Detection – Use pg_stats to discover the most frequent query parameter values.

• Compatibility — pgAssistant is compatible with any LLM that supports the OpenAI API.
• Local LLM Support – Use Ollama or other locally hosted models seamlessly.
• Use AI to perform RFC compliance Checks – Verify if table definitions comply with relevant RFCs.
• Use AI to verify table compliance with your SQL guidelines - Just provide a valid URL to your guidelines.
• Use AI to optimize a query – AI can help you define new indexes or rewrite a query.

Check this post to try out the new open-source gpt-oss model integration with pgAssistant and Ollama.

If running an LLM locally isn’t an option for you, this post might be worth checking out.

• pgAssistant is Docker based – Easy to deploy

Choose your preferred setup method:

Follow the guide Get Started with Docker

Follow the guide Get Started with Python

Need help? Check the complete documentation and articles on the pgAssistant Blog.

RSS feed : https://beh74.github.io/pgassistant-blog/index.xml

Most database optimization tools are deterministic:
they tell you what is slow, but not why it’s slow or how to fix it beyond metrics.

pgAssistant bridges that gap by combining deterministic analysis with the reasoning power of LLMs:

• Check compliance against standards like RFCs
• Suggest structural improvements such as adding missing primary keys
• Offer optimization paths based on context, not just numbers

Note: LLMs can make mistakes—sometimes big ones. Always validate suggestions, test extensively, and use pgAssistant in non-production environments.
The goal is to make developers more autonomous, educated, and less dependent on DBA time.

Thanks to the creators of Volt Bootstrap 5 Dashboard for the beautiful UI framework that powers pgAssistant’s interface.
You saved me countless hours of front-end work!