Tuning PostgreSQL for SSDs With random_page_cost and More

PostgreSQL's planner chooses between reading a table in order and jumping to rows through an index. To choose, it needs to know how much more a jump costs than a sequential read, and the default answer is four times. That number came from spinning disks, where a random read means a head movement and a sequential read does not. On a modern SSD the difference is small, and a server whose planner still believes the four-to-one ratio leaves speed on the table by preferring full scans over indexes. This article explains the cost model, the values to use, the related I/O setting and how to verify the change on your own queries. It belongs to the series that tuning PostgreSQL settings from real numbers and workloads opens.

The cost model in one paragraph

Every plan has a cost made of parts: reading pages (seq_page_cost for pages read in order, default 1.0; random_page_cost for pages read out of order, default 4.0), processing rows (cpu_tuple_cost 0.01, cpu_index_tuple_cost 0.005, cpu_operator_cost 0.0025) and so on. The planner picks the plan with the lowest total. For a query that touches a small fraction of a table, an index scan costs a few random page reads and wins. As the fraction grows, random reads add up, and at some point reading the whole table sequentially is cheaper. The crossover moves with random_page_cost: at 4.0, an index scan stops being competitive at perhaps 1 to 5 percent of the rows; at 1.1, it stays competitive up to a much larger share.

Why the default is wrong for SSD

On a hard disk, a random 8 kB read costs a seek of several milliseconds, and a sequential read of the same page costs a fraction of a millisecond, a ratio of 10 to 100 in raw terms. The default of 4.0 was a compromise that already assumed much of the data was cached. On an SSD, random and sequential reads cost almost the same in latency, with the difference coming from request overhead and read-ahead. The effective ratio is 1.0 to 1.5. Setting random_page_cost = 1.1 tells the planner the truth.

The visible symptom of leaving the default: a query that filters on an indexed column and returns 5 percent of a large table uses a sequential scan, and runs in 20 seconds, while forcing the index (SET enable_seqscan = off) runs in 2 seconds. Another symptom: nested loop joins with index probes are avoided in favor of hash joins that read whole tables.

Choosing the value

Many guides say 1.1 for SSD and that is a fine start. The cloud case is trickier: a network volume has fast sequential throughput and noticeable latency per request, so random reads are more expensive than on local NVMe, and 2.0 may suit better than 1.1. The way to choose is empirical: pick a handful of queries with a choice of plans, try values from 1.1 to 2.0 and compare actual times, not estimated costs.

Set it:

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

It reloads without a restart, and you can try it per session first with SET random_page_cost = 1.1; and run EXPLAIN on queries you care about.

effective_io_concurrency

This setting says how many asynchronous I/O requests a session can issue at once for operations that can prefetch: bitmap heap scans (and, from version 18, more cases with asynchronous I/O). Hard disks benefit from a small number matching spindles; SSDs and NVMe handle many concurrent requests, so values of 100 to 300 are typical. The default is 1 before version 18 and 16 from version 18. A higher value makes bitmap scans over large result sets faster on SSD by keeping the device busy. Test on a query that uses a Bitmap Heap Scan with a large number of pages. maintenance_io_concurrency is the equivalent for maintenance operations such as vacuum.

ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET maintenance_io_concurrency = 100;

Do not set it so high that other sessions starve for I/O; on shared storage, leave room.

Mixed storage: per tablespace costs

If a server has a fast tablespace for hot tables and a slow one for archives, set the costs per tablespace:

ALTER TABLESPACE fast_nvme SET (random_page_cost = 1.0, effective_io_concurrency = 256);
ALTER TABLESPACE slow_hdd  SET (random_page_cost = 4.0, effective_io_concurrency = 2);

Tables and indexes in each tablespace are costed with its values. Cloud setups with a single volume class rarely need this.

Checking the effect

Lowering the cost changes plans across the system, mostly favorably and sometimes not. To check:

1. Record the plans of your 20 most important queries, using EXPLAIN (ANALYZE, BUFFERS) at the old value.

2. Set the new value for a session and record the plans again.

3. Compare, as in comparing plans before and after a change: which queries changed plan, and were they faster?

4. Apply it server-wide when the changes are improvements or neutral, and watch pg_stat_statements for regressions over a week.

Use the diff of the plan structures to spot flips quickly. Expect plans that switch from sequential scans to index or bitmap scans for mid-selectivity filters, and from hash joins to nested loops with index probes for small outer sets. Check that big report queries that rely on sequential scans still do, since the planner will still pick them when they are cheaper.

Also watch the cold-cache case. A plan that is fast with a warm cache and slow with a cold one is a risk after restarts or failovers; the lower random cost makes such plans more likely. If your storage handles cold random reads well (NVMe), that is not a worry. If it does not (burstable cloud volumes with low baseline IOPS), keep a moderate value like 1.5 to 2.0.

Interaction with effective_cache_size

The two settings tell the planner about the hardware from different sides. random_page_cost says how costly a miss is; effective_cache_size says how likely a hit is. A server with a large cache and fast storage should say both: low miss cost and a high cached fraction. A planner told one without the other can make odd choices. See choosing effective_cache_size. If you change both at once and plans improve, you cannot say which helped; for the sake of understanding, change them one at a time in a test.

Related settings that matter on SSD

seq_page_cost stays at 1.0 as the reference unit. cpu_tuple_cost and friends rarely need changing. wal_compression can reduce WAL volume at some CPU cost and suits write-heavy loads on SSD where endurance and bandwidth matter. checkpoint_flush_after and the checkpoint timing in checkpoint settings and write spikes affect SSD write patterns. autovacuum_vacuum_cost_limit should rise, since the default throttle assumes slow disks, as in autovacuum settings worth changing. huge_pages helps large buffer pools. On the OS side: use noatime mounts, the none or mq-deadline I/O scheduler for NVMe and make sure TRIM is enabled.

When lowering random_page_cost makes plans worse

It backfires when the storage is not actually fast for random reads: a spinning disk array, a throttled cloud volume that exhausts its IOPS budget under load or a network filesystem with high latency. It backfires when the working set is far larger than memory and the workload runs many concurrent index scans, saturating the device's IOPS where a sequential scan would use bandwidth efficiently. It backfires on queries where the planner's selectivity estimate is wrong, because a lower random cost amplifies the damage of choosing an index scan for what is really a big fraction of the table. If you see regressions after lowering it, check estimates first, then raise the value in steps (1.1, 1.5, 2.0) and find the point where the important queries are at their best.

What to do next

Check SHOW random_page_cost. If it is 4.0 on a server with SSD or NVMe, test 1.1 in a session against your ten slowest queries. The manual documents the constants in planner cost constants and the I/O settings in asynchronous behavior. Next, see using PGTune output sensibly.