Developer Options

allow_in_place_tablespaces

• What it does: Allows creating tablespaces directly inside the pg_tblspc directory, bypassing normal symlink requirements.
• Why it matters: This option simplifies testing and development by eliminating the need for symbolic links when working with tablespaces. However, it violates standard PostgreSQL tablespace conventions and can lead to confusion in production environments where proper directory structure is essential for maintenance and backups.
• Ideal value & Best Practice: Default off. Enable only in development/test environments for convenience. Never use in production as it violates standard operating procedures and may cause issues with backup tools and maintenance scripts.

allow_system_table_mods

• What it does: Permits modifications to the structure of system catalogs and tables.
• Why it matters: This is an extremely dangerous option that can completely corrupt your database. Modifying system tables directly can break internal assumptions, cause crashes, and make the database unusable. It should only be used by core developers testing specific features.
• Ideal value & Best Practice: Default off. Never enable this in any environment except for PostgreSQL core development. Even in testing, use with extreme caution and only on disposable databases.

backtrace_functions

• What it does: Generates detailed backtraces for errors occurring in specified functions, aiding in debugging.
• Why it matters: Provides crucial debugging information for developers working on complex functions by showing the exact call stack when errors occur. This can significantly reduce debugging time for intricate PL/pgSQL functions or C extensions.
• Ideal value & Best Practice: Default empty. Specify function names when debugging specific issues. Remove after debugging to avoid performance overhead. Useful in development but avoid in production due to potential performance impact.

debug_discard_caches

• What it does: Aggressively flushes system caches to simulate cache-less behavior for testing.
• Why it matters: Helps developers test how their applications perform under worst-case scenario cache conditions. This is valuable for identifying performance issues that might only appear when caches are cold or under memory pressure.
• Ideal value & Best Practice: Default off. Enable temporarily during performance testing to validate cache efficiency. Never use in production as it will severely degrade performance.

debug_io_direct

• What it does: Bypasses the operating system's buffer cache, forcing all I/O to use direct access.
• Why it matters: Allows testing of I/O performance without filesystem caching effects. This helps identify true disk performance characteristics and test how the database behaves under pure direct I/O conditions.
• Ideal value & Best Practice: Default off. Use only for specific I/O performance testing. Not recommended for production as it eliminates beneficial OS caching.

debug_logical_replication_streaming

• What it does: Forces specific streaming or serialization behaviors in logical replication for testing purposes.
• Why it matters: Essential for developers working on logical replication features, allowing them to test edge cases and verify replication behavior under different streaming conditions.
• Ideal value & Best Practice: Default off. Use only when developing or testing logical replication features. Can significantly alter replication behavior and should not be used in production.

debug_parallel_query

• What it does: Forces the query planner to use parallel query nodes even when it normally wouldn't.
• Why it matters: Helps test the parallel query infrastructure by forcing parallel execution paths. This is valuable for identifying issues with parallel query execution and testing performance characteristics.
• Ideal value & Best Practice: Default off. Enable temporarily during parallel query development or testing. Can cause suboptimal plan choices, so avoid in production.

ignore_checksum_failure

• What it does: Allows the database to continue processing after detecting checksum failures instead of aborting.
• Why it matters: Extremely dangerous option that can lead to data corruption. Only useful for data recovery specialists attempting to salvage data from corrupted databases where some data is better than none.
• Ideal value & Best Practice: Default off. Use only as a last resort for data recovery from damaged databases. Always immediately backup any recovered data and rebuild the database from scratch.

ignore_invalid_pages

• What it does: Allows recovery to continue after encountering invalid page references in WAL records.
• Why it matters: Similar to ignore_checksum_failure, this is a recovery-only option for damaged databases. It can help complete recovery of a corrupted cluster but may lead to further corruption or data loss.
• Ideal value & Best Practice: Default off. Use only during recovery from severely damaged databases under expert guidance. Always follow with a full pg_dump and database rebuild.

ignore_system_indexes

• What it does: Disables reading from system indexes while still allowing updates.
• Why it matters: Primarily used for testing query performance without system index usage. Can help identify performance issues but will significantly slow down system catalog access.
• Ideal value & Best Practice: Default off. Use temporarily for testing purposes. The performance degradation makes this unsuitable for production use.

jit_debugging_support

• What it does: Registers JIT-compiled functions with debuggers for low-level debugging.
• Why it matters: Essential for developers working on PostgreSQL's JIT compilation infrastructure. Allows detailed inspection of generated machine code and debugging of JIT-related issues.
• Ideal value & Best Practice: Default off. Enable only when actively debugging JIT compilation issues. Adds significant overhead and should not be used in production.

jit_dump_bitcode

• What it does: Writes out LLVM bitcode to facilitate JIT debugging and analysis.
• Why it matters: Provides developers with detailed information about the JIT compilation process, helping to identify optimization issues and compilation errors.
• Ideal value & Best Practice: Default off. Use only during JIT development or debugging. The bitcode files can be large and numerous, consuming significant disk space.

jit_expressions

• What it does: Controls whether expressions are eligible for JIT compilation.
• Why it matters: Allows fine-grained control over JIT compilation for testing specific components. Useful for isolating JIT performance characteristics of expression evaluation.
• Ideal value & Best Practice: Default on (when JIT is enabled). Generally should be left enabled for performance benefits unless specifically testing non-JIT behavior.

jit_profiling_support

• What it does: Registers JIT-compiled functions with performance profilers like perf.
• Why it matters: Enables detailed performance analysis of JIT-compiled code, helping developers optimize the JIT compiler and identify performance bottlenecks.
• Ideal value & Best Practice: Default off. Enable only during performance analysis of JIT code. Adds overhead and should not be used in production.

jit_tuple_deforming

• What it does: Controls JIT compilation of tuple deforming operations.
• Why it matters: Tuple deforming is a performance-critical operation, and JIT compilation can significantly speed it up. This option allows testing the impact of JIT on this specific operation.
• Ideal value & Best Practice: Default on (when JIT is enabled). Generally beneficial for performance and should be left enabled unless testing specific aspects of tuple processing.

post_auth_delay

• What it does: Introduces a delay after authentication but before allowing the connection to proceed.
• Why it matters: Provides a window for developers to attach debuggers to newly established connections. Extremely useful for debugging connection startup issues and authentication problems.
• Ideal value & Best Practice: Default 0. Set to a few seconds (2-10) when debugging connection issues. Always disable after debugging to avoid connection timeouts.

pre_auth_delay

• What it does: Introduces a delay before starting authentication processing.
• Why it matters: Similar to post_auth_delay, this allows time for debuggers to attach early in the connection process. Useful for debugging very early connection establishment issues.
• Ideal value & Best Practice: Default 0. Use temporarily during connection debugging. Can cause client connection timeouts if left enabled.

remove_temp_files_after_crash

• What it does: Controls whether temporary files are cleaned up after a backend crash.
• Why it matters: Helps manage disk space after crashes but may remove files needed for debugging crash causes. The trade-off is between disk space management and debugging capability.
• Ideal value & Best Practice: Default on for most environments. Consider disabling temporarily when debugging persistent crash issues to preserve diagnostic information.

send_abort_for_crash

• What it does: Changes the signal sent to child processes after a backend crash from SIGQUIT to SIGABRT.
• Why it matters: SIGABRT typically generates more detailed core dumps, which can be valuable for debugging crash causes. However, it may change crash behavior in ways that affect recovery.
• Ideal value & Best Practice: Default off. Enable when debugging crashes to get better diagnostic information. Return to default after debugging.

send_abort_for_kill

• What it does: Changes the signal sent to stuck child processes from SIGKILL to SIGABRT.
• Why it matters: SIGABRT allows processes to perform cleanup and generate core dumps, while SIGKILL terminates immediately without cleanup. This can help debugging but may delay termination of truly stuck processes.
• Ideal value & Best Practice: Default off. Use when debugging process hanging issues. SIGKILL is more reliable for terminating stuck processes in production.

trace_connection_negotiation

• What it does: Enables detailed logging of the pre-authentication connection handshake process.
• Why it matters: Provides invaluable debugging information for connection establishment issues, SSL negotiation problems, and authentication protocol errors.
• Ideal value & Best Practice: Default off. Enable temporarily when debugging connection issues. Generates significant log output and should not be left enabled.

trace_notify

• What it does: Generates detailed debugging output for LISTEN and NOTIFY operations.
• Why it matters: Helps debug asynchronous notification systems and identify issues with inter-process communication via NOTIFY.
• Ideal value & Best Practice: Default off. Enable when debugging NOTIFY/LISTEN functionality. Can generate substantial log output during heavy use.

trace_sort

• What it does: Emits detailed information about resource usage in sorting operations.
• Why it matters: Provides insights into sort performance characteristics, memory usage, and algorithm selection. Valuable for debugging sorting-related performance issues.
• Ideal value & Best Practice: Default off. Enable temporarily when analyzing sort performance. The detailed output can be overwhelming during normal operation.

wal_consistency_checking

• What it does: Enables additional consistency checks for specified WAL resource managers during replay.
• Why it matters: Extremely valuable for developers working on WAL system or storage layers. Helps identify subtle consistency issues that might not cause immediate failures but could lead to corruption.
• Ideal value & Best Practice: Default empty. Enable for specific resource managers during development and testing. Adds significant overhead and should not be used in production.

zero_damaged_pages

• What it does: Allows processing to continue after detecting damaged page headers by zeroing out the damaged page.
• Why it matters: This is a last-resort recovery option for databases with corrupted pages. It can allow a damaged database to continue running but will destroy the data on affected pages.
• Ideal value & Best Practice: Default off. Use only for emergency recovery when the alternative is complete database loss. Always follow with immediate backup and database rebuild.

---

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