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

A developer tested PgCache, a proxy that caches database reads, in front of a 16-million-row Postgres database. The purpose was to compare PgCache's performance against a properly tuned Postgres database. To set up the test, the developer cloned a GitHub repository, configured Docker Compose to run two containers - one for the original Postgres on port 5433, and another for PgCache on port 5432.

The PgCache container kept its cache in shared memory, which required at least twice the shared_buffers size. The developer then ran a benchmark test, executing four queries - point lookup by id, count users by tier, revenue by country, and top products per category - 150 times each with a concurrency level of 10. Both the original database and PgCache served the queries, but PgCache significantly reduced the query times from hundreds or thousands of milliseconds to under one millisecond.

The developer also tested PgCache's ability to handle updates - inserting a new enterprise user directly into the original Postgres database. PgCache's cached count updated almost immediately after the insertion, showing that the cache remained consistent with the latest data. The first ninety seconds of the test were slower as PgCache was syncing with the Postgres database, but after that, the cached reads were almost instantaneous, demonstrating the efficiency of PgCache in reducing query times.