🗃️ Database Index Strategist

Index Strategy: Audit Log Slow Query

Query Pattern Analysis

The query has 3 filter dimensions + 1 sort:

• workspace_id = (always equality, always present) — most selective, varies per call
• created_at >= AND < (range, always present) — typically last 7-30 days
• event_type = (equality, OPTIONAL — null often) — moderately selective
• ORDER BY created_at DESC LIMIT 100

This is a classic compound-filter + range + sort pattern. The plan currently chooses the wrong index because none of your existing indexes match the access pattern.

EXPLAIN Plan Walkthrough

Index Scan Backward using idx_events_created_at on events
  Filter: ((workspace_id = '...'::uuid) AND (created_at >= '...') AND ...)
  Rows Removed by Filter: 1247563

Translation: Postgres uses the created_at index to scan backward (newest first), reads each row, then filters by workspace + date range. It scans 1.25M rows to find 100 matching the workspace. That's the slow part — Rows Removed by Filter: 1247563.

The issue: existing indexes don't combine workspace_id + created_at. The planner picks created_at (covers the sort) but loses the workspace selectivity.

Recommended Indexes

Drop existing inefficient indexes (audit them first):

-- These don't match your query pattern; review pg_stat_user_indexes for usage:
SELECT indexrelname, idx_scan, idx_tup_read 
FROM pg_stat_user_indexes 
WHERE relname = 'events';

If idx_events_user_id and idx_events_workspace_id show low idx_scan, drop them. They're write-tax without read benefit.

Add the right index:

-- Composite index matching the query pattern
CREATE INDEX CONCURRENTLY idx_events_workspace_created 
  ON events (workspace_id, created_at DESC);

Why this index:

• workspace_id first (equality filter — limits to ~10K rows per workspace)
• created_at DESC second (matches sort + range filter)
• Postgres can use this for: filter by workspace, then range scan by created_at, in DESC order natively (no sort needed)

Expected impact: query drops from ~4.5s to <50ms. The planner walks ~10K workspace rows + filters by date range, instead of 1.25M rows.

Optional: covering index (if event_type filter is common)

If a significant fraction of queries filter by event_type, add a partial index:

-- Partial index for the most-queried event types
CREATE INDEX CONCURRENTLY idx_events_workspace_created_user_login 
  ON events (workspace_id, created_at DESC) 
  WHERE event_type = 'user.login';

Only use if event_type filtering is high-volume. Otherwise, the main composite index is sufficient.

For JSONB metadata queries (future-proofing)

If you start querying inside the metadata JSONB:

-- GIN index on JSONB for containment queries
CREATE INDEX CONCURRENTLY idx_events_metadata_gin 
  ON events USING GIN (metadata);

Only add when actually needed; GIN indexes are ~3x bigger than B-tree.

Composite Index Column Order

For (workspace_id, created_at DESC):

Why workspace_id first:

• Equality filter — most selective at workspace level
• Postgres builds the B-tree by workspace, then within each workspace by created_at
• Reading is: 'find this workspace's branch, then walk created_at within it'

Why created_at DESC:

• Matches the ORDER BY direction
• Postgres reads index entries in stored order — DESC matches your query exactly

Wrong order would be: (created_at, workspace_id). Then the planner would have to scan all events in the date range across all workspaces, then filter. Much slower.

Rule of thumb:

1. Equality columns first (high selectivity)

2. Range columns after

3. ORDER BY columns at the end (in matching direction)

Special Index Types in Your Case

Partial Index (consider for cardinality reasons)

If 80% of your audit log queries filter by recent dates only:

CREATE INDEX idx_events_recent 
  ON events (workspace_id, created_at DESC) 
  WHERE created_at >= NOW() - INTERVAL '90 days';

Only indexes recent rows. Smaller index, faster build, less write tax. But: requires periodic rebuild as 'recent' rolls forward.

Expression Index (not needed here, but for future)

For case-insensitive search on event_type:

CREATE INDEX idx_events_event_type_lower ON events (LOWER(event_type));

Matches queries like WHERE LOWER(event_type) = 'user.login'.

GIN Index (when querying JSONB)

If metadata contains queryable structures:

-- For 'metadata @> ...' queries
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

-- Or path-specific (smaller):
CREATE INDEX idx_events_metadata_user_id ON events ((metadata->>'user_id'));

Evaluate only when JSONB queries are actually slow.

Write Amplification

Before: 4 indexes (incl. PK). Write amplification ~5x (each write updates the row + updates 4 indexes).

After: 2 indexes (PK + composite). Write amplification ~3x.

Net write performance improvement: ~25-30% faster writes. With 95/5 read-write split, your write speedup is meaningful.

If you keep idx_events_user_id (assume some queries use it), still net better than 4 indexes.

Verification After Apply

Before deploying:

-- 1. Build new index CONCURRENTLY in production (no table lock)
CREATE INDEX CONCURRENTLY idx_events_workspace_created 
  ON events (workspace_id, created_at DESC);

-- 2. Test query plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, event_type, ... 
FROM events 
WHERE workspace_id = '...' AND created_at >= '...' AND created_at < '...' 
ORDER BY created_at DESC LIMIT 100;

-- Should show: Index Scan using idx_events_workspace_created
-- Execution Time: <50ms

Pass criteria:

• Plan uses new composite index
• Execution time <50ms (down from ~4.5s)
• No more 'Rows Removed by Filter' line

After deploying, monitor production:

• Audit log endpoint p95 latency drops from ~5s to <100ms
• DB CPU from queries drops noticeably

Migration Plan

For adding the new index in production:

-- CONCURRENTLY = no table lock; takes longer to build
CREATE INDEX CONCURRENTLY idx_events_workspace_created 
  ON events (workspace_id, created_at DESC);

For 50M rows, expect ~10-30 minutes to build. Monitor during build:

-- Check index build progress (PG 12+)
SELECT phase, pg_size_pretty(bytes_done) AS done, pg_size_pretty(bytes_total) AS total 
FROM pg_stat_progress_create_index;

For dropping unused indexes (after audit):

-- Verify safe to drop:
SELECT indexrelname, idx_scan, last_idx_scan 
FROM pg_stat_user_indexes 
WHERE relname = 'events' AND indexrelname = 'idx_events_user_id';

-- If idx_scan = 0 over 30+ days of usage:
DROP INDEX CONCURRENTLY idx_events_user_id;

Use CONCURRENTLY for both create + drop in production. No table lock.

Reset stats first if you want a clean window of measurement:

SELECT pg_stat_reset();
-- Wait 30 days
-- Then evaluate idx_scan counts

What This Strategy Won't Solve

• Won't help if writes are saturating I/O. Index strategy helps reads; write throughput needs different optimizations (partitioning, sharding, async writes).
• Won't fix queries that scan large date ranges. Customer asks for '5 years of audit log' — even with indexes, that's 100M rows. Requires data retention policy or partitioning.
• Won't help if statistics are stale. Run ANALYZE events; periodically for the planner to choose the right index.
• Won't compensate for poor query design. WHERE workspace_id::text = $1 (cast that prevents index use) — fix the query, not the index.
• Won't replace partitioning at scale. When events table reaches 1B rows, even good indexes get slow. Consider table partitioning by created_at month.

Key Takeaways

• Composite (workspace_id, created_at DESC) is your fix. Drops query from 4.5s → <50ms by matching the access pattern.
• Drop unused single-column indexes (workspace_id alone, user_id alone) after audit. Each unused index taxes writes ~10-30%.
• Build with CONCURRENTLY in production. No table lock; takes longer but safe.
• Composite index column order: equality first (workspace_id), range second (created_at). Match query pattern.
• Audit pg_stat_user_indexes quarterly. Most production DBs have 30%+ unused indexes silently taxing writes.