pg_stat_insights: PostgreSQL Performance Monitoring Extension

📦 View on GitHub | 📥 Download Latest Release | 📖 Documentation

Executive Summary

PostgreSQL databases require comprehensive performance monitoring to identify slow queries, optimize cache usage, track resource consumption, and ensure replication health. pg_stat_insights provides a complete solution with 52 detailed metrics tracked across 42 specialized pre-built views, making it a powerful drop-in replacement for pg_stat_statements. The extension is production-ready, fully tested, and compatible with PostgreSQL 16, 17, and 18.

Introduction

Database administrators constantly face the challenge of maintaining optimal PostgreSQL performance. While standard PostgreSQL statistics provide basic metrics, they often lack the granularity and depth needed for effective optimization and troubleshooting. When queries slow down, cache efficiency drops, or replication lag increases, administrators need detailed insights to identify root causes and implement solutions quickly.

pg_stat_insights extends PostgreSQL's native statistics capabilities by adding enhanced analytics, automatic response time categorization, comprehensive WAL tracking, detailed JIT compilation statistics, time-series aggregation, and extensive replication monitoring. All of this functionality comes in a single, well-integrated extension that operates with minimal performance overhead.

Comprehensive Metrics Coverage

pg_stat_insights tracks 52 distinct metrics organized into eight comprehensive categories that cover every aspect of query execution and database performance. The execution metrics category includes detailed timing information with plans, calls, rows, and statistical measures including total, minimum, maximum, mean, and standard deviation for both planning and execution times. This statistical depth allows administrators to understand not just average performance, but also variability and consistency of query execution.

Buffer I/O metrics provide complete visibility into how queries interact with PostgreSQL's shared buffer cache, local buffers, and temporary file operations. The extension tracks hits, reads, dirtied blocks, and writes across all three buffer types, along with precise timing information for each I/O operation when enabled. This granular I/O tracking helps identify queries that are causing excessive disk reads or writes, enabling targeted optimization efforts.

WAL statistics capture write-ahead log generation at the query level, tracking the number of WAL records created, full page images written, total bytes generated, and instances where WAL buffers became full. This information is crucial for understanding replication impact, planning WAL archiving capacity, and identifying write-heavy operations that might be causing replication lag.

JIT compilation metrics provide detailed insights into PostgreSQL's Just-In-Time compilation system, tracking function compilation counts, generation time, inlining operations, optimization passes, code emission, and tuple deforming operations. These metrics help administrators understand when JIT compilation is beneficial versus when it adds overhead.

Parallel execution metrics track the planned versus actual number of parallel workers launched, enabling analysis of parallel query efficiency. Metadata columns identify queries by user, database, query ID, and whether they're top-level or nested queries, while timestamp columns track when statistics collection began for each query pattern.

Pre-Built Views for Instant Insights

pg_stat_insights provides 42 specialized views organized into distinct categories, each designed to answer specific performance questions without requiring complex SQL queries. The query performance views include the main statistics view containing all 52 metrics, along with specialized views for identifying slowest queries by total time, most frequently called queries, highest I/O consumers, poor cache performers, queries exceeding performance thresholds, execution errors, plan estimation issues, response time distribution histograms, and time-series aggregations.

The replication monitoring system includes 17 comprehensive views covering both physical and logical replication scenarios. Physical replication views provide health monitoring with automatic status classification, bottleneck detection that identifies whether lag is caused by network, disk I/O, or replay operations, performance ratings from excellent to critical based on configurable thresholds, WAL tracking with retention analysis, and threshold-based alerting. Logical replication views monitor subscription health and sync status, publication configuration and active subscribers, per-table synchronization progress, conflict detection and resolution, and WAL safety tracking.

The index monitoring system includes 11 specialized views that provide comprehensive index analytics. These views track index usage statistics with scan frequency and utilization metrics, detect index bloat by comparing actual versus expected index sizes, calculate efficiency ratings and cache performance, generate maintenance recommendations with priority levels, identify potential missing indexes based on sequential scan patterns, provide cluster-wide index health summaries, surface critical alerts for unused or bloated indexes, track historical index size growth trends, monitor lock contention statistics, and consolidate all metrics into a single JSON dashboard format suitable for Grafana and Prometheus integration.

Time-series bucket views enable trend analysis by grouping statistics into hourly or daily buckets, allowing administrators to identify performance patterns, growth trends, and anomalies over time. These views track query performance trends, index usage patterns, index size growth, replication statistics, and replication lag trends with automatic severity classification.

Response Time Categorization and SLA Monitoring

One of the unique features of pg_stat_insights is its automatic query categorization system that groups queries into six execution time buckets. Queries executing in less than one millisecond are classified as ultra-fast, representing simple indexed lookups and cached operations. Fast queries fall between one and ten milliseconds, typically representing basic joins and small aggregations. Normal queries execute between ten and one hundred milliseconds, covering complex queries and medium aggregations. Slow queries take between one hundred milliseconds and one second, often involving large joins or full table scans. Very slow queries execute between one and ten seconds, typically representing heavy analytics or batch operations. Critical slow queries exceed ten seconds and require immediate attention.

This categorization system enables automatic SLA monitoring without requiring custom queries. Administrators can quickly understand the distribution of query performance across their database, identify queries that violate performance thresholds, and track performance trends over time. The histogram summary view provides a complete breakdown showing the percentage of queries and total execution time in each category, making it easy to understand overall database performance characteristics.

Installation and Configuration

Installing pg_stat_insights requires PostgreSQL 16, 17, or 18 with development headers and standard build tools. The installation process begins by cloning the repository from GitHub, building the extension using standard PostgreSQL extension build procedures, and installing it into the PostgreSQL installation directory.

# Clone the repository
git clone https://github.com/pgelephant/pg_stat_insights.git
cd pg_stat_insights

# Build and install
make clean && make
sudo make install

Once installed, administrators must add pg_stat_insights to the shared_preload_libraries configuration parameter in postgresql.conf, which requires a PostgreSQL server restart.

The extension provides 11 configuration parameters that allow fine-tuning of monitoring behavior and resource usage. The max_queries parameter controls how many distinct query patterns are tracked, with a default of 5,000 queries. This parameter requires a restart to change and directly impacts memory usage, with approximately 100 bytes per tracked query. Administrators should set this value based on their workload characteristics, with larger databases requiring higher values to capture all important query patterns.

# Load the extension (restart required)
shared_preload_libraries = 'pg_stat_insights'

# Optional: Configure parameters (11 available)
pg_stat_insights.max_queries = 5000                    # Max unique statements tracked
pg_stat_insights.track_utility = on                    # Track utility commands
pg_stat_insights.track_planning = off                   # Track planning statistics
pg_stat_insights.track_wal = on                         # Track WAL generation
pg_stat_insights.track_jit = on                         # Track JIT compilation
pg_stat_insights.track_replication = on                 # Track replication stats
pg_stat_insights.track_io_timing = off                  # Track I/O timing
pg_stat_insights.track_parallel_queries = on            # Track parallel workers
pg_stat_insights.track_minmax_time = on                 # Track min/max times
pg_stat_insights.track_level = 'top'                    # Track top-level or all queries
pg_stat_insights.histogram_buckets = 10                 # Response time buckets

Restart PostgreSQL and create the extension:

CREATE EXTENSION pg_stat_insights;

Verification: After creating the extension, verify it was installed successfully:

SELECT 
    extname,
    extversion,
    extrelocatable
FROM pg_extension
WHERE extname = 'pg_stat_insights';

 extname          | extversion | extrelocatable
------------------+------------+---------------
 pg_stat_insights | 3.0        | f
(1 row)

The extension is now installed and ready to use. Verify that the main statistics view is accessible:

SELECT count(*) as total_metrics 
FROM information_schema.columns 
WHERE table_name = 'pg_stat_insights';

 total_metrics
--------------
           52
(1 row)

The verification confirms that pg_stat_insights is tracking all 52 metrics. The extension is now collecting statistics for all queries executed in the database.

Understanding Configuration Parameters

Tracking parameters control which aspects of query execution are monitored. Each parameter can be adjusted based on your specific monitoring needs. The track_utility parameter enables monitoring of DDL commands like CREATE, ALTER, and DROP statements, which is useful for understanding schema change impact. When enabled, you can track how long index creation takes, how schema changes affect performance, and identify expensive DDL operations.

-- Enable utility command tracking
ALTER SYSTEM SET pg_stat_insights.track_utility = on;
SELECT pg_reload_conf();

-- Verification: Check that utility tracking is enabled
SELECT name, setting, source
FROM pg_settings
WHERE name = 'pg_stat_insights.track_utility';

 name                           | setting | source
--------------------------------+---------+--------
 pg_stat_insights.track_utility | on      | configuration file
(1 row)

The track_planning parameter enables detailed planning statistics, useful for identifying queries with expensive planning phases. When enabled, you can see how long query planning takes relative to execution time, identify queries that plan frequently, and detect plan instability issues.

-- Enable planning statistics tracking
ALTER SYSTEM SET pg_stat_insights.track_planning = on;
SELECT pg_reload_conf();

-- After running some queries, check planning statistics
SELECT 
    query,
    plans,
    calls,
    round((plans::numeric / NULLIF(calls, 0)), 3) as plan_per_call_ratio,
    round(mean_plan_time::numeric, 2) as avg_plan_ms,
    round(mean_exec_time::numeric, 2) as avg_exec_ms
FROM pg_stat_insights
WHERE plans > 0
ORDER BY mean_plan_time DESC
LIMIT 5;

 query                              | plans | calls | plan_per_call_ratio | avg_plan_ms | avg_exec_ms
------------------------------------+-------+-------+---------------------+-------------+------------
 SELECT * FROM complex_join WHERE   |   234 |   234 |                1.000 |       12.34 |      234.56
 SELECT COUNT(*) FROM events WHERE |  1234 |  5678 |                0.217 |        5.67 |       89.12
(2 rows)

The track_wal parameter monitors write-ahead log generation, essential for replication monitoring and capacity planning. When enabled, you can identify which queries generate the most WAL, understand replication lag sources, and plan WAL archiving capacity.

The track_jit parameter captures Just-In-Time compilation statistics, helping evaluate JIT effectiveness. When enabled, you can see which queries benefit from JIT compilation, identify queries where JIT overhead exceeds benefits, and optimize JIT thresholds.

The track_io_timing parameter provides precise I/O operation timing, though it may add measurable overhead on some systems. When enabled, you can identify slow storage subsystems, measure I/O performance, and optimize disk-intensive queries. Test this parameter carefully in production as it can add 2-5% CPU overhead.

-- Enable I/O timing (test overhead first)
ALTER SYSTEM SET pg_stat_insights.track_io_timing = on;
SELECT pg_reload_conf();

-- After running queries, analyze I/O performance
SELECT 
    query,
    shared_blks_read,
    round(shared_blk_read_time::numeric, 2) as read_time_ms,
    round((shared_blk_read_time / NULLIF(shared_blks_read, 0))::numeric, 3) as ms_per_block,
    CASE 
        WHEN shared_blk_read_time / NULLIF(shared_blks_read, 0) > 10 THEN '[SLOW] Very Slow Storage'
        WHEN shared_blk_read_time / NULLIF(shared_blks_read, 0) > 1 THEN '[WARNING] Slow Storage'
        WHEN shared_blk_read_time / NULLIF(shared_blks_read, 0) > 0.1 THEN '[OK] Normal (HDD)'
        ELSE '[FAST] Fast (SSD)'
    END AS storage_speed
FROM pg_stat_insights
WHERE shared_blks_read > 100
ORDER BY (shared_blk_read_time / NULLIF(shared_blks_read, 0)) DESC
LIMIT 10;

 query                         | shared_blks_read | read_time_ms | ms_per_block | storage_speed
-------------------------------+------------------+--------------+--------------+----------------
 SELECT * FROM large_table     |           123456 |      1234.56 |        0.010 | [FAST] Fast (SSD)
 SELECT * FROM archived_data   |            56789 |       567.89 |        0.010 | [FAST] Fast (SSD)
(2 rows)

The track_parallel_queries parameter monitors parallel worker utilization, important for understanding parallel query efficiency. The track_minmax_time parameter captures minimum and maximum execution times, enabling variability analysis. The track_level parameter controls whether only top-level queries or all nested queries are tracked, with top-level being recommended for production to minimize overhead. The track_replication parameter enables replication monitoring views. The histogram_buckets parameter controls the granularity of response time distribution, with a default of 10 buckets.

Most configuration parameters can be changed without restarting PostgreSQL by using ALTER SYSTEM and pg_reload_conf(), making it easy to adjust monitoring behavior based on changing requirements. However, the max_queries parameter requires a restart, so it should be set appropriately during initial configuration.

-- Check current configuration
SELECT 
    name,
    setting,
    unit,
    source,
    pending_restart
FROM pg_settings
WHERE name LIKE 'pg_stat_insights%'
ORDER BY name;

 name                                | setting | unit | source              | pending_restart
-------------------------------------+---------+------+---------------------+----------------
 pg_stat_insights.histogram_buckets | 10      |      | configuration file  | f
 pg_stat_insights.max_queries       | 5000    |      | configuration file  | f
 pg_stat_insights.track_io_timing   | off     |      | configuration file  | f
 pg_stat_insights.track_jit         | on      |      | configuration file  | f
 pg_stat_insights.track_level       | top     |      | configuration file  | f
 pg_stat_insights.track_minmax_time | on      |      | configuration file  | f
 pg_stat_insights.track_parallel_queries | on  |      | configuration file  | f
 pg_stat_insights.track_planning    | off     |      | configuration file  | f
 pg_stat_insights.track_replication | on      |      | configuration file  | f
 pg_stat_insights.track_utility     | on      |      | configuration file  | f
 pg_stat_insights.track_wal          | on      |      | configuration file  | f
(11 rows)

Practical Usage Scenarios

Finding slow queries is one of the most common use cases for pg_stat_insights. The pg_stat_insights_top_by_time view automatically identifies queries consuming the most cumulative execution time, making it easy to prioritize optimization efforts. Administrators can quickly see which queries are contributing most to overall database load, enabling data-driven decisions about where to focus optimization efforts.

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_insights_top_by_time 
LIMIT 10;

 query                              | calls | total_exec_time | mean_exec_time | rows
------------------------------------+-------+-----------------+----------------+------
 SELECT * FROM orders WHERE status  |  1247 |        12456.78 |           9.99 | 15234
 UPDATE inventory SET quantity =    |   892 |         8934.12 |          10.02 |   892
 SELECT COUNT(*) FROM events WHERE  |  2341 |         7823.45 |           3.34 | 89234
 INSERT INTO logs (timestamp, msg)  | 15678 |         6712.34 |           0.43 | 15678
 DELETE FROM temp_data WHERE date   |   234 |         3421.56 |          14.62 |  1234
(10 rows)

The pg_stat_insights_slow_queries view filters for queries with mean execution time exceeding 100 milliseconds, helping identify consistently slow queries that may indicate missing indexes, inefficient plans, or resource contention.

SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_insights_slow_queries 
ORDER BY mean_exec_time DESC
LIMIT 10;

 query                              | calls | mean_exec_time | total_exec_time
------------------------------------+-------+----------------+----------------
 SELECT * FROM orders WHERE status  |  1247 |         999.12 |       1245678.9
 UPDATE inventory SET quantity =    |   892 |         567.23 |        505969.2
 SELECT COUNT(*) FROM events WHERE  |  2341 |         234.56 |        549089.0
(3 rows)

Cache efficiency analysis becomes straightforward with the pg_stat_insights_top_cache_misses view, which automatically calculates cache hit ratios and identifies queries with poor cache performance. Queries with cache hit ratios below 80% typically indicate opportunities for optimization through increased shared_buffers configuration, additional indexes, table partitioning, or cache pre-warming strategies. The view provides both the raw block statistics and calculated ratios, making it easy to understand cache performance at a glance.

SELECT 
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    round((shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100), 2) as cache_hit_ratio
FROM pg_stat_insights_top_cache_misses 
LIMIT 10;

 query                              | calls | shared_blks_hit | shared_blks_read | cache_hit_ratio
------------------------------------+-------+-----------------+------------------+----------------
 SELECT * FROM large_table WHERE id | 15234 |          892341 |          1081245 |          45.23%
 SELECT * FROM archived_orders      |  8923 |          234567 |           215678 |          52.17%
 SELECT data FROM cold_storage      |  3421 |           89234 |           140123 |          38.91%
(3 rows)

Queries with <80% cache hit ratio need attention. Consider increasing shared_buffers, adding indexes, partitioning large tables, or pre-warming cache for frequently accessed data.

WAL generation tracking helps administrators understand which queries are generating the most write-ahead log data, which directly impacts replication performance and WAL archiving requirements. Queries that generate large amounts of WAL may benefit from optimization to reduce replication lag, especially in high-availability environments. The extension tracks WAL records, full page images, total bytes, and buffer saturation events, providing complete visibility into write activity.

SELECT 
    query,
    calls,
    wal_records,
    pg_size_pretty(wal_bytes::bigint) as wal_size,
    wal_fpi as full_page_images
FROM pg_stat_insights 
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 10;

 query                         | calls | wal_records | wal_size | full_page_images
-------------------------------+-------+-------------+----------+-----------------
 CREATE INDEX CONCURRENTLY ON  |     1 |     234567 |   1178 MB |           12345
 INSERT INTO events VALUES ... | 98765 |     189234 |    900 MB |            8923
 UPDATE orders SET status =    | 45678 |     145678 |    692 MB |            5678
 DELETE FROM temp_data WHERE   | 12345 |      98765 |    470 MB |            3421
 VACUUM ANALYZE large_table    |    12 |      67890 |    323 MB |            2345
(5 rows)

I/O intensive operations are easily identified through the pg_stat_insights_top_by_io view, which ranks queries by total I/O operations across shared, local, and temporary buffers. This view helps identify queries that are causing excessive disk reads, which often indicates opportunities for index optimization or query rewriting.

SELECT 
    query,
    calls,
    shared_blks_read,
    temp_blks_read,
    shared_blks_read + temp_blks_read as total_io
FROM pg_stat_insights_top_by_io 
LIMIT 10;

 query                         | calls | shared_blks_read | temp_blks_read | total_io
-------------------------------+-------+------------------+----------------+---------
 CREATE INDEX CONCURRENTLY ON  |     1 |        1234567 |        987654 |  2222221
 SELECT * FROM events ORDER BY |  1234 |         892345 |        123456 |  1015801
 VACUUM ANALYZE large_table    |    12 |         567890 |              0 |   567890
 SELECT DISTINCT user_id FROM  |  5678 |         345678 |         98765 |   444443
 SELECT COUNT(*) GROUP BY date |  3421 |         234567 |         12345 |   246912
(5 rows)

When I/O timing is enabled, administrators can also identify slow storage subsystems by analyzing the time per block read, with typical values ranging from 0.1 milliseconds for SSDs to 10 milliseconds or more for traditional hard drives.

Response time distribution analysis through the histogram summary view provides a high-level overview of database performance characteristics. Administrators can quickly see what percentage of queries fall into each performance category, enabling SLA monitoring and trend analysis. This view is particularly useful for executive reporting and capacity planning, as it provides clear metrics about overall database health.

SELECT * FROM pg_stat_insights_histogram_summary
ORDER BY bucket_order;

 bucket_name | query_count | total_time | avg_time | percentage
-------------+-------------+------------+----------+-----------
 <1ms        |       45234 |   12.34 ms |  0.27 ms |     62.3%
 1-10ms      |       18923 |  123.45 ms |  6.52 ms |     26.1%
 10-100ms    |        5678 |  345.67 ms | 60.89 ms |      7.8%
 100ms-1s    |        1892 |  678.90 ms |358.59 ms |      2.6%
 1-10s       |         567 | 2345.67 ms |4138.18 ms|      0.8%
 >10s        |         123 |12345.00 ms|100365.8ms|      0.4%
(6 rows)

The histogram shows that 62.3% of queries execute in less than 1 millisecond, indicating excellent overall performance. However, 0.4% of queries exceed 10 seconds, which may require investigation. The percentage column shows the distribution of queries across performance categories, making it easy to understand overall database health at a glance.

JIT compilation statistics help administrators understand when PostgreSQL's Just-In-Time compilation system is providing benefits versus when it's adding overhead. Queries with high JIT generation time relative to execution time may benefit from JIT threshold adjustments or query optimization. The extension tracks all aspects of JIT compilation including function compilation, inlining, optimization, code emission, and tuple deforming operations.

SELECT 
    query,
    jit_functions,
    round(jit_generation_time::numeric, 2) as gen_time_ms,
    round(jit_inlining_time::numeric, 2) as inline_time_ms,
    round(jit_optimization_time::numeric, 2) as opt_time_ms,
    round(jit_emission_time::numeric, 2) as emit_time_ms,
    round(mean_exec_time::numeric, 2) as exec_time_ms,
    round((jit_generation_time / NULLIF(mean_exec_time, 0) * 100)::numeric, 1) as jit_overhead_pct
FROM pg_stat_insights 
WHERE jit_functions > 0
ORDER BY jit_generation_time DESC
LIMIT 10;

 query                         | jit_functions | gen_time_ms | inline_time_ms | opt_time_ms | emit_time_ms | exec_time_ms | jit_overhead_pct
-------------------------------+---------------+-------------+----------------+-------------+-------------+-------------+------------------
 SELECT * FROM large_join      |           156 |       45.23 |          12.34 |       23.45 |        34.56 |      234.56 |             19.3
 SELECT COUNT(*) GROUP BY      |            89 |       34.56 |           9.87 |       18.23 |        23.45 |      189.12 |             18.3
 SELECT DISTINCT id FROM       |            67 |       28.91 |           7.65 |       14.32 |        19.87 |      156.78 |             18.4
(3 rows)

The query shows JIT compilation overhead for queries using JIT. The jit_overhead_pct column shows that JIT generation time represents 18-19% of total execution time for these queries, which is reasonable. If this percentage exceeds 50%, consider adjusting JIT thresholds or optimizing the queries.

Replication monitoring through the comprehensive replication views enables administrators to maintain healthy replication environments. The physical replication views provide health status classifications, bottleneck detection that identifies whether lag is caused by network, disk I/O, or replay operations, and performance ratings based on configurable thresholds.

-- Monitor physical replication health
SELECT 
    application_name,
    client_addr,
    replay_lag_mb,
    replay_lag_seconds,
    health_status,
    CASE 
        WHEN replay_lag_seconds < 1 THEN '[OK] Healthy'
        WHEN replay_lag_seconds < 10 THEN '[WARNING] Warning'
        ELSE '[CRITICAL] Critical'
    END AS status
FROM pg_stat_insights_physical_replication
ORDER BY replay_lag_seconds DESC NULLS LAST;

 application_name | client_addr | replay_lag_mb | replay_lag_seconds | health_status | status
------------------+-------------+---------------+--------------------+---------------+------------------
 node2            | 10.0.1.12   |          0.12 |               0.12 | HEALTHY       | [OK] Healthy
 node3            | 10.0.1.13   |          0.09 |               0.09 | HEALTHY       | [OK] Healthy
(2 rows)

Logical replication views monitor subscription health, publication configuration, per-table sync status, and conflict detection. The replication dashboard view consolidates all replication metrics into a single JSON structure, making it easy to integrate with monitoring systems like Grafana and Prometheus.

-- Monitor logical replication subscriptions
SELECT 
    subscription_name,
    status,
    slot_name,
    publications,
    health_status,
    recommendation
FROM pg_stat_insights_subscriptions
ORDER BY 
    CASE health_status
        WHEN 'Active' THEN 3
        WHEN 'No replication slot' THEN 2
        ELSE 1
    END;

 subscription_name | status  | slot_name      | publications | health_status | recommendation
-------------------+---------+----------------+--------------+---------------+---------------
 my_subscription   | enabled | my_slot        | {pub1}       | Active        | 
(1 row)

-- Check per-table sync status
SELECT 
    subscription_name,
    table_name,
    sync_state_description,
    status_message
FROM pg_stat_insights_subscription_stats
WHERE sync_state != 's'
ORDER BY subscription_name, table_name;

 subscription_name | table_name      | sync_state_description | status_message
-------------------+-----------------+------------------------+------------------
 my_subscription   | public.events   | Synchronized           | Table is fully synchronized
 my_subscription   | public.orders   | Data copy              | Currently copying initial data
(2 rows)

Index monitoring views provide comprehensive analytics for index optimization. The index bloat view estimates bloat by comparing actual index size against expected size based on tuple access patterns, calculating bloat ratios and wasted space. Indexes with bloat ratios above 2.0 are flagged as high severity, indicating that REINDEX operations may be beneficial.

-- Find indexes with bloat
SELECT 
    schemaname,
    tablename,
    indexname,
    bloat_severity,
    estimated_bloat_size_mb,
    needs_reindex
FROM pg_stat_insights_index_bloat
WHERE bloat_severity IN ('HIGH', 'MEDIUM')
ORDER BY estimated_bloat_size_mb DESC;

 schemaname | tablename | indexname           | bloat_severity | estimated_bloat_size_mb | needs_reindex
------------+-----------+---------------------+----------------+-------------------------+---------------
 public     | orders    | orders_status_idx   | HIGH           |                   234.5 | t
 public     | events    | events_timestamp_idx| MEDIUM         |                    89.2 | t
(2 rows)

The missing indexes view analyzes sequential scan patterns to identify tables that may benefit from additional indexes, calculating benefit scores based on scan frequency and tuple read counts.

-- Find potential missing indexes
SELECT 
    schemaname,
    tablename,
    benefit_score,
    estimated_index_size_mb,
    recommended_index_type,
    priority
FROM pg_stat_insights_missing_indexes
WHERE priority = 'HIGH'
ORDER BY benefit_score DESC
LIMIT 10;

 schemaname | tablename | benefit_score | estimated_index_size_mb | recommended_index_type | priority
------------+-----------+---------------+-------------------------+-----------------------+----------
 public     | orders    |       1234567 |                    12.3 | btree                 | HIGH
 public     | events    |         89234 |                     8.9 | btree                 | HIGH
(2 rows)

The index efficiency view calculates efficiency ratings from excellent to unused based on index versus sequential scan ratios, providing recommendations for index tuning or removal.

-- Analyze index efficiency
SELECT 
    schemaname,
    tablename,
    indexname,
    efficiency_rating,
    index_scan_ratio,
    recommendation
FROM pg_stat_insights_index_efficiency
WHERE efficiency_rating IN ('UNUSED', 'POOR')
ORDER BY index_scan_ratio ASC
LIMIT 10;

 schemaname | tablename | indexname         | efficiency_rating | index_scan_ratio | recommendation
------------+-----------+-------------------+-------------------+------------------+------------------
 public     | users     | users_old_idx     | UNUSED            |             0.00 | Consider dropping this index
 public     | logs      | logs_temp_idx     | POOR              |             0.05 | Review index usage or consider removal
(2 rows)

Advanced Configuration and Tuning

pg_stat_insights offers three configuration profiles optimized for different environments. The development profile enables maximal tracking including planning statistics, I/O timing, and all query levels, providing comprehensive visibility at the cost of approximately 10-15% CPU overhead. This profile is ideal for development and debugging scenarios where understanding every aspect of query execution is more important than minimizing overhead.

The production profile balances comprehensive monitoring with performance impact, disabling planning tracking and I/O timing to keep overhead around 2-5% CPU. This profile tracks top-level queries only, includes WAL and JIT statistics, and provides sufficient visibility for production monitoring without significantly impacting application performance. Most production environments should start with this profile and adjust based on specific requirements.

The minimal profile provides basic query performance monitoring with less than 1% CPU overhead, making it suitable for performance-sensitive environments or systems with limited resources. This profile tracks only essential metrics and disables advanced features like WAL tracking, JIT statistics, and replication monitoring. While it provides less visibility, it ensures minimal impact on database performance.

Memory usage scales linearly with the max_queries parameter, with approximately 100 bytes per tracked query. A database tracking 5,000 queries will use approximately 10 megabytes of shared memory, while tracking 50,000 queries would require approximately 100 megabytes. Administrators should monitor the number of tracked queries and adjust max_queries accordingly, increasing it if important queries are being evicted or decreasing it if memory usage becomes a concern.

Performance Overhead and Production Readiness

pg_stat_insights is designed for minimal performance impact in production environments. Under typical production workloads, the extension adds less than 1% CPU overhead even when tracking thousands of queries. Memory usage is predictable and scales linearly with the number of tracked queries, making it easy to plan resource allocation. Query latency impact is negligible, with less than 0.01 milliseconds of overhead per query execution.

The extension has been tested at scale in production deployments tracking over 5,000 unique queries with negligible performance impact. The overhead remains consistent even under heavy load, making it suitable for high-traffic production databases. Statistics are stored in shared memory, ensuring fast access without disk I/O overhead.

Quality Assurance and Testing

pg_stat_insights includes a comprehensive TAP test suite with 150 test cases across 16 test files, providing 100% code coverage of all 52 metrics. The test suite verifies compatibility with PostgreSQL 16, 17, and 18, ensuring the extension works correctly across all supported versions. The custom StatsInsightManager.pm testing framework provides thorough validation of all functionality, including edge cases and error conditions.

The extension is written in production-grade C code with zero compilation warnings, following PostgreSQL coding standards and best practices. All code is reviewed for security, performance, and correctness before release. The comprehensive test suite ensures that new features and bug fixes don't introduce regressions, maintaining high quality across releases.

Comparison with Alternatives

When compared to pg_stat_statements, pg_stat_insights provides significant advantages including 8 additional metrics, 40 more pre-built views, 6 additional configuration options, automatic response time categorization, time-series tracking capabilities, enhanced cache analysis with calculated ratios, comprehensive replication monitoring, extensive index monitoring, and 30+ pages of detailed documentation. The extension maintains compatibility with pg_stat_statements queries, making migration straightforward.

Compared to pg_stat_monitor, pg_stat_insights offers a more focused set of 52 metrics optimized for practical use cases, 37 additional specialized views, advanced response time histograms, superior time-series analysis capabilities, comprehensive replication monitoring with 17 dedicated views, complete index monitoring with 11 specialized views, and a comprehensive test suite with 150 TAP tests ensuring production quality.

Production Deployment Best Practices

Successful production deployment begins with setting appropriate limits for the max_queries parameter based on workload characteristics. Small databases with less than 10 gigabytes typically need 1,000 to 5,000 tracked queries, while large databases exceeding one terabyte may require 20,000 to 50,000 tracked queries. Setting the limit too low causes important queries to be evicted from tracking, while setting it too high wastes shared memory.

-- Check how many queries are currently tracked
SELECT 
    COUNT(*) as tracked_queries,
    (SELECT setting::int FROM pg_settings WHERE name = 'pg_stat_insights.max_queries') as max_queries,
    ROUND((COUNT(*)::numeric / (SELECT setting::int FROM pg_settings WHERE name = 'pg_stat_insights.max_queries') * 100), 1) as utilization_pct
FROM pg_stat_insights;

 tracked_queries | max_queries | utilization_pct
-----------------+-------------+-----------------
            3456 |        5000 |            69.1
(1 row)

The utilization is at 69.1%, which is healthy. If utilization exceeds 90%, consider increasing max_queries. If utilization is below 20%, you may be able to reduce max_queries to save memory.

Regular statistics reset during maintenance windows helps prevent stale data from accumulating. The pg_stat_insights_reset() function clears all statistics, while the overloaded version allows resetting specific queries by user, database, and query ID.

-- Check statistics age before reset
SELECT 
    min(stats_since) as oldest_stat,
    max(stats_since) as newest_stat,
    count(*) as total_queries,
    NOW() - min(stats_since) as stats_age
FROM pg_stat_insights;

 oldest_stat           | newest_stat            | total_queries | stats_age
-----------------------+------------------------+---------------+------------------
 2024-12-01 10:00:00   | 2024-12-15 14:30:00    |          3456 | 14 days 04:30:00
(1 row)

If statistics are older than 30 days, consider resetting during a maintenance window:

-- Reset all statistics (do during maintenance windows)
SELECT pg_stat_insights_reset();

-- Verification: Statistics should be cleared
SELECT COUNT(*) as remaining_queries FROM pg_stat_insights;

 remaining_queries
------------------
                0
(1 row)

You can also reset statistics for a specific query:

-- Reset statistics for a specific query
SELECT pg_stat_insights_reset(
    (SELECT userid FROM pg_stat_insights WHERE queryid = 1234567890 LIMIT 1),
    (SELECT dbid FROM pg_stat_insights WHERE queryid = 1234567890 LIMIT 1),
    1234567890
);

Monitoring statistics age through the stats_since and minmax_stats_since columns helps ensure data freshness and validity.

Exporting statistics to CSV for external analysis enables integration with business intelligence tools, custom dashboards, and long-term trend analysis. The COPY command makes it easy to export slow queries, cache performance data, or any other statistics for further analysis outside the database.

-- Export slow queries to CSV
COPY (
    SELECT 
        query,
        calls,
        total_exec_time,
        mean_exec_time,
        rows,
        shared_blks_read,
        shared_blks_hit
  FROM pg_stat_insights_top_by_time 
  LIMIT 100
) TO '/tmp/slow_queries.csv' WITH CSV HEADER;

Verification: Check that the file was created:

wc -l /tmp/slow_queries.csv
head -5 /tmp/slow_queries.csv

101 /tmp/slow_queries.csv
query,calls,total_exec_time,mean_exec_time,rows,shared_blks_read,shared_blks_hit
"SELECT * FROM orders WHERE status",1247,12456.78,9.99,15234,1234,5678
"UPDATE inventory SET quantity =",892,8934.12,10.02,892,567,2345
"SELECT COUNT(*) FROM events WHERE",2341,7823.45,3.34,89234,890,1234
"INSERT INTO logs (timestamp, msg)",15678,6712.34,0.43,15678,234,5678

The CSV file contains 101 lines (100 data rows plus 1 header row), ready for analysis in external tools.

Getting Started: Complete Setup Example

Getting started with pg_stat_insights is straightforward. This section provides a complete step-by-step example that demonstrates the entire setup process from installation to running your first performance analysis queries.

Step 1: Install the Extension

After cloning the repository and building the extension, add it to shared_preload_libraries in postgresql.conf and restart PostgreSQL. Creating the extension in each database that requires monitoring is a simple CREATE EXTENSION command.

-- Create the extension
CREATE EXTENSION pg_stat_insights;

-- Verification: Confirm extension is installed
SELECT 
    extname,
    extversion
FROM pg_extension
WHERE extname = 'pg_stat_insights';

 extname          | extversion
------------------+-----------
 pg_stat_insights | 3.0
(1 row)

Step 2: Run Some Queries to Generate Statistics

After installation, run some queries in your database to generate statistics. The extension automatically begins tracking all queries.

-- Run some sample queries
SELECT COUNT(*) FROM orders;
SELECT * FROM users WHERE email = 'test@example.com';
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

Step 3: View Your First Performance Insights

Once queries have been executed, you can immediately begin analyzing performance. The pg_stat_insights_top_by_time view provides an instant view of the slowest queries.

-- View slowest queries
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_insights_top_by_time 
LIMIT 10;

 query_preview                              | calls | total_ms | avg_ms | rows
--------------------------------------------+-------+----------+--------+------
 SELECT COUNT(*) FROM orders                 |     1 |   123.45 | 123.45 |     1
 SELECT * FROM users WHERE email =         |     1 |    45.67 |  45.67 |     1
 UPDATE products SET price = price * 1.1  |     1 |    89.12 |  89.12 |   234
(3 rows)

The histogram summary view shows overall performance distribution, giving you a high-level understanding of database health.

-- View performance distribution
SELECT 
    bucket_label,
    query_count,
    ROUND((query_count::numeric / SUM(query_count) OVER () * 100), 1) AS pct_queries,
    ROUND(total_time::numeric, 2) AS total_ms,
    ROUND((total_time / SUM(total_time) OVER () * 100), 1) AS pct_time
FROM pg_stat_insights_histogram_summary
ORDER BY bucket_order;

 bucket_label | query_count | pct_queries | total_ms | pct_time
--------------+-------------+-------------+----------+----------
 <1ms         |           0 |         0.0 |     0.00 |      0.0
 1-10ms       |           0 |         0.0 |     0.00 |      0.0
 10-100ms     |           3 |       100.0 |   258.24 |    100.0
 100ms-1s     |           0 |         0.0 |     0.00 |      0.0
 1-10s        |           0 |         0.0 |     0.00 |      0.0
 >10s         |           0 |         0.0 |     0.00 |      0.0
(6 rows)

From there, administrators can dive deeper into specific areas of interest using the specialized views for cache analysis, I/O monitoring, replication health, or index optimization. The extension provides comprehensive visibility into all aspects of PostgreSQL performance, enabling data-driven optimization decisions.

Resources and Community

pg_stat_insights is an open-source project developed for the PostgreSQL community, available under the MIT license. The GitHub repository provides access to source code, issue tracking, release downloads, and community discussions. Comprehensive documentation is available online with 30+ pages covering installation, configuration, metrics reference, views documentation, usage examples, troubleshooting guides, and integration instructions for Prometheus and Grafana.

The project welcomes contributions from the community, with clear contributing guidelines and comprehensive documentation for developers. Regular releases include new features, performance improvements, and bug fixes, with detailed release notes explaining changes and migration paths.

Conclusion

pg_stat_insights provides a comprehensive solution for PostgreSQL performance monitoring, combining 52 detailed metrics with 42 specialized views that cover query performance, replication health, and index optimization. The extension serves as a drop-in replacement for pg_stat_statements while providing significantly enhanced capabilities including automatic response time categorization, comprehensive replication monitoring, extensive index analytics, and time-series trend analysis.

The extension is production-ready with comprehensive testing, minimal performance overhead, and extensive documentation. Whether administrators need to optimize query performance, troubleshoot slow queries, monitor replication health, analyze index efficiency, or maintain production databases, pg_stat_insights provides the visibility and insights needed to keep PostgreSQL running at peak performance.

The combination of detailed metrics, specialized views, automatic categorization, and comprehensive monitoring capabilities makes pg_stat_insights an essential tool for any PostgreSQL administrator serious about database performance optimization and monitoring.

pg_stat_insights is developed for the PostgreSQL community. Version 3.0. PostgreSQL 16, 17, and 18 supported.

Related Blog Posts

• pg_stat_insights 1.0.0 Release Announcement - Learn about the initial release of pg_stat_insights with 52 metrics and 11 pre-built views. Production-ready PostgreSQL performance monitoring extension.
• Index Monitoring with pg_stat_insights v3.0.0 - Comprehensive guide to monitoring PostgreSQL indexes using pg_stat_insights. Track index usage, detect bloat, identify missing indexes, and optimize performance with 11 specialized views.

Support

For questions, issues, or commercial support, contact admin@pgelephant.com