Your database is often the first bottleneck. Queries slow down. Connections pile up. The disk fills. Scaling databases is harder than scaling stateless services. In this lesson, you'll learn indexing strategies, read replicas, sharding, and when to choose which database for your use case.
1. Learning Objectives
By the end of this lesson, you will be able to:
- Choose the right database for your use case (SQL vs NoSQL)
- Design efficient indexes for common queries
- Implement read replicas for horizontal read scaling
- Use sharding for write scaling
- Understand CAP theorem and trade-offs
- Implement database migration strategies
2. Why This Matters
Real-world scenario: Your app becomes popular. 1 million users. Your single PostgreSQL database is at 99% CPU. Queries are slow. Users are angry. You need to scale—but how? Read replicas? Sharding? Move to NoSQL? The wrong choice means weeks of rewrites.
3. Core Concepts
Database Types
SQL (Relational) Databases:
┌─────────────────────────────────────────────────────────────────┐
│ PostgreSQL, MySQL, SQLite, Oracle, SQL Server │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Complex queries with joins │
│ • ACID transactions │
│ • Structured, consistent data │
│ • Relationships between data │
├─────────────────────────────────────────────────────────────────┤
│ Examples: User accounts, orders, inventory, financial data │
└─────────────────────────────────────────────────────────────────┘
NoSQL Document Databases:
┌─────────────────────────────────────────────────────────────────┐
│ MongoDB, CouchDB, Firestore │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Semi-structured data │
│ • Rapid iteration │
│ • Horizontal scaling │
│ • JSON-like documents │
├─────────────────────────────────────────────────────────────────┤
│ Examples: Product catalogs, CMS content, user profiles │
└─────────────────────────────────────────────────────────────────┘
NoSQL Key-Value Stores:
┌─────────────────────────────────────────────────────────────────┐
│ Redis, DynamoDB (KV mode), Memcached, Etcd │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Ultra-low latency │
│ • Caching │
│ • Session storage │
│ • Leader election │
├─────────────────────────────────────────────────────────────────┤
│ Examples: Session cache, rate limiting, real-time leaderboard │
└─────────────────────────────────────────────────────────────────┘
NoSQL Wide-Column Stores:
┌─────────────────────────────────────────────────────────────────┐
│ Cassandra, HBase, ScyllaDB │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Massive write throughput │
│ • Time-series data │
│ • IoT data │
│ • High availability │
├─────────────────────────────────────────────────────────────────┤
│ Examples: Sensor data, messaging history, activity logs │
└─────────────────────────────────────────────────────────────────┘
NoSQL Graph Databases:
┌─────────────────────────────────────────────────────────────────┐
│ Neo4j, Amazon Neptune, ArangoDB │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Highly connected data │
│ • Relationship traversal │
│ • Social networks │
│ • Recommendation engines │
├─────────────────────────────────────────────────────────────────┤
│ Examples: Social graphs, fraud detection, supply chain │
└─────────────────────────────────────────────────────────────────┘
Search Databases:
┌─────────────────────────────────────────────────────────────────┐
│ Elasticsearch, OpenSearch, Solr │
├─────────────────────────────────────────────────────────────────┤
│ Best for: │
│ • Full-text search │
│ • Log analytics │
│ • Real-time aggregations │
├─────────────────────────────────────────────────────────────────┤
│ Examples: Product search, log analysis, analytics dashboards │
└─────────────────────────────────────────────────────────────────┘Database types and use cases
CAP Theorem
CAP Theorem: You can only have two of Consistency, Availability, Partition tolerance.
┌─────────────────────────────────────────────────────────────────┐
│ │
│ CONSISTENCY (C) │
│ All nodes see same data │
│ │ │
│ │ │
│ CP ──────────┼────────── AP │
│ (Postgres) │ (Cassandra) │
│ │ │
│ │ │
│ PARTITION TOLERANCE (P) │
│ System works despite network splits │
│ │
│ (Availability not shown) │
│ │
└─────────────────────────────────────────────────────────────────┘
Real databases are CP or AP (P always required in distributed systems)
CP (Consistency + Partition Tolerance):
├── Strong consistency │
├── Slower writes │
├── Example: HBase, MongoDB (default), Zookeeper │
AP (Availability + Partition Tolerance): │
├── Eventual consistency │
├── Fast writes │
├── Example: Cassandra, DynamoDB, Riak │
CA (Consistency + Availability): │
├── Not possible in distributed systems │
├── Single-node databases (PostgreSQL single instance) │
PACELC Theorem (Extension of CAP):
├── If Partition (P), choose between A or C │
├── Else (E), choose between L (Latency) or C (Consistency) │
├── Example: DynamoDB is AP/LC (if partition→Available, else→Low Latency)
└── Example: HBase is CP/EC (if partition→Consistent, else→Eventual)CAP Theorem and database trade-offs
Indexing Strategies
-- PostgreSQL indexing examples
-- Single column index (most common)
CREATE INDEX idx_users_email ON users(email);
-- Multi-column index (for queries filtering on both columns)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Partial index (only index subset of rows)
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;
-- Expression index (index on function result)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Covering index (includes extra columns to avoid table lookup)
CREATE INDEX idx_users_covering ON users(id, email, name) INCLUDE (created_at);
-- GIN index (for full-text search, arrays, JSON)
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', content));
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- When to add indexes:
-- ✓ Columns in WHERE clauses
-- ✓ Columns in JOIN conditions
-- ✓ Columns in ORDER BY
-- ✓ Columns in GROUP BY
-- When NOT to add indexes:
-- ✗ Small tables (<1000 rows)
-- ✗ Columns with low cardinality (e.g., boolean)
-- ✗ Frequently updated columns (index maintenance cost)
-- ✗ Write-heavy tables (each index slows writes)
-- Check query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
-- Find missing indexes
SELECT schemaname, tablename, attname, null_frac, n_distinct
FROM pg_stats
WHERE n_distinct > 100
ORDER BY n_distinct DESC
LIMIT 20;Database indexing strategies
Read Replicas
Read Replicas Architecture:
┌─────────────┐
Writes ────────►│ PRIMARY │
│ Database │
└──────┬──────┘
│ Async Replication
┌──────────────┼──────────────┐
│ │ │
▼ ▼ ▼
┌───────────┐ ┌───────────┐ ┌───────────┐
│ REPLICA 1 │ │ REPLICA 2 │ │ REPLICA 3 │
│ (Read) │ │ (Read) │ │ (Read) │
└───────────┘ └───────────┘ └───────────┘
▲ ▲ ▲
│ │ │
└──────────────┼──────────────┘
│
Read Load Balancer
│
┌──────┴──────┐
│ App Reads │
└─────────────┘
Benefits:
├── Scale read traffic horizontally (add more replicas)
├── Offload reporting/analytics to replicas
├── Disaster recovery (promote replica to primary)
├── Geographic distribution (read replicas in different regions)
Limitations:
├── Replication lag (seconds to minutes)
├── Eventual consistency (reads might be stale)
├── Write traffic still hits single primary
PostgreSQL Read Replica Setup:
-- On primary:
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
-- Create replication user:
CREATE USER replicator WITH REPLICATION PASSWORD 'secure_password';
-- On replica:
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -P
-- Create recovery.conf:
standby_mode = 'on'
primary_conninfo = 'host=primary_host user=replicator password=secure_password'
Application Connection Management:
# Python example with read/write splitting
import psycopg2
from random import choice
write_db = "postgresql://primary:5432/db"
read_dbs = [
"postgresql://replica1:5432/db",
"postgresql://replica2:5432/db",
"postgresql://replica3:5432/db"
]
def get_db(operation):
if operation in ['INSERT', 'UPDATE', 'DELETE']:
return psycopg2.connect(write_db)
else:
return psycopg2.connect(choice(read_dbs))Read replica architecture and implementation
Sharding
Sharding: Splitting data across multiple databases
┌─────────────────────────────┐
│ Application │
└─────────────┬───────────────┘
│
┌─────────────▼───────────────┐
│ Sharding Layer │
│ Hash(shard_key) % N │
└─────────────┬───────────────┘
│
┌─────────────┬───────────┼───────────┬─────────────┐
│ │ │ │ │
▼ ▼ ▼ ▼ ▼
┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐
│Shard 0 │ │Shard 1 │ │Shard 2 │ │Shard 3 │
│Users │ │Users │ │Users │ │Users │
│Hash │ │Hash │ │Hash │ │Hash │
│0-25% │ │26-50% │ │51-75% │ │76-100% │
└────────┘ └────────┘ └────────┘ └────────┘
Sharding Strategies:
1. Range-based Sharding
├── customer_id 1-1000 → shard 0
├── customer_id 1001-2000 → shard 1
├── Pros: Simple, efficient range queries
├── Cons: Hot spots (new users go to last shard)
└── Use case: Time-series data
2. Hash-based Sharding (most common)
├── shard = hash(customer_id) % num_shards
├── Pros: Even distribution, no hot spots
├── Cons: Range queries need to query all shards
└── Use case: User data, orders
3. Directory-based Sharding
├── Lookup table mapping key → shard
├── Pros: Flexible, easy to rebalance
├── Cons: Lookup overhead, single point of failure
└── Use case: When you need dynamic rebalancing
4. Geographic Sharding
├── US users → shard_us
├── EU users → shard_eu
├── Pros: Data locality, compliance (GDPR)
├── Cons: Cross-region queries slow
└── Use case: Multi-region applications
Implementation Pattern (Hash-based):
import hashlib
class ShardingRouter:
def __init__(self, num_shards=4):
self.num_shards = num_shards
self.connections = [connect_to_shard(i) for i in range(num_shards)]
def get_shard(self, key):
hash_val = int(hashlib.md5(str(key).encode()).hexdigest(), 16)
return hash_val % self.num_shards
def execute(self, key, query):
shard = self.get_shard(key)
return self.connections[shard].execute(query)
Problems Sharding Creates:
├── Cross-shard joins (impossible or expensive)
├── No foreign key constraints across shards
├── Transactions across shards (use Saga pattern)
├── Rebalancing when adding shards
└── Additional application complexityDatabase sharding strategies
Database Migration Strategies
Zero-Downtime Database Migrations:
1. Expand-Contract Pattern (Phased Migration):
┌─────────────────────────────────────────────────────────────────┐
│ Phase 1: Expand (add new column) │
│ ALTER TABLE users ADD COLUMN new_name VARCHAR(255); │
│ Application writes to both old and new columns │
├─────────────────────────────────────────────────────────────────┤
│ Phase 2: Backfill (copy data) │
│ UPDATE users SET new_name = old_name WHERE new_name IS NULL; │
├─────────────────────────────────────────────────────────────────┤
│ Phase 3: Switch (start reading from new column) │
│ Application reads from new column only │
├─────────────────────────────────────────────────────────────────┤
│ Phase 4: Contract (remove old column) │
│ ALTER TABLE users DROP COLUMN old_name; │
└─────────────────────────────────────────────────────────────────┘
2. Blue-Green Database Migration:
┌─────────────────────────────────────────────────────────────────┐
│ Before: App → Database (old schema) │
├─────────────────────────────────────────────────────────────────┤
│ Step 1: Create new database with new schema │
│ Step 2: Set up replication old → new │
│ Step 3: Wait for replication to catch up │
│ Step 4: Stop writes to old database │
│ Step 5: Switch application to new database │
│ Step 6: Verify all working │
│ Step 7: Decommission old database │
└─────────────────────────────────────────────────────────────────┘
3. Feature Flag Migration:
┌─────────────────────────────────────────────────────────────────┐
│ while has_data_to_migrate: │
│ if feature_flag.enabled("new_database"): │
│ migrate_batch() │
│ else: │
│ read_from_old() │
│ verify_consistency() │
└─────────────────────────────────────────────────────────────────┘
Tools:
├── Flyway (version control for database)
├── Liquibase (declarative migrations)
├── Alembic (Python)
├── Goose (Go)
└── ActiveRecord migrations (Rails)Zero-downtime database migration strategies
4. Complete Project: Multi-Tier Database Architecture
Production Database Architecture:
┌─────────────────────────────────────────────────────────────────┐
│ APPLICATION │
└─────────────┬───────────────┬───────────────┬─────────────────┘
│ │ │
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ Cache │ │ Search │ │ Primary │
│ Redis │ │Elasticsearch│ │PostgreSQL │
└─────┬──────┘ └──────┬─────┘ └──────┬─────┘
│ │ │
│ │ │
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ Session │ │ Search │ │ Replica 1 │
│ Cache │ │ Cluster │ │ (Read) │
└────────────┘ └────────────┘ └────────────┘
│
▼
┌────────────┐
│ Replica 2 │
│ (Analytics)│
└────────────┘
Configuration Example:
# docker-compose-database.yml
version: '3.8'
services:
postgres-primary:
image: postgres:15
environment:
POSTGRES_PASSWORD: ${DB_PASSWORD}
command: |
-c max_connections=500
-c shared_buffers=2GB
-c effective_cache_size=6GB
-c maintenance_work_mem=512MB
-c checkpoint_completion_target=0.9
-c wal_buffers=16MB
-c wal_level=replica
-c max_wal_senders=10
volumes:
- primary-data:/var/lib/postgresql/data
postgres-replica-1:
image: postgres:15
environment:
POSTGRES_PASSWORD: ${DB_PASSWORD}
command: |
-c hot_standby=on
-c primary_conninfo=host=postgres-primary port=5432 user=replicator password=${REPLICA_PASSWORD}
depends_on:
- postgres-primary
redis-cache:
image: redis:7-alpine
command: redis-server --maxmemory 4gb --maxmemory-policy allkeys-lru
volumes:
- redis-data:/data
elasticsearch:
image: docker.elastic.co/elasticsearch/elasticsearch:8.11.0
environment:
- cluster.name=search-cluster
- node.name=node-1
- discovery.type=single-node
- "ES_JAVA_OPTS=-Xms4g -Xmx4g"
volumes:
- es-data:/usr/share/elasticsearch/data
pgbouncer: # Connection pooler
image: edoburu/pgbouncer
environment:
DATABASE_URL: postgresql://postgres:${DB_PASSWORD}@postgres-primary:5432/db
POOL_MODE: transaction
DEFAULT_POOL_SIZE: 200
pgbouncer-read:
image: edoburu/pgbouncer
environment:
DATABASE_URL: postgresql://postgres:${DB_PASSWORD}@postgres-replica-1:5432/db
POOL_MODE: transaction
DEFAULT_POOL_SIZE: 500
Application Connection Logic:
class DatabaseRouter:
def __init__(self):
self.write_pool = PgBouncerClient('pgbouncer:6432')
self.read_pool = PgBouncerClient('pgbouncer-read:6432')
self.cache = RedisClient('redis-cache:6379')
self.search = ElasticsearchClient('elasticsearch:9200')
def get_user(self, user_id):
# Check cache first
cached = self.cache.get(f"user:{user_id}")
if cached:
return cached
# Read from replica
user = self.read_pool.query("SELECT * FROM users WHERE id = %s", user_id)
# Update cache
self.cache.setex(f"user:{user_id}", 300, user)
return user
def create_order(self, order_data):
# Write to primary
order = self.write_pool.execute("INSERT INTO orders ... RETURNING *", order_data)
# Invalidate cache
self.cache.delete(f"user:{order.user_id}")
# Index for search (async)
self.search.index(index='orders', id=order.id, body=order)
return orderComplete multi-tier database architecture
5. Common Errors & Solutions
6. Summary Checklist
7. Next Steps
Next lesson: Backend Lesson 12.3: Caching Strategies (Redis)
Comments (0)
This is exactly what I needed! The initContainer approach solved our migration issues completely. Thanks for the detailed guide!
ReplyLeave a Comment