Skip to main content

work_mem recommendation

The work_mem recommendation uses multiple signals to decide whether memory-related query operations are spilling and whether a configuration change is likely to help. The docs describe the approach, but intentionally avoid publishing the exact scoring and sizing formulas.

Signals used​

  • Temporary file activity — helps identify whether sorts, hashes, or other intermediate work are spilling to disk.
  • Real-time wait events — highlights sessions currently waiting on spill-related I/O.
  • Query statistics — when pg_stat_statements is available, pocketPG uses it to identify which query shapes appear to be responsible for spill pressure, including spill-per-query breakdowns and spill-type classification (sort, hash, temp table).
  • Spill trend — change in temp_bytes over the sample window shows whether spill is increasing, decreasing, or stable directionally.
  • Configured memory settings — includes work_mem and hash_mem_multiplier (PG13+), which gives hash operations extra memory budget beyond work_mem.
  • Bimodal workload detection — when OLTP and batch queries share the same database, pocketPG may recommend session-level SET LOCAL instead of a global increase.
  • Observed concurrency — estimates pressure from active workload behavior rather than assuming every connection is doing expensive memory work.
  • Host memory context — uses cloud-reported or inferred memory to keep suggestions conservative.
  • Signal quality — recommendations are downgraded when the server is idle, statistics were recently reset, or required extensions are unavailable.

Why the recommendation may be conservative​

PostgreSQL does not expose exact concurrent sort/hash memory allocation from SQL. pocketPG therefore treats work_mem as an evidence-backed estimate, not an exact capacity planner. If the evidence is incomplete, the recommendation should be applied gradually and rechecked after a representative workload window.

Per-query guidance​

When a small number of queries dominate the spill signal, pocketPG may suggest session- or transaction-local memory changes for those workloads instead of only recommending a global setting. This is especially useful when OLTP traffic and batch/reporting queries share the same database.

Feedback loop​

When you copy the work_mem SQL, pocketPG stores a local baseline for the connected browser. On later Tuning tab loads, the app can compare spill and latency signals and classify the outcome:

  • Spill down and latency down — Effective
  • Spill down, latency unchanged — Partial
  • Spill unchanged — No impact (with inline rollback SQL)