pgcache_pgrx

A PostgreSQL extension for cache invalidation tracking, built with pgrx.

Overview

pgcache_pgrx provides generation-based tracking infrastructure for PostgreSQL tables. It tracks which rows have been accessed at what "generation" (a monotonically increasing version number), enabling efficient cache invalidation strategies.

The extension uses PostgreSQL's shared memory (dshash backed by DSA) to store generation data, making it accessible across all backend processes.

Note: This extension also includes an experimental in-memory Table Access Method (TAM). The TAM is a work in progress and not the focus of this documentation.

Features

• Generation tracking via CustomScan: Automatically tracks row access during SELECT queries
• Trigger-based tracking: Track INSERT and UPDATE operations via triggers
• Shared memory storage: Generation data stored in PostgreSQL shared memory (dshash)
• Primary key hashing: Rows identified by a hash of their primary key values
• Bulk purge operations: Efficiently delete stale cached rows based on generation threshold

Installation

Prerequisites

• Rust (stable)
• PostgreSQL 13-18
• pgrx 0.16.1

Build and Install

# Install pgrx if not already installed
cargo install cargo-pgrx

# Initialize pgrx for your PostgreSQL version
cargo pgrx init --pg18 $(which pg_config)

# Build and install the extension
cargo pgrx install --release

Configuration

Add to postgresql.conf:

shared_preload_libraries = 'pgcache_pgrx'

Restart PostgreSQL, then create the extension:

CREATE EXTENSION pgcache_pgrx;

Usage

Setting the Query Generation

The mem.query_generation GUC controls generation tracking:

-- Enable tracking with generation 100
SET mem.query_generation = 100;

-- Run queries - accessed rows will be recorded with generation 100
SELECT * FROM my_table WHERE id = 1;

-- Disable tracking
SET mem.query_generation = 0;

When mem.query_generation > 0, SELECT queries on heap tables automatically record each accessed row's primary key hash along with the current generation.

Trigger-Based Tracking

For INSERT and UPDATE operations, use triggers:

-- Enable tracking on a table (creates an AFTER INSERT OR UPDATE trigger)
SELECT pgcache_enable_tracking('my_table'::regclass);

-- Now INSERTs and UPDATEs are tracked when mem.query_generation > 0
SET mem.query_generation = 100;
INSERT INTO my_table VALUES (1, 'data');
UPDATE my_table SET value = 'new_data' WHERE id = 1;
SET mem.query_generation = 0;

-- Disable tracking (removes the trigger)
SELECT pgcache_disable_tracking('my_table'::regclass);

Inspecting Generation Data

-- Check if shared memory is available
SELECT pgcache_generation_available();

-- Dump all generation entries
SELECT * FROM pgcache_generation_dump();
-- Returns: table_oid, pk_hash, generation

-- Look up a specific entry
SELECT pgcache_generation_lookup('my_table'::regclass, pk_hash);

Computing Primary Key Hash

-- Get the pk_hash for a row using its ctid
SELECT pgcache_pk_hash('my_table'::regclass, ctid) FROM my_table WHERE id = 1;

-- Join generation data with table rows
SELECT t.*, d.generation
FROM my_table t
JOIN pgcache_generation_dump() d
  ON d.table_oid = 'my_table'::regclass
 AND d.pk_hash = pgcache_pk_hash('my_table'::regclass, t.ctid);

Purging Stale Data

-- Purge generation entries for a specific table (threshold inclusive)
-- Removes entries where 0 < generation <= threshold
SELECT pgcache_generation_purge('my_table'::regclass, 150);

-- Purge generation entries for all tables
SELECT pgcache_generation_purge_all(150);

-- Delete actual rows AND purge generation entries
-- This deletes rows from tables where their generation <= threshold
SELECT pgcache_purge_rows(150);

API Reference

GUC Settings

Setting	Type	Default	Description
mem.query_generation	integer	0	Current query generation. Set > 0 to enable tracking.
pgcache.fold_interval_ms	integer	1000	Interval between background fold passes. Lower for more responsiveness to bursty writes; higher for less idle wakeup cost.
pgcache.fold_enabled	boolean	on	Kill switch for the fold worker (debug).

Functions

Function	Returns	Description
pgcache_generation_available()	boolean	Check if shared memory is initialized
pgcache_generation_record(table_oid, pk_hash, generation)	boolean	Manually record a generation entry (appends to the reverse log)
pgcache_generation_lookup(table_oid, pk_hash)	bigint	Look up generation for a specific row (folds first)
pgcache_generation_fold()	bigint	Drain the reverse log into the forward hash. Returns items folded
pgcache_generation_reverse_bytes()	bigint	Current DSA bytes held in reverse-log chunks
pgcache_generation_purge(table_oid, threshold)	bigint	Purge entries for a table, returns count
pgcache_generation_purge_all(threshold)	bigint	Purge entries for all tables, returns count
pgcache_generation_dump()	SETOF record	Dump all entries (table_oid, pk_hash, generation); folds first
pgcache_pk_hash(table_oid, ctid)	bigint	Compute pk_hash for a row
pgcache_enable_tracking(table_oid)	boolean	Create tracking trigger on table
pgcache_disable_tracking(table_oid)	boolean	Remove tracking trigger from table
pgcache_purge_rows(threshold)	bigint	Delete rows and purge entries, returns deleted count

Generation Semantics

• Generation values must be > 0 for threshold-based purges to touch a row. Generation 0 is a placeholder for rows ingested via CDC; it is promoted periodically via pgcache_generation_zero_promote.
• When a row is recorded at multiple generations, the maximum wins after fold.
• Purge operations remove entries where 0 < generation <= threshold.
• Scan writes append to the reverse log and do not touch the forward hash directly. A background worker (pgcache_fold) drains the reverse log into the forward hash on a timer and on pressure signals; pgcache_generation_purge*, pgcache_generation_lookup, and pgcache_generation_dump each fold inline so callers see current state.

Architecture

┌───────────────────────────────────────────────────────────────────────┐
│                         PostgreSQL Shared Memory                      │
├───────────────────────────────────────────────────────────────────────┤
│                                                                       │
│  Writers (CustomScan + Trigger)            Readers (purge / lookup /  │
│  ───────────────────────────────            dump)                     │
│         │                                         │                   │
│         │ append only                             │ read, evict       │
│         ▼                                         ▼                   │
│  ┌─────────────────────────┐             ┌──────────────────────────┐ │
│  │     Reverse Log         │   fold →    │   Forward Hash           │ │
│  │ gen → chunk list of     │════════════▶│ (table_oid, pk_hash) →   │ │
│  │   (table_oid, pk_hash)  │  max-wins   │   max_generation         │ │
│  └──────────▲──────────────┘             └──────────▲───────────────┘ │
│             │ drain                                  │                │
│             │                                        │                │
│       ┌─────┴───────────────┐                 ┌──────┴──────┐         │
│       │ pgcache_fold        │   maintenance   │ purge /     │         │
│       │ bgworker (timer +   │   lock serialize│ fold inline │         │
│       │  pressure latch)    │◀═══════════════▶│ callers     │         │
│       └─────────────────────┘                 └─────────────┘         │
│                                                                       │
└───────────────────────────────────────────────────────────────────────┘

The forward hash is a lazily-maintained materialized view of the reverse log; see ADR/ADR-001-lazy-forward-hash-generation-tracking.md for the rationale.

Testing

# Run all tests
cargo pgrx test pg18

# Run a specific test
cargo pgrx test pg18 test_pgcache_purge_rows