Munchable's server keeps one shaped snapshot of curated ingredient vocabulary that the apps load, plus a single version number for it. It is about 40,000 rows in Postgres, it changes a few times a day when a curation job writes, and devices ask for it constantly.
So it is cached, cheapest tier first: module memory for 60 seconds per serverless instance, then Redis, then Postgres. Textbook.
For several weeks it had three separate bugs, none of which threw anything, and the combined effect was that the expensive tier was doing nearly all the work. Here they are in increasing order of how much I liked finding them.
One: the cap was measured on the string we did not store
The snapshot goes into Redis gzipped and base64-encoded, because Upstash's REST API carries roughly a megabyte per request and the raw JSON is bigger than that. There was a cap, and the cap had a bug:
async function cacheInRedis(snapshot: OverlaySnapshot) {
if (!redis) return;
try {
await redis.set(VERSION_KEY, snapshot.version, { ex: REDIS_TTL_S });
const packed = await encodeSnapshot(snapshot);
if (packed.length <= REDIS_MAX_BYTES) {
await redis.set(OVERLAY_KEY, packed, { ex: REDIS_TTL_S });
} else {
// ...
await redis.del(OVERLAY_KEY);
}
} catch {
// ...
}
}
The structure is right. The constant was not. REDIS_MAX_BYTES was 512 KB, and it had been measured against the uncompressed JSON, while the length being compared is the compressed, base64-encoded payload. Compressed, this snapshot is a few hundred KB. Uncompressed it passed 512 KB once the overlay reached roughly twenty thousand rows, and then never passed again.
Read that else branch again with that in mind. Every read that fell through to Postgres rebuilt the snapshot, tried to cache it, decided it was too big, and deleted the key. The Redis tier did not degrade. It stopped existing, and it kept itself from coming back.
What that cost: every instance whose 60 second memory cache had expired paid a full table read, which on the Free plan measured about seven seconds at 32,000 rows. On a serverless host that is not one instance, it is each of them, on their first request.
The fix is three lines of comment and one number:
/**
* Cap on the snapshot as STORED, i.e. after gzip (see `cacheInRedis`).
*
* Upstash's REST API takes about a megabyte per request, so this is the
* ceiling that matters, not the size of the JSON.
* ...
* If this is ever exceeded again the fix is to shard the key, not to raise it
* past what one Upstash request can carry.
*/
const REDIS_MAX_BYTES = 900 * 1024;
And the branch learned to speak:
console.warn(
`[taxonomy] overlay snapshot is ${packed.length} bytes compressed, over the ${REDIS_MAX_BYTES} byte cache cap; ` +
`serving from Postgres on every memory-cache miss. Shard the key.`,
);
The general rule I took from this: a cap must be compared against the artefact that is actually stored, and a code path that disables a cache has to log. Silence is the worst possible outcome here, because a disabled cache looks exactly like a slow database, and I spent a while looking at the wrong one. The sentence to write in the log is not "cache miss", it is what is now happening on every request and what to do about it.
Two: the TTL was pretending to be the freshness mechanism
The Redis TTL was five minutes. That sounds conservative and prudent. It was neither, because of how writes work in this module: a write does not refill the cache, it forgets it. The curation path publishes the new version and evicts the snapshot, so the next reader rebuilds from the table and the cached copy can never be stale.
Which means a TTL is not what keeps the data fresh. Eviction is. The TTL is only a backstop against an eviction being lost.
With traffic comfortably below one vocabulary read per five minutes, a five minute TTL meant the common case was: a read arrives, the key has expired, rebuild from Postgres. Measured at 36 full rebuilds a day, i.e. the cache was mostly a mechanism for adding a Redis round trip to a Postgres query.
// A day, not minutes: `publishVersion` evicts the snapshot and republishes
// the version on every write, so the TTL is only a backstop for a lost
// eviction, not the freshness mechanism.
const REDIS_TTL_S = 24 * 60 * 60;
If your write path evicts, your TTL should be set by how bad a lost eviction is, not by how fresh you want the data. Those are different questions and the second one is already answered.
One safety rail makes the long TTL comfortable: a reader that already knows a newer version than the cached one bypasses both caches. A device that has seen version N in a response header can never be answered with something older by an instance whose memory cache is behind.
Three: max(col) FILTER (where ...) will not touch your index
This is the one I would put on a poster.
Checking whether anything changed is a version read, and the version is max(updated_at) across the rows that count. In Drizzle it looked like this:
// Before
const VERSION_SQL = sql<string>`coalesce((extract(epoch from
max(${catalogTaxonomyEntries.updatedAt})
filter (where ${catalogTaxonomyEntries.status} = 'active')
) * 1000)::numeric(20,3)::text, '0')`;
db.select({ version: VERSION_SQL }).from(catalogTaxonomyEntries);
Perfectly reasonable SQL. FILTER is the modern, readable way to restrict an aggregate, and I had reached for it because the restriction belongs to the aggregate conceptually.
Postgres has a specific optimisation for min() and max(): with a matching btree index it rewrites the aggregate into an index scan that reads a single entry from one end. explain shows it as a Result node with an InitPlan doing Limit over an index scan. It is the difference between touching three index pages and reading the table.
That rewrite does not apply when the aggregate carries a FILTER clause. With FILTER, you get a full scan, every time, and nothing in the query looks wrong.
Cost here: 815 ms on the Free plan for 40,000 rows, on every product lookup whose caches had gone cold. The fix is to say the same thing in the place the planner understands:
/**
* The `status = 'active'` restriction is a WHERE, not a FILTER on the
* aggregate, and that is load bearing: Postgres only turns `max(col)` into
* "read the last index entry" when the aggregate has no FILTER clause.
*/
function taxonomyVersionQuery(ex: DbExecutor) {
return ex
.select({ version: VERSION_SQL })
.from(catalogTaxonomyEntries)
.where(eq(catalogTaxonomyEntries.status, 'active'));
}
Identical result, three index pages. Both version reads were duplicated inline in three places before, which is why they are functions now: a query with a planner trap in it should exist once.
Note the asymmetry, because it is not obvious. WHERE and FILTER are interchangeable only when the aggregate is the whole select list and you do not need the unfiltered rows for anything else. The moment you want two differently filtered aggregates in one statement, FILTER is the right tool and you have given up the index shortcut for both. That is a fine trade if you know you are making it.
The shape of all three
Each of these bugs had the same signature: correct-looking code, no error, no log line, and a cost paid on the slowest path. The database was never the problem. The thing in front of the database was quietly declining to do its job, three different ways, and the only reason any of it surfaced is that a 0.5 GB Free plan instance is slow enough to notice when you ask it to read 40,000 rows.
If you keep one thing: after you add a cache, go and measure how often it actually serves. Not whether it works. How often it is used. A cache with a 3 percent hit rate and a cache that is broken are the same cache.
Look at it yourself
- munchable.app/about is the page that says we maintain the ingredient database ourselves. The snapshot in this post is how that curated vocabulary reaches a phone, and these three bugs were all in the delivery of it rather than in its contents.
- munchable.app/privacy is worth opening alongside: nothing in the cache above is keyed on a person, because the conditions you pick and your scan history never leave your device. A cached snapshot shared by every reader is only possible because it contains no one's data.
- munchable.app/conditions lists the seven conditions the app covers, each with its own guide. Those seven are the reason the vocabulary is big enough for the snapshot to need gzip at all.
