9.4 Server Memory Sizing & Core Performance Parameters

9.4 Server Memory Sizing & Core Performance Parameters

[!NOTE] The Dual Caching Philosophy: Unlike some monolithic database systems that bypass the operating system filesystem and manage all memory and raw disk blocks internally, PostgreSQL is designed to work in synergy with the underlying operating system. PostgreSQL maintains an internal shared memory cache (shared_buffers), but also relies extensively on the operating system kernel page cache to cache table and index data. Tuning PostgreSQL memory requires balancing internal allocations with OS page cache capacity.

Improper memory sizing can cause catastrophic operational failures. Over-allocating shared_buffers leads to memory double-buffering and starvation of operating system caches. Over-allocating work_mem can trigger the Linux Out-Of-Memory (OOM) Killer to abruptly terminate PostgreSQL processes. Sizing these parameters correctly is a fundamental core competency.

---

PostgreSQL Memory Architecture Overview

PostgreSQL memory is bifurcated into two distinct categories: Shared Memory Areas (allocated once at postmaster startup and accessible to all backend processes) and Local Backend Memory Areas (allocated dynamically by individual dedicated backend processes for private query execution).

+-------------------------------------------------------------------------+
|                       PostgreSQL Memory Architecture                    |
+-------------------------------------------------------------------------+
|  SHARED MEMORY (Allocated at postmaster startup)                        |
|  +-------------------------------------------------------------------+  |
|  | shared_buffers: Caches 8KB table and index blocks read from disk  |  |
|  | wal_buffers: Caches WAL records before flushing to pg_wal         |  |
|  +-------------------------------------------------------------------+  |
|                                                                         |
|  LOCAL BACKEND MEMORY (Allocated per backend / per operation)           |
|  +-------------------------------------------------------------------+  |
|  | Backend 1: work_mem (Sort Node) + work_mem (Hash Join Node)       |  |
|  | Backend 2: work_mem (Hash Aggregation)                            |  |
|  | Autovacuum / Maintenance Backend: maintenance_work_mem            |  |
|  +-------------------------------------------------------------------+  |
|                                                                         |
|  OPERATING SYSTEM KERNEL                                                |
|  +-------------------------------------------------------------------+  |
|  | OS Page Cache (Filesystem cache for PostgreSQL data and WAL files)|  |
|  +-------------------------------------------------------------------+  |
+-------------------------------------------------------------------------+

---

shared_buffers: Sizing Guidelines and Restart Rules

shared_buffers is PostgreSQL's primary dedicated cache for holding table and index 8KB data blocks read from storage.

Sizing Guidelines for Dedicated Database Servers

• General Rule: Sized to 25% of total physical RAM on dedicated servers (e.g., 16GB on a 64GB server; 32GB on a 128GB server).
• Minimum Recommendation: For development or tiny servers with less than 1GB RAM, 15%–25% of RAM.
• Why Not 80% of RAM?: In PostgreSQL, setting shared_buffers above 40% of system RAM rarely improves throughput and often degrades performance due to double-buffering: data pages reside in both PostgreSQL's shared_buffers and the operating system page cache. Furthermore, writes flushed from shared_buffers must pass through the OS page cache before hitting physical disk. Leaving 50%–75% of RAM available for the OS page cache ensures fast file reads, optimal kernel writeback buffering, and memory for sorting.

Configuration Context

shared_buffers has a configuration context of postmaster:

• It is allocated as a single, contiguous shared memory segment during cluster startup.
• A full PostgreSQL service restart is strictly required for changes to take effect. Modifying it via ALTER SYSTEM or editing postgresql.conf followed by SELECT pg_reload_conf(); will not change the active setting.

---

work_mem: Allocation Rules and Multiplication Hazards

work_mem specifies the maximum amount of private memory to be used by internal operations before spilling intermediate data to temporary disk files on storage.

Operations Governed by work_mem

• Sort Operations: Explicit ORDER BY clauses, DISTINCT, UNION, and Merge Joins.
• Hash Tables: In-memory hash tables for Hash Join and hash-based aggregations (GROUP BY).
• Bitmap Operations: In-memory creation of tuple bitmaps during Bitmap Index Scan.

The Memory Multiplication Hazard

[!WARNING] Per-Operation, NOT Per-Connection! A common misconception is that work_mem is allocated once per connected client. In reality, work_mem is allocated per individual sort or hash operation within each query!

If a single query contains three Hash Join nodes and two explicit ORDER BY sort operations, that single backend query can allocate up to:

$\text{Memory Consumed} = 5 \times \text{work\_mem}$

If work_mem is set to 64MB, a single query can consume $5 \times 64\text{MB} = 320\text{MB}$. If 100 concurrent clients run similar queries simultaneously, total memory demand would reach $100 \times 320\text{MB} = 32\text{GB}$! If physical RAM is exhausted, the Linux kernel Out-Of-Memory (OOM) killer activates and forcefully terminates PostgreSQL processes.

Safe Tuning Strategy

• Default Value: 4MB (conservative baseline).
• Global Setting: Keep work_mem modest globally in postgresql.conf (e.g., 16MB to 64MB).
• Dynamic Session-Level Tuning: For complex analytical reporting queries or batch ETL processes, elevate work_mem temporarily within that specific session or transaction block:

-- Temporarily elevate work_mem for a heavy analytical reporting session
SET work_mem = '512MB';

SELECT customer_id, count(*), sum(order_total)
FROM orders
GROUP BY customer_id
ORDER BY sum(order_total) DESC;

-- Reset back to default
RESET work_mem;

---

maintenance_work_mem: Accelerating Maintenance Commands

maintenance_work_mem specifies the maximum amount of memory used by maintenance operations:

• VACUUM and VACUUM FULL (holds dead tuple pointers during heap and index scanning).
• CREATE INDEX and REINDEX (holds keys during B-Tree building).
• ALTER TABLE ADD FOREIGN KEY (validating parent-child constraints).

Sizing Guidelines

• Default Value: 64MB.
• Production Recommendation: Sized to 1GB to 2GB on modern production database servers.
• Why Safe to Elevate?: Unlike work_mem, maintenance operations are run infrequently and typically by only one administrative or autovacuum process at a time. Sizing maintenance_work_mem to 1GB–2GB drastically accelerates index creation times and enables VACUUM to hold up to 170 million dead tuple pointers in a single pass before requiring intermediate index passes.

---

effective_cache_size: Optimizer Advisory Estimation

effective_cache_size is a purely advisory parameter that does NOT allocate any memory.

Purpose and Sizing

• Function: Informs the Cost-Based Optimizer (CBO) of the total aggregate memory available for caching table and index blocks across both PostgreSQL (shared_buffers) and the host operating system kernel page cache.
• Recommended Value: Sized to 50% to 75% of total system RAM on dedicated servers (e.g., 48GB on a 64GB machine).
• Impact on Query Plans: If effective_cache_size is set too low (e.g., the default 4GB on a 64GB server), the optimizer assumes that index pages will rarely reside in cache and will require expensive physical disk reads. This causes the optimizer to favor Sequential Scans over Index Scans even when indexes are readily available. Setting it accurately encourages the optimizer to select index paths for indexed queries.

---

Checkpoint Tuning and I/O Smoothing

A checkpoint is a periodic point in the transaction log (WAL) sequence at which all data files have been updated to reflect all information in the log. During a checkpoint, the checkpointer process flushes all dirty shared buffer pages to disk and writes a checkpoint record to WAL.

+-------------------------------------------------------------------------+
|                         Checkpoint I/O Smoothing                        |
+-------------------------------------------------------------------------+
|  checkpoint_timeout = 15min                                             |
|  checkpoint_completion_target = 0.9                                     |
|                                                                         |
|  [Checkpoint Begins] ────────────────────────────────────► [13.5 min]   |
|  │ <────────── Flushes dirty pages at smooth, regulated rate ─────────> │
|  0 min                                                                  |
|                                                                         |
|  Spreads physical I/O over 90% (13.5 minutes) of the interval.          |
|  Prevents disk write saturation and eliminates query latency spikes!    |
+-------------------------------------------------------------------------+

Core Checkpoint Parameters

1. checkpoint_timeout: Maximum time between automatic checkpoints (default 5min). On production OLTP databases, this should be increased to 15min to 30min to reduce total write volume.
2. checkpoint_completion_target: The fraction of the checkpoint interval during which dirty page flushes should be completed. Default is 0.9 in modern PostgreSQL. The checkpointer calculates the rate of page flushing so that writing completes over $0.90 \times \text{checkpoint_timeout}$ (e.g., $0.9 \times 15\text{ min} = 13.5\text{ minutes}$). This spreads disk I/O evenly across the interval, preventing catastrophic I/O write spikes.
3. max_wal_size: The maximum volume of WAL files that can accumulate before a checkpoint is forced ahead of schedule (default 1GB). On active databases, default 1GB triggers checkpoints every few seconds or minutes! Increasing max_wal_size to 16GB to 64GB ensures checkpoints are governed by time rather than WAL volume exhaustion.

---

Connection Management and max_connections

max_connections dictates the maximum number of concurrent client connections the database server will accept (default 100).

The Pitfall of High max_connections

Because PostgreSQL uses a process-per-connection architecture, each connected backend is a distinct operating system process with its own memory overhead, CPU context-switching costs, and lock competition in shared memory.

• Setting max_connections = 1000 or 2000 directly in postgresql.conf is a severe anti-pattern! Under high concurrency, hundreds of active processes fight for CPU cores and memory, leading to connection thrashing, CPU cache evictions, and severe performance collapse.
• The Industry Standard Solution: Keep max_connections modest (e.g., 100 to 300) and place an external connection pooler like PgBouncer in front of the database. PgBouncer multiplexes thousands of incoming application connections across a small, highly efficient pool of active PostgreSQL backend processes using transaction-level pooling.

---

Core Memory & Performance Configuration Summary

---

Exam Tips and Common Pitfalls

• Exam Trap: Sizing shared_buffers Beyond 40%: Sizing shared_buffers to 80% or 90% of system RAM is almost always wrong in PostgreSQL because of double-buffering with the OS kernel page cache. 25% of RAM is the standard guideline.
• Exam Trap: Configuration Context of shared_buffers: shared_buffers cannot be reloaded dynamically with pg_reload_conf(); it requires a full PostgreSQL service restart (postmaster context).
• Exam Trap: How work_mem Is Allocated: Remember that work_mem is not allocated per connection. It is allocated per sort or hash operation within each query plan. A single query with multiple sorts and joins can consume multiples of work_mem.
• Exam Trap: Does effective_cache_size Allocate RAM?: No. effective_cache_size is an advisory figure used strictly by the query optimizer to estimate the likelihood of finding index pages in memory. It allocates zero bytes of physical memory.