effective_cache_size

A planner estimate of how much PostgreSQL data cache is effectively available to one query; it changes plan costing but allocates no memory.

Note

Fact — official short description: “Sets the planner’s assumption about the total size of the data caches.”

Identity

Type , Valueinteger: Upstream pg_settings type
Context , Valueuser: Settable by an ordinary user
Unit , Value8kB: Raw unit
Range , Value1 – 2147483647: Raw limits in the last observed version
Enum values , Value—: — for non-enum types
Category , ValueQuery Tuning / Planner Cost Constants: Upstream classification
Latest boot value , Value524288: 4 GiB (524288 × 8kB)

Lifecycle

Fact	Value
First observed	PG9.0 (research boundary)
Present in	PG9.0–19 Beta 3
Removed in	No
Introduction commit	Not asserted: predates the PG9.0 research boundary
Commit date	—
Discussion	—

Default history

Measured PG9.0–19 Beta 3 boot defaults
Versions	Raw `boot_val`	Unit	Human value
PG9.0–9.3	`16384`	`8kB`	128 MiB (16384 × 8kB)
PG9.4–19 Beta 3	`524288`	`8kB`	4 GiB (524288 × 8kB)

How it works

effective_cache_size is a cost-model input. Higher values make index scans look more attractive; lower values make sequential scans more attractive.

The estimate should reflect both shared_buffers and the portion of the operating-system page cache likely to hold PostgreSQL data, while accounting for overlap and for concurrent queries sharing the same cache capacity.

Changing this parameter does not resize PostgreSQL shared memory, reserve kernel cache, or guarantee that pages remain cached between queries. Its effect is indirect, through plan selection.

Tuning advice

Tip

Advice. These are workload-specific starting points and must be validated with measurements.

Workload	Guidance
OLTP	Estimate the cache actually usable by PostgreSQL after OS and co-located service needs, then validate index-heavy plans with EXPLAIN. Avoid copying a fixed percentage from a host with different concurrency or working-set behavior.
OLAP	Large scans can evict or compete with cached data, so do not equate installed RAM with cache available to a single analytical query. Calibrate against representative mixed and cold-cache runs.
Small nodes	Leave room for the OS and other services; a value near total RAM is usually an overstatement on a shared small host. Treat it as a planner estimate, not a memory target.

Pigsty

Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.

Template	Effective value	Versus upstream boot	Source expression
OLTP	`24576MB`	different	`{{ pg_effective_cache_size }}MB`
OLAP	`24576MB`	different	`{{ pg_effective_cache_size }}MB`
CRIT	`24576MB`	different	`{{ pg_effective_cache_size }}MB`
TINY	`24576MB`	different	`{{ pg_effective_cache_size }}MB`

Caution

Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 = 24576MB (dcs); OLAP: PG9.0–19 Beta 3 = 24576MB (dcs); CRIT: PG9.0–19 Beta 3 = 24576MB (dcs); TINY: PG9.0–19 Beta 3 = 24576MB (dcs). Advice, pending human review — Editorial inference: using that remainder as the planner’s effective-cache estimate is a simplification that should be checked against real OS cache, overlap, co-located services, and query concurrency.

Common pitfalls

Expecting the setting to allocate or reserve memory.
Setting it equal to installed RAM without subtracting memory unavailable to PostgreSQL caching.
Ignoring that concurrent queries on different data sets share the effective cache.
Overstating it and then attributing index-heavy plan choices to unrelated cost parameters.

shared_buffers · random_page_cost · seq_page_cost · effective_io_concurrency · max_connections