Database Scaling Patterns - D B M S
- Query Optimization & Connection Pool Implementation
- Cache frequently used non-dynamic data like, booking history, payment history, user profiles etc
- Introduce Database Redundancy. (Or may be use NoSQL)
- Use connection pool libraries to Cache DB connections
- Multiple application threads can use same DB connection
- Vertical Scaling or Scale-up
- Upgrading our initial tiny machine
- RAM by 2x and SSD by 3x etc
- More you scale up, cost increases exponentially
- Command Query Responsibility Segregation (CQRS)
- Separate read/write operations physical machine wise
- 2 more machines as replica to the primary machine
- All read queries to replicas
- All write queries to primary
- Multi Primary Replication
- Distribute write request to replica also
- All machines can work as primary & replica
- Multi primary configuration is a logical circular ring
- Write data to any node
- Read data from any node that replies to the broadcast first
- Partitioning of Data by Functionality
- What about separating the tables in separate DB schema?
- What about putting that DB in separate machine with primary-replica or multi-primary configuration?
- Different DB can host data categorized by different functionality
- Backend or application layer has to take responsibility to join the results
- Horizontal Scaling or Scale-out
- Sharding, multiple shards
- Allocate 50 machines, all having same DB schema, each machine just hold a part of data
- Locality of data should be there
- Each machine can have their own replicas, may be used in failure recovery
- Data Centre Wise Partition
- Requests traveling across continents are having high latency
- What about distributing traffic across data centres?
- Data centres across continents
- Enable cross data centre replication which helps disaster recovery