Techniques to scale your Relational Databases - Part 1
Tags: database, architecture, performance, devops
Published: 2021-05-07 09:53:03

### Relational database management system (RDBMS)

A relational database like SQL is a collection of data items organized in tables.

**ACID** is a set of properties of relational database [transactions](https://en.wikipedia.org/wiki/Database_transaction).

* **Atomicity** - Each transaction is all or nothing
* **Consistency** - Any transaction will bring the database from one valid state to another
* **Isolation** - Executing transactions concurrently has the same results as if the transactions were executed serially
* **Durability** - Once a transaction has been committed, it will remain so

There are many techniques to scale a relational database like :
* **Replication**
* **Federation**
* **Sharding**
* **Denormalization**
* **SQL tuning**

In this article, I will explain more about ***Replication*** its advantages and disadvantages. I will be explaining about remaining techniques in upcoming blog posts.

In general, there are two types of replication.
 1. [Master-Slave replication](#master-slave-replication)
 2. [Master-Master replication](#master-master-replication)

## Master-Slave replication

The master serves reads and writes, replicating writes to one or more slaves, which serve only reads.  Slaves can also replicate to additional slaves in a tree-like fashion.  If the master goes offline, the system can continue to operate in read-only mode until a slave is promoted to a master or a new master is provisioned.

<p align="center">
  <img src="http://i.imgur.com/C9ioGtn.png"/>
  <br/>
</p>

##### **Disadvantages**:

* Additional logic is needed to promote a slave to a master.


## Master-Master replication

Both masters serve reads and writes and coordinate with each other on writes.  If either master goes down, the system can continue to operate with both reads and writes.

<p align="center">
  <img src="http://i.imgur.com/krAHLGg.png"/>
  <br/>
</p>

##### **Disadvantages**: 

* You'll need a load balancer or you'll need to make changes to your application logic to determine where to write.
* Most master-master systems are either loosely consistent (violating ACID) or have increased write latency due to synchronization.
* Conflict resolution comes more into play as more write nodes are added and as latency increases.

<hr/>

##### **Disadvantages of replication** :

* There is a potential for loss of data if the master fails before any newly written data can be replicated to other nodes.
* Writes are replayed to the read replicas.  If there are a lot of writes, the read replicas can get stuck with replaying writes and can't do as many reads.
* The more read slaves, the more you have to replicate, which leads to greater replication lag.
* On some systems, writing to the master can spawn multiple threads to write in parallel, whereas read replicas only support writing sequentially with a single thread.
* Replication adds more hardware and additional complexity.

##### **References** :
* [Scalability, availability, stability, patterns](http://www.slideshare.net/jboner/scalability-availability-stability-patterns/)

In the upcoming articles, I will discuss more about other scalability techniques like Sharding, Federation, Denormalization & SQL tuning.

<hr/>

## Thank you for reading
Hope you find these resources useful. If you like what you read and want to see more about system design, microservices and other technology-related stuff... You can follow me on 
- [Twitter here](https://twitter.com/vishnuchi).
- Subscribe to my [Newsletter here](https://www.getrevue.co/profile/vishnuch).

<a href="https://www.buymeacoffee.com/vishnuchi" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/v2/default-yellow.png" alt="Buy Me A Coffee" style="height: 60px !important;width: 150px !important;" ></a>