PG Analyzer

A real-time PostgreSQL query performance monitoring tool built with Tauri, React, and TypeScript. PG Analyzer helps you identify slow queries, analyze execution plans, and track query performance over time.

⚠️ Disclaimer

This project is created for educational and learning purposes only. It demonstrates the integration of Tauri, React, and PostgreSQL monitoring capabilities. Please use responsibly and ensure you have proper permissions before monitoring any production databases.

Features

• 🚀 Real-time Query Monitoring - Monitor PostgreSQL queries as they execute
• 📊 Performance Metrics - Track execution time, call frequency, and resource usage
• 🔍 Query Analysis - Get AI-powered suggestions for query optimization
• 📈 Historical Tracking - View query performance trends over time
• 🎯 Execution Plan Visualization - Interactive visualization of query execution plans
• 💾 Local Storage - Query history stored in embedded DuckDB for fast analytics
• 🎨 Modern UI - Clean, responsive interface with sortable and resizable tables

Prerequisites

• PostgreSQL 12+ with pg_stat_monitor extension installed
• Rust 1.70+ (for building from source)
• Node.js 18+ and npm/yarn (for development)
• macOS, Windows, or Linux

Installing pg_stat_monitor

The pg_stat_monitor extension is required for advanced query monitoring:

-- Install the extension
CREATE EXTENSION pg_stat_monitor;

-- Configure PostgreSQL (postgresql.conf)
shared_preload_libraries = 'pg_stat_monitor'
pg_stat_monitor.pgsm_enable_query_plan = on

Installation

Download Pre-built Binaries

Download the latest release for your platform from the Releases page.

Build from Source

1. Clone the repository:

git clone https://github.com/Antoine-C/pg-analyzer.git
cd pg-analyzer

2. Install dependencies:

npm install

3. Build the application:

npm run tauri build

The built application will be available in src-tauri/target/release/bundle/.

Usage

1. Launch PG Analyzer

   • Open the application
   • You'll be prompted to configure your PostgreSQL connection

2. Configure Database Connection

   • Host: Your PostgreSQL server address
   • Port: PostgreSQL port (default: 5432)
   • Database: Name of the database to monitor
   • Username: PostgreSQL user with appropriate permissions
   • Password: User password

3. Monitor Queries

   • The main dashboard shows real-time query statistics
   • Click on any query to see detailed analysis
   • Use the sortable columns to find problematic queries

4. Analyze Performance

   • View execution plans with interactive visualization
   • Get AI-powered optimization suggestions
   • Track query performance history over time

Development

Setup

1. Install Rust and Cargo:

curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

2. Install Node.js dependencies:

npm install

3. Install Tauri CLI:

cargo install tauri-cli

Running in Development

npm run tauri dev

This will start the application in development mode with hot reloading.

Project Structure

pg-analyzer/
├── src/                    # React frontend
│   ├── components/         # React components
│   ├── App.tsx            # Main application component
│   └── ...
├── src-tauri/             # Rust backend
│   ├── src/
│   │   ├── main.rs        # Application entry point
│   │   ├── postgres.rs    # PostgreSQL connection logic
│   │   └── ...
│   └── Cargo.toml         # Rust dependencies
├── package.json           # Node.js dependencies
└── README.md

Configuration

PostgreSQL Permissions

The PostgreSQL user needs the following permissions:

• SELECT privilege on pg_stat_monitor views
• CONNECT privilege on the target database

-- Grant necessary permissions
GRANT CONNECT ON DATABASE your_database TO your_user;
GRANT SELECT ON pg_stat_monitor TO your_user;

Application Settings

Settings are stored locally and include:

• Database connection details
• Query refresh intervals
• UI preferences

Troubleshooting

Common Issues

Connection Failed

• Verify PostgreSQL is running and accessible
• Check firewall settings
• Ensure pg_stat_monitor extension is installed
• Verify user permissions

No Query Data

• Ensure pg_stat_monitor is properly configured
• Check that queries are being executed on the monitored database
• Verify shared_preload_libraries includes pg_stat_monitor

Build Errors

• Update Rust: rustup update
• Clear build cache: cargo clean
• Update dependencies: npm update

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Built with Tauri - A framework for building native apps with web technologies
• Uses pg_stat_monitor for PostgreSQL monitoring
• Query analysis powered by OpenAI
• Data storage with DuckDB

Support

• 🐛 Issues: GitHub Issues
• 💬 Discussions: GitHub Discussions

---

Made with ❤️ for the PostgreSQL community