What is pgAssistant?

May 3, 2026 · View on GitHub

pgAssistant

pgAssistant

PostgreSQL performance and schema analysis tool
Understand and improve PostgreSQL workloads using execution plans and database context.

⭐ If pgAssistant is useful to you, consider starring the repository.

What is pgAssistant?

pgAssistant is an open-source tool that helps developers analyze and improve PostgreSQL databases.

It focuses on combining database introspection with practical recommendations, rather than just displaying metrics.

Main capabilities:

  • Global Advisor: database-wide deterministic analysis
  • Query analysis based on real execution plans (EXPLAIN ANALYZE)
  • Schema inspection (DDL) with relational visualization
  • Detection of common structural issues (indexes, foreign keys, data types)
  • Database configuration and statistics checks
  • Optional AI-assisted analysis

Deterministic analysis

Since version 2.8, pgAssistant introduces the Global Advisor.

It runs a set of checks directly against PostgreSQL system catalogs and produces a list of recommendations.

Each recommendation includes:

  • A rank (priority)
  • A confidence level
  • An estimated impact
  • An estimated effort
  • A suggested SQL statement when relevant

Typical findings include:

  • Missing or unused indexes
  • Redundant indexes
  • Foreign key issues
  • Outdated statistics
  • Table bloat or missing maintenance
  • Configuration problems

This analysis is deterministic: given the same database state, it produces the same results.

Query-level analysis

pgAssistant can also analyze individual queries using real execution plans:

  • EXPLAIN ANALYZE parsing
  • Index suggestions
  • Join and execution strategy insights

This is useful for investigating specific slow queries.

Optional AI assistance

AI can be enabled as an additional layer, but is not required.

It can help with:

  • Query rewrites
  • Additional optimization suggestions
  • Naming and convention checks

When used, AI is provided with database context (schema, statistics, plans) to improve relevance.

Changelog

View the full changelog

Screenshots

Dashboard

Dashboard

Global advisor summary

Global advisor

Global advisor recommandations

Global advisor

Query Insight

Query Insight

Query Insight Relational view

Query Insight

SQL Advisor

SQL Advisor

AI Query Optimization

LLM Optimize Query

Quick Start

https://beh74.github.io/pgassistant-blog/doc/startup_docker/

Python (local setup)

https://beh74.github.io/pgassistant-blog/doc/startup_python/

Live demo

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

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

⚠️ The public demo does not use AI
⚠️ Do not provide personal API keys

The demo database is reset daily.

Who is it for?

  • Developers working with SQL
  • DevOps engineers troubleshooting performance
  • Teams without dedicated database expertise

Documentation

https://beh74.github.io/pgassistant-blog/

License

MIT

Acknowledgments

UI based on Volt Bootstrap 5 Dashboard:
https://github.com/themesberg/volt-bootstrap-5-dashboard