pg_local_cache
English

Transaction-aware cache invalidation in PostgreSQL

Deleting a cache entry is not enough if an earlier read can refill it after the deletion. Suppose a reader starts loading an old row, a writer commits a new value and invalidates the key, and then that earlier loader publishes its result. A cache needs to reject that late publication as well.

The 2.0 implementation fences affected keys or relations on the database write path. A fill carries generation information so it can be rejected after an invalidation. Cached positive entries also carry tuple visibility information. An ineligible entry falls back to a source-table read. See the technical reference for the contract.

What readers see during an update Under READ COMMITTED, start with revision zero. A transaction updates it to one. The writer sees one through a source-table read, while another session still sees zero. After COMMIT, a new statement in the other session sees one. After ROLLBACK, it still sees zero. Committed rowrevision = 0 BEGIN; UPDATE Transaction open Writer reads 1 Other session reads 0 COMMIT ROLLBACK Next readrevision = 1 Next readrevision = 0
READ COMMITTED, starting from revision 0. “Next read” means a new statement in the other session after COMMIT or ROLLBACK. The writer reads its own change through the source table.

Test with two sessions

Start the local demo. Open this command in two terminals:

docker compose -f examples/compose.yaml exec postgres \
  psql -X -v ON_ERROR_STOP=1 -U demo -d pglc_demo

In session A, read row 42 and note its revision, then read it again to warm it:

SELECT (local_cache.mget('public.items'::regclass, ARRAY[42]::bigint[]))[1]::jsonb ->> 'revision';
SELECT (local_cache.mget('public.items'::regclass, ARRAY[42]::bigint[]))[1]::jsonb ->> 'revision';

In session B, update the row but leave the transaction open:

BEGIN;
UPDATE public.items SET revision = revision + 1 WHERE id = 42;
SELECT (local_cache.mget('public.items'::regclass, ARRAY[42]::bigint[]))[1]::jsonb ->> 'revision';

B sees its own increment. This read bypasses the cache. Repeat A’s query while B remains open: A must still see the committed revision, not B’s uncommitted value. In B, run ROLLBACK; another query in A must still return the original revision.

Now run in B:

BEGIN;
UPDATE public.items SET revision = revision + 1 WHERE id = 42;
COMMIT;

A query started in A after that commit must return the incremented revision. This is the relevant boundary: an older running statement is not required to switch to a snapshot taken after it started. PostgreSQL documents that behavior under Read Committed.

The executable Node.js test asserts these observations with separate connections.

Cases that deliberately bypass the cache

REPEATABLE READ, SERIALIZABLE, recovery, parallel execution, and transactions that have written mapped data use the source-table path. An oversized row may be returned successfully without being cached. A cache hit rate near zero is not necessarily a failed installation: check the workload and bypass counters.

When the application needs SELECT ... FOR UPDATE, use the ordinary PostgreSQL operation; mget does not replace row locking.

Inspect the cause of a miss

Use local_cache.stats() and local_cache.health() as an administrator. Compare counter snapshots before and after a controlled test. Keep SQL mget counters separate from RESP counters. After intentional DDL, follow the documented reconcile_table or reconcile_all procedure instead of assuming a previously attached mapping still describes the changed table.

Updated .