Postgres Performance for SaaS: When to Index, When to Cache

If you get indexing and caching wrong in a SaaS backed by Postgres, you pay in latency, cloud bills, and eventually outages under load. Fixing it later—under customer pressure—is more expensive than making the right calls early.

Here’s the short answer: index stable, selective access patterns (joins, filters, sorts) that run frequently and must be correct right now. Cache expensive, repeatable results where slight staleness is acceptable (dashboards, aggregates, top-N, cross-tenant reads). Don’t use a cache to mask missing indexes; don’t over-index write-heavy tables.

A mental model for Postgres performance in SaaS

Performance decisions get easier if you categorize your workload:

• OLTP queries: point lookups, short transactions, high QPS. Favor precise B-Tree indexes, minimal payload, and transaction discipline.
• Analytical-ish queries inside SaaS: aggregates over days/weeks, top lists, heavy joins. Candidates for caching or materialized views.
• Multi-tenant filters: nearly every query constraints by tenant_id. The default index strategy changes. See our deeper take on multi-tenant Postgres with Prisma.
• Real-time UX vs eventual UX: billing pages and user profiles require correctness; leaderboards and “trending” feeds tolerate seconds of staleness.

Key levers:

• Selectivity: the fraction of rows matched. High-selectivity filters earn indexes. Low-selectivity filters often don’t.
• Fan-out: number of rows touched before results are returned. Reduce fan-out with the right index order and pagination by indexed cursors.
• Repetition: if the same heavy result is read repeatedly by many users, cache it.
• Volatility: if the underlying data changes constantly, caches become tricky; consider materialized views or read replicas.

One light dry-wit for morale: “There are two hard things in computer science: cache invalidation and naming things. Index naming is optional; invalidation isn’t.”

When to index: rules that hold up in production

Indexing is your first line of defense. It improves correctness and performance without introducing new moving parts. But over-indexing hurts writes and bloats storage.

Index the contract of every hot query

• Where clauses: WHERE tenant_id = ? AND status = 'active'
• Joins: foreign-key columns used for joins must be indexed on the referenced side.
• Ordering: if you ORDER BY created_at DESC LIMIT 50, a composite index (tenant_id, created_at DESC) will avoid a sort.
• Uniqueness: enforce business invariants with UNIQUE (potentially partial) indexes, e.g., unique email per tenant.

Use the right index types and shapes

• B-Tree: default for equality and range. Most of your indexes.
• GIN/GiST: for jsonb, full-text search, trigram ILIKE search (pg_trgm).
• Partial indexes: only index hot subsets, e.g., active rows.
• Covering indexes: INCLUDE (small_columns) can make an index-only scan possible without inflating the key size.

-- Safe index creation without exclusive lock on writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_tenant_created
ON orders (tenant_id, created_at DESC)
INCLUDE (total_amount);

-- Partial index for hot subset
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sessions_active_tenant
ON sessions (tenant_id, last_seen DESC)
WHERE active = true;

-- JSONB containment and text search
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_meta_gin
ON users USING gin (metadata jsonb_path_ops);

-- Fast ILIKE searches (requires pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_projects_name_trgm
ON projects USING gin (name gin_trgm_ops);

Validate with EXPLAIN ANALYZE, not vibes

• Use EXPLAIN (ANALYZE, BUFFERS) to see if your index is used, buffer hits vs reads, and actual time.
• Parameterized queries can mislead the planner (row count estimates). Keep ANALYZE healthy; if needed, add extended statistics.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE tenant_id = $1 AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

When not to index

• Low-selectivity boolean columns on large tables—unless combined with tenant or recent time.
• Highly volatile counters updated on every write (indexes amplify write cost).
• Huge text/jsonb columns that aren’t queried—index what you search, not what you store.

Heuristic that works: if a query runs >1000/day and touches >0.1% of a big table without an index, index it. If it’s rare or ad hoc, consider a temporary index during a migration or push it to analytics.

When to cache: rules that pay off

Indexes optimize how Postgres reads; caching avoids the read entirely. Reach for caching when:

• The result is expensive to compute (multi-join aggregate, PL/pgSQL function) and used by many users.
• You can tolerate staleness for a few seconds to minutes.
• The dataset fits in a memory cache and is referenced frequently.

What to cache

• Aggregated dashboards: revenue by week, active users by plan.
• Cross-tenant public pages: marketing leaderboards, featured content.
• Derived lists with complex filtering that don’t need exact real-time correctness.
• Object caching for hot rows with fat JSON payloads.

Cache architecture patterns

• Cache-aside (lazy): check Redis first; on miss, query Postgres, populate Redis.
• Write-through: write to cache and DB together; simpler reads, trickier to ensure atomicity.
• Event-driven invalidation: on data change, publish an event to recompute or evict keys.
• Background warmers: precompute popular keys ahead of traffic spikes.

// TypeScript: cache-aside with Redis and Postgres (pg + ioredis)
import { Pool } from 'pg';
import Redis from 'ioredis';

const pg = new Pool({ connectionString: process.env.DATABASE_URL });
const redis = new Redis(process.env.REDIS_URL!);

const key = (tenantId: string) => `dash:v1:${tenantId}:rev:7d`;

export async function get7dRevenue(tenantId: string) {
  const k = key(tenantId);
  const cached = await redis.get(k);
  if (cached) return JSON.parse(cached);

  const sql = `
    SELECT date_trunc('day', created_at) AS d, sum(total_amount) AS amt
    FROM orders
    WHERE tenant_id = $1 AND status = 'paid'
      AND created_at >= now() - interval '7 days'
    GROUP BY 1 ORDER BY 1
  `;
  const { rows } = await pg.query(sql, [tenantId]);
  // 60s TTL: feels real-time, far cheaper
  await redis.set(k, JSON.stringify(rows), 'EX', 60);
  return rows;
}

// Invalidation on write (simplest case)
export async function createOrder(o: { tenantId: string; amt: number; }) {
  const client = await pg.connect();
  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO orders(tenant_id, total_amount, status) VALUES ($1,$2,$3)', [o.tenantId, o.amt, 'paid']);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
  await redis.del(key(o.tenantId)); // soft invalidation; background warmers can refill
}

For high-volume invalidation, pair Redis with an event bus (e.g., NOTIFY/LISTEN, Kafka, or lightweight SNS/SQS). Postgres triggers can nudge the cache when hot rows change:

-- Simple trigger to notify changes for a tenant-level cache key
CREATE OR REPLACE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('order_change', NEW.tenant_id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_notify
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE PROCEDURE notify_order_change();

When not to cache

• Transactional reads that must reflect the just-committed write (e.g., payment state).
• Rarely accessed data with poor temporal locality—will only bloat Redis.
• Anything you cannot reliably invalidate. Stale financial numbers trigger support tickets.

Index, cache, replica, or materialized view?

Use the right tool for the access pattern. Here’s a practical comparison:

Rules of thumb:

• Try an index first unless it’s obviously an aggregate or cross-tenant list.
• If results can be stale for 30–120 seconds and are expensive to compute, cache.
• If the same heavy query powers multiple pages, consider a materialized view with scheduled REFRESH CONCURRENTLY.
• If reads overwhelm the primary and your workload is mostly GETs, add read replicas and route appropriately.

A practical reference architecture for SaaS Postgres performance

A baseline that scales comfortably to mid-seven-figure monthly requests without heroics:

• Client -> CDN/Edge (Cloudflare/Akamai) for static assets and edge cache of public APIs.
• API (Node/Go/Rust) with connection pooling via PgBouncer session/transaction mode.
• Postgres primary for writes and OLTP reads; 1–2 read replicas for read-mostly endpoints.
• Redis for cache-aside objects and aggregates (LRU + TTL), with a small pub/sub channel for invalidations.
• Background jobs (BullMQ/Sidekiq/Temporal) to precompute heavy reports and refresh materialized views.
• Observability: pg_stat_statements, slow-query logging, Prometheus/Grafana, and APM.

Flow example for a cached dashboard:

1. API checks Redis for dash:v1:${tenant}:rev:7d.
2. On miss: query Postgres primary (if correctness matters) or replica (if staleness tolerable).
3. Store result in Redis with 60–180s TTL.
4. On orders change, trigger publishes tenant_id to an invalidation worker, which deletes or recomputes the key.

If you’re building real-time leaderboards (gaming or social), the shape shifts: prefer Redis sorted sets as source of truth and periodically reconcile to Postgres. For a broader treatment of real-time backends, see our notes on scalable realtime backends for browser games.

What breaks in production

• CONCURRENTLY isn’t magic: CREATE INDEX CONCURRENTLY avoids blocking writes, but it still uses CPU/IO. On big tables, schedule during off-peak and monitor.
• Autovacuum starvation: frequent updates/deletes create bloat; without tuned autovacuum, indexes degrade. Watch pg_stat_user_tables for n_dead_tup.
• Too many indexes: each insert/update touches every relevant index. Three great indexes beat ten mediocre ones.
• Plan instability: Postgres may flip between index scan and seq scan based on statistics. Keep default_statistics_target sensible and analyze after major data shifts.
• Cache stampedes: a popular key expires and 1000 requests miss at once. Use jittered TTLs, SETNX locks, or request coalescing.
• Replica lag surprises: reading from a replica right after writing can return stale data. Route read-after-write to primary or implement read-your-write tokens.
• Hot keys in Redis: one tenant dominates traffic. Shard keys (v1:${tenant}:${bucket}) or pre-split across slots if using cluster.
• Migrations and long transactions: they hold old row versions and block vacuum; latency creeps up, then falls off a cliff.

Operational guardrails that help:

• Enable pg_stat_statements and review top queries weekly.
• Log queries slower than 250ms; chase them down methodically.
• Cap DB connections at sane levels and front with PgBouncer; prefer a small pool and queuing over thrashing.
• Use feature flags to roll out index changes and query rewrites gradually.

Cost and ROI: where to spend first

You can burn a lot of time and money building a perfect cache while a single composite index would have solved the page’s 900ms p95.

• Phase 1 (most ROI): query hygiene, composite/partial indexes, pooling, and pg_stat_statements. Hours to days of work; outsized impact.
• Phase 2: targeted caching for aggregates and hot objects; background jobs for precompute; light invalidation. Medium effort; big wins on dashboards and “explore” views.
• Phase 3: read replicas and materialized views when read load or heavy aggregates dominate. Operational complexity increases; ongoing maintenance.
• Phase 4: denormalization or domain-specific stores (e.g., time-series DB) when workload outgrows Postgres for that slice.

Cost notes:

• Indexes cost storage and write performance; measure insert/update latency and size growth.
• Redis adds another critical component; factor its HA cost and paging risk if memory sizing is off.
• Read replicas double+ storage and compute for the DB layer; plan for monitoring and failover.
• Observability isn’t optional—budget for it. It’s cheaper than a 3 a.m. incident that takes your team offline for a day.

If you touch AI features that balloon read patterns (e.g., vector-search pre/post-filters), remember: keep OLTP hot paths in Postgres indexed; offload exploratory analytics to a warehouse. We’ve covered the cost side of AI-heavy workloads elsewhere; the principle—measure before scaling—still applies.

A step-by-step tuning playbook you can run this week

1. Instrument: enable pg_stat_statements, slow query logs at 250ms, and request traces to tag endpoints with DB timings.
2. Baseline: capture top 20 queries by total time and by mean time. Group by endpoint and by tenant.
3. Triage: for each query, note rows examined vs returned and whether an index is used. Kill naive ILIKE '%foo%' without trigrams.
4. Index: add missing composite indexes to cover WHERE + ORDER BY. Use CONCURRENTLY. Re-run EXPLAIN ANALYZE.
5. Reduce payload: select only needed columns; avoid SELECT *. Consider INCLUDE for covering.
6. Pagination sanity: use keyset pagination on indexed columns for infinite scrolls.
7. Caching: apply cache-aside to any aggregate powering multiple views with acceptable staleness. Start with 60–120s TTL.
8. Materialize: if a query remains heavy and stable, build a materialized view and refresh periodically or on event.
9. Replicas: route read-mostly endpoints to replicas; keep read-after-write on primary.
10. Watch: after each change, compare p50/p95 by endpoint. Roll back or iterate.

Concrete examples

Multi-tenant events feed

• Query: WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 50.
• Index: (tenant_id, created_at DESC); store small columns in INCLUDE for index-only scans.
• Caching: not needed unless many users read the same tenant feed; if so, cache page 1 with 15–30s TTL.

Search by name with partial match

• Problem: ILIKE '%anna%' on users(name).
• Fix: pg_trgm + GIN index; optionally a small Redis cache for the top 100 popular names per tenant.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_name_trgm
ON users USING gin (name gin_trgm_ops);

Rolling 7-day revenue across all tenants (marketing page)

• Access: public, cross-tenant, repeated.
• DB: precompute with a materialized view and refresh every minute; front with Redis + CDN.

CREATE MATERIALIZED VIEW mv_rev7d AS
SELECT date_trunc('day', created_at) AS d, sum(total_amount) AS amt
FROM orders WHERE status = 'paid'
GROUP BY 1;

-- Avoid blocking selects during refresh
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_mv_rev7d_d ON mv_rev7d (d);

-- Cron: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_rev7d;

Hot counters

• If you increment like counts frequently, an index on the counter column is useless. Keep counters denormalized in a small table or Redis with periodic flush to Postgres.

FAQ

How do I know if I need an index or a cache?

If it’s a frequent OLTP query where correctness matters now and the filter is selective, index. If it’s a heavy, repeatable result and small staleness is fine, cache.

Should I cache per-tenant or globally?

Cache per-tenant for tenant-scoped data to isolate hot tenants and simplify invalidation. Use global caches for public aggregates and leaderboards.

Is it safe to read from replicas in a SaaS app?

Yes for read-mostly endpoints where small replication lag is acceptable. For read-after-write flows (e.g., just created an object), route to primary.

How long should my TTL be?

Start with 60–180 seconds for dashboards. Add jitter (±10–20%) to avoid synchronized expirations. Shorten or lengthen based on freshness needs and load.

When do I use materialized views instead of Redis?

When you need SQL semantics on precomputed results, want fewer moving parts, or need transactional refreshes. Redis excels at sub-ms hot lookups; materialized views keep results in Postgres and are simpler to reason about for SQL-heavy apps.

Can too many indexes slow me down?

Yes. Each write must update all relevant indexes. Measure insert/update latency and prune unused or redundant indexes regularly.

Key takeaways

• Index first for stable, selective OLTP queries; cache for heavy, repeatable results with acceptable staleness.
• Composite, partial, and covering indexes solve most latency issues without new infrastructure.
• Caches must have a clear invalidation story; use TTLs, jitter, and event-driven evictions to avoid stampedes.
• Materialized views and read replicas are strong complements when reads dominate or aggregates are heavy.
• Watch production: pg_stat_statements, slow logs, and careful rollouts catch regressions before customers do.

If you’re building or scaling a Postgres-backed SaaS and want pragmatic help deciding where to index, cache, or denormalize, MTBYTE can design and ship it with you. Reach out at /contact.