effective_cache_size

effective_cache_size doesn't allocate anything — it tells the planner roughly how much memory is available for caching across PostgreSQL's shared_buffers and the operating system's page cache. The planner uses it to judge how likely repeated index lookups are to hit cache, which influences whether it favors index scans over sequential scans.

A common setting is ~50–75% of system RAM. Setting it too low makes the planner pessimistic about caching and biases it toward sequential scans; too high can over-favor index plans. It's a pure planner hint with no memory cost, so it's safe to set and takes effect without a restart.

It allocates nothing

This is the most misunderstood setting on the list: effective_cache_size reserves no memory. It's purely an estimate you give the planner of how much memory is available for caching across PostgreSQL's shared_buffers and the operating system's page cache combined.

The planner uses it when costing repeated index access — for example, deciding whether a nested-loop with an index lookup on the inner side will mostly hit cache. A realistic value helps it choose index scans when they'll actually be cheap.

Picking a value

A common choice is 50–75% of system RAM, on the assumption the OS cache plus shared_buffers together hold roughly that much of your data. It doesn't have to be exact — it's a hint, not a guarantee.

• Too low → the planner assumes index pages won't be cached, inflates the cost of index scans, and drifts toward sequential scans.
• Too high → it may over-favor index plans that don't pay off.

Because nothing is allocated, you can change it freely — even per session — to nudge plan choices, and it takes effect without a restart.

Relationship to random_page_cost

effective_cache_size and random_page_cost work together to decide index-vs-seq-scan. On fast storage you typically lower random_page_cost (random reads are cheap) and set effective_cache_size realistically high (much of the data is cached) — both push the planner toward indexes where they help.

• Index Scan
• Bitmap Heap Scan
• Sequential Scan

Does effective_cache_size allocate memory?

No. It's an estimate the planner uses for costing — it never reserves or uses RAM. You can change it freely (even per session) to nudge plan choices.

• shared_buffers
• random_page_cost
• work_mem