layout: doc title: Transaction-aware cache invalidation in PostgreSQL seo_title: “PostgreSQL Cache Invalidation: Commit and Rollback | pg_local_cache” description: Test pg_local_cache 2.0 invalidation with concurrent PostgreSQL sessions. Check uncommitted updates, read-your-writes, rollback, committed reads, and fallback rules. section: Cache invalidation permalink: /docs/cache-invalidation.html

last_modified_at: “2026-09-14”

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.

{% include diagrams/transaction.html id=“invalidation-transaction” %}

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. It is a regression example, not an exhaustive proof of concurrent correctness. Broader integration tests remain in the repository’s tests directory.

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. This example does not test replica failover, custom trigger configurations, or untested extension combinations.

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.