Reporting and Logging / What to Log

application_name

• What it does: Sets the application name that identifies the client connection in logs, statistics, and system views.
• Why it matters: This parameter is crucial for monitoring and troubleshooting as it helps identify which application or service is generating specific database activity. Properly set application names make it easier to trace queries, diagnose performance issues, and audit database usage across different services and applications.
• Ideal value & Best Practice: Set from the client connection string using application_name=your_app_name. Use descriptive names like 'web_app', 'reporting_service', or 'batch_processor'. Establish naming conventions across your organization for consistency in monitoring and logging.

debug_pretty_print

• What it does: Controls whether debug output (parse trees, execution plans) is formatted with indentation for better readability.
• Why it matters: When debugging complex queries, properly formatted output significantly improves readability and makes it easier to understand query structure and execution plans. This is particularly valuable for developers and DBAs working with complex queries and trying to understand PostgreSQL's internal representations.
• Ideal value & Best Practice: Default on is recommended for all environments where debug output might be examined. The performance overhead is negligible, and the improved readability is worth the minimal cost.

debug_print_parse

• What it does: Logs the parse tree of each executed query to the server log.
• Why it matters: Provides deep insight into how PostgreSQL parses and interprets SQL queries. This is extremely valuable for debugging query parsing issues, understanding how PostgreSQL interprets complex SQL, and educational purposes. However, it generates substantial log volume.
• Ideal value & Best Practice: Default off. Enable temporarily for specific debugging sessions. Use with log_statement to control which statements are logged. Not recommended for production due to high log volume.

debug_print_plan

• What it does: Logs the execution plan of each query to the server log.
• Why it matters: Essential for query performance analysis and optimization. Seeing the actual execution plan chosen by the planner helps identify suboptimal plan choices, missing indexes, or statistics issues. This is more detailed than EXPLAIN output as it shows the actual plan used.
• Ideal value & Best Practice: Default off. Enable for specific troubleshooting sessions. Combine with log_statement to focus on problematic queries. Monitor log volume when enabled.

debug_print_rewritten

• What it does: Logs the rewritten parse tree after query rewrite rules have been applied.
• Why it matters: Shows how PostgreSQL transforms the original query through views, rules, and other rewrite mechanisms. This is valuable for debugging view behavior, rule systems, and understanding how PostgreSQL's rewrite phase works.
• Ideal value & Best Practice: Default off. Enable only when specifically debugging query rewrite issues. Generates very verbose output, so use selectively.

log_autovacuum_min_duration

• What it does: Sets the minimum execution time for autovacuum operations to be logged.
• Why it matters: Helps monitor autovacuum activity and identify tables that require vacuuming attention. Logging long-running autovacuum operations can reveal table bloat issues, performance problems, or autovacuum configuration issues.
• Ideal value & Best Practice: Default -1 (disabled). Set to 1000ms or 5000ms to log autovacuum operations taking longer than 1-5 seconds. Adjust based on your monitoring needs and log volume tolerance.

log_checkpoints

• What does it do: Logs information about each checkpoint operation.
• Why it matters: Checkpoint logging provides crucial insights into write patterns and I/O performance. It helps monitor checkpoint frequency, duration, and write volumes, which is essential for tuning checkpoint-related parameters and identifying I/O bottlenecks.
• Ideal value & Best Practice: Default off. Set to on for production environments to monitor checkpoint behavior. The log volume is manageable and the insights are valuable for performance tuning.

log_connections

• What it does: Logs each successful client connection to the database.
• Why it matters: Provides audit trail of database access and helps monitor connection patterns. This is essential for security auditing, connection pooling monitoring, and understanding client application behavior.
• Ideal value & Best Practice: Default off. Set to on for production environments for security auditing. Consider the log volume implications for very high-connection environments.

log_disconnections

• What it does: Logs each client disconnection, including session duration.
• Why it matters: Completes the connection audit trail and helps identify connection leak issues. Session duration information is valuable for monitoring application behavior and identifying abnormal connection patterns.
• Ideal value & Best Practice: Default off. Enable alongside log_connections for complete connection tracking. Particularly useful for detecting connection pool issues.

log_duration

• What it does: Logs the execution duration of each completed SQL statement.
• Why it matters: Provides detailed performance monitoring without the overhead of full statement logging. This is valuable for identifying slow queries and understanding database workload characteristics without generating excessive log volume.
• Ideal value & Best Practice: Default off. Set to on for performance monitoring. Combine with log_min_duration_statement to focus on slow queries rather than logging every statement.

log_error_verbosity

• What it does: Controls the amount of detail included in error messages.
• Why it matters: Affects how much information is available for debugging errors. More verbose error messages include additional context like error codes, positions, and internal details that are crucial for troubleshooting.
• Ideal value & Best Practice: Default default. Set to verbose for development and troubleshooting. Use terse for production if log volume is a concern, but default is usually appropriate.

log_hostname

• What it does: Controls whether to resolve and log hostnames instead of IP addresses in connection logs.
• Why it matters: Hostnames are more readable than IP addresses in logs, but DNS resolution adds overhead and potential points of failure. This trade-off affects both performance and log usefulness.
• Ideal value & Best Practice: Default off. Enable if you need hostname information and have reliable DNS. Test performance impact in your environment before enabling in production.

log_line_prefix

• What it does: Specifies the format of information prefixed to each log line.
• Why it matters: Controls what contextual information is available in log messages, which is crucial for log analysis, debugging, and monitoring. A well-chosen prefix makes log parsing and analysis much easier.
• Ideal value & Best Practice: Recommended: '%m [%p] %q%u@%d ' (timestamp, process ID, role, database). Adjust based on your logging needs and analysis tools. Include enough context to identify when and where each log message originated.

log_lock_waits

• What it does: Logs information about lock waits that exceed the deadlock_timeout.
• Why it matters: Essential for identifying locking issues and contention problems. Lock wait logging helps diagnose performance issues caused by locking and identifies transactions that are blocking others.
• Ideal value & Best Practice: Default off. Set to on in production environments to monitor locking behavior. Particularly valuable for applications with high concurrency.

log_parameter_max_length

• What it does: Sets the maximum length of parameter values logged with statements.
• Why it matters: Controls how much parameter data is included in log messages, balancing debugging usefulness with log volume and security. Very long parameter values (like large text or binary data) can explode log volume if logged completely.
• Ideal value & Best Practice: Default 0 (no parameters). Set to 64 or 128 to capture meaningful parameter information without excessive log growth. Use -1 with caution as it can log very large values.

log_parameter_max_length_on_error

• What it does: Sets the maximum length of parameter values logged when statements error out.
• Why it matters: Provides parameter context for debugging failed statements while controlling log volume. Error scenarios often benefit from more detailed parameter information for debugging.
• Ideal value & Best Practice: Default 0 (no parameters). Set to 256 or 512 to get useful parameter context for error debugging. Can be higher than log_parameter_max_length since errors are less frequent.

log_recovery_conflict_waits

• What it does: Logs information about recovery conflicts on standby servers.
• Why it matters: Essential for monitoring hot standby behavior and identifying queries that are causing or experiencing recovery conflicts. This helps tune standby configuration and identify problematic query patterns.
• Ideal value & Best Practice: Default off. Set to on for standby servers to monitor recovery conflict issues. Crucial for maintaining standby performance and stability.

log_replication_commands

• What it does: Logs each replication command received by the server.
• Why it does: Provides detailed visibility into replication activity, which is valuable for debugging replication issues and monitoring replication health. However, it can generate significant log volume in busy replication environments.
• Ideal value & Best Practice: Default off. Enable temporarily for replication debugging. Not recommended for continuous use in production due to log volume.

log_statement

• What it does: Controls which types of SQL statements are logged.
• Why it matters: One of the most important logging parameters, it balances between monitoring needs and log volume. Different settings provide different levels of visibility into database activity.
• Ideal value & Best Practice: Default none. Use ddl for most production environments to track schema changes. Use mod to also track data modifications. Use all only for specific debugging due to high volume.

log_temp_files

• What it does: Logs the creation of temporary files larger than the specified size.
• Why it matters: Helps identify queries that are using disk-based sorting or aggregation, which often indicates insufficient work_mem. This is valuable for performance tuning and identifying queries that need optimization.
• Ideal value & Best Practice: Default -1 (disabled). Set to 1024 (1MB) to log temporary file usage. Monitor and adjust work_mem for queries that frequently use temporary files.

log_timezone

• What it does: Sets the time zone used for timestamps in log messages.
• Why it matters: Ensures consistent timestamp formatting across logs, which is crucial for log analysis, correlation, and debugging. Consistent timezone settings make it easier to analyze events across distributed systems.
• Ideal value & Best Practice: Set to your local timezone or UTC for consistency. UTC is recommended for distributed systems to avoid timezone confusion. Example: 'UTC' or 'Europe/Istanbul'.

---

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