effective_cache_size
Sets the planner's assumption about the total size of the data caches.
How you change it
Any session (SET)
Defaults and ranges on this page were read from pg_settings on live PostgreSQL 18.4. Treat older majors as needing their own check before you copy a value.
That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.
What this means
A planner hint, not an allocation: it tells the optimizer roughly how much memory (PostgreSQL shared buffers plus the operating system page cache) is available for caching data. A higher value makes index scans look cheaper because the planner assumes repeated page reads will hit cache, nudging it away from sequential scans.
When it matters
Set it on every dedicated database host -- the default is far too low for modern servers.
| Default | 4 GB |
|---|---|
| Range | 1 – 2147483647 |
| Type | integer |
| Change scope | Any session (SET) |
| Category | Planner |
SHOW effective_cache_size;Tradeoffs
- • Too low: the planner underestimates cache, over-favoring sequential scans.
- • It changes plans only; it never reserves memory, so setting it high costs nothing directly.
- • A common starting point is roughly half to three-quarters of total RAM on a dedicated host.