pg_cache_hit_ratio — The Fastest PostgreSQL Health Check — Blog

What Does This Tool Do?

pg_cache_hit_ratio calculates the buffer cache hit ratio for the current database: the proportion of data blocks read from the shared buffer cache versus those that had to be fetched from disk. A ratio above 99% is ideal — below 95% indicates insufficient memory or a misconfigured shared_buffers setting.

How Is It Called?

Connecting First

Before calling any performance tool, you need to connect to the target database. mcp-postgres supports multiple database profiles — ideal for multi-cluster setups:

● ● ● Claude CLI — Connection

claude> Connect using profile "production-db"

▶ Calling tools...
→ pg_connect { "profile": "production-db" }

{
"status": "connected",
"database": "mcp_test",
"version": "PostgreSQL 16.x",
"host": "db.example.com"
}

Claude CLI (Natural Language)

● ● ● Claude CLI — Cache Analysis

claude> How efficient is my buffer cache?

▶ Calling tools...
→ pg_cache_hit_ratio {}

Buffer Cache Hit Ratio for mcp_test: 99.10%

Details:
Blocks Hit: 1,596,791
Blocks Read: 14,581

Assessment: EXCELLENT — Database is operating almost entirely from RAM.

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Cache Analysis

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_cache_hit_ratio", {});

// Response:
[
{
    "datname": "mcp_test",
    "cache_hit_ratio": "99.10",
    "blocks_hit": "1596791",
    "blocks_read": "14581"
}
]

What Happens Under the Hood?

The tool executes a single, efficient query against pg_stat_database:

-- Buffer cache hit ratio from pg_stat_database
SELECT sum(blks_hit) AS heap_hit, sum(blks_read) AS heap_read, CASE WHEN sum(blks_hit) + sum(blks_read) = 0 THEN 0 ELSE sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read)) END AS ratio FROM pg_stat_database WHERE datname = current_database();

The statistics in pg_stat_database are cumulative since the last statistics reset. The value therefore represents overall performance, not a point-in-time snapshot.

Output in Detail

datname — Database name
cache_hit_ratio — Hit ratio as a percentage value (e.g., "99.10")
blocks_hit — Number of blocks read from the shared buffer cache
blocks_read — Number of blocks read from disk

Pro Tip: A low hit ratio right after a PostgreSQL restart is normal — the buffer cache needs to be warmed up first. Wait at least one hour under normal workload before evaluating the ratio.

What Does the Test Suite Validate?

The test suite validates pg_cache_hit_ratio against an active PostgreSQL instance — the ratio is computed from cumulative statistics and requires no special seed data:

● ● ● vitest run — Testsuite

✓ pg_cache_hit_ratio > returns ratio between 0 and 1
✓ pg_cache_hit_ratio > includes percentage field

Test Files 1 passed (1)
Tests 2 passed (2)
Duration 124ms

Test Data Scenario

No special seed data is required for this tool. The ratio is computed from cumulative pg_stat_database statistics that are updated with every database activity. Tests ensure that:

The cache_hit_ratio is a numeric percentage value
The result includes datname, blocks_hit, and blocks_read fields

Summary

pg_stat_database

Data Source

Output Fields

Tests

read-only

Type

pg_cache_hit_ratio is the simplest and fastest health check for PostgreSQL. A single call delivers a clear assessment of whether your memory allocation is sufficient. Ideal as the first step in any performance troubleshooting session.

Together with pg_slow_query_digest and pg_index_advisor, it forms the core of the /pg-perf workflow skill.

pg_cache_hit_ratio — The Fastest PostgreSQL Health Check