pg_stat_insights: Deep PostgreSQL Performance Analytics

Comprehensive Database Intelligence with Query Optimization & Intelligent Recommendations

pg_stat_insights DashboardLive Analytics

Top Slow Queries

SELECT * FROM large_table WHERE ...2.4s avg

UPDATE orders SET status = ...1.8s avg

SELECT COUNT(*) FROM events ...1.2s avg

Cache Hit Ratio

98.7%

Total Queries

1.2M

Active Connections

127

Replication Lag

0.2s

Optimization Recommendations

• Create index on users.email (80% queries)

• Vacuum table orders (15% bloat detected)

• Consider partitioning events table (50M rows)

Query AnalyticsTable StatisticsIndex AnalysisCache MonitoringReplication InsightsBloat DetectionLock AnalysisRecommendations

Complete Database Analytics

Why pg_stat_insights

Query Performance Analytics

Leverage pg_stat_statements to identify slow queries, analyze execution patterns, and track query performance over time. Automatic detection of queries exceeding thresholds with detailed statistics.

Table & Index Insights

Monitor table growth, detect bloat, analyze vacuum effectiveness, and track index usage. Get recommendations for missing indexes and identify unused indexes consuming space.

Cache Efficiency Analysis

Track buffer cache hit ratios, analyze cache effectiveness, and optimize shared_buffers configuration. Detect cache misses and recommend memory adjustments.

Replication Monitoring

Real-time lag detection across all replicas, WAL sender/receiver monitoring, and streaming replication health checks with automatic alerting for lag spikes.

Connection Statistics

Monitor active, idle, and idle-in-transaction connections. Detect connection leaks, analyze connection patterns, and optimize max_connections settings.

Lock & Bloat Detection

Identify blocking queries, analyze lock types, detect table/index bloat, and recommend maintenance operations like VACUUM or REINDEX.

Vacuum Analytics

Track autovacuum progress, analyze vacuum effectiveness, detect vacuum candidates, and optimize autovacuum settings for better performance.

Intelligent Recommendations

Get actionable insights powered by pattern analysis including index suggestions, vacuum recommendations, configuration optimizations, and query rewrites.

Depth

Feature Matrix

Analytics Category	Data Sources	Key Metrics	Output
Query Analytics	pg_stat_statements	Execution time, calls, rows, cache hits	Slow query list, performance trends
Table Statistics	pg_stat_user_tables	Rows, scans, tuples, bloat percentage	Bloat analysis, vacuum recommendations
Index Usage	pg_stat_user_indexes	Scans, tuples, size, usage patterns	Missing/unused index recommendations
Cache Efficiency	pg_stat_database, pg_buffercache	Hit ratio, blocks read/hit, cache usage	Memory optimization suggestions
Replication Health	pg_stat_replication	Lag bytes/seconds, WAL position, state	Lag alerts, replication monitoring
Connection Stats	pg_stat_activity	Active, idle, idle-in-transaction counts	Connection leak detection
Lock Analysis	pg_locks	Lock types, blocking queries, wait times	Deadlock prevention insights
Vacuum Monitoring	pg_stat_progress_vacuum	Progress, tuples, phases, duration	Autovacuum optimization