How Caching Works
PgCache is a transparent caching proxy that sits between your application and PostgreSQL. Your application connects to it like any Postgres server, with no code changes. 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. The settings on this page are optional. With the defaults, PgCache caches every query it can serve safely.
For how this compares with other ways to cache Postgres, such as materialized views, Redis and read replicas, see the guide to PostgreSQL caching. For another caching proxy, see PgCache vs Readyset.
Architecture
┌──────────┐ queries ┌──────────┐ uncacheable ┌──────────┐
│ │ ─────────────▶ │ │ ────────────────▶ │ │
│ App │ │ pgcache │ │PostgreSQL│
│ │ ◀───────────── │ │ ◀──────────────── │ (origin) │
└──────────┘ responses └────┬──┬──┘ responses └─────┬────┘
│ │ │
cache │ │ CDC stream │
read/ │ │ (logical replication) │
write │ └────────────────────────────┘
▼
┌──────────┐
│ Cache │
│ DB │
└──────────┘
- Your application sends queries to PgCache as if it were a normal PostgreSQL server
- PgCache analyzes each query to determine if it’s cacheable
- Cacheable queries are served from the cache database when a cached result exists (cache hit)
- Cache misses and non-cacheable queries are forwarded to the origin database
- 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.
To see which of your own queries fall on each side of this line, run a pg_stat_statements export or a PostgreSQL log through the Fit Analyzer. It runs in your browser, and nothing is uploaded.
Cacheable Queries
- Single-table SELECT statements
- INNER JOIN, LEFT JOIN, and RIGHT JOIN queries, including
USINGandNATURAL JOIN, plus CROSS JOIN. TheONclause accepts any predicate aWHEREdoes beside its equalities (ON r.order_id = o.id AND r.deleted_at IS NULL,ON r.order_id = o.id AND r.amount > 5); subqueries inONare not cached - 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. withPARTITION BYandORDER BY) - CASE expressions
- Arithmetic expressions in the SELECT list
- Uncorrelated subqueries in
SELECT,FROM(derived tables), andWHERE(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, includingMATERIALIZED/NOT MATERIALIZED - Set operations:
UNION,UNION ALL,INTERSECT,EXCEPT - Immutable functions anywhere (
lower(),upper(),abs(), …), and in the query’s own SELECT list the stable builtins whose value depends only on the clock and the session’s settings (now(),to_char(),date_trunc(),concat(), …), recomputed on every hit - Session terms (
current_user,current_setting('app.tenant'), a claim extracted from a JSON setting): the read is keyed on the term’s value, so each tenant or role has its own entry and sessions with equal values share it - LIMIT / OFFSET: Queries differing only in LIMIT share a cache entry. Aggregates,
GROUP BY,DISTINCT, window functions, joins and set operations populate in full - 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
- LATERAL subqueries
- Non-equality correlated predicates: Correlated subqueries using
<,>, orOR-connected correlation - RECURSIVE CTEs
- Volatile functions anywhere (
random(),nextval(),set_config(), …), and stable functions outside the SELECT list or off the allowlist - 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
- PgCache creates a publication and replication slot on the origin database and subscribes to changes
- The publication is dynamically managed — only tables with active cached queries are included, reducing CDC overhead
- When data changes on the origin (INSERT, UPDATE, DELETE, TRUNCATE), the change event is streamed to PgCache via logical replication
- PgCache identifies which cached queries are affected by the change
- 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, including the tables a row-level security policy reads. Other queries are forwarded to the origin. This is useful when you want fine-grained control over which data is cached. Removing a table from the allowlist through PUT /config forwards its queries at once and evicts the ones cached on it within about a second.
Tables with Row-Level Security (RLS): PgCache caches RLS-enabled tables by folding the policies that apply to the reading role into the query, with the session values they read (
current_user, a tenant setting) filled in, so each tenant and role is cached as its own entry. See Compatibility → Known Limitations for the policy shapes supported and what forwards instead.
allowed_tables = ["users", "orders", "products"]Excluded Tables
excluded_tables is the opposite list: every table is cacheable except the ones it names. Use it to keep write-heavy or sensitive tables (audit_logs, sessions, jobs) out of the cache without listing every other table. A query that references an excluded table anywhere — a join, a subquery, a CTE, or a row-level security policy on another table — is forwarded to the origin, and the table is never copied into the cache or added to PgCache’s publication.
excluded_tables = ["audit_logs", "analytics.events"]
- Names take the same forms as
allowed_tablesand match case-insensitively:"audit_logs"excludes the table in every schema,"analytics.events"only in that schema. - Unqualified references: with
"analytics.events"excluded, a query that writes a bareeventsis forwarded whichever schema it resolves to. Writepublic.eventsto have another schema’s table of the same name cached. - Both lists: when
allowed_tablesis set too, a table must be allowed and not excluded. PgCache logs a warning for a table named in both, since it is never cached. - Partitioned tables: name the partitioned table itself. Naming one partition does not keep its rows out of queries on the parent.
- At runtime: excluding a table through
PUT /configforwards its queries at once; within about a second the queries cached on it are evicted and its cached rows dropped.
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 whenresult_rows × mv_size_ratio ≤ source_rows(default ratio10) - 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. An UPDATE that keeps a row’s primary key and changes only columns the query never reads, such as a updated_at or counter column outside its select list, predicates, GROUP BY and ORDER BY, leaves the materialized result in place; the same rule keeps in-process memoized results.
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.
Coverage is judged per table reference. Every table the new query reads, in every subquery, derived table, CTE and set-operation arm, must be covered by a cached query that loaded that table in full: a single-table query, the preserved side of an outer join (users in users LEFT JOIN orders), or any fully loaded reference of a query that reads the table more than once. A query that reads the same table twice, such as a self-join, needs every occurrence covered.
A reference constrained to a finite set is covered value by value when no single cached query holds the whole set: WHERE id IN (1, 2, 3) is served from cached WHERE id = 1, WHERE id = 2 and WHERE id = 3 together.
A join is also covered as a whole by a cached join of the same shape: the same tables, join types and ON clauses, whatever the aliases, select list, GROUP BY or ORDER BY. The cached join loaded every row of that shape its WHERE admits, so a join of the shape whose WHERE implies the cached one, column by column on each table reference, is served from its rows. The cached join’s WHERE must be made of single-column predicates (ANDed comparisons, IN, BETWEEN, null tests); an OR or a column-to-column comparison there disqualifies it.
Examples of supported patterns:
- Full coverage — cached
SELECT * FROM userscoversSELECT * FROM users WHERE id = 1 - Narrower equality — cached
WHERE tenant_id = 1coversWHERE tenant_id = 1 AND status = 'active' - Tighter range — cached
WHERE value > 50coversWHERE value > 100 - Range containment — cached
WHERE value BETWEEN 10 AND 200coversWHERE value >= 50 AND value <= 150 - Point within range — cached
WHERE value > 50coversWHERE value = 100 - IN-set subset — cached
WHERE id IN (1, 2, 3, 4, 5)coversWHERE id IN (2, 3)andWHERE id = 4 - Range covers IN-set — cached
WHERE value > 0coversWHERE value IN (50, 100) - Outer-join preserved side — cached
SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.tenant_id = 1coversSELECT * FROM users WHERE tenant_id = 1 AND status = 'active' - Set operations — cached
SELECT * FROM usersandSELECT * FROM adminscoverSELECT id FROM users WHERE active UNION SELECT id FROM admins - Point parents cover an IN list — cached
WHERE id = 1andWHERE id = 2together coverWHERE id IN (1, 2) - Same-shape join — cached
SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.tenant_id = 7covers the same join withWHERE o.tenant_id = 7 AND o.status = 'open'
Literals are compared as values of their column’s type, so a range over dates, timestamps or numbers is covered however its bounds are spelled ('2026-01-01', '10.50'). Text ranges are covered only on a column whose collation orders by bytes (C, POSIX or the builtin provider); under any other collation, text supports equality and IN-set coverage only, because PgCache does not reproduce the collation’s order itself.
The check also runs when an invalidated query is readmitted: if cached queries that are still current cover it, it is stamped from their rows instead of repopulated from origin.
Subsumption is tracked via the pgcache.cache.subsumptions, pgcache.cache.subsumptions_split, pgcache.cache.subsumptions_join and pgcache.cache.readmission_subsumptions metrics.
Cache Size Management
PgCache bounds the cache along two axes: memory and disk.
For memory, PgCache measures whole-system used memory (the proxy plus the cache PostgreSQL it manages) against a budget — 80% of detected RAM by default, lowerable via memory_limit. As the budget is approached, registration of new distinct queries is throttled and queries are evicted; already-cached queries keep serving.
For disk, PgCache measures live cache-volume usage (via statvfs) against an effective ceiling — an explicit disk_limit, or one auto-derived from the volume’s free space when unset. When usage crosses the ceiling, an escalating reclaim ladder purges dead-generation rows, then sweeps dirty materialized views, then drops the fewest-query source table. Which queries are evicted follows the cache_policy setting: CLOCK (second-chance, the default) or FIFO. The older cache_size byte cap is deprecated and ignored — use disk_limit.
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
- On startup, cacheable reads are forwarded until the replication stream has caught up to the position PgCache read its catalogs at, so an entry never registers against state a change made during startup has already superseded
- Uncacheable queries always go directly to the origin, so they’re always consistent
- Schema changes on cached tables (
ALTER TABLE,DROP TABLE, partition attach, detach and drop) evict the affected queries at their position in the stream, reported by the schema-change signal on the origin; a partition’sTRUNCATEarrives on the replication stream itself
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.
Read-Your-Own-Writes
A connection always sees its own writes, even while CDC is still catching up. Each connection records the writes it forwards to the origin. Until a write has been applied to the cache, a cacheable read on that connection is served from cache only if PgCache can prove the read does not overlap the write; otherwise it is forwarded to the origin.
- The check is row-level, per table reference: a pending
INSERT INTO orders (id, tenant_id, ...) VALUES (...)for tenant 7 does not affectSELECT ... FROM orders WHERE tenant_id = 3, whetherordersis read alone or joined. A joined table with no filter of its own, or on theNULL-filled side of an outer join, carries only what the other side’s filters imply through theON, so a pending write to it forwards the read.UPDATEandDELETEwith simpleAND-ed column comparisons are checked the same way. - A pending
UPDATEthat assigns only columns a read never references does not affect it, for single-table and join reads alike. - A write is matched to the cached tables its rows can land in, resolving its name as origin does: a qualified name in its schema, an unqualified one along the connection’s
search_path. A write into a partition child gates reads of the cached parent, and a write through an auto-updatable view gates reads of its base table. When two schemas on the path hold the name, or an uncached relation may shadow a cached one, reads of every candidate are forwarded. - Writes PgCache cannot analyze precisely are handled conservatively:
TRUNCATE,MERGE, and complexUPDATE/DELETEpredicates gate every read of the table; DDL,CALL,EXPLAIN ANALYZE, and multi-statement queries gate every cacheable read on the connection. A plainEXPLAINonly plans and gates nothing. - A pending write clears once PgCache’s settled watermark (
cdc.settled_lsnin/status) passes the write’s commit — usually within the normal replication lag.
Other connections see the write once CDC applies it, as described above. The pgcache.raw.* metrics report how often reads are forwarded for this reason.
Transactions
Cacheable reads inside a transaction block are served from cache when the block runs at READ COMMITTED (PostgreSQL’s default) and the read does not overlap the block’s own uncommitted writes. Reads in REPEATABLE READ or SERIALIZABLE blocks, and in a block that has already failed, are forwarded to the origin, since the cache cannot reproduce a pinned snapshot.
PgCache reads the session’s default isolation level with the session-settings probe sent when a connection is established (see Compatibility → Session Settings), and tracks statements that change it (BEGIN ... ISOLATION LEVEL, SET TRANSACTION, SET default_transaction_isolation, SET SESSION CHARACTERISTICS). While the level is unknown, reads are forwarded. The pgcache.txn.* metrics report in-transaction serves and forwards by reason.