A 99.99% Cache Hit Ratio Didn't Answer the PostgreSQL Memory Question

A PostgreSQL production workload showed a buffer-cache hit ratio of approximately 99.99%. That is the kind of number that invites a quick conclusion: caching is excellent, therefore memory is fine.

At the same time, monitoring showed free memory dropping when connection utilisation spiked. The instance had recently been scaled from 64 GiB to 128 GiB of memory, so the tuning question was not simply “add more RAM.” The useful investigation was to separate the different things people casually call “database memory.”

CACHE HIT RATIO

99.99%

MEMORY SCALE-UP

64 → 128 GiB

FREE MEMORY

fell with connections

shared_buffers is not the whole memory model§

PostgreSQL uses a process-per-connection architecture. Each client connection has a backend process, and memory consumption is not limited to the shared buffer cache. A simplified picture of one host’s memory is shared_buffers, backend/session memory, per-query work memory for sorts, hashes and joins, temporary/session work, and OS/filesystem cache — each a separate consumer.

A high hit ratio tells me something useful about one part of that picture. It does not prove that every other memory consumer is healthy.

The strongest correlation was connection count§

The monitoring data showed connection-utilisation spikes lining up with drops in free memory — consistent with the backend-process model. More connections means more backend processes and more opportunities for per-session and per-query allocations. Idle connections are not free either; they still retain a backend process and session state.

The working chain was: a connection spike produces more backend processes, which produces more session/query memory opportunity, which produces lower free memory. Correlation alone does not assign an exact byte count to each backend, but it was enough to reject the idea that shared_buffers was the only setting worth discussing.

Why the 99.99% hit ratio still mattered§

The high cache hit ratio was useful evidence. It suggested that PostgreSQL was finding nearly all requested blocks in its own shared buffer cache, so it did not need to issue a read for those blocks. That made a large increase in shared_buffers difficult to justify from this metric alone.

That wording matters: PostgreSQL’s buffer-hit statistics do not tell you whether a block read by PostgreSQL ultimately came from physical storage or was satisfied by the operating system’s filesystem cache. The metric is about PostgreSQL’s cache, not a direct physical-disk-read counter.

If the database cache is already doing its job, allocating most newly available RAM to an even larger shared cache can have diminishing value while reducing headroom for backend processes, query execution, maintenance and the operating system.

Temporary files answer a different question§

The next area to inspect was query execution that exceeded available memory for operations such as sorts or hashes. pg_stat_statements can identify statements associated with temporary-block writes:

SELECT
    query,
    total_exec_time,
    temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

Database-level temporary-file activity can be inspected with:

SELECT
    datname,
    temp_files,
    temp_bytes / 1024 / 1024 AS temp_size_mb
FROM pg_stat_database
ORDER BY temp_size_mb DESC;

Those are different questions from cache hit ratio. A query can find its base pages in cache and still spill work to temporary storage.

work_mem has to be treated as a multiplier§

The tuning plan considered gradually increasing work_mem for this workload. The initial range under consideration was modest — roughly 8–16 MB globally — with larger values tested selectively at session level for expensive operations:

SELECT set_config('work_mem', '32MB', false);
SELECT current_setting('work_mem');

The reason for the conservative approach is that work_mem is not “memory per server,” and not simply “memory per connection.” Multiple operations in one query can each use it, many queries can execute concurrently, and hash operations can be affected by hash_mem_multiplier. The capacity question is therefore closer to:

possible memory pressure ≈ concurrent memory-consuming operations × per-operation memory budget

That makes aggressive global changes particularly risky during connection spikes — exactly the condition already associated with lower free memory.

The larger instance changed the budget, not the method§

After the move from 64 GiB to 128 GiB, the analysis proposed a conservative allocation rather than simply doubling every PostgreSQL memory parameter. For this specific workload, the planning range was approximately:

shared_buffers       32–40 GiB
effective_cache_size ~96 GiB
work_mem             start around 8 MiB, test upward carefully
maintenance_work_mem increase only if maintenance is the bottleneck

These are workload-specific planning values from this investigation, not generic PostgreSQL sizing rules. The reasoning: the existing cache hit ratio was already extremely high; free-memory pressure correlated with connection spikes; temporary-file activity and query execution needed separate measurement; and extra RAM should preserve headroom outside shared_buffers. effective_cache_size is also a planner estimate, not a reservation of that amount of RAM.

Change one dimension at a time§

The tuning plan intentionally avoided changing several memory settings together — baseline, change one parameter, observe, compare, only then change the next thing. Useful baseline signals included free memory, swap behaviour, connection utilisation, temporary-file volume, buffer-cache hit ratio and query runtime from pg_stat_statements.

Connection pooling was an architectural option§

Because connection spikes correlated with memory pressure, connection pooling was part of the recommended design space. On Amazon RDS, RDS Proxy was one option considered for reducing uncontrolled backend-connection growth. The important point was not the product choice — it was recognising that database memory pressure could partly be a connection architecture problem. A PostgreSQL parameter cannot fully compensate for an application creating more concurrent backends than the workload needs.

What the evidence did and did not support§

The evidence supported: cache performance was excellent, connection spikes correlated with lower free memory, and query/temp activity still needed independent measurement.

It did not support increasing shared_buffers dramatically, and it did not justify a large global work_mem increase without concurrency testing. That distinction was the main value of the exercise.

Takeaway§

A cache hit ratio answers a cache question. It does not answer the entire memory question. On PostgreSQL, I want to look at the whole allocation model: shared cache, connections, per-operation memory, temporary work, maintenance and OS/cache behaviour.

In this case, 99.99% was not the end of the investigation. It was the evidence that told me where not to start.