C03 — Performance Parameters¶
The GUCs and runtime statistics that most directly govern query performance: memory sizing (shared_buffers, work_mem, maintenance_work_mem), planner and parallelism settings, and — where pg_stat_statements is installed — real execution statistics rather than just configuration values. Several of these checks report a jsonb breakdown rather than one number, because "is this setting right" genuinely depends on more than one figure at once (available RAM, connection count, workload shape).
20 checks, PGHF03-001 through PGHF03-020. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.
PGHF03-001 — shared_buffers eviction ratio¶
The background writer evicting a large share of allocated buffers rather than clean ones is a sign shared_buffers is undersized for the active working set — the server ends up doing avoidable disk I/O to re-fetch pages it just wrote out.
How to fix
Raise shared_buffers (a common starting point is ~25% of system RAM, tuned from there against the actual hit ratio in PGHF03-016):
This requires a full restart, not a reload — plan a maintenance window. Re-check this ratio and PGHF03-016's cache-hit percentage afterward to confirm it actually improved.
PGHF03-002 — work_mem worst-case total¶
work_mem is granted per sort/hash operation, not per connection — under enough concurrent parallel query load the true worst-case memory consumption (work_mem times concurrent operations) can be far larger than it looks at a glance, risking an out-of-memory crash.
How to fix
Lower work_mem cluster-wide, or set it per-role/per-session for only the queries that genuinely need more:
ALTER SYSTEM SET work_mem = '32MB';
-- or, scoped to just the role that runs the heavy reporting queries:
ALTER ROLE reporting_user SET work_mem = '256MB';
work_mem reloads without a restart. Re-run this check's worst-case-total calculation against the new value before assuming it's safe.
PGHF03-003 — maintenance_work_mem¶
A too-low maintenance_work_mem slows autovacuum and DDL operations like index builds — this memory is only used during maintenance, so there's little downside to being generous with it relative to available RAM.
How to fix
Autovacuum workers each use up to autovacuum_work_mem if set, or fall back to this value otherwise — see PGHF05-011 if several concurrent workers need to be accounted for separately.
PGHF03-004 — effective_cache_size¶
Left at its conservative factory default, the planner underestimates how much of the working set the OS page cache can actually hold, and ends up favoring sequential scans over index scans it would otherwise prefer.
How to fix
Set it to roughly the sum of shared_buffers plus the OS-level page cache available for this database (commonly ~50-75% of system RAM):
This only informs the planner's cost estimates — it doesn't allocate any memory itself, so there's no downside to sizing it generously.
PGHF03-005 — max_parallel_workers_per_gather¶
A value of 1 or less effectively disables parallel query regardless of how many worker processes the server has available — a single missed setting quietly turns off a whole class of query speedups.
How to fix
Confirm max_worker_processes and max_parallel_workers are large enough to actually supply that many workers cluster-wide — this setting alone is a per-query cap, not a guarantee.
PGHF03-006 — min_parallel_table_scan_size¶
A threshold set too high excludes moderately-sized tables from ever being considered for a parallel scan, even when parallelism would clearly help — worth checking it still matches current table sizes.
How to fix
Lower it so tables of the size actually being queried become eligible for a parallel plan:
After changing it, confirm with EXPLAIN on a representative query that a parallel plan is actually chosen — eligibility doesn't guarantee the planner picks one.
PGHF03-007 — checkpoint_completion_target¶
A low value concentrates checkpoint I/O into a short burst instead of spreading it across the full checkpoint interval, producing periodic latency spikes that are otherwise avoidable.
How to fix
Spreads the same checkpoint I/O across nearly the full interval between checkpoints instead of a burst right after each one starts.
PGHF03-008 — Requested checkpoint ratio¶
Checkpoints forced by WAL volume rather than the schedule mean max_wal_size is undersized for the write rate — forced checkpoints cause unplanned I/O spikes that a properly-sized max_wal_size would spread out instead.
How to fix
Raise max_wal_size so the write rate no longer outruns the checkpoint schedule:
Re-check the requested-vs-scheduled checkpoint ratio (pg_stat_checkpointer on PG17+, pg_stat_bgwriter before) after a representative period at the new value, and increase further if forced checkpoints are still frequent.
PGHF03-009 — wal_compression¶
Leaving WAL compression off spends more disk I/O and replication bandwidth than necessary on full-page images, for a CPU cost that's usually cheap by comparison on modern hardware.
How to fix
ALTER SYSTEM SET wal_compression = 'zstd'; -- or 'lz4'/'pglz', depending on what's compiled in
SELECT pg_reload_conf();
Only newly-written full-page images are compressed going forward — this has no effect on WAL already generated.
PGHF03-010 — random_page_cost¶
The traditional default (4.0) assumes spinning-disk seek costs — left unchanged on SSD-backed storage, the planner overestimates the cost of index scans relative to sequential scans and picks worse plans than the hardware actually supports.
How to fix
On SSD/NVMe-backed storage, bring it much closer to seq_page_cost (typically 1.0):
Confirm with EXPLAIN on a query that was previously choosing a sequential scan over an obviously-better index scan.
PGHF03-011 — effective_io_concurrency¶
A low value on SSD-backed storage prevents bitmap heap scans from issuing the concurrent prefetch reads that fast storage is actually capable of servicing, leaving real I/O throughput on the table.
How to fix
A value in the low hundreds is typical for a single SSD/NVMe device; scale it up further for a RAID array or networked block storage capable of higher queue depths.
PGHF03-012 — JIT overhead¶
JIT compilation trades a fixed per-query compile cost for faster execution on expensive queries — on an OLTP workload dominated by short queries, that fixed cost can make JIT a net latency regression rather than a win.
How to fix
If the observed breakdown shows JIT compile time dominating short-query latency, either raise the cost threshold at which JIT kicks in, or turn it off entirely for this workload:
ALTER SYSTEM SET jit = off;
-- or, keep JIT for genuinely expensive queries only:
ALTER SYSTEM SET jit_above_cost = 500000;
SELECT pg_reload_conf();
Compare pg_stat_statements's jit_functions/jit_generation_time totals before and after to confirm the change actually reduced overhead rather than just moved it.
PGHF03-013 — wal_buffers¶
An undersized wal_buffers increases contention on the WAL write lock under concurrent write load, since backends have less shared space to buffer WAL records before a flush is forced.
How to fix
Set it explicitly rather than leaving it at -1 (auto-sized as a small fraction of shared_buffers):
Requires a restart, since it's allocated from shared memory at startup.
PGHF03-014 — default_statistics_target¶
Left at its modest default, the planner has coarser statistics to work with on columns with non-uniform data distribution, increasing the odds of a bad plan on exactly the queries that need good statistics most.
How to fix
Raise it cluster-wide, or scope it to just the columns that need it (cheaper than a blanket increase, since ANALYZE cost scales with the target):
ALTER SYSTEM SET default_statistics_target = 200;
SELECT pg_reload_conf();
-- targeted alternative:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
Either way, run ANALYZE afterward — the new target only takes effect on the next statistics collection, not retroactively.
PGHF03-015 — Temp file spill¶
Any spill to disk means a sort or hash operation outgrew work_mem and fell back to much slower disk-based processing — sustained spill volume is a direct signal that work_mem is undersized for the workload.
How to fix
- Find the actual query with
log_temp_files(log every spill and its size) orpg_stat_statements.temp_blks_written. - Raise
work_mem— either cluster-wide or scoped to the role/session running the offending query (seePGHF03-002for the sizing tradeoff):
ALTER SYSTEM SET log_temp_files = 0; -- log every temp file, to find the culprit
SELECT pg_reload_conf();
- For a one-off heavy query,
SET work_memfor just that session instead of raising the cluster-wide default.
PGHF03-016 — Database cache hit ratio¶
A low cache hit ratio means a large share of reads are hitting disk instead of shared_buffers — one of the single clearest, most workload-independent signals that the buffer pool is undersized for the active data set.
How to fix
See PGHF03-001's remediation (raise shared_buffers) — this check and that one are two views of the same underlying capacity problem. If the working set genuinely exceeds available RAM, no shared_buffers value fixes it; consider partitioning/archiving cold data, or adding RAM.
PGHF03-017 — track_io_timing¶
Without per-block I/O timing, it's impossible to tell whether a slow query is CPU-bound or I/O-bound from pg_stat_statements alone — enabling it is what makes several other diagnostic checks in this framework (and EXPLAIN (ANALYZE, BUFFERS)) actually informative.
How to fix
The overhead is typically small on modern hardware with a fast clock_gettime(), but if it's a concern, measure it directly with pg_test_timing before enabling cluster-wide.
PGHF03-018 — Parallel worker pool saturation¶
Requires PostgreSQL 14+.
If the cluster-wide parallel worker pool (max_parallel_workers) is saturated, queries that should run in parallel silently fall back to serial execution — no error, just slower. Nothing else in this framework checks actual parallel-worker demand against that ceiling.
How to fix
Requires a restart. Also confirm max_worker_processes is large enough to actually supply that many background workers cluster-wide — max_parallel_workers is itself capped by it. If saturation is being driven by a few oversized queries rather than genuine steady-state demand, consider max_parallel_workers_per_gather (PGHF03-005) as a cheaper per-query lever before raising the cluster-wide ceiling.
PGHF03-019 — track_wal_io_timing¶
Requires PostgreSQL 14+.
Without it, high WAL activity (already visible via PGHF14-003) can't be distinguished from slow WAL flush latency — the WAL-side analogue of what PGHF03-017's track_io_timing already does for heap block I/O.
How to fix
Same overhead profile as PGHF03-017's track_io_timing — typically small on modern hardware, measurable directly with pg_test_timing if in doubt.
PGHF03-020 — pg_stat_io relation-extend pressure¶
Requires PostgreSQL 16+.
High relation-extension time is a direct, otherwise-invisible signal of insert-heavy/bulk-load workloads doing many small file-growth syscalls instead of a few large ones — same view PGHF13-002 already queries, but a column pair (extends/extend_time) nothing existing reads.
How to fix
- Check whether the pressure lines up with a known bulk-load or high-insert-rate workload — if so, this may just be the honest cost of that workload, not a misconfiguration.
- Where it's avoidable, batch inserts into larger transactions/COPY operations rather than many small ones, so each relation extension amortizes over more rows.
- Revisit
autovacuum_naptime/vacuum aggressiveness on the affected tables (PGHF05-006) — a table that's bloating faster than it's reclaimed grows (and re-extends) more than it needs to.
Continue to C04 — Long-Running Queries & Lock Contention.