layout: doc title: PostgreSQL primary-key cache benchmarks seo_title: “PostgreSQL Row Cache Benchmarks: mget vs Batched SQL” description: Reproduce pg_local_cache 2.0 benchmarks against a prepared PostgreSQL ANY query, including warm reads, cold fills, concurrent updates, latency, and write overhead. section: Benchmarks permalink: /docs/BENCHMARKS.html

last_modified_at: “2026-09-14”

PostgreSQL primary-key cache benchmarks

Compare the explicit local_cache.mget API with one prepared, batched PostgreSQL query. Both paths return the same ordered JavaScript objects, including duplicate keys and null positions. Neither baseline sends a separate network request for each key.

No reference performance result is published yet. The commands below produce a measured report on your machine. CI runs are correctness checks on shared runners, not production capacity estimates.

Run

Start the disposable demo, then run from the repository root:

npm --prefix examples/node-postgres ci --ignore-scripts
CLIENTS=4 REQUESTS=2000 REPEATS=3 BATCHES=1,16,64 \
  npm --prefix examples/node-postgres run --silent benchmark > benchmark.json
python3 scripts/benchmark_report.py benchmark.json > benchmark.md

REQUESTS is the total number of requests per sample, not a per-client count. Cold-fill samples instead visit all 4,096 demo rows exactly once. With a batch of 64 that is only 64 latency observations; do not treat its p99 as a stable tail estimate.

The runner uses only the loopback demo connection, checks the database and table marker, and rejects a non-2.0 extension. It resets the demo rows between samples. It does not accept an arbitrary production DATABASE_URL.

Exact read queries

Path Query sent to PostgreSQL Client work included in timing
SQL mget SELECT local_cache.mget('public.items'::regclass, $1::bigint[]) AS rows Decode the text array and parse each JSON row
Prepared SQL baseline SELECT id::text AS key, row_to_json(i)::text AS row FROM public.items AS i WHERE id = ANY($1::bigint[]) Restore input order, duplicates, and missing positions, then parse each JSON row

Both queries use named prepared statements through node-postgres. Read and UPDATE statements are prepared on each connection before timing. The baseline reads the same attached table through ordinary PostgreSQL; attachment does not rewrite its SELECT. The JSON baseline matches the cache’s whole-row contract. If your application needs only two columns, also measure its existing projection without whole-row JSON conversion.

Source: queries.mjs and benchmark.mjs.

Workloads

Sample Dataset and operation What to inspect
Warm reads Repeated reads of 128 rows; 1,024 cache slots Read latency, requested keys/s, and actual cache hits
Cold fill Each of 4,096 rows visited once after cache invalidation Miss/fill cost; the source pages are already warm
Mixed reads and writes Every twentieth request is an UPDATE; remaining requests read the hot set Separate read/write latency and cache-counter deltas
Writes, unattached UPDATE a separate copy without cache triggers Write baseline
Writes, attached The same UPDATE against the attached table Cost of cache invalidation on writes

The update is UPDATE <table> SET revision = revision + 1 WHERE id = $1. The runner selects from two fixed table names; no user-controlled identifier is interpolated. Both tables begin each sample with the same rows and revisions. Read-path order and write-table order alternate between repetitions.

Recorded output

benchmark.json records extension and harness revisions separately, the PostgreSQL and Node.js versions, client OS/architecture/CPU, visible CPU count, cache settings, row counts, concurrency, and every sample. Retain the JSON, not just the rendered summary. Dependencies are pinned by the committed lockfile; retain that file with results too.

Each sample includes elapsed time, completed requests/s, requested read keys/s, read and write p50/p95/p99, observation counts, and SQL cache-counter deltas. Requested keys/s includes duplicates and missing keys; it is not a count of unique rows returned. A mixed sample’s requests/s includes both reads and writes. The report keeps repetitions separate rather than averaging their percentiles.

These are client-observed timings over loopback TCP, including transfer, driver decoding, and JSON parsing. They are not executor-only timings. The fixed number of concurrent clients is a closed-loop load: a client sends its next request after the previous one finishes. This does not model an independently arriving production request stream or correct for coordinated omission.

What this does not establish

This small dataset does not establish behavior for large rows, a skewed production key distribution, long transactions, network RTT, pool exhaustion, replica reads, crash recovery, or sustained write-heavy traffic. The runner does not measure server CPU consumption or peak resident memory; the configured memory budget is not a measured RSS value. Docker Desktop also includes a VM.

Use a dedicated host for a reference result. Record CPU and memory limits, PostgreSQL image ID, filesystem, container/VM details, and whether client and server share CPU resources. Keep all repetitions, including slower ones. Check query plans and cache counters before attributing any difference to the cache.

To share a result, open a workload report with the JSON, configuration, and the workload you actually need to support.