liveBook · Manning

Shared buffer

As explained in Just Use Postgres!

A shared buffer is a preallocated PostgreSQL memory area that caches pages from tables and indexes.

A shared buffer is a preallocated PostgreSQL memory area that caches pages from regular tables and indexes. Query plans report whether PostgreSQL found pages in memory or had to load them from disk. section 4.4

Read the Buffers metrics as follows:

  • shared hit=3 means PostgreSQL accessed three cached pages without reading them from disk. section 4.4
  • shared hit=3 read=4 means three pages were already cached and four pages came from disk. section 4.4
  • Use EXPLAIN (analyze, buffers on) to execute a query and include its buffer activity in the plan. section 4.4

Inspecting cached pages

EXPLAIN (analyze, buffers on) SELECT username, level, score FROM game.player_stats WHERE player_id = 250; A plan containing Buffers: shared hit=3 shows that the index scan and table lookup used three pages already held in shared buffers. section 4.4

EXPLAIN (analyze, buffers on) 
SELECT username, level, score 
FROM game.player_stats
WHERE player_id = 250;

Run the query in a PostgreSQL session to inspect shared-buffer usage alongside the execution plan. section 4.4

How Just Use Postgres! covers it

How deep it goes
Explains cached pages and hit/read metrics through EXPLAIN output, stopping short of buffer-management internals.
Examples
Uses an indexed player lookup producing Buffers: shared hit=3 and contrasts it with plans containing disk reads.
What it assumes
Assumes basic SQL and execution-plan familiarity while defining shared buffers and their reported metrics.
What you can do after
Diagnose query memory pressure with EXPLAIN (analyze, buffers on) and interpret shared hit and read counts.
How it connects
Connects shared buffers with EXPLAIN, execution plans, indexes, tables, and disk reads.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.