Scaling a database is not just about upgrading servers. It requires choosing the right type of database, optimizing queries, implementing connection pools, and transitioning architectural models as workload scales. Let us explore this in a step-by-step case study.
Imagine you are building a new ride-hailing app, CabFlow. Let us look at how the architecture starts and scales over time.
At this stage, all application data (customers, trips, real-time driver coordinates, and transaction history) is stored inside a single small machine database.
Your application becomes famous and downloads spike! Now, you are processing ~30 bookings per minute.
API response latency increases dramatically. Simple actions like logging in, searching for driver history, or displaying past rides take seconds. Customers start dropping off due to sluggishness.
Multiple client requests concurrently lock rows in the `Drivers` table to reserve them. Relational locks conflict, causing transactions to time out, face deadlocks, starve, and return frequent booking failures.
To address the 30 bookings/minute bottlenecks without spending money on bigger servers, we implement query optimization and caching.
Offload read operations by caching user profiles, ride histories, and payment logs in an in-memory cache (like Redis).
Identify heavy slow-running SQL queries, add B-Tree indexes on search fields (like `DriverID` or `PassengerID`), and optimize joining logic.
Cache open database sockets so that threads do not waste database process CPU constantly handshaking TCP connections.
Opening a physical database connection is an expensive operation. It involves initiating a TCP Handshake, authenticating credentials, and allocating a dedicated thread/memory space on the database server. If done for every single API call:
Connection Pooling solves this by establishing a collection (pool) of active connections on startup.
By installing and configuring connection pooling drivers at the application layer (e.g. `pg-pool` or `Sequelize` for Node.js, `HikariCP` for Java, or `SQLAlchemy` for Python). We define parameters like:
A common pitfall occurs when developer teams scale their application servers horizontally (e.g. adding 15 NodeJS Docker containers behind a load balancer to handle web traffic) but leave the database server unscaled.
Suppose each Node server has `maxPoolSize = 30`. If you have only 1 application instance, the database only experiences 30 active connection processes.
If you scale horizontally to 30 Application Server instances, each instance starts its own pool. The database is suddenly hammered with:
30 App instances × 30 connections = 900 active DB sockets!
Each database connection consumes physical operating system processes/threads, socket descriptor limits, and buffer memory. At 900 connections, Postgres or MySQL will run out of RAM, hit the OS connection limit, context-switch processes constantly, lock up, and refuse incoming traffic.
As the startup grows and launches in more locations, the request rate climbs to 300 bookings per minute. Caching and pooling are no longer enough. The database CPU hits 95%. It is time to implement Vertical Scaling.
Single thread processing. Transactions 2 and 3 must wait, leading to deadlock, lock timeouts, and thread starvation.
Parallel core execution. Multiple transactions execute concurrently in microseconds, eliminating wait latency and deadlocks.
Vertical Scaling is the process of upgrading the physical hardware capacity of the existing single database instance. Instead of changing any application code or routing logic, you simply migrate the database to a larger, more powerful machine:
Vertical scaling is highly pocket-friendly and easy in the initial stages. Cloud platforms (like AWS or GCP) let you upgrade database sizes with a single click. There is no need to partition tables, write routing logic, or manage distributed queries.
Hardware costs do not scale linearly. A machine with 128 cores is exponentially more expensive than an 8-core instance. Furthermore, you will eventually hit a hardware ceiling—there are physical limits to how much RAM or CPU a single physical motherboard can support.
You decided to scale the business to 2 more cities. The scaled-up big machine is no longer able to handle the massive volume of read/write requests simultaneously.
Instead of querying the same database instance for everything, we separate read and write operations physically:
To solve the primary write bottleneck, you ask: "Why not distribute write requests to the replicas also?" E.g., let all database nodes write data.
In a Multi-Primary configuration, all database nodes are logically connected in a circular ring. Every machine acts as both a primary and a replica:
As you scale to 5 more cities, the write throughput grows to ~50 req/s. The system enters deep pain again. Because all nodes accept writes, conflict resolution is a nightmare. If Passenger 1 matches with Driver A on Node 1, and Passenger 2 matches with the same Driver A on Node 2 at the exact same millisecond, the nodes must negotiate who won the write. This leads to massive sync latency, locks, and aborted bookings.
To reduce conflicts and load, we analyze the schemas. We notice that driver location updates (telemetry) are clogging up the transaction tables.
Instead of putting all tables in one schema, we divide them by domain functionality onto separate physical machines:
Because tables reside on physically separate servers, the database can no longer perform native SQL `JOIN` queries.The backend application layer must now take the responsibility to join results. The backend must call DB 1 for profiles, DB 2 for booking status, and stitch the logs together in application memory.
Now, your team plans to expand the business to other countries across the globe!
To scale CabFlow across continents, functional partitioning is insufficient. We need a way to store billions of user records horizontally.
Instead of one monster server, we allocate 50 smaller machines, all having the exact same schema. Each machine holds only a subset (shards) of the data rows.
Sharding is notoriously hard to implement. It requires a custom routing layer, mapping keys cleanly, and handling complex shard key updates. However, it is the only way to scale the database infinitely as you expand globally.
When operating globally, requests travelling across continents (e.g. India app connecting to US databases) face severe latency due to the speed of light.
We deploy completely independent Data Centres across continents. All queries in Europe are directed to the Europe Data Centre, and Asian requests to the Asia Data Centre.
Data centres replicate vital master tables asynchronously. If a catastrophic network failure shuts down Data Centre US, Data Centre EU has the backup data ready, ensuring disaster recovery and zero downtime.
By scaling from a single SQL instance to connection-pooled, horizontally-partitioned, and cross-datacenter replicated engines, CabFlow is fully equipped to handle global scale. Time to plan the IPO! 😉
Click through the steps of the scaling journey below to see the database topology change and review the architectural choices made at each step:
Relational SQL (Postgres / MySQL)
~1 ride booking / 5 mins
None yet. System is fast, lightweight, and extremely cheap to run.
A single instance stores all customers, trips, and location histories. High ACID compliance ensures no double-booking errors.