Preset Options

block_size

• What it does: Shows the size of a disk block used by PostgreSQL for storage management.
• Why it matters: The block size is a fundamental storage parameter determined during database cluster initialization. It affects I/O performance, storage efficiency, and memory usage. Most operations (reading, writing, caching) work in units of this block size, making it a critical factor in database performance characteristics.
• Ideal value & Best Practice: Default 8192 bytes (8KB) is standard and recommended for most workloads. This value is fixed at cluster initialization and cannot be changed without creating a new cluster. Choose based on your typical workload and storage system characteristics.

data_checksums

• What it does: Indicates whether data page checksums are enabled for the entire database cluster.
• Why it matters: Checksums provide critical protection against data corruption by detecting storage-level errors. When enabled, PostgreSQL calculates and verifies checksums for every data page, ensuring data integrity at the storage level. This is especially important for enterprise environments where data corruption must be detected early.
• Ideal value & Best Practice: Set to on for production environments requiring high data integrity. This setting is determined during cluster initialization with initdb --data-checksums and cannot be changed later. The small performance overhead is justified by the data protection benefits.

data_directory_mode

• What it does: Shows the filesystem permissions mode of the PostgreSQL data directory.
• Why it matters: The data directory mode controls filesystem access to critical database files. Proper permissions are essential for security, preventing unauthorized access to database files while allowing the PostgreSQL processes necessary access. This setting helps ensure that sensitive data remains protected at the filesystem level.
• Ideal value & Best Practice: Typically 0700 (read, write, execute for owner only). This restricts access to the PostgreSQL user only. Ensure this permission is maintained and never widened for convenience, as it could expose sensitive database files.

debug_assertions

• What it does: Indicates whether the PostgreSQL server was compiled with assertion checks enabled.
• Why it matters: Assertions are debugging aids that check for impossible conditions within the database code. A server with assertions enabled may catch bugs earlier but typically runs slower due to the additional checking. This is primarily useful for development and testing builds rather than production environments.
• Ideal value & Best Practice: Typically off in production builds for performance reasons. Development builds may have this enabled for better debugging capability. This is determined at compile time and cannot be changed without recompiling PostgreSQL.

huge_pages_status

• What it does: Indicates the current status of huge pages usage by the PostgreSQL shared memory.
• Why it matters: Huge pages can improve performance for large-memory systems by reducing Translation Lookaside Buffer (TLB) misses and memory management overhead. The status shows whether the server is successfully using huge pages, which can significantly benefit memory-intensive workloads.
• Ideal value & Best Practice: on when huge pages are properly configured and available. Requires appropriate OS configuration and sufficient huge pages allocated. Monitor performance benefits to determine if the configuration complexity is justified for your workload.

in_hot_standby

• What it does: Indicates whether the server is currently running in hot standby mode (read-only replica).
• Why it matters: This parameter helps identify the current role of the database instance, which is crucial for connection routing, application behavior, and maintenance operations. Applications may need to behave differently when connected to a primary server versus a standby replica.
• Ideal value & Best Practice: Automatically set by PostgreSQL based on replication role. Applications should check this parameter to determine if they're connected to a writable primary or read-only replica.

integer_datetimes

• What it does: Shows whether the server uses integer-based datetime storage (as opposed to floating-point).
• Why it matters: This affects the precision and storage format of temporal data. Integer-based datetimes provide more consistent precision and better performance for timestamp operations. This setting is determined at compile time and affects all datetime operations.
• Ideal value & Best Practice: Default on in modern PostgreSQL versions. This provides better precision and performance than the older floating-point implementation. This setting is fixed at compile time.

max_function_args

• What it does: Shows the maximum number of arguments that functions can accept.
• Why it matters: This limit affects database design and application development, particularly for complex functions with many parameters. Understanding this limit helps prevent "too many arguments" errors during function creation.
• Ideal value & Best Practice: Typically 100 arguments, which is sufficient for virtually all use cases. If approaching this limit, consider redesigning functions to use composite types or other parameter organization strategies.

max_identifier_length

• What it does: Shows the maximum allowed length for database object names (tables, columns, etc.).
• Why it matters: This limit affects database schema design and naming conventions. Longer names can be more descriptive but may cause issues with some tools or applications. Understanding this limit helps prevent identifier truncation or creation errors.
• Ideal value & Best Practice: Default 63 characters (64 including null terminator). Plan naming conventions within this limit, and be aware that some replication or migration tools may have lower effective limits.

max_index_keys

• What it does: Shows the maximum number of columns that can be included in a single index.
• Why it matters: This limit affects index design and query optimization strategies. Understanding this constraint is important when designing composite indexes for complex query patterns.
• Ideal value & Best Practice: Typically 32 columns, which is more than sufficient for most practical indexing scenarios. Rarely need to approach this limit in real-world applications.

segment_size

• What it does: Shows the number of pages per disk file (segment) used for large table storage.
• Why it matters: PostgreSQL breaks large tables into multiple files (segments) to overcome filesystem size limits. This parameter determines how large each segment file can grow before creating a new one, affecting storage management and maximum table size.
• Ideal value & Best Practice: Default 1GB per segment (131072 pages of 8KB each). This provides a good balance between file count and individual file size for most filesystems.

server_encoding

• What it does: Shows the default character set encoding for the database server.
• Why it matters: The server encoding determines how text data is stored and affects sorting, comparison, and storage efficiency. Consistent encoding settings are crucial for proper text handling and avoiding encoding-related issues.
• Ideal value & Best Practice: UTF8 is the recommended encoding for all modern applications, supporting all Unicode characters. This is set during database creation and should be consistent across all databases in a cluster.

server_version

• What it does: Shows the PostgreSQL server version as a human-readable string.
• Why it matters: This is essential for compatibility checking, troubleshooting, and determining available features. Applications and tools often need to check the server version to enable version-specific functionality or workarounds.
• Ideal value & Best Practice: Automatically set to the actual PostgreSQL version. Applications should parse this string to determine server capabilities and compatibility requirements.

server_version_num

• What it does: Shows the PostgreSQL server version as an integer for easy comparison.
• Why it matters: Provides a machine-readable format for version checking, making it easier for applications to programmatically determine if the server meets minimum version requirements or has specific features.
• Ideal value & Best Practice: Automatically set based on the actual version. Use this for programmatic version checks rather than parsing the string version.

shared_memory_size

• What it does: Shows the size of the server's main shared memory area (rounded to nearest MB).
• Why it matters: The shared memory size is crucial for understanding memory allocation and performance characteristics. It includes the shared buffers, lock space, and other shared data structures that affect overall database performance.
• Ideal value & Best Practice: Automatically calculated based on configuration. Monitor this value to ensure adequate shared memory allocation for your workload and connection count.

shared_memory_size_in_huge_pages

• What it does: Shows the number of huge pages needed for the main shared memory area.
• Why it matters: Helps in configuring huge pages by indicating how many are required for the current shared memory configuration. A value of -1 indicates that the calculation couldn't be performed.
• Ideal value & Best Practice: Use this value to properly configure huge pages in the operating system. Ensure sufficient huge pages are allocated to avoid falling back to regular pages.

ssl_library

• What it does: Shows the name and version of the SSL library used by PostgreSQL.
• Why it matters: Important for security auditing, compatibility checking, and troubleshooting SSL-related issues. Different SSL libraries may have different features, performance characteristics, and security considerations.
• Ideal value & Best Practice: Typically shows OpenSSL version information. Keep the SSL library updated to address security vulnerabilities and ensure compatibility with client applications.

wal_block_size

• What it does: Shows the block size used in the write-ahead log (WAL).
• Why it matters: The WAL block size affects WAL performance and storage efficiency. This is typically the same as the main block size but is shown separately as it's conceptually distinct and could potentially differ in future versions.
• Ideal value & Best Practice: Typically 8192 bytes (8KB), matching the main block size. This provides consistent I/O characteristics across all database operations.

wal_segment_size

• What it does: Shows the size of write-ahead log segments.
• Why it matters: WAL segments are the unit of WAL file management and replication. The segment size affects WAL archiving, replication throughput, and backup strategies. Larger segments can reduce file handling overhead but may increase WAL retention requirements.
• Ideal value & Best Practice: Default 16MB in recent versions (previously 1MB). This size provides a good balance between management overhead and granularity for replication and archiving operations.

---

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