pg_stat_insightsDocumentation

Complete guide to deep PostgreSQL performance analytics. Query optimization, table/index analysis, cache monitoring, and intelligent recommendations for production databases.

Query Analytics
Table Statistics
Index Analysis
Cache Monitoring

Documentation Sections

Everything you need to master PostgreSQL performance analytics

Getting Started

Installation and basic setup of pg_stat_insights

  • Extension installation
  • Configuration
  • First insights
  • Basic queries

Query Analytics

Deep analysis of query performance and patterns

  • Slow query detection
  • Execution patterns
  • Query optimization
  • Performance trends

Table & Index Analysis

Comprehensive table and index usage statistics

  • Bloat detection
  • Index usage
  • Missing indexes
  • Vacuum recommendations

Cache Monitoring

Buffer cache efficiency and optimization

  • Hit ratios
  • Cache usage
  • Memory optimization
  • Performance tuning

Replication Insights

Real-time replication monitoring and analysis

  • Lag monitoring
  • WAL analysis
  • Health checks
  • Failover insights

API Reference

Complete API documentation and examples

  • REST endpoints
  • Python client
  • WebSocket API
  • Integration examples

Best Practices

Optimization strategies and recommendations

  • Performance tuning
  • Monitoring setup
  • Alert configuration
  • Maintenance

Analytics Categories

Comprehensive performance insights across all database aspects

Query Performance

Comprehensive analysis of query execution patterns and performance bottlenecks

Execution time
Call counts
Rows processed
Cache hits

Table Statistics

Detailed table-level metrics for storage optimization and maintenance planning

Row counts
Bloat percentage
Scan ratios
Vacuum stats

Index Usage

Index utilization analysis and recommendations for optimization

Scan counts
Usage patterns
Size tracking
Efficiency ratios

Cache Efficiency

Buffer cache performance monitoring and memory optimization insights

Hit ratios
Buffer usage
Cache misses
Memory stats

Replication Health

Real-time replication monitoring across all standby servers

Lag times
WAL position
Sync status
Health scores

Connection Stats

Connection monitoring and lock contention analysis

Active connections
Idle sessions
Lock analysis
Wait events

Data Sources

PostgreSQL extensions and system catalogs for comprehensive analytics

SourcePurposeKey MetricsStatus
pg_stat_statements
Query execution statistics
Execution timeCall countsRows+1 more
Required
pg_stat_kcache
Kernel cache metrics
CPU timeSystem timeI/O time+1 more
Optional
pg_qualstats
Predicate statistics
Qual usagePredicate frequencyIndex usage+1 more
Optional
pg_buffercache
Buffer cache analysis
Cache usageBuffer hitsPage stats+1 more
Optional
pg_stat_user_tables
Table-level statistics
Row countsScansTuples+1 more
Built-in
pg_stat_user_indexes
Index usage statistics
Scan countsTuplesSize+1 more
Built-in
pg_stat_replication
Replication monitoring
Lag timesWAL positionSync status+1 more
Built-in
pg_stat_activity
Connection monitoring
Active sessionsQuery statesWait events+1 more
Built-in

Quick Start

Enable pg_stat_insights in your PostgreSQL database

SQL Commands
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;
CREATE EXTENSION IF NOT EXISTS pg_qualstats;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
-- Configure pg_stat_statements
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
SELECT pg_reload_conf();
-- Access via pgSentinel
# Dashboard: http://localhost:3000/insights
# API: http://localhost:8000/api/v1/insights/*

Start Analyzing Performance

Dive deep into PostgreSQL performance with comprehensive analytics and intelligent recommendations.

Getting Started GuideProduct OverviewAPI Reference