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 Version | Status |
|---|---|
| 18 | Supported (primary target) |
| 17 | Supported |
| 16 | Supported |
| 15 and below | Not 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:
| Settings | Effect |
|---|---|
search_path, current role | Decide 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_abbreviations | Decide 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_isolation | Decides 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-zerogin_fuzzy_search_limit, or aclient_encodingother than UTF-8. These change how a statement is parsed.- A
search_paththat listspg_catalogafter 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
| Pattern | Example |
|---|---|
| Single table SELECT | SELECT * FROM users WHERE active = true |
| INNER JOIN | SELECT u.id, u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id |
| LEFT / RIGHT JOIN | SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id |
| Predicates in JOIN ON | SELECT 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 JOIN | SELECT * FROM users JOIN orders USING (user_id), SELECT * FROM users NATURAL JOIN orders |
| CROSS JOIN | SELECT * FROM colors CROSS JOIN sizes |
| Equality comparisons | WHERE status = 'active' |
| Inequality comparisons | WHERE age > 18, WHERE price <= 100 |
| AND / OR conditions | WHERE active = true AND role = 'admin' |
| IN / NOT IN | WHERE id IN (1, 2, 3) |
| IS NULL / IS NOT NULL | WHERE deleted_at IS NULL |
| NOT expressions | WHERE NOT archived |
| GROUP BY / HAVING | SELECT status, COUNT(*) FROM orders GROUP BY status |
| ORDER BY | SELECT * FROM products ORDER BY price ASC, including NULLS FIRST / NULLS LAST |
| Aggregate functions | SELECT COUNT(*), SUM(total) FROM orders |
| FILTER on aggregates | SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders (also inside OVER (...)) |
| Window functions | SELECT 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 expressions | SELECT CASE WHEN active THEN 'yes' ELSE 'no' END FROM users |
| Arithmetic expressions | SELECT price * quantity AS total FROM line_items |
| JSON operators | WHERE data ->> 'kind' = 'invoice', WHERE data @> '{"tags": ["red"]}', WHERE data ? 'k', SELECT data #> '{who,name}' FROM docs: ->, ->>, #>, #>>, @>, <@, ?, `? |
| Typecasts | SELECT col::int FROM t, WHERE created_at = '2026-01-01'::date |
| Uncorrelated subqueries | WHERE id IN (SELECT user_id FROM orders), derived tables, scalar subqueries |
| CTEs | WITH active AS (SELECT * FROM users WHERE active) SELECT * FROM active |
| Set operations | SELECT id FROM users UNION SELECT id FROM admins |
| Functions in SELECT | SELECT COALESCE(name, 'unknown') FROM users |
COALESCE, NULLIF, GREATEST, LEAST anywhere | WHERE COALESCE(deleted_at, created_at) > '2024-01-01': evaluated by PostgreSQL itself, so immutable wherever they stand |
| Immutable functions in WHERE | WHERE lower(name) = 'foo', WHERE abs(balance) > 100 |
| LIMIT / OFFSET | SELECT * 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 TO | WHERE 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 FROM | WHERE tag IS DISTINCT FROM 'hidden': NULL counts as a value, as on origin |
| BETWEEN | WHERE price BETWEEN 10 AND 100, including SYMMETRIC |
| ANY / ALL | WHERE id = ANY(ARRAY[1, 2, 3]), WHERE score > ALL(ARRAY[80, 90]) |
| Mixed wildcards and columns | SELECT *, id, name FROM users |
| EXISTS / NOT EXISTS | WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id) |
| Correlated subqueries | WHERE id IN (SELECT ...), NOT IN (SELECT ...), scalar subqueries, EXISTS/NOT EXISTS — decorrelated into equivalent JOINs |
| IS [NOT] TRUE/FALSE/UNKNOWN | WHERE active IS TRUE, WHERE flag IS NOT FALSE |
| Extended query protocol | Parameterized queries via Parse/Bind/Execute |
Not Cached (Forwarded to Origin)
| Pattern | Reason |
|---|---|
| FULL JOIN | Not yet supported for caching |
| LATERAL subqueries | Not supported |
| RECURSIVE CTEs | Not supported |
| Non-equality correlated predicates | Correlated subqueries using <, >, or OR-connected correlation are not decorrelated |
| Volatile functions | A 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 allowlist | A 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 rules | A 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 expression | The 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 clauses | FOR UPDATE, FOR SHARE, etc. are always forwarded to the origin |
| Views | Queries against views are forwarded to the origin |
Table inheritance and ONLY | A 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 identity | A 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 lacks | A 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 settings | See Session Settings |
| Extension and composite types | Tables 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 |
| COPY | COPY … 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 statements | INSERT, 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.
| Language | Libraries |
|---|---|
| Python | psycopg2, psycopg3, asyncpg, SQLAlchemy |
| JavaScript/TypeScript | node-postgres (pg), Prisma, Drizzle, Knex |
| Java | JDBC, HikariCP, jOOQ |
| Go | pgx, database/sql with pq driver |
| Rust | tokio-postgres, sqlx, diesel |
| Ruby | pg gem, ActiveRecord |
| .NET | Npgsql, 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
| Connection | Configuration |
|---|---|
| Client to PgCache | tls_cert and tls_key in config (PEM format) |
| PgCache to origin | ssl_mode = "require" or "verify-full" in [origin] section |
| PgCache to replication | ssl_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 = 5432Known Limitations
-
Table, column, schema and function privileges: A cache hit is served only to a role that origin would let run the read:
SELECTon every referenced column (or the table),USAGEon each table’s schema, andEXECUTEon 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 aREVOKEtakes 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 everyrole_poll_intervalseconds (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(orcurrent_role,session_user) andcurrent_setting('name')of a custom setting such asapp.tenant_id, set withSET,SET LOCALorset_config, including a claim extracted from a JSON setting (current_setting('request.jwt.claims', true)::jsonb ->> 'sub', the Supabase pattern) withcoalesce,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 (LIKEarrives as~~,INas= ANY(ARRAY[…])), includingLIKE,ILIKE, the regular-expression operators,SIMILAR TOandIS 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, aBYPASSRLSrole, or the owner unlessFORCE ROW LEVEL SECURITYis on) reads the whole table from cache. PgCache’s own role must be a superuser or haveBYPASSRLS, since population has to see every row; when it is neither, queries over secured tables are forwarded instead andpgcache checknames theALTER ROLEto 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 connectionsearch_pathhold 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 aLANGUAGE sqlhelper such asauth.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_forwardscounts these by reason andpgcache.queries.rls_skippedthe uninlinable policies. Policy changes,ENABLE/DISABLE/FORCE ROW LEVEL SECURITYand 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 OFtrigger or aDO INSTEADrule, 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 COMMITTEDtransaction blocks (and outside blocks).REPEATABLE READandSERIALIZABLEblocks are forwarded entirely. pgcache cannot observe adefault_transaction_isolationchange made by a configuration reload under a live session, or bySETinside 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_numericandlc_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_FLAVOURoverrides 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 theCcollation 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_slotson the origin.