max(x) uses the index, max(x) FILTER (where ...) scans the table

The source material outlines how a mobile app called Munchable uses an ingredient database to determine if a packaged food item fits a user's gut condition. The key issue discussed is the performance of a SQL query that finds the latest version of the ingredient data based on a status field called "active."

When the query is written as MAX(updated_at) FILTER (WHERE status = 'active'), Postgres cannot utilize an index to perform this operation efficiently. This is because the FILTER clause prevents the optimizer from rewriting the query to use the index. The query instead forces a sequential scan of the entire table and applies an aggregate function, which is much slower.

By rewriting the query as MAX(updated_at) WHERE status = 'active', Postgres can use the index on the updated_at column to quickly find the most recent row with the active status. This results in a significant performance improvement, reducing the query time from 815 milliseconds to a few milliseconds.

The article also mentions a related issue with caching the ingredient data in Redis, where the Time-To-Live (TTL) was set to five minutes. This TTL was being used as a fallback in case of cache eviction, rather than as a measure of data freshness. The write path already ensures that the snapshot and version are updated whenever the data changes, so the TTL was not actually providing fresh data but rather serving as a backup mechanism.

The article concludes by adjusting the TTL to 24 hours, recognizing that it should only be a backup in case of eviction, not a freshness guarantee.