Resource Usage / Memory

autovacuum_work_mem

• What it does: Sets the maximum memory that can be used by each autovacuum worker process for vacuum operations.
• Why it matters: This parameter directly affects autovacuum performance and efficiency. More memory allows autovacuum to process more dead tuples in memory before writing to disk, significantly speeding up vacuum operations. However, setting it too high can cause memory pressure when multiple autovacuum workers are active simultaneously.
• Ideal value & Best Practice: Default -1 (uses maintenance_work_mem). Set to 64MB to 1GB based on your system memory. Ensure total autovacuum memory (autovacuum_work_mem × autovacuum_max_workers) doesn't exceed available RAM.

commit_timestamp_buffers

• What it does: Sets the size of the dedicated buffer pool used for caching commit timestamp data.
• Why it matters: When track_commit_timestamp is enabled, this buffer caches commit timestamp information for efficient access. Proper sizing ensures good performance for features that use commit timestamps without excessive memory usage.
• Ideal value & Best Practice: Default 0 (automatically sized). Set explicitly only if you have specific performance requirements with commit timestamp usage. Typically best left at default.

dynamic_shared_memory_type

• What it does: Selects the implementation method for dynamic shared memory allocation.
• Why it matters: Different operating systems and platforms have varying capabilities for shared memory management. This parameter ensures PostgreSQL uses the most efficient method available for your specific environment.
• Ideal value & Best Practice: Default posix (most systems). On Windows, uses windows. Generally should be left at default unless you have specific platform requirements.

hash_mem_multiplier

• What it does: Sets a multiplier for the amount of memory hash operations can use relative to work_mem.
• Why it matters: Hash joins and hash-based operations often benefit from more memory than sort operations. This parameter allows hash operations to use additional memory beyond the standard work_mem limit.
• Ideal value & Best Practice: Default 2.0. Increase to 4.0 for data warehouse workloads with large hash joins. Decrease to 1.0 for OLTP workloads where memory is constrained.

huge_page_size

• What it does: Specifies the size of huge pages to request from the operating system.
• Why it matters: Huge pages can reduce TLB misses and improve memory management efficiency for large databases. This parameter must match the system's huge page configuration.
• Ideal value & Best Practice: Typically 2MB or 1GB depending on system configuration. Must match the OS huge page size. Requires proper OS configuration.

huge_pages

• What it does: Enables or disables the use of huge pages for the main shared memory segment.
• Why it matters: Huge pages can significantly improve performance for large databases by reducing translation lookaside buffer (TLB) misses. However, they require proper operating system configuration and may not be available on all systems.
• Ideal value & Best Practice: Default try (use if available). Set to on for large databases (>64GB shared_buffers) if huge pages are properly configured. Requires OS-level huge page configuration.

logical_decoding_work_mem

• What it does: Sets the maximum memory for logical decoding before spilling to disk.
• Why it matters: Controls memory usage for logical replication and decoding operations. Higher values improve logical decoding performance but increase memory usage. Lower values cause more disk spilling but reduce memory footprint.
• Ideal value & Best Practice: Default 64MB. Increase to 256MB or 512MB for busy logical replication setups. Monitor logical decoding performance and adjust accordingly.

maintenance_work_mem

• What it does: Sets the maximum memory for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE.
• Why it matters: This is crucial for maintenance performance. Larger values allow these operations to complete faster by doing more work in memory. However, this memory is allocated per operation and can be substantial when multiple maintenance operations run concurrently.
• Ideal value & Best Practice: Default 64MB. Set to 1GB to 2GB for large databases. Should be 10-25% of total system RAM, but consider concurrent operations.

max_prepared_transactions

• What it does: Sets the maximum number of simultaneously prepared transactions.
• Why it matters: Prepared transactions are used for two-phase commit and some connection poolers. Each prepared transaction consumes shared memory, so this setting affects both functionality and memory usage.
• Ideal value & Best Practice: Default 0 (disabled). Set to the maximum number of expected prepared transactions if using two-phase commit. Typically 100-500 for most applications.

max_stack_depth

• What it does: Sets the maximum stack depth for server processes.
• Why it matters: Prevents stack overflow in deep recursion scenarios. This is a safety parameter that should generally be left at system-appropriate defaults unless you have specific deep recursion requirements.
• Ideal value & Best Practice: Default 2MB. Generally should not be changed unless advised by support. Incorrect settings can cause server crashes.

min_dynamic_shared_memory

• What it does: Sets the amount of dynamic shared memory reserved at server startup.
• Why it matters: Prevents allocation failures for dynamic shared memory by reserving memory upfront. This ensures that operations requiring dynamic shared memory won't fail due to memory fragmentation.
• Ideal value & Best Practice: Default 0MB. Set only if you experience dynamic shared memory allocation failures. Typically not needed for most deployments.

multixact_member_buffers & multixact_offset_buffers

• What it does: These set the size of buffer pools for multi-transaction status information.
• Why it matters: Multi-transactions are used for row locking by multiple transactions. These buffers cache multi-transaction state information for efficient access.
• Ideal value & Best Practice: Default 0 (automatically sized). Adjust only if you have heavy multi-transaction usage and specific performance issues.

notify_buffers

• What it does: Sets the size of the buffer pool for NOTIFY/LISTEN messages.
• Why it matters: Controls the amount of shared memory allocated for asynchronous notification messages. Larger buffers allow more pending notifications but consume more shared memory.
• Ideal value & Best Practice: Default size based on max_connections. Increase if you use NOTIFY/LISTEN extensively and experience message loss.

serializable_buffers

• What it does: Sets the size of the buffer pool for serializable transaction isolation information.
• Why it matters: When using serializable isolation level, this buffer stores transaction conflict information. Larger sizes can improve serializable transaction performance.
• Ideal value & Best Practice: Default 0 (automatically sized). Increase only if using serializable isolation level and experiencing performance issues.

shared_buffers

• What it does: Sets the amount of memory allocated for PostgreSQL's shared buffer cache.
• Why it matters: This is the most important memory parameter. It determines how much data can be cached in memory, directly affecting read performance. The shared buffer cache is PostgreSQL's primary cache mechanism.
• Ideal value & Best Practice: Default 128MB. Set to 25-40% of total system RAM for dedicated database servers. For example, 16GB on a 64GB system. Don't exceed 40% to leave memory for other uses.

shared_memory_type

• What it does: Selects the shared memory implementation for the main shared memory region.
• Why it matters: Different shared memory implementations have different characteristics and platform compatibility. This ensures PostgreSQL uses the appropriate method for your operating system.
• Ideal value & Best Practice: Default mmap (most systems). Generally should be left at default unless you have specific platform requirements.

subtransaction_buffers & transaction_buffers

• What it does: These set the size of buffer pools for transaction status information.
• Why it matters: These buffers cache transaction state information for efficient access. They help manage transaction status tracking without excessive locking.
• Ideal value & Best Practice: Default 0 (automatically sized). Typically best left at default values unless you have specific transaction-heavy workloads.

temp_buffers

• What it does: Sets the maximum number of temporary buffers available to each session for temporary table access.
• Why it matters: Temporary buffers are used for access to temporary tables. Each session allocates its own temp_buffers, so this setting affects both performance and memory usage per connection.
• Ideal value & Best Practice: Default 8MB. Increase to 16-32MB if you use temporary tables extensively. Consider your max_connections when setting this value.

vacuum_buffer_usage_limit

• What it does: Sets the buffer pool size that VACUUM and ANALYZE can use.
• Why it matters: Controls how much of the shared buffer cache vacuum operations can use. This helps prevent vacuum from evicting too much useful data from the cache.
• Ideal value & Best Practice: Default 0 (no limit). Set to limit vacuum's impact on the buffer cache if needed. Typically 256MB to 1GB depending on shared_buffers size.

work_mem

• What it does: Sets the maximum memory for query operations like sorting and hashing before switching to disk-based operations.
• Why it matters: This is a crucial parameter for query performance. It determines how much memory individual operations can use. Higher values allow more operations to complete in memory, improving performance.
• Ideal value & Best Practice: Default 4MB. Calculate as (total RAM - shared_buffers) / (max_connections × 2). For OLTP: 4-16MB, for OLAP: 64-256MB. Monitor temp file usage to tune this parameter.

---

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