How does query result caching work in an ORM or database layer, and what invalidates it?

#Where query result caching lives

"Query result caching" means storing the output of a query — not a raw table row, but the shaped result of running a specific SQL statement (or ORM query) with specific parameters — so that a repeat of the same query can be answered without touching the database at all. It shows up at three different layers, and they behave very differently:

• Database-level caching. Some databases used to cache query results internally (MySQL's query cache, removed entirely in MySQL 8.0 because it caused more contention than it saved). Modern databases mostly don't do this anymore; they cache pages and plans, not result sets, because invalidating result-level caches correctly turned out to be too expensive to do transparently.
• ORM-level caching. Frameworks like Hibernate (Java/Kotlin) ship a "second-level cache" that sits between the ORM and the database, keyed by entity or by query, backed by an in-memory or distributed cache provider (Ehcache, Infinispan, Redis).
• Application-level caching. The most common pattern in practice: a repository or service function wraps a query, checks an external cache (usually Redis or Memcached) before hitting the database, and writes the result back after a miss. This is "cache-aside" (or "look-aside") caching, and it's what most teams actually build by hand.

All three share the same shape: a cache key derived from the query and its parameters, a cached value (the serialized result set), and a TTL or invalidation rule that decides how long that value is allowed to live.

#A concrete example

Here's a cache-aside read path for "give me a customer's active orders," using Redis as the cache in front of a relational database:

Wrap long lines
import hashlib
import json
import redis

cache = redis.Redis(host="localhost", port=6379, db=0)

def cache_key(sql: str, params: tuple) -> str:
    fingerprint = hashlib.sha256(f"{sql}|{params}".encode()).hexdigest()
    return f"qcache:v1:{fingerprint}"

def get_active_orders(db, customer_id: int, ttl_seconds: int = 30):
    sql = "SELECT id, total, status FROM orders WHERE customer_id = %s AND status = 'active'"
    key = cache_key(sql, (customer_id,))

    cached = cache.get(key)
    if cached is not None:
        return json.loads(cached)

    rows = db.execute(sql, (customer_id,)).fetchall()
    result = [dict(row) for row in rows]

    cache.setex(key, ttl_seconds, json.dumps(result))
    cache.sadd("qcache:tags:orders", key)  # remember: this key depends on "orders"

    return result

Nothing unusual so far — check the cache, fall through to the database on a miss, store the result with a TTL. The cache.sadd line is doing something worth noticing: it records, in a set, every cache key that was built from a query touching the orders table. That bookkeeping is what makes invalidation possible at all.

#The invalidation problem

Here's why query result caching is harder than caching a single row by its primary key. A cached row (user:42) has one clear owner: the row with id 42. When that row changes, you delete user:42, and you're done — invalidation is a lookup.

A cached query result has no single owner. SELECT * FROM orders WHERE customer_id = 42 AND status = 'active' depends on every row that could possibly match that predicate, which is not knowable without re-running the query. An UPDATE orders SET status = 'cancelled' WHERE id = 501 might invalidate that cached result (if order 501 belonged to customer 42 and was active) or might not (if it belonged to someone else) — and the cache has no way to know which, because it only stored the output, not the inputs that determined it.

This is the reason cache invalidation shows up on every "hardest problems in computer science" joke list. Every practical strategy is a compromise between correctness and cache hit rate:

The example above uses whole-table invalidation, which is the honest default for most teams:

def update_order_status(db, order_id: int, new_status: str):
    db.execute(
        "UPDATE orders SET status = %s WHERE id = %s",
        (new_status, order_id),
    )
    db.commit()

    stale_keys = cache.smembers("qcache:tags:orders")
    if stale_keys:
        cache.delete(*stale_keys)
        cache.delete("qcache:tags:orders")

Every cached query that ever touched orders gets thrown away the moment any row in orders changes — including queries for completely unrelated customers. That's wasteful, but it's correct, and correct-but-wasteful beats fast-but-wrong every time a cache bug reaches production.

🧠 Check your understanding
0/2 · 0/2 answered
1. Why is invalidating a cached query result harder than invalidating a cached single row by its primary key?
A query's result depends on a predicate that can match an unknown, changing set of rows, not one fixed keyORMs refuse to cache raw SQL queriesQuery results are always too large to store in memoryRedis cannot expire JSON-serialized values
2. In the whole-table invalidation example, an UPDATE to one order clears every cached query tagged 'orders', including queries for unrelated customers. What is this approach trading away?
Correctness, since some clients may see stale data anywayNothing — this has no downsideDurability, since Redis may lose the deleted keys permanentlyCache hit rate, since unrelated cached results are evicted unnecessarily