How Caching Works

PgCache is a transparent caching proxy that sits between your application and PostgreSQL. It automatically determines which queries can be cached, stores their results in a local PostgreSQL cache database, and uses CDC (Change Data Capture) to keep cached data synchronized with the origin.

Architecture

┌──────────┐    queries     ┌──────────┐    uncacheable    ┌──────────┐
│          │ ─────────────▶ │          │ ────────────────▶ │          │
│   App    │                │ pgcache  │                   │PostgreSQL│
│          │ ◀───────────── │          │ ◀──────────────── │ (origin) │
└──────────┘   responses    └────┬──┬──┘    responses      └─────┬────┘
                                 │  │                            │
                           cache │  │    CDC stream              │
                           read/ │  │   (logical replication)    │
                           write │  └────────────────────────────┘
                                 ▼
                            ┌──────────┐
                            │  Cache   │
                            │   DB     │
                            └──────────┘
  1. Your application sends queries to PgCache as if it were a normal PostgreSQL server
  2. PgCache analyzes each query to determine if it’s cacheable
  3. Cacheable queries are served from the cache database when a cached result exists (cache hit)
  4. Cache misses and non-cacheable queries are forwarded to the origin database
  5. The CDC stream continuously processes changes from the origin, keeping cache entries fresh or invalidating when necessary

What Gets Cached

PgCache analyzes each incoming SQL statement and determines whether it can be safely cached. Only queries where PgCache can guarantee correct results are cached.

The list of supported query patterns is actively expanding. If your query isn’t cached today, it may be in a future release.

Cacheable Queries

  • Single-table SELECT statements
  • INNER JOIN, LEFT JOIN, and RIGHT JOIN queries with equality join conditions
  • WHERE clauses using comparison operators: =, !=, <, <=, >, >=
  • Boolean operators in WHERE: AND, OR
  • IN and NOT IN expressions
  • IS NULL and IS NOT NULL checks
  • NOT expressions
  • GROUP BY and HAVING clauses (aggregation is performed on cached rows)
  • ORDER BY clauses (sorting is applied at retrieval time)
  • Aggregate functions in the SELECT list (COUNT, SUM, AVG, etc.)
  • Window functions (ROW_NUMBER, RANK, etc. with PARTITION BY and ORDER BY)
  • CASE expressions
  • Arithmetic expressions in the SELECT list
  • Uncorrelated subqueries in SELECT, FROM (derived tables), and WHERE (IN, NOT IN, scalar), including nested subqueries
  • Correlated subqueries: EXISTS, NOT EXISTS, scalar, IN, NOT IN — automatically decorrelated into equivalent JOINs for caching. Only equality correlation predicates are supported
  • Common Table Expressions: WITH (CTE) queries, including MATERIALIZED / NOT MATERIALIZED
  • Set operations: UNION, UNION ALL, INTERSECT, EXCEPT
  • Functions in the SELECT list
  • Immutable functions in WHERE and JOIN clauses (lower(), upper(), abs(), etc.)
  • LIMIT / OFFSET: Queries differing only in LIMIT share a cache entry
  • Pattern matching: LIKE, NOT LIKE, ILIKE, NOT ILIKE
  • Range expressions: BETWEEN, NOT BETWEEN, BETWEEN SYMMETRIC
  • Array comparisons: = ANY(...), op ALL(...)
  • Mixed wildcards and columns: SELECT *, id, name FROM ...

Not Cached

These query patterns are passed through directly to the origin database:

  • Non-SELECT statements: INSERT, UPDATE, DELETE, DDL statements
  • FULL JOIN and CROSS JOIN
  • LATERAL subqueries
  • Non-equality correlated predicates: Correlated subqueries using <, >, or OR-connected correlation
  • RECURSIVE CTEs
  • Non-immutable functions outside the SELECT list: Stable or volatile function calls in WHERE, JOIN, or other clauses
  • Locking clauses: FOR UPDATE, FOR SHARE, etc.
  • Views: Queries against views are forwarded to the origin

Queries that aren’t cached still work — they’re transparently forwarded to the origin with no overhead beyond the proxy hop.

Cache Maintenance

PgCache uses PostgreSQL logical replication (CDC) to keep its cache synchronized with the origin database. This is fundamentally different from TTL-based caching — PgCache knows about changes as they happen and can often update cached data in place rather than simply invalidating it.

How CDC Works

  1. PgCache creates a publication and replication slot on the origin database and subscribes to changes
  2. The publication is dynamically managed — only tables with active cached queries are included, reducing CDC overhead
  3. When data changes on the origin (INSERT, UPDATE, DELETE, TRUNCATE), the change event is streamed to PgCache via logical replication
  4. PgCache identifies which cached queries are affected by the change
  5. Affected cache entries are updated directly when possible, or invalidated and re-populated on the next query

Table Allowlist

By default, PgCache considers all tables for caching. You can restrict caching to specific tables using the allowed_tables setting. When configured, only queries where every referenced table is in the allowlist will be cached. Other queries are forwarded to the origin. This is useful when you want fine-grained control over which data is cached.

Tables with Row-Level Security (RLS): PgCache does not currently apply RLS policies on cached results. Until RLS support lands, omit RLS-enabled tables from allowed_tables (or otherwise exclude them) to avoid serving rows that would be filtered on the origin.

allowed_tables = ["users", "orders", "products"]

Pinned Queries

Pinned queries are pre-populated into the cache at startup and protected from eviction. They are automatically re-populated after CDC invalidation. This ensures that critical queries are always served from cache.

pinned_queries = [
    "SELECT * FROM settings",
    "SELECT * FROM categories",
]

# Shorthand: pin entire tables
pinned_tables = ["settings", "categories"]

Each entry in pinned_tables is expanded to SELECT * FROM {table} and merged with pinned_queries.

Single-table pinned queries with no joins or subqueries are always kept up to date via CDC — they are updated in place and never invalidated. This makes them ideal for reference tables like settings, categories, or lookup data.

Cache Admission

By default, a cacheable query is admitted on its first occurrence. With the CLOCK policy you can require a query to be seen multiple times before admission by raising admission_threshold (default 1 — admit immediately; higher values delay admission until the query has been seen that many times, which keeps one-off queries out of the cache at the cost of some initial cache misses). Queries that were previously cached and then invalidated by CDC events are fast-readmitted regardless of the threshold.

Materialized Results

For queries that reduce or transform a large input into a small output — aggregates, window functions, GROUP BY, DISTINCT — PgCache maintains a second tier of cache that stores the result rather than recomputing it from cached source rows. Example: SELECT count(*) FROM large_table returns a single integer per serve from a materialized result instead of re-aggregating millions of rows.

Eligibility is decided by query shape at registration:

  • Always materialize: Window functions (recomputing the partition/sort on every serve is expensive regardless of cardinality)
  • Materialize when it pays off: Aggregates in the SELECT list, GROUP BY / HAVING, DISTINCT, set operations other than UNION ALL. These apply a size gate at first population: the result is materialized only when result_rows × mv_size_ratio ≤ source_rows (default ratio 10)
  • Skip: Plain filter/projection, UNION ALL, ORDER BY + LIMIT without a reducing shape

Materialized results are kept fresh by the same CDC stream that drives source-row invalidation. When a materialized result is dirty, the serve path falls back to evaluating the query over the source-row cache while the materialized result is being reconstructed.

Request Coalescing

When multiple concurrent requests arrive for the same query that’s currently being populated, PgCache shares a single backend execution among all of them. Subsequent requests wait for the in-flight load instead of triggering parallel populations against the origin. This prevents thundering-herd amplification on cold caches and after query invalidation.

Predicate Subsumption

When a new query arrives, PgCache checks whether its result set is already covered by an existing cached query. If so, the result can be served directly from the cached superset without querying the origin. Subsumption is per-column: for each column the cached query constrains, the new query’s constraint on that column must be implied by the cached constraint.

Examples of supported patterns:

  • Full coverage — cached SELECT * FROM users covers SELECT * FROM users WHERE id = 1
  • Narrower equality — cached WHERE tenant_id = 1 covers WHERE tenant_id = 1 AND status = 'active'
  • Tighter range — cached WHERE value > 50 covers WHERE value > 100
  • Range containment — cached WHERE value BETWEEN 10 AND 200 covers WHERE value >= 50 AND value <= 150
  • Point within range — cached WHERE value > 50 covers WHERE value = 100
  • IN-set subset — cached WHERE id IN (1, 2, 3, 4, 5) covers WHERE id IN (2, 3) and WHERE id = 4
  • Range covers IN-set — cached WHERE value > 0 covers WHERE value IN (50, 100)

Subsumption is tracked via the pgcache.cache.subsumptions metric.

Cache Size Management

When cache_size is configured, PgCache enforces a maximum cache size in bytes. When the limit is reached, queries are evicted using a CLOCK (second-chance) algorithm: recently-accessed queries get a reprieve, while idle queries are evicted first. A FIFO policy is also available via the cache_policy setting.

Consistency Guarantees

PgCache provides eventual consistency between the cache and origin:

  • Changes on the origin are streamed to PgCache via CDC with minimal delay
  • Replication lag is tracked and exposed as metrics (pgcache.cdc.lag_seconds, pgcache.cdc.lag_bytes) so you can monitor freshness
  • The cache is fully reset on every PgCache startup, ensuring a clean state with no stale data carried over
  • Uncacheable queries always go directly to the origin, so they’re always consistent

The lag between a write on the origin and the corresponding cache invalidation is typically sub-second, depending on network latency and origin server load.