How I Put PgCache in Front of a 16-Million-Row Postgres Database

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 p