Scaling PostgreSQL & Redis for High-Throughput Systems (30,000+ Concurrent Users)

-- Inspecting top slow queries using pg_stat_statements
SELECT 
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    rows,
    query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 5;

-- Execution plan of the unoptimized feed query (142ms runtime per invocation!)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, user_id, action, target_id, created_at 
FROM activity_events 
WHERE user_id = 'c4b8e2a0-4c31-4e78-9f12-0001a4e21b5b' 
  AND status = 'PUBLISHED' 
ORDER BY created_at DESC 
LIMIT 20;

-- Refactored Compound Index using Equality -> Sort Order + INCLUDE payload
CREATE INDEX CONCURRENTLY idx_events_user_status_created 
ON activity_events (user_id, status, created_at DESC)
INCLUDE (action, target_id);

-- Optimal Execution Plan: 0.082ms runtime (1,700x speedup)
-- Limit  (cost=0.42..8.45 rows=20) (actual time=0.041..0.058 rows=20 loops=1)
--   Buffers: shared hit=4 read=0
--   -> Index Only Scan using idx_events_user_status_created on activity_events

import json
import random
import redis.asyncio as aioredis
from typing import Optional, List, Dict, Any

class FeedCacheManager:
    def __init__(self, redis_client: aioredis.Redis, base_ttl_sec: int = 300):
        self.redis = redis_client
        self.base_ttl = base_ttl_sec

    def _get_jittered_ttl(self) -> int:
        """Add +/- 15% random jitter to prevent synchronized cache expiration."""
        variance = int(self.base_ttl * 0.15)
        return self.base_ttl + random.randint(-variance, variance)

    async def get_user_feed(self, user_id: str) -> Optional[List[Dict[str, Any]]]:
        cache_key = f"feed:v2:{user_id}"
        cached_payload = await self.redis.get(cache_key)
        if cached_payload:
            return json.loads(cached_payload)
        return None

    async def set_user_feed(self, user_id: str, data: List[Dict[str, Any]]) -> None:
        cache_key = f"feed:v2:{user_id}"
        ttl = self._get_jittered_ttl()
        await self.redis.set(cache_key, json.dumps(data), ex=ttl)

; Production pgbouncer.ini configuration
[databases]
app_production = host=10.0.1.50 port=5432 dbname=app_production auth_user=pgbouncer_auth

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Transaction mode returns database connection immediately after COMMIT
pool_mode = transaction
max_client_conn = 1200
default_pool_size = 30
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3.0
max_db_connections = 60

idle_transaction_timeout = 30.0
query_timeout = 10.0

# Resolving Transaction Pooling with unnamed prepared statements
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'app_production',
        'USER': 'app_user',
        'HOST': '10.0.1.60', # PgBouncer host
        'PORT': '6432',
        'OPTIONS': {
            # Disable server-side named prepared statements for PgBouncer transaction mode
            'options': '-c statement_timeout=5000',
        },
        'CONN_MAX_AGE': 0, # Let PgBouncer manage socket lifecycles
    }
}