Database Scaling
Query Optimization
Analyze query patterns and create the right indexes to speed up data retrieval. B-tree indexes for range queries, hash indexes for equality lookups, composite indexes for multi-column queries, partial indexes for filtered subsets, covering indexes to avoid table lookups.
- B-tree indexes for range queries
- Hash indexes for equality lookups
- Composite indexes for multi-column queries
- Partial indexes for filtered subsets
- Covering indexes to avoid table lookups
- Speeding up WHERE clause filtering
- Optimizing JOIN operations
- Supporting ORDER BY without sorting
- Enforcing uniqueness constraints
Pre-compute complex query results and store them for faster access. Unlike regular views, materialized views persist computed results to disk. Refresh strategies include on-demand, scheduled, and incremental updates.
- Pre-computed query results
- Physical storage of results
- On-demand refresh
- Scheduled refresh
- Incremental updates
- Dashboard and reporting queries
- Aggregations over large datasets
- Complex joins used repeatedly
- Data warehouse summaries
Reduce complex joins by duplicating data to improve query performance. Intentionally violates normal forms to optimize read-heavy workloads. Strategies include adding redundant columns, pre-joined tables, and summary tables.
- Redundant columns for fast access
- Pre-joined tables
- Summary/aggregate tables
- Embedded documents (NoSQL)
- Computed columns
- Read-heavy OLAP workloads
- Eliminating expensive multi-table joins
- Embedding frequently accessed related data
- NoSQL document model design
Caching & Pooling
Store frequently accessed data in a faster storage layer (Redis, Memcached) to reduce database load. Cache-aside, read-through, write-through, and write-behind patterns. Cache invalidation strategies include TTL, event-driven, and version-based approaches.
- Cache-aside pattern
- Read-through caching
- Write-through caching
- Write-behind (write-back) caching
- TTL-based invalidation
- Session data storage
- Frequently read reference data
- API response caching
- Query result caching
Maintain a pool of reusable database connections to reduce connection overhead. Avoids expensive connection establishment for each request. Tools include PgBouncer, ProxySQL, HikariCP.
- Connection reuse
- Pool size configuration
- Connection timeout management
- Idle connection reaping
- Connection multiplexing
- High-concurrency applications
- Serverless database access
- Microservices architectures
- Connection limit management
Scaling Infrastructure
Boost database server capacity by adding more CPU, RAM, storage, or faster disks. Scale-up approach that increases single-node resources. Simpler than horizontal scaling but has hardware limits.
- CPU upgrade for compute-bound queries
- RAM increase for caching
- SSD/NVMe for faster I/O
- Network bandwidth increase
- No application changes required
- Initial scaling before sharding
- Memory-bound workloads
- CPU-intensive queries
- Storage capacity expansion
Create replicas of your primary database on different servers for scaling reads and improving availability. Synchronous replication ensures consistency, asynchronous improves performance. Primary-replica or multi-primary topologies.
- Primary-replica (master-slave) topology
- Multi-primary replication
- Synchronous replication
- Asynchronous replication
- Automatic failover
- Scaling read-heavy workloads
- Geographic distribution
- Disaster recovery
- Reporting without impacting production
Horizontally partition data across multiple database instances based on a shard key. Each shard holds a subset of data. Strategies include hash-based, range-based, and directory-based sharding.
- Hash-based sharding
- Range-based sharding
- Directory-based sharding
- Shard key selection
- Cross-shard query routing
- Scaling beyond single-node limits
- Multi-tenant data isolation
- Geographic data partitioning
- Very large datasets (TB+)