In the output, look for :

• buffer hits – served from shared_buffers
• buffer reads – pulled from disk

If you run the same query again, most of the time, the second run shows far more hits because now the pages are already in shared_buffers.

Important Note

In large production workloads, not everything can or should fit in memory. So you will see disk reads, and that’s normal. The goal isn’t that everything must be a cache hit. The goal is :

• Disk I/O shouldn’t be your bottleneck, and 
• Reads and writes should be smooth and 
• Latency shouldn’t spike because the cache is too small or mis-sized

If you want deeper visibility into what is currently stored in shared_buffers or which tables are occupying memory, PostgreSQL gives you tools for that. Extensions like:

let you inspect shared buffers directly and understand memory usage patterns.

work_mem

After shared_buffers, the next memory parameter we need to focus on is work_mem.

And this is probably the most dangerous memory setting in PostgreSQL if you don’t fully understand how it works – not because it’s bad, but because it multiplies quietly. Many production outages caused by out-of-memory errors can be traced back to a misunderstanding of work_mem.

work_mem defines the limit or the maximum amount of memory allocated for executing operations such as:

• Sorting, when performing operations like ORDER BY, DISTINCT, and GROUP BY.
• JOINs usage (with hashing to build in-memory hash-tables, for example, for the hash join).
• Set operations like UNION, INTERSECT, and EXCEPT.
• Creating the bitmap arrays for the bitmap scan method

This parameter affects the efficiency of query execution and the overall performance of the database. It’s important to note that work_mem is allocated for each operation, not per the PostgreSQL session. This is a crucial detail, as a single SQL query can perform multiple sorting or join operations, each of which will consume its own area of memory. And some of these can be paralleized by PostgreSQL, and when that happens, each parallel worker uses up to work_mem per operation. If an operation runs sequentially, it can use up to work_mem. But if the same operation runs under a Gather node with, say, five parallel workers, then that single operation can consume:

5 × work_mem

This is exactly how databases run out of memory, even when the application hasn’t changed, because work_mem multiplies across:

• Parallel workers
• Multiple memory-intensive operations in a query
• Concurrent queries running at the same time

This is why the most important thing to remember is that work_mem is per operation, and it can be used multiple times inside a single query, across many concurrent queries. 

How do we tune work_mem?

By default, PostgreSQL sets work_mem to 4MB. For many simple OLTP workloads with high concurrency, this is actually fine. But for analytical or reporting queries, 4MB is often too small. 

If the  work_mem is too small, PostgreSQL starts spilling to disk, and you’ll typically see:

• Temporary files are being created
• Sorts switching to disk-based algorithms
• Increased disk I/O and latency spikes

If the work_mem is too large, it will cause memory pressure or worst OOM kills.

We can measure if work_mem needs tuning using:

EXPLAIN (ANALYZE, BUFFERS)
SELECT …