effective_cache_size, random_page_cost, I/O Concurrency, AIO, and Storage Characteristics

Learning outcomes

The memory budget is stable, but the planner chooses a sequential scan where an engineer expects an index scan. Someone copies “SSD settings” from a blog: random_page_cost=1.1, effective_io_concurrency=256, and a giant effective_cache_size. A plan changes—but the host's real storage, cache residency, concurrency, and PostgreSQL 18 asynchronous I/O (AIO) behavior were never measured.

1. Start with the current planner/I/O model

SELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN (  'effective_cache_size',  'seq_page_cost',  'random_page_cost',  'effective_io_concurrency',  'maintenance_io_concurrency',  'io_method',  'io_workers',  'io_max_concurrency',  'io_combine_limit',  'io_max_combine_limit')ORDER BY name;

Some settings are planner estimates, some affect runtime I/O request concurrency, and some require startup/restart. A performance review must distinguish those roles before comparing values.

2. effective_cache_size allocates zero bytes

effective_cache_size tells the planner how much cache might effectively be available to a single query, considering PostgreSQL shared buffers and the useful portion of the operating-system cache under expected concurrency. It does not allocate shared memory and does not reserve kernel page cache.

SHOW shared_buffers;SHOW effective_cache_size;SELECT name, context, source, short_descFROM pg_settingsWHERE name IN ('shared_buffers','effective_cache_size');

Increasing it can make repeated index probes look more likely to find pages cached and therefore cheaper. That can change plan selection without changing one byte of actual cache capacity.

3. Page costs are relative values on one model scale

seq_page_cost conventionally defines the cost scale for sequential page access; random_page_cost prices non-sequential page access relative to it. Only relative values matter. PostgreSQL's defaults already assume substantial caching of random accesses, which is why the default random cost is not a literal HDD random/sequential latency ratio.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT event_id, customer_id, occurred_at, amountFROM app.ch22_perfWHERE customer_id BETWEEN 11000 AND 11100  AND occurred_at >= TIMESTAMPTZ '2026-01-02 00:00+00'ORDER BY customer_id, occurred_at DESC;

Record estimated rows/cost, actual rows, scan type, buffers, and execution time. The planner cost is not elapsed milliseconds. Chapter 11 already established that cost and time are separate domains.

4. Compare two hypotheses in one transaction

BEGIN;SET LOCAL effective_cache_size = '128MB';SET LOCAL seq_page_cost = 1.0;SET LOCAL random_page_cost = 6.0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT event_id, customer_id, occurred_at, amountFROM app.ch22_perfWHERE customer_id BETWEEN 11000 AND 11100  AND occurred_at >= TIMESTAMPTZ '2026-01-02 00:00+00'ORDER BY customer_id, occurred_at DESC;ROLLBACK;

BEGIN;SET LOCAL effective_cache_size = '8GB';SET LOCAL seq_page_cost = 1.0;SET LOCAL random_page_cost = 1.5;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT event_id, customer_id, occurred_at, amountFROM app.ch22_perfWHERE customer_id BETWEEN 11000 AND 11100  AND occurred_at >= TIMESTAMPTZ '2026-01-02 00:00+00'ORDER BY customer_id, occurred_at DESC;ROLLBACK;

The plan may change—or not. A plan change proves only that the cost assumptions influence planning. It does not prove that the new assumptions match the real storage system or that the new plan is consistently faster under production concurrency.

5. PostgreSQL 18 has a real asynchronous I/O subsystem

PostgreSQL 18 can queue multiple eligible I/O requests. io_method=worker uses dedicated I/O worker processes and is the upstream default; io_uring requires a build with liburing support; sync executes AIO-eligible operations synchronously. Changing io_method requires server startup/restart, so it is not a casual session benchmark toggle.

SELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN (  'io_method',  'io_workers',  'io_max_concurrency',  'io_combine_limit',  'io_max_combine_limit',  'effective_io_concurrency',  'maintenance_io_concurrency')ORDER BY name;

6. effective_io_concurrency is runtime I/O concurrency, not a page-cost replacement

In PostgreSQL 18, effective_io_concurrency controls how many concurrent storage I/O operations a session attempts for eligible work and defaults to a modern-hardware-oriented value. Raising it too far can increase latency for all queries by overwhelming a device or shared storage path.

BEGIN;SET LOCAL effective_io_concurrency = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT sum(amount)FROM app.ch22_perfWHERE sort_key BETWEEN 10000 AND 850000;SET LOCAL effective_io_concurrency = 16;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT sum(amount)FROM app.ch22_perfWHERE sort_key BETWEEN 10000 AND 850000;ROLLBACK;

This is not a clean storage benchmark because the second execution may see warmer PostgreSQL/OS caches. Repeat in both orders, use multiple runs, preserve the same workload and background load, and corroborate with pg_stat_io plus host storage metrics. AIO benefits are operation- and cache-state-dependent.

7. Observe I/O by PostgreSQL backend/object/context

SELECT backend_type, object, context,       reads, read_bytes, read_time,       writes, write_bytes, write_time,       writebacks, extends, fsyncs, fsync_time,       stats_resetFROM pg_stat_ioWHERE object IN ('relation','wal')ORDER BY object, backend_type, context;

These are cumulative PostgreSQL counters. If track_io_timing/track_wal_io_timing are off, corresponding timing fields are zero. Reads can be satisfied by the kernel page cache; pg_stat_io alone is not a physical-device latency monitor.

8. pg_aios is in-flight AIO evidence, not a dashboard rate

SELECT pid, io_id, state, operation,       off, length, target, target_desc,       f_sync, f_localmem, f_bufferedFROM pg_aiosORDER BY pid, io_idLIMIT 50;

pg_aios contains currently in-use asynchronous I/O handles; it can be empty between operations. It is mainly a tuning/developer diagnostic, not a cumulative throughput view. Access is restricted to superusers or roles with pg_read_all_stats by default.

9. OS-level evidence closes the loop

# Linux:iostat -x 1vmstat 1# macOS alternatives:# iostat -w 1# vm_stat 1

Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Read',            '\PhysicalDisk(*)\Avg. Disk sec/Write',            '\PhysicalDisk(*)\Disk Reads/sec',            '\PhysicalDisk(*)\Disk Writes/sec'

Host tools reveal queueing/latency/throughput outside PostgreSQL, but virtualization, cloud volumes, SAN caches and container limits can move the true bottleneck elsewhere. Keep PostgreSQL and infrastructure timestamps aligned so before/after comparisons describe the same interval.

10. Tablespaces can model different storage classes

Planner page costs and I/O concurrency can be overridden at the tablespace level when different relations truly live on storage with different characteristics. Do this only when the physical placement really differs; otherwise it creates a misleading catalog model.

Authoritative references

Performance settings are hardware-, concurrency-, plan-, operating-system-, and version-sensitive. These primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Query Planning Cost Constants
• PostgreSQL 18 — Resource Consumption / AIO
• PostgreSQL 18 — Release 18 AIO Changes
• PostgreSQL 18 — pg_aios
• PostgreSQL 18 — Monitoring / pg_stat_io