Study guide
9.4 Server Memory Sizing & Core Performance Parameters
PostgreSQL Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & PostgreSQL Foundation
1.1 EDB PostgreSQL Associate Certification Overview 1.2 The PostgreSQL Relational Model & Architecture Principles 1.3 Study Strategies, Lab Setup & Exam-Day Execution
2 Chapter 2: PostgreSQL Architecture & Installation
2.1 The PostgreSQL Server Process Model 2.2 Database Clusters & Cluster Initialization (initdb) 2.3 Filesystem Layout, Service Control & Startup/Shutdown 2.4 Server Configuration Parameters
3 Chapter 3: Demonstrating SQL Knowledge
3.1 Relational Data Types & Table Design 3.2 Integrity Constraints & Data Validation 3.3 Querying, Joining & Aggregating Data 3.4 DML Operations, Transaction Control & MVCC Mechanics
4 Chapter 4: PostgreSQL Administrative Tools
4.1 Interactive Command-Line Mastery with psql 4.2 GUI Administration with pgAdmin 4.3 Client & Server Command-Line Utilities 4.4 System Catalogs & Information Schema
5 Chapter 5: Core Database Administration & Security
5.1 Managing Databases, Schemas & Tablespaces 5.2 Roles, Privileges & Object Ownership 5.3 Client Authentication & pg_hba.conf Architecture
6 Chapter 6: Routine Maintenance & Data Operations
6.1 The PostgreSQL Vacuuming Engine & MVCC Space Reclamation 6.2 Analyzing Query Statistics & Reindexing 6.3 High-Performance Data Loading & Data Movement
7 Chapter 7: Backup and Recovery Strategies
7.1 Logical Backups with pg_dump & pg_dumpall 7.2 Restoring Logical Backups 7.3 Physical Base Backups with pg_basebackup 7.4 Write-Ahead Logging & Point-in-Time Recovery
8 Chapter 8: High Availability & Replication
8.1 Physical Streaming Replication Architecture 8.2 Synchronous vs. Asynchronous Replication 8.3 Replication Slots & Standby Management 8.4 Failover, Switchover & High-Availability Design
9 Chapter 9: Monitoring, Diagnostics & Performance Tuning
9.1 Cumulative Statistics & Activity Monitoring 9.2 Query Execution Plans & Index Optimization 9.3 Server Logging Configuration & Autovacuum Tuning 9.4 Server Memory Sizing & Core Performance Parameters
9.4 Server Memory Sizing & Core Performance Parameters
Key Takeaways Explain This Topic AI
shared_buffers serves as PostgreSQL's primary dedicated cache for table and index data blocks; on dedicated database servers, sizing it to 25% of total system RAM prevents memory wastage while working harmoniously with the OS page cache.
Modifying shared_buffers requires a complete PostgreSQL service restart because its contiguous shared memory segment is allocated strictly during postmaster process initialization.
work_mem defines the memory allocated for internal sorting, hash tables, and bitmap operations per operation per query backend, meaning a query with multiple joins and sort nodes can consume multiples of work_mem in a single session.
maintenance_work_mem provides memory for intensive administrative tasks like VACUUM, CREATE INDEX, and foreign key additions; increasing it from the 64MB default to 1GB–2GB substantially accelerates maintenance speed.
Checkpoint tuning flattens write I/O bursts by setting checkpoint_completion_target to 0.9, extending checkpoint_timeout to 15–30 minutes, and increasing max_wal_size to prevent frequent forced checkpoints.
Last updated: September 2026
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: Memory Consumed = 5 × work_mem \text{Memory Consumed} = 5 \times \text{work\_mem} Memory Consumed = 5 × work_mem
If work_mem is set to 64MB, a single query can consume 5 × 64 MB = 320 MB 5 \times 64\text{MB} = 320\text{MB} 5 × 64 MB = 320 MB . If 100 concurrent clients run similar queries simultaneously, total memory demand would reach 100 × 320 MB = 32 GB 100 \times 320\text{MB} = 32\text{GB} 100 × 320 MB = 32 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
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.
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 × checkpoint_timeout 0.90 \times \text{checkpoint\_timeout} 0.90 × checkpoint_timeout (e.g., 0.9 × 15 min = 13.5 minutes 0.9 \times 15\text{ min} = 13.5\text{ minutes} 0.9 × 15 min = 13.5 minutes ). This spreads disk I/O evenly across the interval, preventing catastrophic I/O write spikes.
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
Parameter Default Value Recommended Production Value Context Requires Restart?
- shared_buffers 128MB 25% of total RAM postmaster Yes (Restart)
- work_mem 4MB 16MB – 64MB (elevate per session) user No (Dynamic)
- maintenance_work_mem 64MB 1GB – 2GB user No (Dynamic)
- effective_cache_size 4GB 50% – 75% of total RAM user No (Dynamic)
- checkpoint_timeout 5min 15min – 30min sighup No (Reload)
- checkpoint_completion_target 0.9 0.9 sighup No (Reload)
- max_wal_size 1GB 16GB – 64GB sighup No (Reload)
- max_connections 100 100 – 300 (use PgBouncer for scale) postmaster Yes (Restart)
- 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.