Postgres effective_cache_size: The Planner Hint That Shapes Index Decisions

Of all the Postgres configuration parameters that ship with confusing defaults, effective_cache_size is the one whose name most actively misleads. It does not allocate memory. It does not control caching. It is purely a hint to the query planner about how much memory the OS is likely to be using for filesystem cache. The planner uses this hint to estimate the cost of index scans relative to sequential scans, and the wrong value produces query plans that are systematically wrong in the same direction.

What the planner does with the number

When the planner is evaluating an index scan, it has to estimate how many of the index and heap pages it will need to read from disk versus how many will already be in cache. The OS page cache holds recently-accessed pages, and a large cache means most index scans hit warm pages. A small cache means index scans pay random-IO costs.

effective_cache_size is the planner's estimate of the total cache available, combining Postgres's shared_buffers and the OS page cache. The default value of 4GB was set when 4GB was a reasonable assumption for a server. On a modern machine with 32GB of RAM, 4GB is an underestimate by roughly an order of magnitude, and the planner systematically chooses sequential scans where index scans would be faster.

The recommended setting is roughly 50-75 percent of total system RAM. On a 32GB server, set effective_cache_size to 24GB. The setting does not allocate any memory; it just tells the planner what to assume.

The asymmetric error

Getting effective_cache_size wrong is asymmetric in a way that most parameters are not. Setting it too low pushes the planner toward sequential scans, which read the whole table even when an index would let it read a small subset. Setting it too high pushes the planner toward index scans, which might pay random-IO costs that sequential reads would amortize.

In practice, the too-low error is much more common and much more damaging. Sequential scans on multi-gigabyte tables produce predictable I/O patterns that look fine in monitoring but waste enormous amounts of work. Index scans that happen to miss cache are at most a few hundred extra ms of latency. The too-high error is bounded; the too-low error is not.

How to verify the setting is working

The diagnostic is the random_page_cost vs seq_page_cost ratio in EXPLAIN output, combined with the planner's estimated cache hit ratio for the query. The planner exposes these indirectly through cost estimates, which are visible in EXPLAIN.

The more direct verification is to compare EXPLAIN ANALYZE results for a query that should use an index. Force the index with SET enable_seqscan = OFF (in a session, never globally). If the forced-index plan is faster than the planner's chosen sequential scan, the planner is undervaluing index scans, and effective_cache_size is one of the likely causes.

pg_buffercache (the extension) shows what is actually in shared_buffers. Combined with OS-level tools like vmtouch or /proc/meminfo Cached, the actual cache size can be measured, and effective_cache_size can be set to match observed cache size with margin.

The interaction with random_page_cost

effective_cache_size pairs with random_page_cost, which is the planner's estimate of the cost of a random page read relative to a sequential one. The default random_page_cost is 4.0, which was set when spinning disks were common. On SSD storage, random reads are roughly the same cost as sequential, so random_page_cost should be 1.1 to 1.5.

Setting random_page_cost too high has the same effect as setting effective_cache_size too low: the planner thinks index scans are more expensive than they actually are, and prefers sequential scans. The two parameters together control the planner's index-vs-sequential decision.

For NVMe SSD storage, the right values are effective_cache_size = 50-75 percent of RAM and random_page_cost = 1.1. For spinning-disk storage on dedicated servers, effective_cache_size still 50-75 percent of RAM but random_page_cost stays at 4.0. For cloud block storage (EBS, Persistent Disk), the values are workload-dependent and worth tuning.

What effective_cache_size does not affect

Configuration changes that allocate memory: shared_buffers, work_mem, maintenance_work_mem. effective_cache_size is informational only and does not change memory consumption.

Query execution: the planner picks the plan, but once chosen, execution does not consult effective_cache_size again. The setting affects plan selection, not plan execution.

Cache behavior: the OS controls what is in the page cache. Postgres has no control over this and does not try.

VACUUM, ANALYZE, or any other maintenance operation: only query planning.

The reload story

effective_cache_size is a SIGHUP parameter, meaning it takes effect on configuration reload without restart. Changes apply to new query plans; cached plans for prepared statements are not invalidated. For most workloads, the change effect is visible within minutes as queries are re-planned.

The setting is a good candidate for the first round of Postgres tuning on a new system: it costs nothing operationally, requires no restart, has no negative side effects when increased toward the correct value, and frequently produces measurable performance improvements without any other changes.

Our use across the four products

DocuMint, CronPing, FlagBit, and WebhookVault are SQLite-based. SQLite has its own cache (page_size * cache_size) plus the OS page cache, and the SQLite query planner is less explicit about cache assumptions than Postgres. Our Postgres migration plan includes effective_cache_size tuning as part of the standard configuration template, starting at 50 percent of RAM and adjusting upward based on observed cache behavior.

The deeper observation is that effective_cache_size is the canonical example of a parameter whose name describes what it does not do. It is a hint, not an allocation. It affects plans, not caches. The default value is wrong on essentially every modern system. And the cost of leaving the default in place is invisible in monitoring because the wrong plans complete successfully and produce correct results, just slowly. The pattern recurs across other Postgres parameters whose defaults date from the early 2000s and whose values are no longer appropriate for modern hardware.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.