When a PostgreSQL row cache helps
A database can serve every page from memory and still spend time executing queries, checking visibility and constructing results. A row cache tries to avoid part of that repeated work. It also adds key handling, cache checks and serialization costs. The useful question is whether the complete application request becomes cheaper for your workload.
2026-10-04 release note: The 2.x SQL API
local_cache.mget(regclass, anyarray)was removed in 3.0.0; use RESPMGETfor row reads.
pg_local_cache exposes an explicit RESP MGET endpoint for complete-row reads. Ordinary SELECT queries retain their normal PostgreSQL execution path. A warm shared_buffers cache and a warm row cache are therefore different experimental conditions.
Write down the result contract first
Compare the same keys, columns and output shape. If the application needs only two columns, comparing that SQL projection with serialized whole rows measures different work. If callers expect duplicates, input order and a null result for each missing key, include that alignment work in every client.
The retained Node.js example source
contains the prepared ANY baseline and a helper that restores input positions.
The ordered WITH ORDINALITY example
shows SQL-side position handling. Neither requires the extension.
Change one workload dimension at a time
| Experiment | What to hold fixed | What it reveals |
|---|---|---|
| Warm repeated reads | Keys, result shape, connections | Reuse of already populated entries |
| Cold or missing keys | Request distribution and batch size | Source-table and negative-result costs |
| Larger batches | Total requested keys and payload shape | Round-trip savings versus per-key work |
| Concurrent writes | Read/write mix and transaction boundaries | Invalidation, refill and visibility costs |
| Wider rows | Key distribution and client placement | Serialization, transport and row-size bypass |
An ineligible read can legitimately use the source table. Inspect counter
deltas around each experiment; a low hit rate alone does not diagnose a broken
installation. Keep SQL and RESP counters separate. The
technical reference describes
local_cache.stats() and local_cache.health().
Use the shared runner, then inspect the evidence
After the quickstart, run the repository’s comparison:
./examples/benchmark.sh all > comparison.json
python3 scripts/benchmark_report.py comparison.json
The benchmark guide lists prerequisites, workload controls and metrics. Keep the raw JSON. Record the extension and harness revisions, PostgreSQL version, machine, connection count and client placement. Compare repeated runs, latency distributions and server resource use alongside throughput. A short correctness smoke run is not a publishable speed result.
Decide from the application boundary
Prepared SQL and RESP MGET use different read paths and result handling. A gain for one does not establish a gain for the other. Benchmark each against your application’s actual request pattern; no single result predicts performance for every workload.
Keep ordinary SQL when joins, projections, locking or unsupported table shapes are required, or when the cache brings no measured benefit. For repeated whole-row reads by primary key, test RESP MGET with the same client work your application actually performs. Continue with the caching decision guide and the invalidation experiment.