PgCache 0.9.1
0.9.1 makes the schema-change event triggers a requirement, keeps partitioned tables current at every level, compares literals and text the way their columns do, and caches the session-dependent queries that Supabase and PostgREST applications send. The changelog lists every change.
Upgrading from 0.9.0
The schema-change triggers are required
Logical replication carries row changes but not DDL. PgCache learns of ALTER TABLE, DROP TABLE, partition changes, and policy, grant and function changes from two event triggers on origin, pgcache_ddl_command_end and pgcache_sql_drop. In 0.9.0 they were optional: without them, a schema change on a cached table was seen only at the table’s next write, and a dropped table or a partition attached with rows could serve stale results.
0.9.1 refuses to start unless both triggers are installed, enabled and at the version its own install script records. The install 0.9.0 left behind is at another version, so every origin needs the install again:
-
PgCache’s role is a superuser (on RDS and Aurora, the master user): PgCache installs or updates the triggers at startup, as before. Nothing to do.
-
Otherwise: before upgrading, an administrator runs the install SQL that the origin check prints:
docker run --rm pgcache/pgcache:0.9.1 check \ --upstream postgres://user:password@your-db-host:5432/myapp
The check reports a missing or outdated signal as a failure, unless PgCache’s role will install it at startup. While PgCache runs, it checks the triggers every 5 seconds; if one is dropped or disabled, it resets the cache and forwards reads until they are back. See Getting Started → Schema-Change Signal.
Removing 0.9.0’s partition truncate triggers
0.9.0 put a statement-level pgcache_truncate trigger on every partition in the origin database, to report a partition’s truncate. The replication stream now reports it itself, so the triggers are no longer used and PgCache ignores what they send.
PgCache does not drop them: dropping a trigger locks its table, which is an administrator’s decision. It logs a warning at startup, with a count, while they remain. To remove them, at a time when the locks are acceptable:
DROP FUNCTION IF EXISTS pgcache.partition_truncate() CASCADE;
DROP FUNCTION IF EXISTS pgcache.partition_signal_install(regclass);Partitions and Inheritance
A partitioned table can be cached at every level at once: the root, an intermediate partition and a leaf each keep their own entry, kept current by CDC whatever order they were first read in. In 0.9.0, once the root was cached, a partition’s changes streamed under the root’s name, so the partition’s own cached queries stopped receiving them and served stale rows. Rows moving between partitions, and partitions attached with a different column order, are applied correctly too.
Two shapes are now forwarded to origin rather than cached:
- A partition whose replica identity lacks the table’s key. That partition’s updates and deletes cannot say which cached row they concern. 0.9.0 cached the table and evicted it at every row that partition streamed.
- Table inheritance and
ONLY. A read of a table with inheritance children, or any read naming a table withONLY, is forwarded, and a cached table that gains an inheritance child is evicted. 0.9.0 ignoredONLY, and a parent’s cached result missed rows changed in its children.
Literals and Text Ranges
PgCache now reads each literal as a value of its column’s type when it checks subsumption, the constraint index and read-after-write. '10.50' and 10.5 are one numeric, 'a ' and 'a' one char(n), and uuids compare regardless of case. Two things follow:
- Date, timestamp and string-spelled numeric ranges subsume. A cached
WHERE created_at >= '2026-01-01'coversWHERE created_at >= '2026-06-01'; before, such ranges never subsumed. - A read-after-write miss is fixed. A pending write that named a row by another spelling of the read’s value (
'10.50'against10.5) was taken for a different row, so the read could be served before CDC applied the write.
Text ranges follow the column’s collation. PgCache decides a text range or ordering predicate itself only on a column whose collation orders by bytes (C, POSIX or the builtin provider), where those ranges now subsume too; under any other collation the cache database evaluates it. In 0.9.0, CDC compared text bounds by bytes, so under a linguistic collation (where 'a' < 'B') a row inserted inside a cached range could be left out of it.
Cache tables now carry each column’s declared collation, so a served ORDER BY on a COLLATE "C" column in an en_US database follows the column. A table with a column whose collation the cache database lacks, or orders differently, is forwarded.
Supabase and PostgREST
Applications that put the tenant or user in the session, as Supabase and PostgREST do, are now served from cache:
- Queries keyed on the session’s value. A query reading
current_user,current_setting('app.tenant')::intor a claim extracted from a JSON setting ((current_setting('request.jwt.claims', true)::jsonb ->> 'sub')::uuid) is cached per value, so each tenant or user has its own entry. Two tokens of one user share an entry although theirexpdiffers. In 0.9.0 such queries were forwarded. - SQL functions inlined. A STABLE or IMMUTABLE
LANGUAGE sqlfunction is replaced by its body before PgCache judges the query, soauth.uid(),auth.role()andauth.jwt()are cached in queries and in row-level security policies, as are application helpers such ascurrent_tenant(). Redefining the function on origin evicts the queries that inlined it. plpgsql and other functions outside PostgreSQL’s own inlining rules are forwarded as before. - The request preamble is not a write.
SELECT set_config('request.jwt.claims', $1, true)no longer makes the rest of the transaction forward under the read-after-write gate, so a PostgREST request (begin, set the claims, read) is served from its second request on. - Settings changed out of sight are read back. A setting or role switched where PgCache cannot see the value (
set_configinside a function,SET ROLEin plpgsql,DO,CALL, a multi-statement batch) is read from the session before the next read over a secured table. - PgCache’s own role checked at runtime. Losing
SUPERUSERorBYPASSRLSis seen without a restart: reads over secured tables forward until the role can bypass again, and a population that can no longer see every row fails rather than loading only part of the table.
Function calls are also resolved the way PostgreSQL resolves them, by schema, name and arity on the session’s search_path. A user function is cached when the cache database holds the same function with the same body, and forwarded at registration otherwise; volatile functions are always forwarded, and stable builtins such as now() in the SELECT list are recomputed on every hit. See Compatibility → Known Limitations.
Text Ordering Matches Origin
The Docker image moved from Alpine to Debian (postgres:18-trixie) with every glibc locale installed. Alpine’s cache cluster had no collation data, so a text ORDER BY served from cache could come back in a different order from origin. The compressed image grows from about 128 MB to about 240 MB per architecture, almost all of it locale data.
The image also carries a second cache PostgreSQL, built on Amazon Linux 2’s glibc, which PgCache starts for origins whose libc predates glibc 2.28, as RDS and Aurora do. CACHE_PG_FLAVOUR forces either build.
At startup PgCache creates the cache database under origin’s encoding, collation and locale provider, then sorts a fixed sample of strings on both and refuses to start if the orders differ, naming the first pair that disagrees.