Database Scaling Patterns and Practices

Posted on March 10, 2024 by Jacob
Tags: Databases, Architecture, Performance, Engineering

Database scaling is one of the most critical challenges in modern system design. This post explores proven patterns for scaling databases effectively, based on real-world implementations and performance analysis.

1. Vertical vs. Horizontal Scaling

The choice between vertical and horizontal scaling isn't binary. Most successful systems employ a hybrid approach, starting with vertical scaling for simplicity and transitioning to horizontal scaling when necessary.

Scaling Type	Pros	Cons	Best For
Vertical	- Simple to implement - No application changes - Strong consistency	- Hardware limits - Expensive - Single point of failure	Early stage applications, ACID requirements
Horizontal	- Nearly unlimited scaling - Cost-effective - High availability	- Complex to implement - Data consistency challenges - Application changes needed	High-scale applications, read-heavy workloads

2. Sharding Strategies

Effective sharding requires careful consideration of data access patterns. Here are the main approaches:

                // Range-based sharding example
                public class RangeShardingStrategy implements ShardingStrategy {
            private final int numShards;

            public int calculateShard(String key) {
                // Simple range-based sharding
                int hash = key.hashCode();
                return Math.abs(hash % numShards);
            }

            public List getTargetShards(String startKey, String endKey) {
                // Calculate shard range for range queries
                int startShard = calculateShard(startKey);
                int endShard = calculateShard(endKey);
                return IntStream.rangeClosed(startShard, endShard)
                    .boxed()
                    .collect(Collectors.toList());
                    }
        }

Choose your sharding key carefully. It should distribute data evenly while minimizing cross-shard queries for common access patterns.

3. Replication Patterns

Common replication architectures:

Master-Slave
Multi-Master
Circular Replication

                // Example: Implementing read-write splitting
                @Transactional(readOnly = true)
                public class ReadWriteSplittingDataSource extends AbstractRoutingDataSource {
            @Override
            protected Object determineCurrentLookupKey() {
                return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? DataSourceType.SLAVE
                : DataSourceType.MASTER;
            }
        }

4. Caching Strategies

Multi-level caching approach:

Application Layer Cache (L1) ↓ Distributed Cache (L2) ↓ Database Cache (L3) ↓ Persistent Storage

                // Implementing cache-aside pattern
                public class CacheAsidePattern {
                    private final Cache cache;
                    private final Database db;

            public V get(K key) {
            V value = cache.get(key);
            if (value == null) {
            value = db.get(key);
            if (value != null) {
                        cache.put(key, value);
                    }
                    }
                    return value;
                    }

            public void put(K key, V value) {
            db.put(key, value);
            cache.invalidate(key); // Invalidate-on-write
            }
        }

5. Query Optimization

Critical areas for query optimization:

Proper indexing strategies
Query plan analysis
Denormalization when appropriate
Materialized views

                -- Example: Optimizing a common query pattern
                CREATE INDEX idx_user_status_created ON users (status, created_at)
                WHERE status = 'active';

        -- Before: Full table scan
        SELECT * FROM users
        WHERE status = 'active'
        AND created_at > '2024-01-01';

        -- After: Index scan only
        EXPLAIN ANALYZE
        SELECT * FROM users
        WHERE status = 'active'
        AND created_at > '2024-01-01';