When to Add a Cache Layer (And When You're Just Optimizing Prematurely)

Somewhere around the time your app starts getting real traffic, a familiar anxiety sets in. Pages feel slow. The Supabase dashboard shows queries averaging 200ms. Someone in Slack suggests Redis. You open a new browser tab and start reading Upstash docs.

Stop. Before you add any infrastructure, let's work through this properly.

---

The Situation

Here's the scenario: your Next.js app is live. Page loads are clocking 2 seconds. The Supabase query panel shows your main data fetch averaging 200ms. You've got a few hundred daily active users and the numbers are creeping up.

The instinct is to add a cache layer — Redis, Upstash, Vercel KV (now sunset and migrated to Upstash as of December 2024). But 200ms is a query problem, not a caching problem. And 2s page loads have four or five other explanations before "the database is slow" even enters the picture.

Adding Redis right now would be like insulating your attic before you've found the broken window. You'd spend a weekend on infrastructure, introduce cache invalidation complexity, and maybe shave 180ms off one request — while the real bottleneck goes unfixed.

The question isn't how to add a cache. It's whether you should.

---

The Options

Option 1: Just Optimize Your Queries

What it is: Fix the actual database problem before reaching for external tools.

How it works: Supabase ships with a built-in Query Performance Advisor that runs index_advisor against your queries and surfaces missing indexes. The Supabase docs note that indexes can speed up reads by an order of magnitude — a single B-tree index on a filtered column can drop a 200ms query to under 5ms. RLS policies using auth.uid() without indexes are a particularly common culprit: wrapping them in a SELECT subquery allows Postgres to cache the result across rows rather than evaluating the function per row.

Honest tradeoffs:

• ✅ No new infrastructure. No new failure modes.
• ✅ Works at any scale — faster queries stay fast.
• ✅ Fixes the actual problem rather than hiding it.
• ⚠️ Write-heavy tables pay a small overhead for each new index.
• ⚠️ Some queries are legitimately expensive and no index will save them.

When it breaks: At extreme read volume (tens of thousands of concurrent users), even a perfectly optimized query still adds database load. That's when caching earns its keep.

---

Option 2: Add CDN/ISR Caching

What it is: Use Next.js Incremental Static Regeneration (ISR) or Cache-Control headers to let the CDN absorb repeat requests.

How it works: For pages where data doesn't change per-user — marketing pages, blog posts, public dashboards — ISR lets Next.js serve a pre-rendered HTML page from Vercel's edge network. The database only gets hit when Next.js regenerates the page in the background, not on every request.

Honest tradeoffs:

• ✅ Zero added infrastructure. It's a config option.
• ✅ Dramatically reduces database load for public, non-personalized pages.
• ✅ Serves HTML from the CDN edge — as fast as static sites.
• ⚠️ Data can be stale by up to revalidate seconds.
• ⚠️ Breaks completely for authenticated, per-user pages.
• ⚠️ Self-hosted multi-instance deployments get split-brain ISR caches (each server maintains its own cache island) — a Redis-backed cache handler is the fix there.

When it breaks: The moment your page has user-specific data, ISR can't help. You also can't use it for anything that must be fresh in real time.

---

Option 3: Add Redis/Upstash KV Store

What it is: An external in-memory store sitting between your application and Supabase, serving cached responses at sub-millisecond latency.

How it works: On a cache miss, you fetch from Supabase, store the result in Redis with a TTL, and return it. On cache hits — which after warm-up will be the majority — you skip the database entirely. On Vercel, Upstash is the right choice: it uses HTTP (no persistent TCP connections, which serverless functions can't maintain), and Vercel KV was built on Upstash anyway before being sunset.

Honest tradeoffs:

• ✅ Sub-millisecond reads at any query complexity.
• ✅ Dramatically reduces Supabase connection load at scale.
• ✅ Enables rate limiting, session storage, and other patterns beyond caching.
• ⚠️ You now operate two data stores. Every write needs a cache invalidation strategy.
• ⚠️ A failed Redis pod can cause production outages if you cache process-critical data without fallback logic.
• ⚠️ Upstash charges per command — at low traffic, it costs more than the problem it solves.
• ⚠️ Stale data is now a product concern, not just a tech concern.

When it breaks: High write volume makes cache invalidation a full-time job. Real-time collaborative features, financial data, or anything where stale reads cause user harm — Redis is the wrong tool.

---

The Recommendation

Start with the query. Open the Supabase dashboard, navigate to Database → Query Performance, and look at what's actually slow. Run EXPLAIN ANALYZE on your worst offenders. Add the missing indexes. Wrap your RLS functions in SELECT subqueries. Enable connection pooling through Supavisor (Supabase's built-in pooler) if you're running serverless functions.

Most apps living with a 200ms query find it drops to under 20ms with the right index. That's a 10x improvement with zero new infrastructure.

Then look at ISR for any public pages. A marketing homepage that hits the database on every request when it could be CDN-cached is leaving free performance on the table.

Only reach for Redis when: your optimized queries are still under load, you're seeing connection exhaustion in Supabase, and your traffic is consistent enough to keep the cache warm. A cold cache is worse than no cache — you're paying the Redis latency on top of the database round-trip.

A useful heuristic: if removing the cache would break your app at current traffic, you need it. If it would just make it a bit slower, you don't — yet.

---

Implementation

Option 1: Query Optimization (Start Here)

-- Check your Postgres cache hit rate (target >99%)
SELECT
  'index hit rate' AS name,
  (sum(idx_blks_hit)) / NULLIF(sum(idx_blks_hit + idx_blks_read), 0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT
  'table hit rate' AS name,
  sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

-- Find tables missing indexes
SELECT
  relname,
  100 * idx_scan / (seq_scan + idx_scan) AS percent_of_times_index_used,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY n_live_tup DESC;

-- Fix slow RLS: wrap auth functions so Postgres caches them per-query
-- Before (evaluated per row):
CREATE POLICY "user_posts" ON posts
  FOR SELECT USING (auth.uid() = user_id);

-- After (evaluated once per query via initPlan):
CREATE POLICY "user_posts" ON posts
  FOR SELECT USING ((SELECT auth.uid()) = user_id);

// Use Supabase's transaction pooler URL in serverless environments
// This prevents connection exhaustion under load
// In .env.local:
// SUPABASE_DB_URL=postgresql://...@<project>.supabase.co:6543/postgres?pgbouncer=true

import { createClient } from '@supabase/supabase-js'

// Only select what you need — never SELECT *
const { data } = await supabase
  .from('posts')
  .select('id, title, created_at, author:profiles(name)')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(20)

Option 2: ISR Caching in Next.js App Router

// app/blog/page.tsx
// This page regenerates at most every 60 seconds
// Between revalidations, Vercel CDN serves the pre-rendered HTML

export const revalidate = 60

export default async function BlogPage() {
  const { data: posts } = await supabase
    .from('posts')
    .select('id, title, excerpt, created_at')
    .eq('status', 'published')
    .order('created_at', { ascending: false })

  return <PostList posts={posts} />
}

// For API routes: set Cache-Control headers for CDN caching
// app/api/public-stats/route.ts

export async function GET() {
  const { data } = await supabase
    .from('public_stats')
    .select('*')
    .single()

  return Response.json(data, {
    headers: {
      // CDN caches for 5 minutes, serves stale while revalidating for 10 more
      'Cache-Control': 'public, s-maxage=300, stale-while-revalidate=600',
    },
  })
}

Option 3: Upstash Redis Cache Layer

npm install @upstash/redis

// lib/cache.ts
import { Redis } from '@upstash/redis'

const redis = new Redis({
  url: process.env.UPSTASH_REDIS_REST_URL!,
  token: process.env.UPSTASH_REDIS_REST_TOKEN!,
})

// Generic cache-aside helper with typed return
export async function withCache<T>(
  key: string,
  ttlSeconds: number,
  fetcher: () => Promise<T>
): Promise<T> {
  const cached = await redis.get<T>(key)
  if (cached !== null) return cached

  const fresh = await fetcher()
  await redis.setex(key, ttlSeconds, JSON.stringify(fresh))
  return fresh
}

// Invalidate a cache key on write
export async function invalidate(key: string) {
  await redis.del(key)
}

// app/api/posts/route.ts
import { createClient } from '@supabase/supabase-js'
import { withCache } from '@/lib/cache'

export async function GET() {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_KEY!
  )

  const posts = await withCache(
    'posts:published:recent',
    300, // 5-minute TTL
    async () => {
      const { data, error } = await supabase
        .from('posts')
        .select('id, title, excerpt, created_at')
        .eq('status', 'published')
        .order('created_at', { ascending: false })
        .limit(20)

      if (error) throw error
      return data
    }
  )

  return Response.json(posts)
}

// app/api/posts/[id]/route.ts — invalidate on write
export async function PATCH(request: Request, { params }: { params: { id: string } }) {
  const body = await request.json()

  const { data, error } = await supabase
    .from('posts')
    .update(body)
    .eq('id', params.id)
    .select()
    .single()

  if (error) return Response.json({ error }, { status: 500 })

  // Invalidate the relevant cache keys
  await invalidate(`post:${params.id}`)
  await invalidate('posts:published:recent')

  return Response.json(data)
}

---

When This Breaks

Approach	Stops working at...	Warning signs
Query optimization	Tens of thousands of concurrent users hitting complex queries	CPU consistently >80%, query times rise even on indexed columns
ISR / CDN caching	Any user-specific or real-time data	Users seeing stale data, multi-instance split-brain on self-hosted setups
Redis cache layer	High write volume, or writes from external systems that bypass cache invalidation	Cache hit rates below 50%, stale data bugs, Redis pod failures causing outages

A Redis-based cache starts paying off when your cache hit rate is above ~80% and you're running at least hundreds of requests per minute. Below that, the cold-cache penalty (Redis latency + Supabase latency on every miss) often makes P99 latency worse than just going to the database directly.

One pattern worth knowing: some teams have successfully replaced Redis with PostgreSQL features — using UNLOGGED tables for ephemeral cache storage, and LISTEN/NOTIFY for pub/sub. The tradeoff is 0.1–1ms more latency, but you eliminate an entire infrastructure dependency. For small-to-mid systems, this is worth considering before reaching for Upstash.

---

Guild Discussion

Here's the question for experienced devs in the thread:

At what point does cache invalidation complexity outweigh the performance benefit — and how do you recognize when a team has crossed that line?

We've all seen codebases where the cache invalidation logic is longer than the feature code, where stale-data bugs take days to reproduce, and where a Redis pod failure took down production because someone cached data they should have queried live. But we've also seen apps where a well-placed cache turned a 3-second page into a 50ms one.

What's your heuristic for knowing when a team is about to over-engineer their caching strategy? And what's the most expensive cache-before-optimizing mistake you've seen or made?

---

Tom Hundley is a contributor to the AI Coding Guild's Architecture Patterns series. This is Day 1 of 30.