Disclaimer: This is a side project, not a production story. The slow-query problem is real, but the database is synthetic data I generated to make it show up on demand. I have no connection to PgCache. Everything here is in a repo you can clone and run. I tested version 0.6.2.
A handful of dashboard queries on one of my projects were fine for a year and then weren't: count users by tier, revenue grouped by country, best-selling products per category. Nothing exotic, just aggregates and joins over tables that had gotten big.
The usual fixes didn't sit right with me. A materialized view means picking a refresh interval and serving slightly stale numbers in between. Redis in front of Postgres means writing and maintaining code that knows which cache entries to throw away on every write. A read replica just runs the same slow query on another machine.
PgCache offers a different trade. It's a proxy that talks the Postgres wire protocol, so your app connects to it as if it were the database. It caches reads. And instead of expiring entries on a timer, it follows Postgres's replication stream and refreshes a cached result when the rows behind it change. That stream is the same feed Postgres uses to copy data to a standby server , a running log of every insert, update, and delete.
The "no timers, no manual invalidation" part is the interesting claim. Here's how it held up.
A database big enough to be slow
First I needed a database where "slow" was real and not a rounding error. I wrote a seed script for a small e-commerce schema and filled it to about 16 million rows:
| Table | Rows | Notes |
|---|---|---|
users |
1,000,000 | 10 countries; tiers 50% free / 33% pro / 17% enterprise |
products |
2,000 | 10 categories |
orders |
5,000,000 | four statuses, random totals, spread over two years |
order_items |
10,000,000 | about two per order |
I added indexes on every foreign key and on every column the test queries filter or group by. That was on purpose. I wanted to compare PgCache against a Postgres that had been tuned properly, not one left slow so that any cache would look good next to it.
Getting it running
The repo has a Docker Compose file with two containers: the origin Postgres on port 5433, and PgCache on 5432.
git clone https://github.com/HuluWZ/pgcache-demo.git
cd pgcache-demo
cp .env.example .env
docker compose up -d
The setup was smaller than I expected. On the Postgres side you turn on logical replication (wal_level = logical) and give the login role permission to replicate. That's the whole list. I had started with a hand-written pg_hba.conf rule and a CREATE PUBLICATION line in the init script, then deleted both once I checked what PgCache does on its own at startup: it creates its own publication and replication slot, and it scopes that publication to just the four tables it ends up caching. I confirmed it afterwards by querying pg_publication. The connection it opens for replication authenticates with the ordinary password.
The one rough edge is memory. PgCache keeps its cache in /dev/shm and won't start unless that's more than twice the size of its internal shared_buffers. The error message tells you the number it wants, so it's a one-line change in .env, but it will stop the first boot cold. After that, the pitch was accurate: change the port your app dials from 5433 to 5432 and leave everything else alone.
The speed test
npm run benchmark runs four queries 150 times each at concurrency 10, against the origin directly and through PgCache. It warms both sides first, so the comparison is warm cache against warm cache ,not a cold database against a primed proxy. After the proxy run it reads PgCache's own hit counter and prints how many of the 150 queries were actually served from cache, so a fast number that quietly came from the database can't hide. All 150 were cache hits every run.
| Query | Origin | Through PgCache |
|---|---|---|
| point lookup by id | 0.3 ms | 0.3 ms |
| count users by tier (1M rows) | 140 ms | 0.5 ms |
| revenue by country (5M-row join) | 1.4 s | 0.5 ms |
| top products per category (10M-row join) | 2.9 s | 0.6 ms |
The point lookup is a wash, and I left it in for that reason. It's already sub-millisecond on the origin, so sending it through another process just adds a hop. That's the honest answer to "does this speed up everything": no, not the queries that were already fast. The other three are the reason you'd do this at all. Each drops from hundreds or thousands of milliseconds to under one, because it stops touching Postgres.
Something I didn't expect: when I ran the top-products query with an extra WHERE filter it hadn't seen before, PgCache answered it from the chunk it had already cached instead of going back to the database. I looked it up in their docs to be sure I wasn't imagining it ,it's a real feature called predicate subsumption. A cached WHERE country = 'US' can also answer WHERE country = 'US' AND tier = 'pro'.
Does it stay correct?
A fast cache that hands back stale rows is worse than no cache. This was the test I cared about.
The test writes a new row straight to the origin Postgres on a connection PgCache never sees, then times how long a cached count served by the proxy takes to catch up:
- Ask PgCache for the count of enterprise users. It misses, runs the query on Postgres, gets 166,666, caches it, returns it.
- Insert one new enterprise user directly on Postgres ;not through PgCache.
- That insert shows up on Postgres's replication stream, which PgCache is following. PgCache updates the cached count.
- Ask PgCache for the count again. It returns 166,667 from cache, already correct.
In steady state this was dull, which is what you want. Inserts, updates, and deletes all showed up in the cached result in under a tenth of a second ; usually 80 to 100 milliseconds, run after run. A write that never goes near the cache lands in a cached read almost immediately.
The first ninety seconds are different
The first time I ran the correctness check right after docker compose up, it took eight seconds instead of a tenth of one. I ran it eight more times. The same eight seconds every time for about a minute and a half, and then it snapped back to normal and stayed there.
I read PgCache's design notes rather than guess. One of them (ADR-035) says that while PgCache is still filling its cache at startup, the first read of a given shape is "watermark-bound under CDC lag" on an idle database. In plainer words: on a fresh, quiet database with no other traffic, that first read waits for the replication stream to catch up, and the wait can be a few seconds. My setup was exactly that , a new container with no load except the one write I was timing. It's a known trade-off, and it clears itself once the cache finishes warming.
I did make the check less alarming about it. It used to give up after five seconds and print a line about checking the replication slot, which sends you after the wrong thing. Now it waits fifteen, and a slow run gets labelled slow with PgCache's lag and cache-warming numbers printed next to it, so it's clear what you're looking at.
TL;DR on the startup window: Wait ~90 seconds after
docker compose upbefore trusting any invalidation latency number. This is expected behavior, not a bug.
A healthcheck that lied to me
PgCache has a /readyz endpoint that's meant to return 200 only once the cache is usable, so I pointed the Compose healthcheck at it, figuring that would let a deploy wait out the slow window above.
It didn't. /readyz went green about three seconds into a cold start, well before the ninety-second window closed, so a rollout that trusts it still walks into the slow reads.
Chasing that turned up something dumber and more useful, though. The container kept reporting unhealthy even when the proxy was clearly fine and curl to the same URL from my laptop returned 200. Inside the container, wget http://localhost:9090/readyz said "connection refused."
The container's /etc/hosts mapped localhost to both the IPv4 and IPv6 loopback addresses, BusyBox wget tried IPv6 first, and PgCache's metrics server only listens on IPv4. So the healthcheck was hitting an address nothing was on, failing forever, and the container never came up healthy even though nothing was actually wrong.
Two fixes:
# docker-compose.yml
healthcheck:
test: ["CMD", "wget", "-qO-", "http://127.0.0.1:9090/readyz"] # literal IPv4, not localhost
start_period: 120s # give the cache time to warm before the check counts
Not really PgCache's fault, but worth knowing before you wire up that probe.
One DELETE that hung, once
Partway through the correctness testing, a single DELETE run went past ten seconds while every run around it finished in under a hundred milliseconds. A ten-second stall on a cache update is the kind of thing you either wave off or take seriously, and that felt worth taking seriously.
So I wrote a proper stress harness instead of re-running by hand. It runs the same concurrent poll against a direct-to-origin DELETE, captures PgCache's metrics and container logs on any timeout, and appends every result to a file so I could accumulate runs across sessions.
I ran a bit over 300 more. It never happened again. Not once.
I also checked whether anything in PgCache's design could produce a multi-second pause. Their notes on cache eviction describe a mostly lock-free approach with slow cleanup pushed onto a background thread, and say plainly that it "never causes a wrong read." Nothing in there looks like a stop-the-world pause. That doesn't prove the stall was my laptop's fault, but I couldn't find a mechanism in PgCache that would cause it and I couldn't make it come back.
The larger sample did show a heavier tail than I first thought , a p99 around 1.3 seconds and a max near 2 ; but nothing close to the original ten.
An older bug, asked about directly
I'd seen a mention that an early 0.5.0 build could leave a cached aggregate stale after a DELETE sent through the proxy. Rather than carry that forward as an assumption, I asked the PgCache team. They confirmed it had been a real bug and that it was fixed in 0.6.0. Of everything in this project, that was the one question that got a clean, direct answer.
Would I use it?
For the problem I started with, yes. The expensive dashboard queries went from unusable to instant, and they stay correct when the data changes under them without me writing a line of invalidation code.
Two things I'd pass on to anyone trying it:
- Give it about a minute and a half after any restart before you trust a latency number, and don't lean on
/readyzto cover that window ,it goes green too early. - Run the DELETE stress test on your own hardware before you take my "never reproduced" as a promise.
Run it yourself
HuluWZ
/
pgcache-demo
Benchmarks PgCache (a transparent Postgres cache with CDC invalidation) against direct origin queries on a 16M-row dataset.
PgCache Demo
A reproducible benchmark of PgCache — a transparent read-through cache that speaks the PostgreSQL wire protocol and keeps cached results fresh with Change Data Capture (CDC) over logical replication.
The demo stands up a 16M-row Postgres database behind PgCache, then measures the same queries run two ways: directly against the origin, and through the proxy with a warm cache. It also measures how fast a write to the origin propagates to the cache.
Architecture
app/ (TypeScript: benchmark.ts, cdc-demo.ts)
│
proxy :5432 │ origin :5433
(cached reads) │ (direct, for comparison)
┌───────────────┴───────────────┐
▼ ▼
┌───────────────┐ ┌──────────────────┐
│ PgCache │ │ │
│ proxy │ │ Postgres 17 │
│ :5432 │ │ origin │
│ :9090 mtrx │ │ │
└───────┬───────┘ └─────────┬────────┘
│ SELECT passthrough + writes │
│ ──────────────────────────────▶│
│ logical replication (CDC) │
│ ◀── pgcache_pub / pgcache_slot ─│
└────────────────────────────────┘
- Cacheable
SELECTs are served from PgCache's…
You need Docker with Compose v2, Node 18 or newer, and about 6 GB of free memory.
git clone https://github.com/HuluWZ/pgcache-demo.git
cd pgcache-demo && cp .env.example .env # use the -amd64 image tag on Intel/AMD
docker compose up -d
docker compose logs -f postgres # wait for the row-count lines
cd app && npm install
npm run benchmark # the speed test
npm run cdc-demo # the correctness test (wait ~90s after startup)
npm run delete-stress -- --runs=100 # add 100 runs to the DELETE stress harness
npm run delete-stress -- --summarize # stats across every batch you've run
If you've tried PgCache , or a similar approach , I'd be curious how it held up. Questions or corrections welcome in the comments.
PgCache: pgcache.com and github.com/PgCache/pgcache. A different project with a similar name shows up in search; the one I tested is the one linked from pgcache.com.
Top comments (1)
Love the transparency here, especially running the stress test to chase down that one hanging DELETE. It's rare to see a tech deep dive that actually highlights the startup quirks and Docker networking traps alongside the performance wins. Great read!