1. Identifying Slow Queries with EXPLAIN ANALYZE and pg_stat_statements
Before applying any optimizations, you must measure with precision. Relying on intuition when tuning database queries is a recipe for wasted engineering hours. We enabled pg_stat_statements in PostgreSQL to track total execution time, call counts, and mean query duration. Using EXPLAIN (ANALYZE, BUFFERS), we uncovered excessive sequential table scans across tables exceeding 2 million rows where appropriate indexes were missing.
-- Identifying top slow queries sorted by total time
SELECT
round(total_exec_time::numeric, 2) AS total_time_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_time_ms,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;Key Implementation Takeaways:
- ✓Enable pg_stat_statements in postgresql.conf for real-time query observability.
- ✓Look for 'Seq Scan' on large tables in query execution plans.
- ✓Pay close attention to Shared Hit Blocks vs. Read Blocks to assess cache efficiency.
2. Strategic Indexing: Beyond Basic Single-Column B-Trees
A common beginner pitfall is blindly indexing every foreign key column. Each index carries write amplification overhead on INSERT, UPDATE, and DELETE. We replaced redundant single-column indexes with composite (compound) B-tree indexes designed specifically around our query filter orders (Equality columns first, Range/Inequality columns second). Furthermore, partial indexes were introduced for queries targeting active user records (e.g., status = 'active'), cutting index disk storage by 70%.
-- Composite index for filtering by team_id and created_at range
CREATE INDEX idx_orders_team_created ON orders (team_id, created_at DESC);
-- Partial index for active subscribers only
CREATE INDEX idx_active_users_email ON users (email)
WHERE is_active = true AND deleted_at IS NULL;Key Implementation Takeaways:
- ✓Follow the ESR rule (Equality, Sort, Range) when structuring composite indexes.
- ✓Utilize partial indexes to keep indexes lightweight and cache-friendly.
- ✓Regularly audit unused indexes using pg_stat_user_indexes to reclaim write throughput.
3. Multi-Tiered Caching: Cache-Aside with Redis and TTL Jitter
Even with sub-millisecond query execution, hitting PostgreSQL for repetitive read-heavy requests wastes valuable connection pool slots. We deployed Redis in a Cache-Aside (Lazy Loading) architecture. To eliminate the dangerous 'Cache Stampede' problem where hundreds of threads simultaneously query the database when a popular key expires, we implemented randomized TTL jitter and distributed locks via Redlock.
import json
import random
from django.core.cache import cache
def get_user_dashboard(user_id: int):
cache_key = f"user_dash:{user_id}"
data = cache.get(cache_key)
if data is not None:
return json.loads(data)
# Cache miss: fetch from PostgreSQL
data = fetch_dashboard_from_db(user_id)
# Add random jitter between 300 and 360 seconds to prevent stampedes
ttl = 300 + random.randint(0, 60)
cache.set(cache_key, json.dumps(data), timeout=ttl)
return dataKey Implementation Takeaways:
- ✓Always serialize cached payloads with efficient formats (e.g., JSON or MessagePack).
- ✓Introduce random TTL jitter (+-10%) to prevent simultaneous mass key expirations.
- ✓Invalidate caches explicitly on data mutations (write-through or event-based invalidation).
Summary & Final Thoughts
Performance optimization is an iterative process. By systematically analyzing slow queries with EXPLAIN ANALYZE, applying targeted composite and partial indexes, and shielding PostgreSQL behind a resilient Redis caching layer, our infrastructure seamlessly absorbed traffic spikes without requiring expensive database hardware upgrades.