Statistics / Cumulative Query and Index Statistics

stats_fetch_consistency

• What it does: Controls the consistency level when accessing statistical data from various system views and functions.
• Why it matters: This parameter determines whether statistics queries return immediately available data (potentially slightly stale) or wait for consistent snapshots. Higher consistency ensures data coherence across multiple statistics queries but may incur slight overhead. Lower consistency provides faster access but might return non-atomic snapshots if statistics are being updated concurrently.
• Ideal value & Best Practice: Default cache provides a good balance between performance and consistency. Use snapshot for applications requiring perfectly consistent statistical views across multiple queries. Reserve none for high-performance requirements where slight staleness is acceptable.

track_activities

• What it does: Enables or disables collection of information about currently executing commands in each session.
• Why it matters: This is fundamental for real-time monitoring and troubleshooting. When enabled, pg_stat_activity shows current query execution, command start times, and session states. Disabling this prevents any real-time monitoring but eliminates the small overhead of tracking per-command execution data.
• Ideal value & Best Practice: Default on is essential for production monitoring. Only disable if you have extreme performance constraints and no need for real-time monitoring. The overhead is typically negligible compared to the operational benefits.

track_activity_query_size

• What it does: Sets the size reserved for storing query text in pg_stat_activity.query field, in bytes.
• Why it matters: This parameter controls how much of each query is captured for monitoring purposes. Queries longer than this limit will be truncated. Setting this too low may truncate important parts of complex queries, while setting too high consumes more shared memory.
• Ideal value & Best Practice: Default 1024 bytes (1KB) is often too small for complex queries. Set to 8192 (8KB) to capture most application queries fully. For environments with very long queries (e.g., ORM-generated queries), consider 16384 (16KB). Monitor actual query lengths in your environment.

track_counts

• What it does: Controls whether database collects statistics about table and index access patterns.
• Why it matters: This statistics collection is crucial for the query planner to make informed decisions about index usage and join strategies. Without these statistics, the planner cannot make cost-based decisions and may choose suboptimal execution plans. This also affects autovacuum's ability to determine when tables need maintenance.
• Ideal value & Best Practice: Default on is essential for normal operation. Never disable in production as it would severely impact query optimization. The overhead is minimal and the benefits for query planning are critical.

track_functions

• What it does: Controls whether to collect statistics on function call frequency and performance.
• Why it matters: Function-level statistics help identify frequently called or slow-performing functions, which is valuable for optimization efforts. This includes both built-in and user-defined functions. The statistics can reveal optimization opportunities in application logic or database functions.
• Ideal value & Best Practice: Default none. Set to pl for PL/pgSQL functions only, or all for all functions. Enable if you need to monitor function performance, but be aware it adds some overhead for function call tracking.

track_io_timing

• What it does: Enables or disables collection of I/O timing statistics for database operations.
• Why it matters: I/O timing data is crucial for identifying storage performance issues and understanding query behavior. When enabled, statistics views show time spent on reading and writing data, helping pinpoint I/O bottlenecks. However, this requires platform support for accurate timing and adds slight overhead.
• Ideal value & Best Practice: Default off due to potential overhead. Enable (on) for performance troubleshooting periods or in environments where I/O monitoring is critical. Test performance impact on your specific platform before enabling permanently.

track_wal_io_timing

• What it does: Controls whether to collect timing statistics specifically for WAL (Write-Ahead Log) I/O operations.
• Why it matters: WAL I/O timing helps identify issues with transaction log performance, which can be a critical bottleneck for write-intensive workloads. This is particularly valuable for troubleshooting commit latency and replication performance issues.
• Ideal value & Best Practice: Default off. Enable when specifically troubleshooting WAL performance issues. The overhead is generally low but should be tested in your environment. Combine with track_io_timing for comprehensive I/O monitoring.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial