# 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 {#identity}

| Field | Value | Meaning |
| --- | --- | --- |
| Type | `integer` | Upstream pg_settings type |
| Context | `user` | Settable by an ordinary user |
| Unit | `8kB` | Raw unit |
| Range | `1` – `2147483647` | Raw limits in the last observed version |
| Enum values | — | — for non-enum types |
| Category | Query Tuning / Planner Cost Constants | Upstream classification |
| Latest boot value | `524288` | 4 GiB (524288 × 8kB) |
{.fields meta="-"}

## Lifecycle {#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 {#default-history}

| 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) |
{.full-width caption="Measured PG9.0–19 Beta 3 boot defaults"}

## How it works {#mechanism}

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 {#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. |
{.full-width}

## Pigsty {#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` |
{.full-width}

> [!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 {#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.

## Related parameters {#related-parameters}

[`shared_buffers`](/parameters/shared-buffers/) · [`random_page_cost`](/parameters/random-page-cost/) · [`seq_page_cost`](/parameters/seq-page-cost/) · [`effective_io_concurrency`](/parameters/effective-io-concurrency/) · [`max_connections`](/parameters/max-connections/)

## References {#references}

- [PostgreSQL 19 Beta 3 — effective_cache_size](https://www.postgresql.org/docs/19/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE)
- [PostgreSQL 18: Using EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html)
- [Pigsty: Parameter Optimization Policy](https://pigsty.io/docs/pgsql/template/tune/)
- [PostgreSQL 19 release notes](https://www.postgresql.org/docs/19/release-19.html)
- [Machine-readable GUC export](/data/guc.jsonl)