Database Caching Strategies: Speed Up Your Application Without Rewriting SQL

Why Caching Changes Everything

Even perfectly optimized SQL queries take time — network round-trips, disk I/O, and CPU for query planning add up. Caching stores the result of expensive operations so that subsequent requests can be served without touching the database at all.

The impact is dramatic: a query that takes 50ms with full database access can be served in under 1ms from an in-memory cache. At scale, caching is often the difference between an application that handles 1,000 requests per second and one that handles 100,000.

Cache-Aside (Lazy Loading)

Cache-aside is the most common pattern. The application first checks the cache; on a cache miss, it fetches from the database and populates the cache for future requests.

-- Database query (only executed on cache miss)
SELECT
  p.id, p.name, p.price, p.description,
  c.name AS category_name,
  AVG(r.rating) AS average_rating,
  COUNT(r.id) AS review_count
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.id = $1
GROUP BY p.id, c.name;

-- Application logic (pseudocode):
-- cache_key = "product:#{product_id}"
-- cached = redis.get(cache_key)
-- if cached: return JSON.parse(cached)
-- result = db.query(sql, [product_id])
-- redis.setex(cache_key, 3600, JSON.stringify(result))  -- TTL: 1 hour
-- return result

Trade-off: The first request after cache expiry hits the database. Under very high traffic, many concurrent requests can miss the cache simultaneously, causing a thundering herd. Mitigate this with a mutex lock on cache population.

Write-Through Caching

Write-through updates the cache immediately whenever data is written to the database. The cache is always fresh; there is no cache miss for recently written data.

-- Application writes to both DB and cache atomically
BEGIN;
  UPDATE products
  SET price = $new_price, updated_at = now()
  WHERE id = $product_id;
COMMIT;
-- Immediately after commit:
-- redis.setex("product:#{product_id}", 3600, serialize(updated_product))
-- redis.del("product_list:*")  -- Invalidate list caches that include this product

Trade-off: Writes are slightly slower because two systems must be updated. Also, cache entries for infrequently read data occupy memory unnecessarily.

Read-Through and Query Result Caching

For complex aggregation queries or reports that are expensive to compute, cache the entire result set with an appropriate TTL.

-- Expensive dashboard query (runs in background, cached for 10 minutes)
SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS new_orders,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND status = 'completed'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

-- Cache key: "dashboard:revenue_30d"
-- TTL: 600 seconds (acceptable staleness for analytics)

For PostgreSQL specifically, the built-in query cache operates at the statement level. You can also use materialized views as a form of query caching:

-- Materialized view: pre-computed result stored as a physical table
CREATE MATERIALIZED VIEW product_stats AS
SELECT
  p.id,
  p.name,
  COUNT(r.id) AS review_count,
  ROUND(AVG(r.rating), 2) AS avg_rating,
  SUM(oi.quantity) AS total_sold
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
WITH DATA;

CREATE UNIQUE INDEX ON product_stats(id);

-- Refresh concurrently (non-blocking, keeps old data available during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;

-- Schedule with pg_cron or application scheduler every 15 minutes

Cache Invalidation Strategies

Cache invalidation is famously hard. Here are the main approaches:

TTL-based: Set an expiry time and accept stale data for that window. Simple and predictable. Good for data that changes infrequently.

Event-driven: Invalidate or update specific cache keys when the underlying data changes. Precise but requires careful tracking of which caches depend on which data.

-- Use a PostgreSQL NOTIFY/LISTEN for real-time cache invalidation
-- Application publishes change events:
SELECT pg_notify('cache_invalidation', json_build_object(
  'table', 'products',
  'id', $product_id,
  'action', 'update'
)::text);

-- Cache layer subscribes to the channel and invalidates matching keys
-- LISTEN cache_invalidation;

Cache stampede protection: Use a short lock to prevent multiple processes from rebuilding the same cache entry simultaneously after expiry:

-- Pseudocode with distributed lock
-- if redis.set("lock:product:#{id}", 1, NX, EX, 5):
--   result = db.query(...)
--   redis.setex("product:#{id}", 3600, serialize(result))
--   redis.del("lock:product:#{id}")
-- else:
--   sleep(0.05); return redis.get("product:#{id}")  -- Wait for lock holder

Understand What to Cache with Erdmates

Knowing which queries to cache starts with understanding your data model. Erdmates helps you visualize relationships and identify the high-cardinality joins and aggregations that are best suited for caching. Model your schema first, then instrument your queries to find the expensive ones worth caching.

Conclusion

Caching is not a substitute for well-designed queries and indexes — it is an additional layer that handles traffic spikes and reduces repeated computation. Choose the right strategy for each use case: cache-aside for entity lookups, write-through for frequently updated entities, and materialized views or TTL-based result caching for expensive aggregations. Always measure the cache hit rate and set TTLs that balance freshness with performance.