PostgreSQL cache invalidation: the late-fill race
Walk through a cache-aside race where an old read refills a deleted key after commit. Understand publication fencing, snapshots, rollback and request-local caches.
Read articleA row cache
inside PostgreSQL.
Read rows by primary key with SQL mget or RESP. PostgreSQL writes automatically invalidate affected cache entries.
Explicit SQL API · PostgreSQL 14–18 · Open source
Measured locally · 15 Sep 2026
839,678requests/s · RESP MGET
3.31× the prepared SQL throughput
in this single-key comparison
- Prepared SQL
- 253,790 req/s
- SQL mget
- 186,296 req/s
Warm cache, 1 key/request. Median of 3 × 5 seconds.
SQL mget was slower here; batch results differ.
One call. Two read paths.
An eligible hit returns the stored row. A miss or bypass reads the source table. Your ordinary SELECT queries keep their existing path.
-- Attach your table once.
SELECT local_cache.attach_table('public.items');
-- Read rows by primary key.
SELECT local_cache.mget(
'public.items', ARRAY[42, 7]::bigint[]
);SQL mget
Up to 1,024 keys per call. Order, duplicates, and NULL positions are preserved.
Ordinary writes
INSERT, UPDATE, and DELETE invalidate affected entries.
Fixed capacity
Allocate the shared row cache at PostgreSQL startup.
Snapshot checks
Ineligible reads use the source table under PostgreSQL visibility rules.
Run the same test.
On every client.
Node.js and Go. Prepared SQL, SQL mget, and RESP MGET.
One runner uses the same keys, batch sizes, connection counts, duration, and result checks. Compare throughput, latency, and PostgreSQL CPU and memory on your machine.
Run the comparisonIs this your workload?
Choosing a cache? Compare PostgreSQL pages, rows, materialized views, and Redis.
Worth measuring
- Repeated complete primary-key lookups.
- A small hot set of whole rows.
- READ COMMITTED on one writable primary.
- An application that can call the explicit SQL API.
Keep ordinary SQL
- Joins, ranges, aggregates, or arbitrary query results.
- RLS, partitioned, or inherited tables.
- A database where you cannot install a native extension.
- Workloads without a measured benefit.
Run a local demo.
Clone the repository and start a disposable PostgreSQL server with sample rows. Docker builds the extension and keeps the demo separate from your databases.
Git and Docker Compose required
git clone https://github.com/profundium/pg_local_cache.git
cd pg_local_cache
docker compose -f examples/compose.yaml up --build --waitRead sample rows and check cache hits. Already cloned the repository? Run the last command from its root. For an existing server, use the installation guide.
Installing on an existing server requires a PostgreSQL restart.
Documentation
Try pg_local_cache locallyRun pg_local_cache 2.0 in disposable PostgreSQL, read sample rows, inspect cache hits, test updates, and remove the demo without changing an existing database.→
PostgreSQL cache benchmarksMeasured pg_local_cache results with Node.js, Go and RESP on Apple M3 Max. Includes the machine, PostgreSQL CPU, memory and methodology.→
PostgreSQL caching decision guideChoose PostgreSQL page caching, prepared SQL, whole-row caching, materialized views, or an external cache by the work you need to avoid.→
PostgreSQL and Redis cache-asideUse PostgreSQL as the source of truth with a Redis cache-aside path, understand stale-read races, and see where pg_local_cache fits.→
Batch PostgreSQL primary-key lookupsReplace N+1 primary-key reads with one parameterized PostgreSQL query, preserve input positions when needed, and compare the explicit pg_local_cache mget path.→
PostgreSQL row cache vs shared_buffersCompare PostgreSQL page caching with pg_local_cache 2.0 whole-row caching. See what a row-cache hit avoids, what it still costs, and when not to add another cache.→
Transaction-aware cache invalidation in PostgreSQLTest pg_local_cache 2.0 invalidation with concurrent PostgreSQL sessions. Check uncommitted updates, read-your-writes, rollback, committed reads, and fallback rules.→
Batch row lookups with node-postgresUse pg_local_cache 2.0 from Node.js with a parameterized bigint array and JSON transport. Preserve order and nulls, and compare with a prepared ANY query.→
Batch row lookups with Go and pgxUse pg_local_cache from Go with pgx, parameterized keys, and decoded JSON rows.→
Connect over RESPRead PostgreSQL rows over RESP2 with redis-cli or Node.js. Includes authentication, client settings, runnable examples and cleanup.→
Install pg_local_cache on PostgreSQL 14-18Install the pg_local_cache PostgreSQL extension with verified Linux binaries or PGXS, then configure preload, restart, verify, and recover safely.→
pg_local_cache technical referenceTechnical reference for pg_local_cache SQL mget, transaction-aware invalidation, bounded PostgreSQL shared memory, monitoring, and optional RESP2.→
Practical notes on PostgreSQL caching
When a PostgreSQL row cache helps: measure the whole read
Design a fair PostgreSQL row-cache comparison using prepared SQL, SQL mget and RESP MGET. Separate warm reads, misses, batch sizes, writes and client costs.
Read articleBatch PostgreSQL reads without losing order or missing keys
Replace N+1 primary-key queries while preserving duplicate IDs, input order, NULL positions and missing rows. Compare ANY, WITH ORDINALITY and SQL mget.
Read articleBefore you try it
Does this replace shared_buffers?
No. PostgreSQL caches database pages. This extension separately caches serialized whole rows by primary key. See the read-path comparison.
Does it cache ordinary SELECT queries?
No. Only explicit local_cache.mget calls use the SQL cache. Your existing queries keep PostgreSQL's normal execution path.
Does it replace Redis?
No. The optional RESP2 endpoint is limited. There is no general-purpose Redis command set, TTL, pub/sub, or distributed coordination. Compare the PostgreSQL and Redis cache-aside paths.
How do I check updates and rollback?
The invalidation guide includes a two-session test. The runnable example checks uncommitted writes, read-your-writes, rollback, and committed updates.