Munchable reads a packaged food's label and tells you whether it fits your gut condition. Point the phone at a barcode, get a verdict in about a second. The conditions it reasons about are at munchable.app/conditions, and a public slice of the ingredient reasoning is at munchable.app/answers, where every page is produced by running the same engine the app runs.
Between the barcode and the verdict there is a small bookkeeping question the app has to answer constantly: which version of the ingredient data am I holding? The phone caches a snapshot of it, the server caches it in Redis and in memory, and every lookup whose caches have gone cold has to ask Postgres for a version string before it can say anything.
That version read, on a 40,000 row table, took 815 ms.
The query
The version is the newest updated_at among the rows that are live. Two tables, two aggregates, one comparison. Written with Drizzle it looked like this, and it looks correct:
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);
There is an index on updated_at. max() over an indexed column is the textbook case for the planner's min/max optimisation: instead of aggregating the table, Postgres rewrites the query into "walk the index from the end and take the first row". You can see it in an EXPLAIN as an InitPlan containing a Limit over an Index Scan Backward.
We were not getting that plan. We were getting a sequential scan and an aggregate, every single time.
FILTER is what stopped it
The optimisation lives in src/backend/optimizer/plan/planagg.c. Before Postgres will turn your max() into an index probe, can_minmax_aggs() checks the aggregate for a handful of disqualifiers, and one of them is this:
an aggregate with a FILTER clause cannot be optimised
Which is reasonable when you think about what the rewrite means. "Walk the index backwards and stop at the first row" is only equivalent to max() if every row the index hands you is a row that counts. A FILTER says some rows do not count, and the index on updated_at knows nothing about status, so the planner cannot know how far it would have to walk. It gives up on the rewrite entirely and falls back to reading everything.
The fix is to say the same thing in a place the planner can reason about:
const VERSION_SQL = sql<string>`coalesce(
(extract(epoch from max(${catalogTaxonomyEntries.updatedAt})) * 1000)::numeric(20,3)::text,
'0'
)`;
db.select({ version: VERSION_SQL })
.from(catalogTaxonomyEntries)
.where(eq(catalogTaxonomyEntries.status, 'active'));
max(x) FILTER (WHERE p) over all rows and max(x) over the rows where p holds are the same value. They are not the same query plan. The second one gets the index scan: Postgres walks updated_at from the newest end, rechecks status on each row it touches, and stops at the first live one. In our data the newest rows are overwhelmingly live, so it reads about three index pages. 815 ms became single digit milliseconds.
The trap is that FILTER is the more modern, more readable syntax, and it is the one you reach for when you have several aggregates in one select. If you have two, that is often still the right call. If the aggregate is a min() or a max() that you were relying on an index to answer, moving the condition into WHERE is not a style preference, it is the difference between a probe and a scan.
Two smaller notes from the same change:
- The version read happens twice, once per table, rather than as one query with two filtered aggregates over a join. Keeping them separate is what lets each one get its own index probe.
- The condition is written as
WHERE status = 'active'in exactly one helper function that both the cached read path and the in-transaction read path call. A version query that disagrees with itself between those two paths is a cache that never settles.
The TTL that was doing the opposite of caching
While measuring this we found the other half of the problem, which had nothing to do with SQL.
The snapshot of the ingredient data is cached in Redis. The TTL was five minutes. That number was chosen, I assume, by asking "how stale can this data be?" and answering "not very".
But the write path already evicts the snapshot and republishes the version on every write. The TTL is not the freshness mechanism, it never was. It is the backstop for the one case where an eviction gets lost. So what the five minute TTL actually bought us was this: Munchable's traffic is well under one taxonomy read per five minutes, so after any quiet spell the next reader found an empty cache and rebuilt the snapshot from Postgres. A 40,000 row read plus shaping, measured at 36 times a day, purely because a timer had decided that correct data was too old to keep.
It is now 24 hours:
// 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;
The rule I would write on the wall: if you invalidate on write, your TTL is insurance against a missed invalidation, and it should be sized to how long you can tolerate one, not to how fresh you want the data. Sizing it like a freshness knob turns a cache into a scheduled rebuild.
See it working
The 373 pages under munchable.app/answers are built by running the engine over the same data this version check guards, so they are the cheapest way to see what the snapshot is for. Two to try:
- Does E330 cause reflux? shows a single additive resolved to what a label actually prints.
- Is onion low FODMAP? shows the same for an ingredient that hides behind a dozen wordings.
For the aisle version, where the version check runs for real before the verdict appears, sign in at app.munchable.app and scan something in your cupboard.
Top comments (0)