pg_local_cache

Overview

PG14-18; requires preload.

Version

Build

You can build the RPM / DEB packages for pg_local_cache using pig build:

pig build pkg pg_local_cache         # build RPM / DEB packages

Install

You can install pg_local_cache directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_local_cache;          # Install for current active PG version

pig ext install -y pg_local_cache -v 18  # PG 18
pig ext install -y pg_local_cache -v 17  # PG 17
pig ext install -y pg_local_cache -v 16  # PG 16
pig ext install -y pg_local_cache -v 15  # PG 15
pig ext install -y pg_local_cache -v 14  # PG 14

dnf install -y pg_local_cache_18       # PG 18
dnf install -y pg_local_cache_17       # PG 17
dnf install -y pg_local_cache_16       # PG 16
dnf install -y pg_local_cache_15       # PG 15
dnf install -y pg_local_cache_14       # PG 14

apt install -y postgresql-18-pg-local-cache   # PG 18
apt install -y postgresql-17-pg-local-cache   # PG 17
apt install -y postgresql-16-pg-local-cache   # PG 16
apt install -y postgresql-15-pg-local-cache   # PG 15
apt install -y postgresql-14-pg-local-cache   # PG 14

Preload:

shared_preload_libraries = 'pg_local_cache';

Create Extension:

CREATE EXTENSION pg_local_cache;

Usage

Sources:

• README.md
• pg_local_cache.control
• sql/pg_local_cache–3.1.0.sql
• sql/pg_local_cache–2.0.4–3.0.0.sql
• docs/UPGRADING.md
• CHANGELOG.md
• 3.0.0 to 3.1.0 upgrade

pg_local_cache 3.1.0 caches whole rows by their complete primary key in bounded shared memory. Cached reads now use authenticated RESP MGET; SQL local_cache.mget is removed and ordinary SELECT queries are not rewritten. PostgreSQL remains the durable source of truth.

Core Workflow

shared_preload_libraries = 'pg_local_cache'
pg_local_cache.database = 'app'
pg_local_cache.role = 'local_cache_worker'
pg_local_cache.bind_address = '127.0.0.1'
pg_local_cache.port = 6380
pg_local_cache.auth_token_file = '/secure/path/token'

CREATE EXTENSION pg_local_cache;
CREATE TABLE public.items (id bigint PRIMARY KEY, value text);
INSERT INTO public.items VALUES (42, 'example');
SELECT local_cache.attach_table('public.items'::regclass);
SELECT local_cache.health();

After authenticating the RESP connection:

MGET CRUD:app.public.items:{"id":42} CRUD:app.public.items:{"id":7}

Operational Boundaries

Append the library to existing preload entries, provision a dedicated worker role and its documented metadata/table grants, secure the token file, and restart. A superuser creates the extension in fixed schema local_cache. Attach only supported permanent primary-key tables. RESP MGET preserves order and duplicate keys and returns null for missing rows. Workers use the configured database role, not the client’s SQL privileges, transaction or snapshot. Keep joins, projections, row locks and session-sensitive reads in ordinary SQL.

local_cache.attach_table installs invalidation triggers; local_cache.detach_table removes a mapping; local_cache.reconcile_table revalidates it after DDL or privilege changes. local_cache.health, local_cache.stats and local_cache.metrics report readiness and resource use. Normal PostgreSQL writes invalidate affected cache entries. RLS, partitioned and inherited tables are outside the documented workload. There is no TTL or distributed cache coordination.

Supports PostgreSQL 14–18 on one writable primary. Non-loopback listeners require pg_local_cache.tls or explicit pg_local_cache.allow_plaintext_network opt-in. TLS uses separate certificate/key settings; a CA setting enables mutual TLS. The reloadable pg_local_cache.enabled switch disables caching.

Before upgrading from 2.x, move SQL MGET callers to the RESP authorization model. Install matching 3.1.0 files, configure listener security, restart, confirm the library version and listener readiness, then run ALTER EXTENSION pg_local_cache UPDATE in each affected database. Dependencies on removed functions or the replaced metrics return type can block migration; the script deliberately avoids CASCADE. No downgrade script is supplied.

Upgrade from 3.0.0 to 3.1.0

Version 3.1.0 changes the shared-memory layout and fixes stale-read races and worker memory leaks. Restart PostgreSQL after installing the matching library, then update the extension in each database; the SQL migration itself changes no objects or attached-table mappings. Verify pg_local_cache.binary_version and local_cache.health() afterward.

pg_local_cache.cache_entries now counts descriptors, while byte capacity comes from slab storage. Review pg_local_cache.lock_partitions, dirty-marker limits, the new pg_local_cache.max_deferred_misses, client slots and the process file-descriptor limit before restarting. MGET deduplicates misses, but single-flight waiters can use later transactions; a complete response is not guaranteed to share one snapshot.