Performance Implications of Using pg_stat_statements vs pg_stat_monitor for Query Analysis

pg_stat_statements provides low-overhead, aggregated query statistics ideal for continuous production monitoring, while pg_stat_monitor captures per-execution details with higher write overhead and unbounded storage growth, making it suitable for targeted diagnostic sessions.

Understanding the performance implications of using pg_stat_statements versus pg_stat_monitor is critical for PostgreSQL observability strategy. The MCP-PostgreSQL-Ops repository implements both extensions to surface query-level performance data, but they differ significantly in resource consumption, storage characteristics, and granularity. Choosing the appropriate extension prevents unnecessary latency spikes and disk space exhaustion in production environments.

How pg_stat_statements Tracks Query Performance

pg_stat_statements maintains aggregated counters per normalized query in a fixed-size, in-memory hash table. The extension updates metrics—such as call count, total execution time, and I/O statistics—only when a statement completes, minimizing executor interference.

The implementation in MCP-PostgreSQL-Ops generates version-aware SQL through get_pg_stat_statements_query() in src/mcp_postgresql_ops/version_compat.py, which selectively queries total_exec_time and mean_exec_time only when the server version supports these columns (PostgreSQL 13+)【src/mcp_postgresql_ops/version_compat.py#L503-L508】. This approach ensures compatibility across PostgreSQL 12–17 without manual adjustments.

Because the extension stores data exclusively in shared memory with a fixed entry limit controlled by pg_stat_statements.max, old entries are overwritten when the hash fills. This design results in negligible CPU overhead (typically less than 1%) and zero write-ahead log (WAL) traffic for statistics collection.

How pg_stat_monitor Captures Detailed Execution Data

pg_stat_monitor provides per-execution granularity by writing one row per query execution to a permanent table. Unlike the aggregated approach, this extension captures timestamps, client IP addresses, bucketed time windows, and execution plans alongside standard counters.

The underlying view in pg_stat_monitor contains discrete rows rather than rolled-up statistics, which is why the MCP-PostgreSQL-Ops codebase describes it as providing "more detailed monitoring data than pg_stat_statements"【src/mcp_postgresql_ops/version_compat.py#L551-L558】. The query generator in version_compat.py handles the different column sets across versions automatically.

Each statement execution triggers an INSERT operation into the pg_stat_monitor table, generating WAL traffic and potentially causing table bloat. Without proper configuration of pg_stat_monitor.purge_interval, storage usage grows unbounded, unlike the fixed-size hash of pg_stat_statements.

Performance Overhead and Storage Impact

The architectural differences create distinct performance profiles suitable for different operational contexts:

• CPU and Latency Impact: pg_stat_statements adds no observable query latency because it only updates shared-memory counters. pg_stat_monitor introduces a slight latency increase on every execution due to the disk write operation.

• Storage Requirements: pg_stat_statements uses a fixed memory allocation regardless of workload volume. pg_stat_monitor requires disk storage proportional to query volume and retention settings, necessitating periodic cleanup jobs to prevent excessive disk use.

• WAL Amplification: pg_stat_statements generates no WAL traffic for statistics collection. pg_stat_monitor creates write amplification through persistent table inserts, impacting high-QPS workloads significantly.

Implementation in MCP-PostgreSQL-Ops

The repository exposes both extensions through dedicated tools that handle version compatibility automatically. In src/mcp_postgresql_ops/mcp_main.py, the get_pg_stat_statements_top_queries and get_pg_stat_monitor_recent_queries functions wrap the underlying query logic with strict observability-only permissions—the docstrings explicitly prohibit statistics resets【src/mcp_postgresql_ops/mcp_main.py#L1549-L1557】【src/mcp_postgresql_ops/mcp_main.py#L1596-L1604】.

Practical usage examples demonstrate the different granularity levels:

# Retrieve top 10 aggregated queries by total execution time

from mcp_postgresql_ops import get_pg_stat_statements_top_queries

result = await get_pg_stat_statements_top_queries(limit=10)
print(result)  # Aggregated statistics across all executions

# Retrieve last 15 individual executions with client metadata

from mcp_postgresql_ops import get_pg_stat_monitor_recent_queries

result = await get_pg_stat_monitor_recent_queries(limit=15)
print(result)  # Per-row data including client_ip and bucket_start_time

Both calls rely on src/mcp_postgresql_ops/version_compat.py to generate appropriate SQL for the target PostgreSQL version, ensuring consistent behavior across upgrades.

Choosing Between Extensions for Production Workloads

Select pg_stat_statements for production-critical databases requiring continuous monitoring without performance degradation. Use it when you need quick "top queries" snapshots and cannot tolerate write amplification or storage growth.

Select pg_stat_monitor for development environments, diagnostic sessions, or forensic analysis requiring per-client correlation and time-windowed granularity. Always configure pg_stat_monitor.purge_interval to bound storage consumption when enabling this extension in production.

Summary

• pg_stat_statements maintains aggregated data in a fixed-size memory hash with <1% CPU overhead and zero storage impact, ideal for always-on production monitoring.

• pg_stat_monitor writes per-execution rows to disk, providing detailed temporal and client metadata at the cost of INSERT overhead and unbounded storage growth without cleanup policies.

• MCP-PostgreSQL-Ops implements version-aware query generators in version_compat.py that automatically handle column differences across PostgreSQL 12–17.

• The public tools get_pg_stat_statements_top_queries and get_pg_stat_monitor_recent_queries enforce read-only observability patterns with explicit prohibitions against statistics resets.

Frequently Asked Questions

Which extension has lower overhead on high-traffic PostgreSQL servers?

pg_stat_statements imposes significantly lower overhead because it updates only shared-memory counters without disk writes. It typically consumes less than 1% CPU and generates no WAL traffic. pg_stat_monitor incurs higher overhead due to per-execution INSERT operations and WAL generation, making it less suitable for very high-QPS production workloads unless carefully configured with aggressive purge intervals.

Can I use both pg_stat_statements and pg_stat_monitor simultaneously?

Yes, both extensions can coexist in the same PostgreSQL instance. The MCP-PostgreSQL-Ops codebase treats them as complementary tools—pg_stat_statements for low-overhead trend analysis and pg_stat_monitor for detailed forensic investigation. However, running both simultaneously increases total resource consumption, so monitor cumulative overhead during peak traffic periods.

How does storage usage differ between the two extensions?

pg_stat_statements uses a fixed amount of shared memory determined by pg_stat_statements.max, with no disk storage for historical data. pg_stat_monitor stores data in a permanent table that grows continuously unless you configure pg_stat_monitor.purge_interval to automatically remove old rows. Without cleanup, pg_stat_monitor can consume significant disk space and cause table bloat in high-volume environments.

What PostgreSQL versions does the MCP-PostgreSQL-Ops implementation support?

The implementation supports PostgreSQL 12 through 17 through version-aware SQL generation in src/mcp_postgresql_ops/version_compat.py. The get_pg_stat_statements_query() and get_pg_stat_monitor_query() functions detect server capabilities and adjust column selections—for example, using total_exec_time only when available in PostgreSQL 13+, ensuring consistent tool behavior across versions without manual configuration changes.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}