Contents

• pg_local_cache
  • Product boundary
  • Capabilities
  • Docker quick start
  • SQL API
  • Install on an existing server
  • Optional RESP2 endpoint
  • Benchmarks
    • Ordinary SQL and RESP comparative smoke
    • Explicit SQL GET/MGET profile
  • Monitoring
  • Releases
  • Current limits
  • Documentation

pg_local_cache

pg_local_cache is a PostgreSQL 14–18 extension for repeated primary-key reads. It keeps hot whole rows in bounded PostgreSQL shared memory and can transparently accelerate supported ordinary SELECT statements after a table is attached.

PostgreSQL remains the source of truth. A cache miss, unsupported query shape, unsafe transaction state, malformed entry, or oversized row runs the original primary-key index plan. Source writes publish transaction-aware invalidation fences before commit visibility, and rollback never exposes uncommitted data.

Applications keep their existing PostgreSQL driver or ORM. The transparent SQL path needs no separate cache process, token, cache client, or proprietary query syntax. Installation is not zero-touch: the extension uses shared_preload_libraries, allocates a bounded cache at postmaster startup, and requires one controlled restart for first activation.

Product boundary

Good fit	Not the product
Repeated exact-primary-key reads whose hot working set fits the configured cache.	A general query or result cache for joins, ranges, aggregates, arbitrary predicates, or full-table scans.
Applications that should keep ordinary PostgreSQL row types, ACLs, drivers, and ORM queries.	A universal Redis/Valkey replacement, distributed cache, pub/sub system, TTL store, or multi-primary coordination layer.
A single writable primary where transaction-aware invalidation is more valuable than maximum standalone cache throughput.	A way to avoid PostgreSQL operations: preload, restart planning, memory sizing, monitoring, and table attachment are still required.

Unsupported or unsafe reads fall back to PostgreSQL rather than returning a partial cached answer. Current packaged builds target PostgreSQL 14–18 on Linux amd64 (glibc or musl), one configured database, and one writable primary.

Capabilities

Capability	Behavior
Transparent SQL fast path	Supported exact-primary-key and bounded single-column IN/ANY reads preserve ordinary row, projection, and ACL semantics.
Explicit JSON API	local_cache.get() and local_cache.mget() provide whole-row JSON for callers that want a cache-shaped API.
Source-plan fallback	Unsupported, unsafe, missing, malformed, or oversized entries execute PostgreSQL’s retained source plan.
Whole rows	Each entry stores one versioned PostgreSQL composite row.
Transactional invalidation	INSERT, UPDATE, DELETE, and TRUNCATE fence affected entries before commit visibility.
Bounded extension memory	Entry capacity, client slots, and deterministic extension allocations are fixed at startup.
Optional RESP2	Trusted internal clients can use authenticated whole-row GET, SET, and DEL.
Operations	SQL metrics, health checks, Prometheus rules, and a Grafana dashboard are included.

Docker quick start

Requirements: Docker with Compose v2 and OpenSSL.

git clone https://github.com/profundium/pg_local_cache.git
cd pg_local_cache

install -d -m 0700 secrets
openssl rand -base64 36 | tr -d '\n' > secrets/postgres_password
chmod 0600 secrets/postgres_password

docker compose -f compose.sql-only.yaml \
  up --detach --build --wait postgres

Open psql:

docker compose -f compose.sql-only.yaml \
  exec postgres psql --username postgres --dbname app

Create and attach a table:

CREATE TABLE public.items (
    id bigint PRIMARY KEY,
    value text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    metadata jsonb
);

INSERT INTO public.items VALUES
    (1, 'hello', true, '{"source":"postgres"}');

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

Use ordinary PostgreSQL SQL. Select every column or only the columns needed by the caller:

SELECT * FROM public.items WHERE id = $1::bigint;

SELECT value, metadata FROM public.items WHERE id = $1::bigint;

SELECT * FROM public.items WHERE id IN (1, 7, 42);

SELECT value, metadata
FROM public.items
WHERE id = ANY($1::bigint[]);

Supported exact-primary-key reads and bounded single-column primary-key batches can use Custom Scan (pg_local_cache_sql); result rows and projection remain ordinary PostgreSQL:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM public.items WHERE id = 1;

SELECT local_cache.health();
SELECT * FROM local_cache.metrics();

SQL API

local_cache.attach_table() discovers the complete primary key, records a whole-row mapping, and installs extension-owned invalidation triggers:

BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT local_cache.attach_table('public.items'::regclass);
COMMIT;

The canonical tuple API is an ordinary exact-primary-key query:

SELECT * FROM public.items WHERE id = 42::bigint;
SELECT metadata FROM public.items WHERE id = 42::bigint;

Composite primary keys use normal SQL predicates, in any order:

SELECT * FROM public.tenant_items
WHERE item_id = 42::bigint AND tenant_id = 'tenant-a';

For a single-column primary key, ordinary SQL can read a set of keys without calling a cache function:

SELECT * FROM public.items WHERE id IN (42, 7, 99);

SELECT id, value
FROM public.items
WHERE id = ANY($1::bigint[]);

This remains a PostgreSQL row set: duplicate and NULL array elements do not create duplicate rows, ordering is not implied, and a missing key contributes no row. Use local_cache.mget() only when the caller needs an ordered JSON array aligned with its input positions.

KV-style callers can opt into the JSON scalar and ordered batch functions:

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

The functions are SECURITY INVOKER: the caller still needs SELECT on the source table. Ordinary tuple reads need no local_cache schema or function grant. Writes remain ordinary PostgreSQL DML, so a transaction can update a row and immediately read its own value; commit invalidates the old entry and rollback never publishes the new one.

The SQL fast path accepts:

• one attached permanent table without inheritance, partitioning, or RLS;
• equality predicates for every primary-key column, including composite keys;
• or IN / = ANY(array) on a single-column primary key;
• constants or external parameters, including an external array parameter;
• SELECT * or direct column projections, including aliases and reordered projections;
• no limit, or a constant LIMIT 1 for scalar lookup; array lookup has no LIMIT.

The transparent array path is deliberately all-or-nothing. It accepts at most 1,024 input elements and 16 MiB of query-local copied tuple data. If any key is missing, snapshot-ineligible, malformed, or outside those bounds, PostgreSQL executes the original full IN/ANY index plan. Cached and source rows are never merged partially. Composite tuple IN, additional filters, ALL, and non-equality operators use PostgreSQL’s normal plan. So do REPEATABLE READ, SERIALIZABLE, recovery, and reads after the current transaction writes an attached table. A nonexistent key returns the normal empty SQL result after consulting the source table.

Application roles need only their normal source-table privileges to benefit from a transparent cached SELECT. Administrative functions are separate:

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

See the technical reference for the exact planner, snapshot, and type rules.

Install on an existing server

Choose your PostgreSQL major and Linux libc, then download the exact latest asset with curl—no GitHub CLI or tag lookup:

PG_MAJOR=18
LIBC=glibc # glibc or musl
BASE=https://github.com/profundium/pg_local_cache/releases/latest/download

curl -fLO "$BASE/pg_local_cache-pg${PG_MAJOR}-linux-${LIBC}-amd64.tar.gz"
curl -fLO "$BASE/SHA256SUMS"
sha256sum --check --ignore-missing --strict SHA256SUMS
tar -xzf "pg_local_cache-pg${PG_MAJOR}-linux-${LIBC}-amd64.tar.gz"
cd "pg_local_cache-"*-"pg${PG_MAJOR}-linux-${LIBC}-amd64"
sudo ./install.sh preflight --database app --mode sql-only
sudo ./install.sh install --database app --mode sql-only

Use glibc for Debian, Ubuntu, RHEL-family and similar systems; use musl for Alpine. The installer rejects a PostgreSQL major, OS, architecture, or libc mismatch before copying files. Use pg_local_cache-source.tar.gz and the target server’s PGXS when a packaged binary does not match.

The existing-database install guide covers checksum verification, read-only preflight, online staging, restart, HA, verification, and rollback.

The first installation requires one restart because shared_preload_libraries is evaluated at postmaster startup. File staging and configuration validation stay online. The installer’s 30-second setting is a warning target; actual interruption depends on shutdown, recovery, and client reconnection.

Optional RESP2 endpoint

SQL-only mode sets pg_local_cache.port=0 and starts no RESP workers. RESP mode uses the same shared cache and invalidation machinery, but has a separate security boundary: one worker role and one shared token cover every accepted mapping, with no TLS or per-client PostgreSQL ACL context.

Keep the listener on loopback or behind an authenticated TLS proxy. A whole-row key has this form:

CRUD:database.schema.table:{"pk_column":<json-scalar>,...}

GET returns the complete row as JSON and reads the source table on a cache miss. Writable mappings expose PostgreSQL-backed SET and DEL. See the wire API and compatibility boundary and the existing-server RESP setup.

Benchmarks

The project reports different interfaces separately. A key resolved inside a 32-key batch is not presented as one SQL statement, and RESP throughput is not used to imply ordinary-SQL throughput.

Ordinary SQL and RESP comparative smoke

CI run 31172234073 for source 71b0aa3 passed every gate after full-working-set stabilization:

Measured lane	pg_local_cache	Comparison target	Relative result
Ordinary SELECT * by complete primary key	123,707 statements/s	65,867 stock PostgreSQL statements/s	1.88x
Reordered direct-column projection	118,679 statements/s	63,952 stock PostgreSQL statements/s	1.86x
Reordered composite-PK predicates	126,550 statements/s	68,746 stock PostgreSQL statements/s	1.84x
Ordinary 32-key SELECT ... IN (...)	865,201 key ops/s; 27,038 statements/s	328,282 key ops/s; 10,259 statements/s	2.64x
Warm RESP2 GET	143,104 ops/s	194,910 Valkey; 197,522 Redis ops/s	0.72x Redis

The RESP row is intentional rather than hidden: in this run, dedicated Valkey and Redis were 1.36–1.38x faster on raw warm GET. The product’s differentiator is transaction-aware PostgreSQL integration and ordinary SQL compatibility, not a claim to beat a dedicated in-memory server at its native protocol.

This was a one-second, one-repetition shared-runner regression smoke with four clients, pipeline depth eight, 128 keys per attached table, 256 cache entries, and two CPU cores per server target. It is evidence that the fast paths work and remain faster in that exact profile, not a production capacity estimate.

Explicit SQL GET/MGET profile

A separate CI run 30803546805 for source fe2d23c measured local_cache.mget() at 64,954 prepared and 66,156 unnamed-extended key ops/s: 10.30x and 10.39x the stock PostgreSQL batch query in that specific 32-key JSON workload. This explicit API and workload are not directly comparable with ordinary SELECT, statements/s, or RESP GET.

See the benchmark methodology and exact commands, the scenario definitions, the latest raw ordinary-SQL/RESP evidence, and the preserved SQL GET/MGET evidence.

Monitoring

local_cache.metrics() exposes typed cache, memory, worker, client, invalidation, backpressure, and mapping counters. The optional stack adds postgres_exporter, Prometheus rules, container memory signals, and a provisioned Grafana dashboard. Start with the monitoring and OOM guide.

Releases

Download source, the platform-labelled binary, checksums, and CI evidence from the latest stable release. Older reviewed versions remain available on the releases page.

Current limits

• PostgreSQL 14–18 on Linux amd64 (glibc or musl). PostgreSQL 14 support follows upstream through November 12, 2026.
• One configured database and one writable primary per extension instance.
• Permanent, non-partitioned tables with a supported primary key; no views, inheritance, or RLS.
• Encoded cache entries are limited to 8 KiB; oversized rows use PostgreSQL.
• Transparent single-column IN/ANY accepts at most 1,024 elements and 16 MiB of query-local tuple copies; larger or mixed-safe batches use PostgreSQL.
• At most 128 mappings and 16 primary-key columns per mapping.
• No TTL, Redis Cluster, Lua, Pub/Sub, multi-primary, or standby cache serving.
• RESP authentication is a shared-token boundary, not PostgreSQL user authentication.

Documentation

• Install on an existing PostgreSQL server
• SQL, consistency, security, and configuration reference
• Benchmarks and latency methodology
• Monitoring and OOM protection
• Benchmark scenarios