Customized Options / pg_stat_statements

pg_stat_statements.max

• What it does: Sets the maximum number of unique SQL statements that pg_stat_statements will track and store in shared memory.
• Why it matters: This parameter directly controls the scope and memory usage of query performance monitoring. Setting it too low may cause frequently executed queries to be evicted from the statistics, leading to incomplete performance data. Setting it too high can consume excessive shared memory, potentially impacting overall database performance. Proper sizing ensures you capture sufficient query history without wasting valuable memory resources.
• Ideal value & Best Practice: Default 5000 is reasonable for most environments. For busy systems with diverse query patterns, increase to 10000-20000. Monitor pg_stat_statements view regularly and adjust if you notice frequent query eviction. Calculate memory usage as approximately max * 200 bytes per tracked statement.

pg_stat_statements.save

• What it does: Controls whether pg_stat_statements statistics are preserved across server restarts or shutdowns.
• Why it matters: Preserving statistics across restarts maintains historical query performance data, enabling long-term trend analysis and comparison. When disabled, all query statistics are reset after each restart, making it difficult to identify performance regressions or understand query behavior over time. This is particularly valuable for capacity planning and detecting gradual performance degradation.
• Ideal value & Best Practice: Set to on for production environments to maintain historical performance data. Be aware that enabling this feature requires periodic writes to disk, which may have a minor I/O impact. Ensure adequate disk space for the statistics file, typically located in the data directory.

pg_stat_statements.track

• What it does: Determines which types of SQL statements are tracked by pg_stat_statements—all statements, only top-level statements, or none.
• Why it matters: This setting balances monitoring comprehensiveness with overhead management. Tracking all statements (including nested queries) provides complete visibility but increases overhead. Tracking only top-level statements reduces overhead but may miss important performance details from stored functions or complex queries. The right choice depends on your troubleshooting needs and performance requirements.
• Ideal value & Best Practice: Default top (top-level statements only) is recommended for most production environments. Use all for comprehensive debugging when investigating performance issues within functions. Avoid none unless you're experiencing significant performance overhead from tracking.

pg_stat_statements.track_planning

• What it does: Controls whether pg_stat_statements tracks and records query planning time in addition to execution time.
• Why it matters: Query planning time can be a significant contributor to overall query latency, especially for complex queries or those involving many joins. Enabling planning time tracking helps identify queries with expensive planning phases, which might benefit from better statistics, hinting, or different indexing strategies. This is crucial for comprehensive query performance analysis.
• Ideal value & Best Practice: Set to on for development and performance tuning environments. For production, evaluate the overhead—typically minimal—and enable if query planning performance is a concern. The additional data provides valuable insights for optimizing complex queries.

pg_stat_statements.track_utility

• What it does: Determines whether utility commands (e.g., CREATE, ALTER, DROP, VACUUM, COPY) are tracked by pg_stat_statements.
• Why it matters: Utility commands can significantly impact database performance and resource usage. Tracking them provides visibility into DDL operations, maintenance activities, and data loading processes. However, these commands tend to be unique (non-parameterized), which might quickly consume pg_stat_statements slots if tracked extensively.
• Ideal value & Best Practice: Default on is useful for monitoring overall database activity. Consider setting to off if you have frequent utility commands that are filling the pg_stat_statements memory and causing frequently executed SELECT/INSERT/UPDATE/DELETE queries to be evicted. For most environments, keeping it enabled provides valuable operational insights.

---

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