To understand the concept of Primary-Replica Database Architecture, it is essential to explain why replication is needed in the first place.

The Problem

As an analogy, a firm was tasked with creating a social media platform for its specific use case. Everything went well until after 3 months, the platform went viral, and was onboarding a lot of users. The database server crashed from time to time, making it impossible for users to make posts or access them on the platform. The firm was contacted to rectify this issue, and their immediate decision was to scale the application server, which initially made sense, because it should be able to handle the number of users coming to the platform.

Well, as good as the solution was, the problem persisted, and the firm had to investigate further to observe what went wrong. It's no surprise that the issue was with data storage. While the application server could now handle the loads from the users of the platform, the issue remains that they are still interacting with a single database. The single database instance became a bottleneck for both write and read operations on the platform, and a single point of failure (SPOF).

They furthermore compared how often users are creating and commenting on posts, to how often they interact with and read posts. On a per-second scale, there are over 10 posts, 100 comments per post and 5 responses per comment in a second. On the other hand, there are over 50 reads on posts, 100 on comments and 50 on replies per comment. Making it 5,000 writes to 250,000 reads from that single database. There is a lot of workload for a single database instance to handle every second.

The need for replication.

The firm had a solution in mind. Instead of a single database writing data in and reading data out, why don't they introduce more instances to handle one of the operations? This called for the need for replication. Two operations are in place: reading and writing data to the database. Looking at the ratio of write to read, 1:50, they decide to introduce two database instances, which will handle the heavy workload of reading from the database. The primary database (the first instance) will now be for only writing to the database and propagating the changes to the other databases.

That's it with the analogy; let's get into the technical details.

The primary-replica database architecture.

The primary-replica architecture is a widely used distributed system design pattern for data storage, primarily in databases, to enhance scalability, high availability, and fault tolerance. It's also known as the master-slave architecture or the leader-follower architecture. I'm sure the analogy above already gave a perspective on what the architecture is. In this architecture, there are two main types of nodes:

1. The Primary (Master/Leader): This node handles all write operations (INSERT, DELETE, UPDATE). It's the authoritative node of the architecture and the single source of truth for the data.

2. The Replica (Slave/Follower): These are one or more nodes that maintain a synchronised, read-only copy of the data from the primary. They handle read operations, distributing the read workload off the primary node.

With this approach, the workload upon the single database instance earlier has been relieved.

How it works:

1. Write Operations: A client sends a write request (e.g creates a new post) to the primary node.

2. Propagation: After the Primary processes the write and updates its local data, it then propagates the changes to connected Replicas.

3. Read Operations: The client (or primarily using a load balancer) directs all read requests to the Replicas to reduce load on the Primary.

Replication Methods

There are two methods for the primary node to propagate changes. The way this is done determines the consistency and speed of the system.

• Synchronous Replication: The Primary waits for one or all Replicas to acknowledge that they have successfully received and applied the update before confirming the write to the client.

• Its advantage is that it ensures data consistency across all nodes, while its disadvantage is that it introduces higher latency for write operations.

• Asynchronous Replication: The Primary confirms the write to the client immediately after updating its local data, and propagates the change to the Replicas in the background.

Obviously, everything in life is a trade-off, and always has its Pros and Cons. Let's take a look at the key advantages and disadvantages of this design pattern.

Advantages

1. Scalability: It's easier to scale read throughput by adding more replicas. For instance, if the reads increase from 250,000 to 1,000,000 per second, adding more replicas will help distribute the load evenly.

2. Availability: If the primary database fails, a replica can be promoted to become the new primary. This is called a failover.

3. Performance: Query response is improved when the read load is spread across multiple replicas.

Disadvantages

1. Scalability: Cannot easily scale write throughput; all writes must go to the Primary.

2. Availability: If the Primary fails, the system cannot process writes until a failover occurs, introducing a temporary single point of failure in the system.

3. Performance: Replication Lag in asynchronous setups can lead to temporary data inconsistency between the Primary and Replicas.

And that's it about the Primary-Replica architecture. It's best done for read-heavy applications (like social media feeds, e-commerce product pages, or news sites) where the volume of read operations significantly outweighs the volume of write operations, like in our analogy earlier.

Next up, we're going to see how to set up a Primary-Replica Architecture using Prisma and a forked Postgres database.

# docker-compose.yml
services:
  postgresql-primary:
    image: 'bitnami/postgresql:latest'
    ports:
      - '6432:6432'
    environment:
      - POSTGRESQL_PORT_NUMBER=6432
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_USERNAME=prisma
      - POSTGRESQL_PASSWORD=prisma
      - POSTGRESQL_DATABASE=test
      - POSTGRESQL_PGAUDIT_LOG=read,write
  postgresql-replica-1:
    image: 'bitnami/postgresql:latest'
    ports:
      - '7432:7432'
    depends_on:
      - postgresql-primary
    environment:
      - POSTGRESQL_PORT_NUMBER=7432
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_HOST=postgresql-primary
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_MASTER_PORT_NUMBER=6432
      - POSTGRESQL_PGAUDIT_LOG=read,write
  postgresql-replica-2:
    image: 'bitnami/postgresql:latest'
    ports:
      - '8432:8432'
    depends_on:
      - postgresql-primary
    environment:
      - POSTGRESQL_PORT_NUMBER=8432
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_HOST=postgresql-primary
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_MASTER_PORT_NUMBER=6432
      - POSTGRESQL_PGAUDIT_LOG=read,write

Here we have a Docker Compose file that helps set up three PostgreSQL databases. The first is the primary, and the other two are the replica databases.

We run it using this command:

docker compose up -d

Make sure you have a node project with Prisma setup. Install this extension from prisma.

npm install @prisma/extension-read-replicas

This package helps connect, intercepts read requests, and direct the operation to the available replica from the managed pool of replicas. Here's how to use it

import { PrismaClient } from '../prisma/generated/client'
import { readReplicas } from '@prisma/extension-read-replicas'

const prisma = new PrismaClient().$extends(
  readReplicas({
    url: [
      process.env.DATABASE_URL_REPLICA_1,
      process.env.DATABASE_URL_REPLICA_2,
    ],
  })
)

// Query is run against the database replicas
await prisma.post.findMany()

// Query is run against the primary database
await prisma.post.create({ 
  data: {/** */},
})

Very simple solution right there. Now you have configured a primary-replica into your codebase. Let's not forget the environment variables.

# .env
DATABASE_URL=postgresql://prisma:prisma@localhost:6432/test
DATABASE_URL_REPLICA_1=postgresql://prisma:prisma@localhost:7432/test
DATABASE_URL_REPLICA_2=postgresql://prisma:prisma@localhost:8432/test

And there we have it.

What of an existing codebase?

A good question. In an existing codebase, you'd have to figure out how you can intercept queries and divert them to the suitable database based on the query operation. This means you'd be building a custom query gateway. And you'd also be managing the connection pool for these databases: the primary and its replicas.

That's it about the concept. I hope you enjoyed this article! For any contribution or question, please use the comment section below. Thanks for reading!

References:

• Prisma Documentation on Read Replicas: https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/read-replicas

• Check Prisma's read-replica extension repo as a guide when building your custom solution.
  https://github.com/prisma/extension-read-replicas/tree/main