Database

PostgreSQL is fast out of the box. But “fast enough for development” and “fast enough for production” are different conversations. These techniques will help you find and fix performance bottlenecks. Finding Slow Queries Enable Query Logging 1 2 3 4 5 6 -- Log queries slower than 500ms ALTER SYSTEM SET log_min_duration_statement = '500ms'; SELECT pg_reload_conf(); -- Check current setting SHOW log_min_duration_statement; pg_stat_statements Extension The most valuable performance tool: ...

Redis is often introduced as “a cache” but it’s really a data structure server. Understanding its primitives unlocks patterns far beyond simple key-value storage. Basic Caching The fundamental pattern: cache expensive operations. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 import redis import json r = redis.Redis(host='localhost', port=6379, decode_responses=True) def get_user(user_id: str) -> dict: # Check cache first cached = r.get(f"user:{user_id}") if cached: return json.loads(cached) # Cache miss - fetch from database user = db.query_user(user_id) # Store in cache with 1 hour TTL r.setex(f"user:{user_id}", 3600, json.dumps(user)) return user Cache-Aside Pattern The application manages the cache explicitly: ...

Every junior developer learns that indexes make queries fast. What they don’t learn is that indexes also make writes slow, consume disk space, and can actively hurt performance when misused. Let’s fix that. What Indexes Actually Do An index is a separate data structure that maintains a sorted copy of specific columns, with pointers back to the full rows. Think of it like the index in a book — instead of reading every page to find “PostgreSQL,” you flip to the index, find the entry, and jump directly to page 247. ...

PostgreSQL’s default configuration is designed to run on minimal hardware without crashing. It’s not designed to perform well. Out of the box, Postgres uses a fraction of available memory and I/O capacity. Proper tuning can improve performance by 10x or more. Memory Configuration shared_buffers The most important setting. This is Postgres’s own cache. 1 2 3 -- Default: 128MB (way too low) -- Recommended: 25% of system RAM shared_buffers = 4GB -- On a 16GB server Don’t set above 40% — the OS needs memory for its own file cache. ...

Database migrations are terrifying. One wrong move and you’ve locked tables, broken queries, or corrupted data. The traditional approach — maintenance window, stop traffic, migrate, restart — works but costs you availability and customer trust. The expand-contract pattern lets you migrate schemas incrementally, with zero downtime, while your application keeps serving traffic. The Core Idea Never make breaking changes in a single step. Instead: Expand: Add the new structure alongside the old Migrate: Move data and update application code Contract: Remove the old structure Each step is independently deployable and reversible. ...

Database connections are expensive. Each new connection requires TCP handshake, authentication, session initialization, and memory allocation on both client and server. Do this for every web request and you’ve got a performance problem hiding in plain sight. The Problem: Connection Overhead A typical PostgreSQL connection takes 50-100ms to establish. For a web request that runs a 5ms query, you’re spending 10-20x more time on connection setup than actual work. 1 2 3 4 5 6 7 8 # The naive approach - connection per request def get_user(user_id): conn = psycopg2.connect(DATABASE_URL) # 50-100ms cursor = conn.cursor() cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) # 5ms result = cursor.fetchone() conn.close() return result Under load, this creates connection storms. Each concurrent request opens its own connection. The database has connection limits. You hit those limits, new connections queue, latency spikes, everything falls apart. ...

The scariest deploy isn’t code—it’s schema changes. One wrong migration can lock tables, corrupt data, or bring down production. Zero-downtime migrations require discipline, but they’re achievable. The Problem Traditional migrations assume you can take the database offline: 1 2 -- Dangerous in production ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL; This locks the table, blocks all reads and writes, and fails if any existing rows lack a value. In a busy system, that’s an outage. ...