Getting Started withpg_stat_insights

Deep PostgreSQL performance analytics in minutes. Identify bottlenecks, optimize queries, and monitor database health.

2-minute setup
PostgreSQL native
Zero dependencies

Setup Steps

Follow these steps to start analyzing your PostgreSQL performance

1

PostgreSQL Extensions

Install required PostgreSQL extensions for deep analytics

Core Extensions

-- Connect to your PostgreSQL database
psql -U postgres -d your_database
-- Enable pg_stat_statements (required)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Enable additional analytics extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;
CREATE EXTENSION IF NOT EXISTS pg_qualstats;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

Configuration

Add to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
Restart PostgreSQL after changes
2

Installation

Install pg_stat_insights Python package

Python Package

# Install via pip
pip install pg-stat-insights
# Or install from source
git clone https://github.com/pgelephant/pg-stat-insights.git
cd pg-stat-insights
pip install -e .

Dependencies

Required Python packages:
asyncpg>=0.29.0
psycopg2-binary>=2.9.0
pydantic>=2.0.0
psutil>=5.9.0
3

Basic Usage

Connect and start collecting insights

Simple Connection

import asyncio
from pg_stat_insights import PgStatInsights
async def main():
# Connect to PostgreSQL
insights = PgStatInsights(
"postgresql://user:pass@localhost:5432/dbname"
)
await insights.connect()
# Get dashboard data
dashboard = await insights.get_complete_dashboard()
print(dashboard)
await insights.close()
asyncio.run(main())

Environment Variables

Set connection details:
export DATABASE_URL="postgresql://user:pass@localhost:5432/dbname"
# Or use .env file
DATABASE_URL=postgresql://user:pass@localhost:5432/dbname
4

Integration with pgSentinel

Use with pgSentinel monitoring platform

pgSentinel Integration

pg_stat_insights is automatically integrated with pgSentinel:
  • Real-time dashboard at /insights
  • REST API endpoints for programmatic access
  • Prometheus metrics export
  • Grafana dashboard integration

API Endpoints

# Available endpoints
GET /api/v1/insights/dashboard
GET /api/v1/insights/queries
GET /api/v1/insights/tables
GET /api/v1/insights/indexes
GET /api/v1/insights/recommendations
5

First Insights

Explore your database performance data

Query Performance

Start by analyzing query performance:
  • Identify slowest queries by execution time
  • Find most frequently executed queries
  • Analyze query patterns and optimization opportunities

Table & Index Analysis

Monitor table and index health:
  • Detect table bloat and vacuum needs
  • Find unused or missing indexes
  • Monitor cache hit ratios

Quick Start Examples

Get started with common analytics tasks

Query Analysis

Find slow queries and optimization opportunities

# Get top slow queries
queries = await insights.get_query_insights(limit=10)
for query in queries:
    print(f"Query: {query['query'][:100]}...")
    print(f"Avg time: {query['avg_time']}ms")
    print(f"Calls: {query['calls']}")
    print("---")

Table Statistics

Monitor table health and bloat

# Get table statistics
tables = await insights.get_table_stats()
for table in tables:
    print(f"Table: {table['table_name']}")
    print(f"Bloat: {table['bloat_percentage']}%")
    print(f"Size: {table['size_mb']}MB")
    print("---")

Cache Analysis

Monitor buffer cache performance

# Get cache hit ratio
cache_stats = await insights.get_cache_stats()
print(f"Buffer hit ratio: {cache_stats['buffer_hit_ratio']}%")
print(f"Index hit ratio: {cache_stats['index_hit_ratio']}%")
print(f"Toast hit ratio: {cache_stats['toast_hit_ratio']}%")

Recommendations

Get intelligent optimization suggestions

# Get performance recommendations
recommendations = await insights.get_recommendations()
for rec in recommendations:
    print(f"Priority: {rec['priority']}")
    print(f"Type: {rec['type']}")
    print(f"Description: {rec['description']}")
    print("---")

Troubleshooting

Common issues and solutions

Extension not found

Ensure PostgreSQL extensions are properly installed and enabled

$ \dx
$ CREATE EXTENSION pg_stat_statements;

Connection refused

Check database connection string and PostgreSQL is running

$ psql $DATABASE_URL
$ systemctl status postgresql

No data in pg_stat_statements

Restart PostgreSQL after enabling extension and run some queries

$ SELECT * FROM pg_stat_statements LIMIT 1;
$ systemctl restart postgresql

Permission denied

Ensure user has proper permissions to access system catalogs

$ GRANT pg_monitor TO your_user;
$ GRANT SELECT ON pg_stat_statements TO your_user;

Next Steps

Now that pg_stat_insights is set up, explore advanced analytics and integration options.

Query Analytics

Deep dive into query performance analysis

API Reference

Complete API documentation and examples

Best Practices

Optimization strategies and monitoring tips