How to reduce the stress on database

In this note, I am going to write about some approaches we can take to reduce the preassure on a database.

Today, application servers can scale horizontally to handle increasing the incoming traffic. But scaling the database is different challenge.

Caching

The first action we can take is to introduce a caching mechanism.

Instead of directly asking the database for every request, the server can first check the cache. If the requested data exist in the cache we can return it directly.

If the cache does not contain the data, we call it a cache miss. When the cache miss happens, the server retrieves the data from the database.

But then another problem appears.

What happens if thhe actual data changes in the database, but the cache still contains the old data?

In that situation, users may receive the outdated data from the cache. We call this stale data.

So, how can we address that problem?

One approach is to set a TTL(Time To Live) for cached data. After the TTL expires, the cached data is removed or considered expired.

But this can introduce another problem.

Suppose a large number of cache entries expire at the same time. Suddenlly, many requestes may miss the cache and go directly to the database. This could create a large spike in database traffic.

So how are we going to handle that????

I will learn about that and write it down later.

Annother common approach is this: when the cache expires and the server needs the data again, the server fetch the data from the database and then stores the fresh data back in the cache. The next request can then retrieve it from the cache again.

However, caching is not a silver bullet for every database scaling problem.

Database Replication

If traffic continues to increase, the next approach is to scale the database.

One common approach is to replicate the database.

Traditionally, this is often described as a primary-replica architecture. There is one primary database responsible for write operations such as updates, inserts, and deletes. Other database replicas can handle read operations.

This is useful because many systems are more read-heavy than write-heavy.

But then another question appears.

How does the application know which database it should use for a read and which database it should use for a write?

One approach is to introduce a routing layer between the application and the database.

Similar to how a load balancer distributes traffic between servers, this layer can help route read and write queries to the appropriate database.

I do not yet know the exact tools and mechanisms used for this, so I will learn about that and write about it later.

Database Sharding

The next approach is sharding.

In replication, multiple database servers contain copies of the same dataset. But in sharding, the dataset is divided into smaller pieces based on some attribute.

For example, data could be partitioned based on a user ID or another attribute. Each partition is called a shard, and different shards can store different parts of the dataset.

I still need to learn more about how sharding works in practice.

As I understand it now, a system can also replicate individual shards. In that architecture, each shard may have its own primary and replicas. The replicas should also be distributed carefully so that a single node failure does not affect all copies of the same data.

I am writing these notes as part of my personal system design journey. I do not want every note to be perfect.

God Bless