Contents

• pg_local_cache: PostgreSQL row cache
  • Try without changing an existing database
  • Measure before adopting
  • Read whole rows by primary key
  • Consistency and workload fit
  • Install on an existing server
  • Optional RESP2 endpoint
  • Develop

pg_local_cache: PostgreSQL row cache

Version 2.0: explicit SQL mget, bounded shared memory, and transaction-aware invalidation for PostgreSQL 14-18.

Cache whole rows by their complete primary key. PostgreSQL remains the source of truth; ordinary writes invalidate affected entries. Ordinary SELECT queries are not rewritten. An optional, limited RESP2 endpoint shares the cache.

Documentation | Try locally | Benchmarks | Installation | Technical reference

Try without changing an existing database

With Docker Compose, from this repository:

docker compose -f examples/compose.yaml up --build --wait

The demo builds extension 2.0.1 from a pinned commit. It binds PostgreSQL to loopback port 55432, keeps disposable data in tmpfs, and mounts no host database. Follow the quickstart to run queries and remove it.

With Node.js 20 or later, check the result contract and concurrent writes:

npm --prefix examples/node-postgres ci --ignore-scripts
npm --prefix examples/node-postgres run demo

Measure before adopting

The benchmark compares mget with a prepared WHERE id = ANY($1) query through the same driver. Both paths return ordered whole-row objects, including duplicates and missing positions. It covers warm reads, cold cache fills, reads mixed with updates, and write overhead.

npm --prefix examples/node-postgres run --silent benchmark > benchmark.json
python3 scripts/benchmark_report.py benchmark.json

No reference speedup is claimed without a recorded run. The report includes requests/s, requested keys/s, read/write p50/p95/p99, cache counters, and the configuration. CI checks the harness; shared-runner timings are not a capacity estimate. See row caching vs shared_buffers for the work a hit can avoid and the work it still does.

Read whole rows by primary key

Attach a permanent table with a supported primary key:

SELECT local_cache.attach_table('public.items'::regclass);

Fetch an ordered text[] of JSON rows:

SELECT local_cache.mget(
  'public.items'::regclass,
  ARRAY[42, 7, 42, NULL]::bigint[]
);

The result preserves order, duplicates, and NULL positions. Missing rows also return NULL. A batch can contain at most 1,024 keys. Use unnest(...) to display one array element per result row; the function itself does not return a set.

Composite keys use text[][] in primary-key column order:

SELECT local_cache.mget(
  'public.tenant_items'::regclass,
  ARRAY[['tenant-a', '42'], ['tenant-b', '7']]::text[][]
);

Grant only the required access:

GRANT SELECT ON public.items TO app_user;
GRANT USAGE ON SCHEMA local_cache TO app_user;
GRANT EXECUTE ON FUNCTION local_cache.mget(regclass, anyarray) TO app_user;

Writes remain ordinary PostgreSQL:

UPDATE public.items SET value = 'new' WHERE id = 42;

See the Node.js example for parameter binding and result decoding, and the invalidation walkthrough for commit, rollback, and read-your-writes.

Consistency and workload fit

Each cache hit is checked against mapping, relation, transaction, row, and snapshot state. REPEATABLE READ, SERIALIZABLE, recovery, parallel execution, and transactions that wrote mapped data bypass the cache. Oversized or unsafe entries fall back to an indexed source-table read.

Worth measuring	Keep using PostgreSQL directly for
Repeated complete primary-key reads	Joins, ranges, aggregates, and full scans
A hot set of whole rows that fits the cache	Arbitrary query-result caching
READ COMMITTED on one writable primary	RLS, partitioned, or inherited tables
An application that can call mget explicitly	Queries that need locking or have no measured benefit

The extension is not a Redis replacement. It provides no TTL, pub/sub, or distributed coordination. SQL mget still uses a PostgreSQL connection.

Install on an existing server

Supported binary target: Linux amd64, PostgreSQL 14-18, glibc or musl. First activation adds pg_local_cache to shared_preload_libraries and requires one controlled PostgreSQL restart.

For a local cluster managed by pg_ctl:

curl -fsSL https://github.com/profundium/pg_local_cache/releases/latest/download/install-latest.sh | bash -s -- app

Replace app with the database name. This command installs and restarts; it is not the disposable demo. Use the installation guide for systemd, Patroni, Kubernetes, checksum-first installation, source builds, RESP, or recovery.

Minimum SQL-only configuration:

shared_preload_libraries = 'pg_local_cache'
pg_local_cache.database = 'app'
pg_local_cache.role = 'local_cache_worker'
pg_local_cache.cache_entries = 16384
pg_local_cache.memory_budget_mb = 384
pg_local_cache.port = 0

Preserve existing preload entries. Size cache entries, relation states, clients, workers, and the memory budget together before restart. Manual source installs also need the role and metadata grants before attaching a table, even when RESP is disabled.

Useful administration functions:

SELECT local_cache.health();
SELECT local_cache.stats();
SELECT local_cache.reconcile_table('public.items'::regclass);
SELECT local_cache.detach_table('public.items'::regclass);

Optional RESP2 endpoint

RESP2 supports authenticated, bounded MGET, SET, DEL, and scoped invalidation. Workers run under one configured PostgreSQL role; network clients do not inherit individual PostgreSQL ACLs. Keep the listener on loopback or behind authenticated TLS, and prefer pg_local_cache.auth_token_file over an inline token. See the technical reference.

Develop

Source builds use PostgreSQL’s PGXS toolchain and the target server’s headers. Follow the source-build procedure.

make verify-static source-test
make docker-smoke
node --test examples/node-postgres/queries.test.mjs

Report a workload | Releases | Contributing

License: MIT.