PostgreSQL Tutorial: Tuning random_page_cost setting

July 10, 2026

Summary: in this tutorial, you will learn how to tune random_page_cost setting in PostgreSQL.

Table of Contents

Core Issues & Background

In PostgreSQL, the Cost-based Optimizer selects the optimal execution path by calculating the estimated resource consumption of different potential execution plans. random_page_cost is one of the most critical configuration parameters affecting query planning.

Background: PostgreSQL’s cost model was introduced around 2000 (about 25 years ago), when the primary storage medium was spinning Hard Disk Drives (HDDs).

Default Values:

seq_page_cost (cost of fetching a page sequentially): 1.0 (the base benchmark).
random_page_cost (cost of fetching a page randomly): 4.0.
CPU operations (e.g., reading a tuple from memory): 0.01 (implying I/O costs heavily dominate query costs).

Core Question: Now that modern hardware is dominated by flash-based storage (SSDs/NVMe) and operating system caching has dramatically improved, is the default value of 4.0 outdated? Is the common advice to lower it on SSDs truly correct?

Experimental Testing & Counter-Intuitive Findings

Using early benchmark methods, by comparing the actual time spent on ‘forced index scans (random I/O)’ versus ‘sequential scans (sequential I/O),’ yielding a highly surprising result:

Spinning Hard Drives (HDDs): The true random-to-sequential I/O cost ratio is as high as ~140.
Modern Solid State Drives (SSDs): The true random-to-sequential I/O cost ratio is roughly ~27 to 28.
Key Conclusion: Even on SSDs, the true physical random I/O cost is much higher than the default of 4.0. The default value of 4.0 has never been a direct, raw reflection of the underlying physical I/O cost itself.

Deep Dive: Why Can’t We Set the Parameter Higher (e.g., to 30)?

Given that the true physical I/O cost gap is 27-fold, what happens if we increase random_page_cost to 30?

Appearance: It might drastically optimize certain individual queries running in isolation.

Consequence: It will likely severely degrade the global performance of the database system as a whole.

Root Cause (Limitations of the Cost Model):

1. Inaccurate Caching Consideration: PostgreSQL’s cost model is inherently simplified. It has no concept of an “active set” (e.g., recent hot e-commerce orders) that currently resides in the OS Page Cache, nor does it factor memory capacity directly into the cost calculations.

2. Ignored Concurrency: The query planner estimates the cost as if the target query were the only one running on the database.

3. Hidden Cost of Cache Eviction (Key Comparison):

Sequential Scan: To retrieve just 1GB of target data (1%), it might read an entire 100GB table into memory. This causes major indirect damage by evicting 100GB of other truly needed data from the Page Cache.
Random Scan / Index Scan: Although a single random read has a higher physical overhead, it is highly localized. It precisely reads only the required 1GB of data, thereby preserving the valuable system cache.

Final Conclusions & Tuning Advice (Best Practices)

Should you lower it on SSDs? Yes. Because compared to old spinning drives, SSDs handle random I/O significantly better. If 4.0 was appropriate for HDDs (and historically worked well), a lower value like 2.0 is reasonable for SSDs.
Avoid over-relying on automated benchmark tools Do not set this parameter purely based on the “raw I/O ratio” generated by a simple benchmark script or command-line tool, as these tools fail to simulate complex caching effects.
Prioritize System-Level Monitoring Performance tuning is always a trade-off between improving local performance for a single query and optimizing the general performance of the database. Any parameter adjustment must be driven by holistic system monitoring. After tuning, observe not just whether the target query became faster, but also how other concurrent queries are doing, and perform iterative optimization.