pg_local_cache technical reference

This document describes the implementation boundaries that affect correctness, capacity planning, security, and client compatibility. For task-oriented instructions, use these guides:

• README and Docker quick start
• Install on an existing PostgreSQL server
• Benchmark and latency methodology
• Monitoring and OOM signals
• Benchmark scenario definitions

Product boundary

pg_local_cache is an in-process PostgreSQL row cache, not a general query result cache. It stores complete table rows addressed by a validated primary key. The planner substitutes only narrow exact-key or bounded single-column IN/ANY shapes and retains the original PostgreSQL index path as the fallback child plan.

It does not cache joins, ranges, aggregates, arbitrary predicates, or full-table results. It does not provide TTLs, pub/sub, clustering, multi-primary cache coordination, or standby cache serving. The optional RESP endpoint exposes the same rows for trusted internal clients, but the product is not positioned as a raw-throughput replacement for Redis or Valkey.

The trade-off is operational: applications can keep ordinary PostgreSQL SQL, row types, ACLs, drivers, and ORM mappings, but the database operator must load a shared library at postmaster startup, reserve bounded shared memory, perform one controlled restart for first activation, attach eligible tables, and monitor the additional in-process state.

The extension and its GUC prefix are named pg_local_cache. User-facing SQL objects are in the local_cache schema, and the default RESP worker role is local_cache_worker. PostgreSQL reserves the pg_ prefix for system schemas and roles; these SQL names therefore omit it.

Supported deployment

The current implementation supports PostgreSQL 14–18 on Linux amd64, one configured database, and one writable primary. It is designed for attached, permanent application tables with an immediate, valid, non-partial B-tree primary key. It does not serve cache entries on standbys and does not coordinate multiple writable primaries.

local_cache.attach_table(regclass, boolean, text) discovers the primary key, records a whole-row mapping, installs extension-owned triggers, and grants the configured worker role only the table privileges required by that mapping. The supported primary-key types are int2, int4, int8, text, varchar, bpchar, and uuid; composite keys may contain 1–16 columns.

The extension rejects temporary or unlogged tables, views, partitioned or inherited tables, extension-owned or system tables, row-level security, partial or expression primary keys, nondeterministic key collations, and non-default primary-key operator classes.

Architecture

Three PostgreSQL execution contexts share the same bounded memory region:

The ordinary SQL path does not run in a background worker. It executes in the application’s PostgreSQL backend under that session’s table permissions and snapshot. Likewise, DML invalidation is collected in the backend executing the source transaction.

Mappings are stored in local_cache.mapping and published to shared memory by generation. A worker accepts a mapping only after validating its relation OID, primary key, triggers, ownership provenance, worker-role privileges, and row shape. local_cache.reconcile_table() and reconcile_all() repeat those checks and repair extension-owned triggers and grants before publishing a new mapping generation.

Shared memory and bounds

The cache is allocated at postmaster startup. Each key identifies a database, relation, namespace, and canonical primary-key value. Each entry can hold a positive value, a negative RESP result, invalidation state, version data, and a bounded load lease.

Important compile-time bounds are:

A whole-row entry always stores a native PostgreSQL composite value. A RESP fill also embeds JSON when both representations fit; a SQL fill stores the composite and renders JSON only if a later RESP read needs it. If the native payload does not fit in 8 KiB, ordinary SQL uses the source plan. A RESP GET can still read and return a wider source row when it fits the 64 KiB response bound, but it will not create an oversized cache entry.

Eviction examines a bounded sample rather than scanning the entire cache. Write-path relation and global invalidation advance version state instead of rewriting every affected entry. Administrative invalidation calls may scan the hash to report an affected-entry count, while correctness still depends on the version change. Load leases are versioned; a late loader cannot overwrite a newer generation after invalidation or eviction.

Entries do not have TTLs. The cache retains an entry until invalidation, eviction, replacement, corruption detection, or an MVCC safety check retires it.

Memory budget and OOM boundary

pg_local_cache.memory_budget_mb is a startup limit for deterministic extension allocations: shared hashes plus RESP client buffers and worker state. Startup fails if the configured extension layout exceeds that budget. local_cache.metrics() reports shared_memory_bytes, worker_memory_bytes, estimated_memory_bytes, and memory_budget_bytes from the same model.

This budget does not include shared_buffers, backend processes, work_mem, the operating system, exporters, or other containers. Set a cgroup or container memory limit with headroom and monitor both the extension estimate and process or container memory. The optional monitoring profile can collect cgroup OOM events through cAdvisor; it requires host-level access and is disabled by default.

RESP connection memory is bounded by the global client limit, per-worker preallocated slots, fixed request/output buffers, idle timeout, output backpressure handling, and slow-client disconnects. In SQL-only mode pg_local_cache.port=0 starts no RESP workers and allocates no RESP client slots.

SQL APIs

The canonical application path is ordinary PostgreSQL SQL over the existing database connection:

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

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

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

This returns the table’s normal tuple type with PostgreSQL’s normal projection, ACL, prepared-statement, scalar zero-or-one-row behavior, and batch row-set semantics. No result-type witness, column definition list, custom driver, or rewritten result decoder is involved. The planner and executor fast path below is an implementation detail.

KV-style callers can opt into the JSON functions:

SELECT local_cache.get('public.items'::regclass, $1::bigint);
SELECT local_cache.mget('public.items'::regclass, $1::bigint[]);

get(regclass, anyelement) returns complete-row JSON text and mget(regclass, anyarray) returns text[]. Duplicate keys and NULL elements are preserved. Bind or cast the scalar/array element to the actual single-column primary-key type.

get(regclass, text[]) is the fallback for composite and heterogeneous primary keys. Its components are in the order recorded by attach_table(). The function converts each component with the primary-key type’s normal PostgreSQL input function.

All three functions are SECURITY INVOKER, enforce SELECT on the source relation, and fail closed to a source lookup whenever the shared entry cannot be used safely. After the current transaction writes an attached table, both the ordinary SQL and function paths bypass the cache and use the transaction’s own snapshot. The normal pre-commit invalidation fence makes the new row refillable only after commit; rollback does not publish it.

The caller needs USAGE on schema local_cache, EXECUTE on the selected function overloads, and its normal source-table SELECT privilege. No RESP listener, authentication token, or cache-specific client connection is involved.

Planner and executor fast path

pg_local_cache.sql_cache is a USERSET GUC and defaults to on. The planner adds Custom Scan (pg_local_cache_sql) only when all of these conditions hold:

• the transaction isolation level is READ COMMITTED;
• PostgreSQL is not in recovery or parallel execution;
• the current transaction has not modified an attached mapping;
• the query contains exactly one attached base table;
• scalar lookup uses primary-key equality against a constant or external parameter, with every PK column present exactly once;
• batch lookup uses equality IN / = ANY(array) on the complete single-column primary key; its array is a constant, external parameter, or an array built only from constants and external parameters;
• the primary key is backed by the validated immediate B-tree primary index;
• every selected expression is a direct column reference;
• the query has no joins, CTEs, subqueries, aggregates, windows, grouping, distinct, ordering, offset, row lock, or set operation;
• scalar lookup has no limit or constant LIMIT 1; batch lookup has no LIMIT.

Examples that can use the path:

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

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

SELECT *
FROM public.tenant_items
WHERE item_id = $1 AND tenant_id = $2;

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

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

Predicate order does not need to match composite-key order. Parameters must be type-compatible with the key. The implementation accepts the same text/varchar representation and lossless widening from int2 to int4/int8 or from int4 to int8; other cross-type forms use the normal planner.

The custom path retains the ordinary primary-key index path as its child. Scalar execution follows this sequence:

1. Revalidate mapping generation, relation shape, triggers, isolation level, snapshot type, and transaction state.
2. Canonicalize primary-key parameters.
3. Read the versioned shared entry.
4. On a valid positive hit, verify the stored tuple’s source transaction against the current MVCC snapshot and decode the composite row.
5. On a miss, negative RESP entry, unsafe snapshot, malformed payload, or stale row-shape fingerprint, execute the child index plan.
6. Fill only after a latest-snapshot visibility proof and only if the read token still matches all relevant generations.

For IN/ANY, the executor evaluates the array once, ignores NULL elements, canonicalizes and deduplicates at most 1,024 keys, and copies at most 16 MiB of validated composite data into query-local memory. It returns cached rows only when every distinct key is a safe positive hit. One miss, negative entry, snapshot rejection, malformed payload, or budget overflow switches the entire statement to the retained PostgreSQL scalar-array index plan. The executor never merges a partial cache result with source rows, so PostgreSQL’s duplicate, NULL, and missing-key semantics are preserved. Rows returned by a cold child scan can refill their individual keys after the normal latest-snapshot proof.

Composite tuple IN, ALL, extra predicates, array queries with LIMIT, and arrays containing unsupported expressions use normal PostgreSQL paths. Runtime safety failures use the retained child plan. A missing row is fetched through that plan and returns zero rows. The SQL path does not trust negative RESP entries as authoritative for an application snapshot.

Application roles need only their normal source-table privileges. They do not need USAGE on local_cache to benefit from a cached SELECT. Attach rejects RLS tables rather than attempting to share rows across policies.

Consistency model

The source table remains authoritative. The cache provides a single-primary, transaction-aware invalidation model rather than asynchronous invalidation from WAL or a message bus.

Source writes

Extension-owned ENABLE ALWAYS row triggers canonicalize the complete old and new primary keys and collect them in backend-local transaction memory. TRUNCATE and selected DDL collect relation or global invalidation. A primary key update collects both keys.

At the pre-commit callback, before the source transaction can become visible, the extension reserves shared dirty markers, advances versions, cancels affected loads, and invalidates existing entries. Readers that overlap this window may observe the previously committed row or the newly committed row, but cannot publish a stale fill across the version fence. Commit releases the dirty markers. Abort releases transaction state without publishing source data. If abort follows publication of the pre-commit fence, affected entries may remain invalid, but the cache cannot expose the uncommitted row.

The per-transaction key list is bounded by pg_local_cache.max_dirty_keys. When that bound is reached, invalidation widens to the relation instead of growing backend memory without limit. If the required shared marker cannot be reserved, the transaction uses a global fence. Both fallbacks preserve the commit boundary at the cost of a colder cache.

PREPARE TRANSACTION is rejected after a transaction modifies an attached mapping. Read-only two-phase transactions are unaffected.

Fills and snapshots

Every miss captures entry, relation, global, and configuration generations. The fill is accepted only if those generations still match after the source read. RESP same-key cold reads use a bounded single-flight lease: followers wait up to pg_local_cache.singleflight_wait_ms, then read the table themselves if necessary without overwriting the leader’s generation. Ordinary SQL backends do not wait on the RESP single-flight lease; a non-owner executes its child plan.

For ordinary SQL, a positive entry records the source tuple’s xmin and the observed full transaction ID. The executor checks snapshot membership before returning the row. The full-XID observation horizon prevents treating a wrapped 32-bit transaction ID as current indefinitely. When that horizon is exceeded, the exact entry is retired and the source plan runs. This is not TTL expiration.

REPEATABLE READ and SERIALIZABLE always use the ordinary plan. A READ COMMITTED transaction that has written an attached mapping also bypasses the cache for its remaining statements, preserving read-your-own-write behavior.

Trigger and DDL provenance

Workers and the SQL path validate the managed statement, row, and truncate triggers, including function OIDs, arguments, enable state, and extension ownership. A mapping with missing or altered provenance is not served.

DDL event triggers reload or invalidate mappings when attached relations or dependent type/output semantics change. Whole-row payloads include a tuple descriptor fingerprint and CRC32C, so bytes saved for an old row shape are not decoded as a new row shape. DROP TABLE forgets the mapping by relation OID; recreating the same name does not attach the new table automatically.

RESP2 wire API

The optional endpoint is KVik-inspired and uses the documented whole-row CRUD key shape below. It has not been conformance-tested against Postgres Pro Enterprise KVik. Client integrations should rely only on the surface documented here.

The whole-row key format is:

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

The JSON object must contain every primary-key field exactly once. Member order is irrelevant; the worker parses values through PostgreSQL input functions and canonicalizes them in primary-index order. Database, schema, and table names in whole-row mappings cannot contain . or :.

SET treats the wire key as authoritative. Primary-key fields may be omitted from the row JSON; when present, they must match the wire key. The value is a complete row, not a JSON patch. Omitted non-key fields become NULL; column defaults do not fill them because the upsert supplies every non-generated column. Unknown JSON fields are rejected. PostgreSQL casts, generated-column rules, constraints, triggers, WAL, and commit still apply. SET and DEL each execute as a separate PostgreSQL transaction.

If the TCP connection closes before a write reply arrives, the client cannot infer whether the database transaction committed. Retrying a non-idempotent operation without reading the source can repeat application effects.

Compatibility boundary

Row payload format

Whole-row cache values use a versioned binary header. All header integers are big-endian; this is an internal shared-cache format, not the RESP wire format.

Version 1 has a 40-byte header followed by the native composite bytes and, when the HAS_JSON flag is set, stored JSON bytes. Decode checks the magic, version, known flags, total lengths, checksum, type OID, typmod, attribute count, descriptor fingerprint, and native tuple header before exposing a row.

Configuration

Except for pg_local_cache.sql_cache, these GUCs are postmaster settings and require a PostgreSQL restart.

max_clients must not exceed workers * max_clients_per_worker. Enabling RESP also consumes PostgreSQL background-worker slots; preserve capacity for replication, parallel queries, and other extensions when setting max_worker_processes.

Security boundary

The SQL and RESP surfaces have different identities:

• ordinary SQL executes as the connected PostgreSQL role, after PostgreSQL has checked its source-table privileges;
• administrative functions in local_cache are revoked from PUBLIC and must be granted to a deploy or monitoring role explicitly;
• RESP executes database operations as one configured worker role after shared token authentication;
• the RESP token grants access to every accepted mapping on the instance;
• RESP has no TLS and no per-client PostgreSQL authorization context.

Keep RESP disabled when it is unnecessary. When enabled, bind to loopback by default, store the token in an OS-user-owned mode 0400 or 0600 regular file, and place any remote access behind network isolation and authenticated TLS. Do not use the inline token in a production configuration or pass tokens on a command line that may be visible in process listings.

The worker role must be LOGIN NOSUPERUSER NOINHERIT; the extension refuses a superuser worker unless allow_superuser=on. Attach and reconcile manage only the grants required by configured mappings.

Metrics and operations

The SQL monitoring surface is:

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

metrics() returns a typed one-row snapshot. health() evaluates memory budget, worker count, client limit, and mapping-generation readiness. stats() returns the detailed JSON diagnostic snapshot. Counters reset at PostgreSQL restart; rate and alert calculations must tolerate resets.

Watch at least:

• cache hit, miss, fill, bypass, eviction, and invalidation rates;
• estimated extension memory versus budget and external process/cgroup memory;
• active and peak clients, rejected connections, backpressure, and slow-client drops;
• dirty-key relation fallbacks;
• mapping reload failures and workers with incomplete mappings;
• worker restarts and health readiness.

The bundled Prometheus rules and Grafana dashboard are described in the monitoring guide. They are templates; alert windows and memory thresholds must be calibrated against the deployment.

Operational limits

• Installing or changing shared-memory and worker settings requires a restart because the extension uses shared_preload_libraries.
• Attaching or reconciling a table takes ShareRowExclusiveLock while managed triggers and grants are validated. Use a bounded lock_timeout and retry.
• RESP writes are PostgreSQL transactions; their latency includes locks, WAL, constraints, triggers, and commit.
• A network failure before a RESP write reply leaves the commit outcome unknown to the client.
• Transparent IN/ANY is bounded to 1,024 elements and 16 MiB of query-local tuple copies; exceeding either bound executes PostgreSQL.
• The cache is not a durability layer. PostgreSQL remains the source of truth.
• Mappings are not automatically recreated for a new relation that reuses a dropped table’s name.
• Arbitrary native extensions are unavailable on most managed PostgreSQL services unless the provider packages them explicitly.

For rollout and rollback procedures, including Patroni and operator-managed clusters, follow the existing-database guide. For performance claims, use the evidence rules in the benchmark guide.