Database Scaling is one of the most important topics in System Design . Normally for any large scale application architecture, the database is usually going to be where your performance bottleneck is.

That’s because while most of your application are essentially stateless, so that you can scale them horizontally as much as you need. All the applications will hit the database for retrieving data, or writing new data.

Key Information

Most of the large-scale web application are very read-intensive, usually around 95% read request and 5% write requests.

So in almost of all cases, you want to prioritise and build around the idea that your database will receive a lot more reads than writes.

Basic Scaling Techniques

  1. Indexes.
    1. index based on column.
    2. speeds up read performance.
    3. writes and updates become slightly slower.
    4. more storage for index.
  2. Denormalisation.
    1. add redundant data to tables to reduce joins.
    2. boosts read performance.
    3. slow down writes.
    4. risk inconsistent data across tables.
    5. code is harder to write.
  3. Connection Pooling.
    1. Allow multiple application threads to use same DB connection. (car pooling)
    2. Saves on overhead of independent database connections.
  4. Caching
    1. not directly related to database.
    2. cache sits in front of database to handle serving content.
    3. can’t cache everything.
  5. Vertical Scaling
    1. Get a bigger server.
    2. Easiest solution when starting out.

Database Basic Scaling

Replication and Partitioning

Read Replicas

  1. Create replica servers to handle reads.
  2. Primary server dedicated only to writes.
  3. Have built-in Fault Tolerance
  4. Have to handle making sure new data reaches replicas.

Database Replica

Horizontal Partition - Sharding

  1. Horizontal Partitioning.
  2. Schema of table stays the same, but split across multiple DBs.
  3. Downside - Hotkeys, no joins across shards.

Sharding in Database will separate one table’s rows into multiple different tables, known as partitions. Each partition has exactly the same schema and columns, but also entirely different rows. The data held in each partition is unique and independent.

Database shards exemplify a shared-nothing architecture . This means

  1. The shards are autonomous.
  2. They don’t shared any of the same data or computing resources.

Vertical Partitioning

  1. Divide schema of database into separate tables.
  2. Generally divide by functionality.
  3. Best when most data in row isn’t need for most queries.

Database Partitioning

When to consider NoSQL

So far we pretty much destroyed all the benefits from a relational database

  1. normalised data.
  2. strong consistency.
  3. simple data model.

The reason why you start to consider NoSQL, is not because NoSQL have any magical power, but it’s the fact that you know what you need to sacrificing.

So you probably start everything from a relational database with all the best practices. By the time you ended up scaling you’ve already lost all of that. So the reason you choose a NoSQL database is you know exactly what you need to sacrifice, you know what specifically you need for your application that you can make trade-offs.

So if you are doing something like transactions and banking, you obviously want consistency. But for other company like Google and Facebook, where you don’t need perfect consistency right away, you could make those trade-offs for scale.

Reference