Compatibility

PgCache is designed to be transparent to client applications. This page covers supported PostgreSQL versions, query patterns, client libraries, and known limitations.

PostgreSQL Version Support

PostgreSQL VersionStatus
18Supported (primary target)
17Supported
16Supported
15 and belowNot supported (requires logical replication protocol version 4)

The origin database must have wal_level = logical enabled for CDC replication.

Session Settings

A cached result is only valid for a session whose settings match the ones it was computed under. pgcache tracks these settings on every client connection:

SettingsEffect
search_path, current roleDecide which table an unqualified name refers to. $user, quoted or not, expands to the current role. Two sessions share a cache entry when their paths resolve the query to the same tables
TimeZone, DateStyle, IntervalStyle, timezone_abbreviationsDecide what a date-time literal means. Literals are canonicalised at registration so sessions on different settings share an entry only when their literals select the same rows
extra_float_digits, bytea_output, client_encoding, lc_monetary, lc_numeric, lc_time, xmlbinary, xmloption (and the date-time settings above)Decide how results are rendered. A cache hit applies the client’s values on the cache connection, so results come back as origin would render them
default_transaction_isolationDecides whether reads inside a transaction block can be served (see Known Limitations)

pgcache samples these settings with one probe when a connection opens. SET, SET LOCAL, RESET, DISCARD ALL and set_config() mark the settings they change as unknown until the next probe. Settings that PostgreSQL reports on change (ParameterStatus, including search_path on PostgreSQL 18+) are updated from the report. While a setting a read depends on is unknown, the read is forwarded to the origin.

Some sessions are forwarded entirely while a setting is in effect:

  • standard_conforming_strings = off, transform_null_equals = on, array_nulls = off, a non-zero gin_fuzzy_search_limit, or a client_encoding other than UTF-8. These change how a statement is parsed.
  • A search_path that lists pg_catalog after another schema, where a user function can shadow a builtin.

A date-time literal pgcache cannot canonicalise (a month name, a special value such as 'now', an interval, or a digit-bearing string whose type is not visible, such as a function argument) is cached under origin’s default settings and served only to sessions using those defaults.

Session terms in queries

A query that reads the session is cached and keyed on what it reads: current_user, current_role, session_user, current_setting('app.tenant') and a claim extracted from a JSON setting, (current_setting('request.jwt.claims', true)::jsonb ->> 'sub')::uuid, including the forms wrapped in coalesce, nullif, a cast or a FROM-less (select …). PgCache evaluates the expression to its value for the session before the read is keyed, so each tenant or role has its own entry and sessions with equal values share one, whatever else their tokens or settings differ in. A value the session cannot supply (a setting never set, a role not yet known) forwards the read, with origin’s own answer or error, as does a value the expression’s type would reject (abc cast to an integer, a sub claim that is not a UUID) and a built-in setting read this way (current_setting('timezone')): only application-defined, dotted settings are keyed. An expression PgCache cannot evaluate itself (a typed nullif, a JSON value compared whole) is keyed on the setting’s raw value instead and left to the database. SET, SET LOCAL and set_config() of a named setting are tracked, a value bound as a parameter included, and a SELECT set_config(…) that names its setting is not treated as a write.

Limitation: a SET or set_config() executed inside a function body, and a configuration reload under a live session, are not visible to the proxy. If your application relies on functions that change search_path or other settings, set them at the session level instead.

Supported Query Patterns

PgCache caches queries that it can safely evaluate and invalidate. All other queries are transparently forwarded to the origin.

To see where your own queries land before you deploy, run a pg_stat_statements export, a PostgreSQL log, or plain SQL through the Fit Analyzer. It sorts every statement into cached or forwarded and names the reason for each forwarded read. It runs in your browser, and nothing is uploaded.

Cached

PatternExample
Single table SELECTSELECT * FROM users WHERE active = true
INNER JOINSELECT u.id, u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id
LEFT / RIGHT JOINSELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id
Predicates in JOIN ONSELECT o.id, r.amount FROM orders o LEFT JOIN refunds r ON r.order_id = o.id AND r.deleted_at IS NULL, ... ON r.order_id = o.id AND r.amount > 5: any predicate WHERE accepts, in inner, LEFT and RIGHT joins. Subqueries in ON are forwarded
USING / NATURAL JOINSELECT * FROM users JOIN orders USING (user_id), SELECT * FROM users NATURAL JOIN orders
CROSS JOINSELECT * FROM colors CROSS JOIN sizes
Equality comparisonsWHERE status = 'active'
Inequality comparisonsWHERE age > 18, WHERE price <= 100
AND / OR conditionsWHERE active = true AND role = 'admin'
IN / NOT INWHERE id IN (1, 2, 3)
IS NULL / IS NOT NULLWHERE deleted_at IS NULL
NOT expressionsWHERE NOT archived
GROUP BY / HAVINGSELECT status, COUNT(*) FROM orders GROUP BY status
ORDER BYSELECT * FROM products ORDER BY price ASC, including NULLS FIRST / NULLS LAST
Aggregate functionsSELECT COUNT(*), SUM(total) FROM orders
FILTER on aggregatesSELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders (also inside OVER (...))
Window functionsSELECT id, name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees, including explicit frames (ROWS/RANGE/GROUPS BETWEEN ...) and named windows (WINDOW w AS (...) / OVER w)
CASE expressionsSELECT CASE WHEN active THEN 'yes' ELSE 'no' END FROM users
Arithmetic expressionsSELECT price * quantity AS total FROM line_items
JSON operatorsWHERE data ->> 'kind' = 'invoice', WHERE data @> '{"tags": ["red"]}', WHERE data ? 'k', SELECT data #> '{who,name}' FROM docs: ->, ->>, #>, #>>, @>, <@, ?, `?
TypecastsSELECT col::int FROM t, WHERE created_at = '2026-01-01'::date
Uncorrelated subqueriesWHERE id IN (SELECT user_id FROM orders), derived tables, scalar subqueries
CTEsWITH active AS (SELECT * FROM users WHERE active) SELECT * FROM active
Set operationsSELECT id FROM users UNION SELECT id FROM admins
Functions in SELECTSELECT COALESCE(name, 'unknown') FROM users
COALESCE, NULLIF, GREATEST, LEAST anywhereWHERE COALESCE(deleted_at, created_at) > '2024-01-01': evaluated by PostgreSQL itself, so immutable wherever they stand
Immutable functions in WHEREWHERE lower(name) = 'foo', WHERE abs(balance) > 100
LIMIT / OFFSETSELECT * FROM products ORDER BY price LIMIT 10 OFFSET 20, bound values (LIMIT $1 OFFSET $2), LIMIT ALL and FETCH FIRST 10 ROWS ONLY
LIKE / ILIKE, regular expressions, SIMILAR TOWHERE name LIKE 'foo%', WHERE name ILIKE '%bar%', WHERE name ~* '^foo', WHERE name SIMILAR TO 'f%': cached as written; a change to a matching or formerly matching row is evaluated by the cache database, so these predicates stay in place under CDC but never narrow what a write affects
IS [NOT] DISTINCT FROMWHERE tag IS DISTINCT FROM 'hidden': NULL counts as a value, as on origin
BETWEENWHERE price BETWEEN 10 AND 100, including SYMMETRIC
ANY / ALLWHERE id = ANY(ARRAY[1, 2, 3]), WHERE score > ALL(ARRAY[80, 90])
Mixed wildcards and columnsSELECT *, id, name FROM users
EXISTS / NOT EXISTSWHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
Correlated subqueriesWHERE id IN (SELECT ...), NOT IN (SELECT ...), scalar subqueries, EXISTS/NOT EXISTS — decorrelated into equivalent JOINs
IS [NOT] TRUE/FALSE/UNKNOWNWHERE active IS TRUE, WHERE flag IS NOT FALSE
Extended query protocolParameterized queries via Parse/Bind/Execute

Not Cached (Forwarded to Origin)

PatternReason
FULL JOINNot yet supported for caching
LATERAL subqueriesNot supported
RECURSIVE CTEsNot supported
Non-equality correlated predicatesCorrelated subqueries using <, >, or OR-connected correlation are not decorrelated
Volatile functionsA volatile function call — e.g. random(), gen_random_uuid(), clock_timestamp(), nextval(), set_config(), pg_notify(), pg_try_advisory_lock() — anywhere in the query, the SELECT list and ORDER BY included. Its result may differ per call and its side effects belong on origin in the session that asked, so the query is forwarded
Stable functions outside the SELECT list, or off the allowlistA stable function call in WHERE, JOIN, ORDER BY, HAVING, a window definition, an aggregate’s ORDER BY or a subquery’s SELECT list is forwarded, unless it is a LANGUAGE sql function whose body PgCache inlines (see the next row). In the query’s own SELECT list only these builtins are cached: now(), transaction_timestamp(), statement_timestamp(), date_bin(), date_part(), date_trunc(), extract(), make_timestamptz(), timezone(), to_char(), to_date(), to_number(), to_timestamp(), concat(), concat_ws(), format(), the json_agg family, and the @@ and || operators. Their value depends only on the clock and the session’s settings, and PgCache recomputes it on every hit (the query is never materialized or memoized). Other stable calls — current_database(), pg_backend_pid(), has_table_privilege(), a user-defined stable function in another language — would answer with the cache connection’s state, so they are forwarded
SQL functions outside the inlining rulesA LANGUAGE sql function that is STABLE or IMMUTABLE has its body inlined into the query, so Supabase’s auth.uid(), auth.role() and auth.jwt() and an application helper such as current_tenant() are cached as what their bodies compute, keyed on the session values the body reads. The rules are PostgreSQL’s own for inlining: not SECURITY DEFINER, no SET clause, not set-returning, no variadic, defaulted or polymorphic parameter, a body that is one SELECT returning one column, not BEGIN ATOMIC. Not inlined, and so forwarded where a stable call is: a STRICT function with arguments, a body with a FROM that names a parameter by name, a body with a LIMIT other than 1, a bare call of a name that a builtin or a second schema also defines (qualify it), and a call that the session’s search_path resolves to another function than the one PgCache inlined, which gets origin’s own answer. A body with a FROM is cached as a scalar subquery of one row; a call of it (or of any body holding a subquery) with a column argument is not inlined, since the body’s tables would capture the column’s name, and is forwarded
FETCH FIRST … WITH TIES, and a LIMIT or OFFSET expressionThe number of rows is not known before the read: WITH TIES ends on the rows’ values, and LIMIT $1::int or OFFSET ($1 - 1) * $2 is evaluated by the origin. A constant, LIMIT $1 and LIMIT $1::bigint are cached
Locking clausesFOR UPDATE, FOR SHARE, etc. are always forwarded to the origin
ViewsQueries against views are forwarded to the origin
Table inheritance and ONLYA read of a table with inheritance children, and any read naming a table with ONLY, is forwarded: the children’s rows would need a cache table keyed on their source relation as well as the key. A cached table that gains an inheritance child is evicted. Declarative partitioning is cached at every level
Partitions without the table’s key in their replica identityA partitioned table with a partition whose replica identity index lacks the table’s key is forwarded, since that partition’s updates and deletes cannot say which cached row they concern
User-defined and extension functions the cache database lacksA query calling a function that is not PostgreSQL’s own is cached only when the cache database holds the same function with the same body (install it there with the same CREATE FUNCTION); otherwise the read is forwarded rather than registered, in the SELECT list as in WHERE. Calls are resolved on the session’s search_path: a user overload of a builtin’s name on the path forwards too, since origin may pick it. A function redefined or dropped on origin evicts the queries calling it, as does moving it to another schema (ALTER EXTENSION … SET SCHEMA, a renamed schema). pgcache.queries.function_forwards counts the forwards by reason. pg_catalog.lower(x) is cached like lower(x)
Sessions with parse-changing settingsSee Session Settings
Extension and composite typesTables with columns of an extension type (e.g. pgvector’s vector, PostGIS geometry) or a composite type are not cached — these types are not reproduced in the cache database, so such queries are forwarded to the origin
COPYCOPY … TO/COPY … FROM is forwarded to the origin and never cached. A COPY … FROM write still propagates to cached tables through CDC like any other write
Non-SELECT statementsINSERT, UPDATE, DELETE, DDL are always forwarded

Queries that aren’t cached still work correctly — they pass through to the origin with no functional difference.

Client Library Compatibility

PgCache speaks the PostgreSQL wire protocol. Any client library that connects to PostgreSQL will work with PgCache without modifications.

LanguageLibraries
Pythonpsycopg2, psycopg3, asyncpg, SQLAlchemy
JavaScript/TypeScriptnode-postgres (pg), Prisma, Drizzle, Knex
JavaJDBC, HikariCP, jOOQ
Gopgx, database/sql with pq driver
Rusttokio-postgres, sqlx, diesel
Rubypg gem, ActiveRecord
.NETNpgsql, Entity Framework

ORMs and query builders work as expected — PgCache analyzes the SQL they generate the same way it handles hand-written queries.

TLS Support

ConnectionConfiguration
Client to PgCachetls_cert and tls_key in config (PEM format)
PgCache to originssl_mode = "require" or "verify-full" in [origin] section
PgCache to replicationssl_mode = "require" or "verify-full" in [replication] section

require encrypts the connection without verifying the server certificate (suitable for self-signed certs). verify-full additionally verifies the certificate against trusted public CAs.

For Docker deployments, TLS certificates can be passed as base64-encoded environment variables (PGCACHE_TLS_CERT, PGCACHE_TLS_KEY).

Note: Channel binding must be disabled for client connections to PgCache. Set channel_binding=disable in your client connection string or library configuration.

Connection Pooler Compatibility

PgCache works alongside connection poolers like PgBouncer. A common pattern is:

  • Application connects to PgCache (caching proxy)
  • PgCache connects to PgBouncer for regular queries (via [origin])
  • PgCache connects directly to PostgreSQL for CDC replication (via [replication])

This is configured using the [replication] section to override the origin connection for logical replication, which requires a direct PostgreSQL connection.

[origin]
host = "pgbouncer.example.com"
port = 6432
user = "app_user"
database = "myapp"

[replication]
host = "postgres-direct.example.com"
port = 5432

Known Limitations

  • Table, column, schema and function privileges: A cache hit is served only to a role that origin would let run the read: SELECT on every referenced column (or the table), USAGE on each table’s schema, and EXECUTE on each function called. The verdicts are read from origin once per role and object, and dropped on every DDL seen through the schema-change signal, so a REVOKE takes effect at the next read. A role whose verdict is missing or negative has its query forwarded, and origin answers with its own rows or error. Role membership and role attributes (SUPERUSER, INHERIT) are not covered by the signal; PgCache polls the role catalog for them every role_poll_interval seconds (default 5) and forgets every verdict on a change, so such a change takes effect within one interval.

  • Row-Level Security (RLS): Tables with RLS are cached by inlining the policies that apply to the reading role as an ordinary predicate, with the session values they read filled in. Supported policy expressions read current_user (or current_role, session_user) and current_setting('name') of a custom setting such as app.tenant_id, set with SET, SET LOCAL or set_config, including a claim extracted from a JSON setting (current_setting('request.jwt.claims', true)::jsonb ->> 'sub', the Supabase pattern) with coalesce, nullif, a cast or a FROM-less (select …) around it; a policy’s predicate may use any operator a cacheable query may, in the spelling PostgreSQL stores it (LIKE arrives as ~~, IN as = ANY(ARRAY[…])), including LIKE, ILIKE, the regular-expression operators, SIMILAR TO and IS DISTINCT FROM; the expression is evaluated to its value for the session before the read is keyed, so each tenant, role and user gets its own cached entry, two sessions whose values agree share one whatever else their tokens or settings differ in, and CDC keeps the entries in place. A role the policies exempt (a superuser, a BYPASSRLS role, or the owner unless FORCE ROW LEVEL SECURITY is on) reads the whole table from cache. PgCache’s own role must be a superuser or have BYPASSRLS, since population has to see every row; when it is neither, queries over secured tables are forwarded instead and pgcache check names the ALTER ROLE to run. Not inlined, and forwarded to origin: a read whose setting the session has not set, a role with no applicable permissive policy, row_security = off, a policy using a non-immutable function or an expression PgCache cannot cache, a policy whose subquery reads another RLS table (or reads a built-in setting rather than a custom one), a policy calling a function that two schemas on PgCache’s connection search_path hold under the same name and arity (its calls are resolved there, where PostgreSQL deparsed them, not on the reading session’s path), a policy calling a function PgCache cannot inline (a plpgsql helper, where a LANGUAGE sql helper such as auth.uid() is inlined with its body) or whose body reads a table by an unqualified name, and a secured table on the nullable side of an outer join. pgcache.queries.rls_forwards counts these by reason and pgcache.queries.rls_skipped the uninlinable policies. Policy changes, ENABLE/DISABLE/FORCE ROW LEVEL SECURITY and owner changes reach the cache through the schema-change signal; role and membership changes through the role catalog poll.

  • Read-after-write through rules and triggers: A connection always reads its own writes (see How Caching Works → Consistency Guarantees), including writes into partition children and through auto-updatable views. The exceptions are writes through a view with an INSTEAD OF trigger or a DO INSTEAD rule, and rows written to other tables by triggers or cascades: a read of those tables can be served from cache before CDC applies the write.

  • Transaction isolation: Cacheable reads are served from cache only in READ COMMITTED transaction blocks (and outside blocks). REPEATABLE READ and SERIALIZABLE blocks are forwarded entirely. pgcache cannot observe a default_transaction_isolation change made by a configuration reload under a live session, or by SET inside a function body; it keeps the level it last observed.

  • Schema changes (DDL): Logical replication reports row changes, not DDL. PgCache requires event triggers on the origin that report DDL as it happens and refuses to start without them (see Getting Started → Schema-Change Signal); installing them needs a superuser or the managed-service master user. It checks for them every 5 seconds while running and resets the cache when one is gone, so a schema change made between a trigger’s removal and that check can be served stale until the reset. Changes to roles and role membership are never signalled; they are observed by the role catalog poll described above.

  • Origin locale on the cache host: The cache database is created with origin’s encoding, collation, ctype and locale provider, and every cache hit applies origin’s default lc_monetary, lc_numeric and lc_time. The cache host must have these locales (the Docker image ships every glibc locale); otherwise PgCache refuses to start and names the locale to install. A locale name is not an order: at startup PgCache sorts a fixed sample of strings on origin and on the cache database and refuses to start when the two orders differ, naming the first disagreeing pair. That happens when the hosts run different collation libraries under the same name. Amazon RDS and Aurora keep the glibc 2.26 order, so the Docker image and the AMI carry a second cache PostgreSQL built on Amazon Linux 2’s glibc and start it for any libc origin older than glibc 2.28 (CACHE_PG_FLAVOUR overrides the choice). For an origin neither build orders like, run the cache on a host whose libc or ICU build matches origin’s, or use ICU or the C collation on both.

  • Cache reset on startup: The cache database is fully reset each time PgCache starts. This ensures a clean state but means the cache is cold after a restart.

  • Single-node: PgCache runs as a single process with an embedded cache. There is no distributed cache or multi-node support.

  • COPY protocol: The PostgreSQL COPY protocol is not proxied through PgCache.

  • LISTEN/NOTIFY: PostgreSQL asynchronous notifications are not proxied.

  • Replication management: PgCache creates and manages its own publication and replication slot on the origin. If PgCache is down for an extended period, WAL can accumulate on the origin. Monitor pg_replication_slots on the origin.