PgAssistant is an open-source tool for PostgreSQL developers to analyze and optimize database performance, identify schema issues, and manage SQL queries. It integrates deterministic analysis with LLM capabilities for deeper insights, RFC compliance checks, and query optimization, aiming to empower developers and reduce reliance on DBAs.

How It Works

PgAssistant combines static SQL linting (via Sqlfluff) and performance analysis tools like PGTune with LLM integrations (OpenAI, Ollama, or custom LLMs). It analyzes database behavior, identifies schema problems, and suggests optimizations. The LLM component can check table definitions against RFCs, help find primary keys, analyze query plans, and suggest index coverage improvements, offering a hybrid approach to database tuning.

Quick Start & Requirements

• Docker: docker-compose up (access at http://localhost:8080/database.html)
• Manual: Requires Python 3.x, virtualenv, pip3 install -r requirements.txt, and flask run.
• Prerequisites: PostgreSQL pg_stat_statements module must be enabled.
• LLM Access: OpenAI API key or a local LLM setup (e.g., Ollama) is needed for AI features.
• Docs: https://github.com/nexsol-technologies/pgassistant-postgres-sample/tree/main

Highlighted Details

• Integrates LLMs (OpenAI, Ollama) for advanced query analysis, RFC compliance checks, and primary key suggestions.
• Includes PGTune for PostgreSQL configuration recommendations and pg_dump for retrieving DDL of top queries.
• Features SQL linting via Sqlfluff and management of custom SQL queries in a JSON file.
• Provides insights into cache usage, index coverage, and common parameter values using pg_stats.

Maintenance & Community

The project appears to be primarily maintained by Nexsol Technologies. Community links or active development forums are not explicitly mentioned in the README.

Licensing & Compatibility

The README does not explicitly state a license. It mentions using Volt Bootstrap 5, which is MIT licensed. Compatibility for commercial use or closed-source linking is not specified.

Limitations & Caveats

The tool is intended for non-production environments due to the inherent inaccuracies of LLMs. Users must ensure the pg_stat_statements module is enabled. LLM accuracy is not guaranteed, and careful testing is advised.