Scaling Patterns in DBMS

Scaling a database means increasing its capacity to handle higher loads — more users, more requests, and more data — without degrading performance. In this case study, we’ll explore how a Cab Booking App evolves from a tiny startup system to a globally scalable platform, applying a series of database scaling patterns to solve real-world performance challenges.

The Beginning: The Startup Phase

At the initial stage:

The app serves ~10 customers.
A single small database server stores all information — customers, trips, locations, bookings, and trip history.
Average load: ~1 booking every 5 minutes.

This architecture is simple and efficient for early growth but not scalable as traffic increases.

The Problem Begins

As the app gains popularity:

Bookings rise to 30 per minute and beyond.
The single database server begins to struggle with:
- High API latency
- Transaction deadlocks and starvation
- Frequent timeouts
- Slow user experience

To fix this, the company must apply database optimization and scaling techniques.

Pattern 1: Query Optimization & Connection Pooling

Concept

Before scaling infrastructure, optimize what already exists.

Techniques

Query Optimization:
- Rewrite inefficient SQL queries.
- Use proper indexes, query plans, and denormalization for frequent lookups.
Caching:
- Cache frequently accessed data (like user profiles, trip history, and payment data) using Redis or Memcached.
Connection Pooling:
- Use libraries like HikariCP or pgBouncer to reuse database connections.
- Allows multiple application threads to share connections efficiently.
Database Redundancy:
- Add replicas or use NoSQL for less-structured data.

Outcome

Query latency drops significantly.
The app handles up to 100 bookings per minute smoothly.

Pattern 2: Vertical Scaling (Scale-Up)

Concept

Upgrade the existing server hardware instead of changing architecture.

Implementation

Increase RAM, CPU cores, and SSD storage.
Optimize database configuration (buffer size, cache limits, I/O throughput).

Advantages

Simple and quick to implement.
Cost-effective up to a point.

Limitations

Beyond a certain capacity, costs grow exponentially.
Hardware upgrades cannot overcome single-node limitations.

Outcome

Performance improves temporarily, handling 300 bookings per minute.

Pattern 3: Command Query Responsibility Segregation (CQRS)

Concept

Separate read and write operations to reduce contention on a single database.

Implementation

Introduce Master–Slave Replication:
- Primary (Master) handles write operations.
- Replicas (Slaves) handle read operations.
Use read replicas to balance query loads across multiple machines.

Advantages

Improved read performance.
Better load distribution across replicas.

Challenges

Replication lag between master and replicas.
Heavy write traffic can still overload the primary database.

Outcome

Efficient up to 500–600 bookings per minute, but write scalability remains limited.

Pattern 4: Multi-Primary Replication

Concept

Allow all nodes to act as both read and write replicas.

Implementation

Each node can process both read and write queries.
Nodes form a logical circular ring to replicate changes to one another.

Advantages

High write scalability — all servers accept writes.
Fault-tolerant, since each node can serve independently.

Challenges

Increased system complexity.
Requires synchronization and conflict resolution.
Risk of data inconsistency under heavy writes.

Outcome

Performance increases, but at ~50 requests/sec, latency and replication delays appear again.

Pattern 5: Partitioning by Functionality

Concept

Split data by functional domains into different databases or schemas.

Implementation

Separate large tables by business functions:
- CustomerDB → customer details.
- BookingDB → bookings and payments.
- LocationDB → routes and geo-data.
Each DB can use its own replication or scaling pattern (single-primary or multi-primary).

Advantages

Improved modularity and fault isolation.
Easier to manage specific functionalities independently.

Challenges

The application layer must handle cross-database joins.
Increases complexity in backend logic.

Outcome

The system supports multi-country operations efficiently.

Pattern 6: Horizontal Scaling (Scale-Out)

Concept

Distribute data horizontally across multiple servers — known as Sharding.

Implementation

Divide data across multiple shards based on a shard key (e.g., customer ID or region).
Each shard has the same schema but holds a subset of total data.
Optionally, each shard has replicas for fault tolerance.

Advantages

Virtually unlimited scalability.
Reduced query latency (localized data access).
Fault isolation — failure in one shard doesn’t affect others.

Challenges

Complex to implement and maintain (requires routing layer).
Re-sharding (redistributing data) is difficult.
Cross-shard queries are slower (Scatter-Gather problem).

Outcome

Enables global scalability across multiple continents.

Pattern 7: Data Centre-wise Partitioning

Concept

Deploy region-specific data centers to minimize network latency and improve reliability.

Implementation

Set up multiple data centers across continents (e.g., Asia, Europe, America).
Implement cross–data center replication for synchronization and disaster recovery.
Route user requests to the nearest data center using Geo-DNS or load balancers.

Advantages

Reduced latency for geographically distributed users.
Improved availability and disaster recovery.
Enables data locality and compliance with regional regulations.

Outcome

System becomes globally resilient, capable of serving millions of requests per minute.

Summary Table of Scaling Patterns

Pattern	Technique	Goal	Key Benefit	Challenge
1	Query Optimization & Connection Pooling	Performance tuning	Low-cost improvement	Limited scalability
2	Vertical Scaling (Scale-Up)	Hardware upgrade	Quick performance boost	Expensive long-term
3	CQRS (Master–Slave)	Separate reads/writes	Load balancing	Replication lag
4	Multi-Primary Replication	Distributed writes	High write throughput	Data conflicts
5	Functional Partitioning	Data by domain	Manageability	Complex joins
6	Horizontal Scaling (Sharding)	Split data by key	Massive scalability	Complex management
7	Data Centre Partitioning	Geo-distribution	Low latency, disaster recovery	High operational cost

Conclusion

In the Cab Booking App’s journey:

The system evolved from a single small database to a globally distributed architecture.
Each scaling pattern introduced a new layer of performance, availability, and fault tolerance.
The trade-off was increasing system complexity.

Ultimately:

“Database scaling is not a single step — it’s an evolutionary process that balances performance, cost, and complexity as the business grows.”

These seven scaling patterns represent the progressive path of DB optimization, used by real-world companies like Uber, Ola, and Lyft to manage massive, global-scale systems efficiently.