Database Scaling Basics
As your application grows, a single database server eventually hits limits. Scaling strategies include vertical scaling (bigger server), read replicas, connection pooling, partitioning, and eventually horizontal sharding. Understanding these fundamentals helps you design systems that can grow.
Scaling Strategies Overview
- Vertical scaling (scale up) - bigger CPU, RAM, faster SSD - easiest, has limits
- Read replicas - copies of the primary for read traffic - horizontal read scaling
- Connection pooling (PgBouncer, ProxySQL) - reuse connections, support more concurrent users
- Table partitioning - split large tables into smaller sub-tables by range, hash, or list
- Caching layer (Redis, Memcached) - cache frequent queries in memory
- Horizontal sharding - split data across multiple databases (complex, last resort)
- CQRS - separate read and write database paths
Scaling Examples
-- 1. Vertical scaling: use a bigger server
-- (no SQL, infrastructure decision)
-- Smallest → Medium → Large → X-Large instance
-- 2. Read replicas: route reads to replica
-- Primary: handles all writes
-- Replica: handles SELECT queries (async replication)
-- Application code: write to primary_url, read from replica_url
-- 3. Connection pooling (PgBouncer config example):
-- [databases]
-- ecommerce = host=localhost port=5432
-- [pgbouncer]
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 20
-- 4. Table partitioning (PostgreSQL PARTITION BY RANGE)
CREATE TABLE orders (
id SERIAL,
created_at TIMESTAMP NOT NULL,
total_amount DECIMAL(10,2),
status VARCHAR(50)
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- Queries filtered by created_at only scan the relevant partition
-- 5. Query cache with Redis (application layer)
-- Cache expensive aggregate:
-- CACHE: redis.set("daily_revenue", json.stringify(result), 3600)
-- READ: redis.get("daily_revenue") || run_query()
-- Check table size
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS size;Quick Quiz
Tip
Tip
Practice Database Scaling Basics in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with Database Scaling Basics is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Database Scaling Basics from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- As your application grows, a single database server eventually hits limits.
- Vertical scaling (scale up) - bigger CPU, RAM, faster SSD - easiest, has limits
- Read replicas - copies of the primary for read traffic - horizontal read scaling
- Connection pooling (PgBouncer, ProxySQL) - reuse connections, support more concurrent users