Primary / Secondary Database — Database Scaling

Primary/Secondary is a database architecture where one database node acts as the Primary for write operations and one or more Secondary nodes maintain copies of the data.

The secondary nodes can be used for read scaling, high availability, reporting, and failover, depending on the database technology.

Basic Architecture

flowchart LR
    API[Application]

    API -->|Writes| P[(Primary)]

    P -->|Replication| S1[(Secondary 1)]
    P -->|Replication| S2[(Secondary 2)]

    API -->|Reads| S1
    API -->|Reads| S2

Why Primary / Secondary?

Suppose an application has heavy read traffic but comparatively fewer writes.

100,000 requests/second

90,000 Reads
10,000 Writes

Instead of forcing every operation onto one database, we can separate responsibilities.

flowchart TD
    U[Users] --> API[Application]

    API -->|Write Operations| P[(Primary)]
    API -->|Read Operations| S1[(Secondary 1)]
    API -->|Read Operations| S2[(Secondary 2)]

    P --> S1
    P --> S2

Complete Example — E-Commerce

Consider an e-commerce application.

Write operations:

• Create order
• Update inventory
• Process payment status
• Update customer details

Read operations:

• Product catalog
• Product details
• Order history
• Customer profile

flowchart LR
    API[Application]

    API -->|Create Order| P[(Primary)]
    API -->|Update Inventory| P
    API -->|Payment Update| P

    API -->|Product Search| S1[(Secondary 1)]
    API -->|Order History| S2[(Secondary 2)]
    API -->|Catalog| S1

    P -->|Replication| S1
    P -->|Replication| S2

How a Write Works

sequenceDiagram
    participant Client
    participant API
    participant P as Primary
    participant S as Secondary

    Client->>API: Create Order
    API->>P: INSERT Order
    P-->>API: Commit Success
    API-->>Client: Order Created

    P->>S: Replicate Change
    S->>S: Apply Change

The primary is normally the authoritative writer. The secondary receives the replicated change according to the configured replication mechanism.

How a Read Works

sequenceDiagram
    participant Client
    participant API
    participant S as Secondary

    Client->>API: Get Product
    API->>S: SELECT Product
    S-->>API: Product
    API-->>Client: Product Response

Primary Failure

The biggest advantage of having secondary nodes is that one can potentially be promoted if the primary fails.

flowchart LR
    API[Application] --> P[(Primary)]

    P --> S1[(Secondary 1)]
    P --> S2[(Secondary 2)]

    P -.-> X[Primary Failure]

    S1 --> N[Promoted Primary]
    N --> API

The exact failover process depends on the database platform and high-availability configuration.

Automatic Failover

flowchart TD
    A[Primary Database] --> M[Health Monitoring]

    M --> B{Primary Healthy?}

    B -->|Yes| A
    B -->|No| C[Select Secondary]
    C --> D[Promote Secondary]
    D --> E[Redirect Application Traffic]

A production system normally requires health monitoring, failure detection, promotion, connection redirection, and application recovery.

Primary / Secondary vs Read Replica

These terms are closely related but describe slightly different aspects of an architecture.

Replication Lag

Secondary databases may temporarily be behind the primary.

sequenceDiagram
    participant API
    participant P as Primary
    participant S as Secondary

    API->>P: Update Customer
    P-->>API: Success

    API->>S: Read Customer
    S-->>API: Old Data

    Note over S: Replication still catching up

    P->>S: Replicate Update

This means applications need to decide which operations require strong consistency and which can tolerate eventual consistency.

Important Design: Read Routing

flowchart TD
    A[Database Request] --> B{Operation}

    B -->|Write| P[(Primary)]
    B -->|Read - Strong Consistency| P
    B -->|Read - Eventual Consistency| S[(Secondary)]

For example, immediately after a payment is completed, the payment status may need to be read from the primary or another strongly consistent source. A product catalog may tolerate reading from a secondary.

Reporting Workloads

Secondary databases can also be useful for reporting workloads so that expensive analytical queries do not compete directly with production writes.

flowchart LR
    P[(Primary Database)] --> S[(Secondary)]

    S --> R[Reporting Queries]
    S --> A[Analytics]

    P --> W[Production Writes]

This can reduce contention, although large analytical workloads may eventually require a separate data warehouse or analytics architecture.

Important Limitation

Primary/Secondary does not automatically solve every scaling problem.

flowchart TD
    A[Database Bottleneck] --> B{Where is the bottleneck?}

    B -->|Reads| C[Read Replicas]
    B -->|Writes| D[Partitioning / Sharding]
    B -->|Queries| E[Index / Query Optimization]
    B -->|Repeated Reads| F[Cache]
    B -->|Single Region Failure| G[Multi-AZ / Multi-Region]

If the primary becomes overloaded by writes, adding more read-only secondaries will not solve the write bottleneck.

Interview Scenario

Interviewer: Your production database has one primary and three secondary databases. The primary fails. What happens?

Answer:

1. Monitoring detects the primary failure.
2. The system determines which secondary is eligible for promotion.
3. The selected secondary is promoted to primary.
4. Application/database routing is updated.
5. Applications reconnect to the new primary.
6. The old primary is recovered and rejoined according to the database platform's recovery process.
7. Any replication lag or unreplicated acknowledged data must be considered when defining the recovery guarantees.

Common Interview Questions

• What is Primary/Secondary architecture?
• What is the difference between primary and secondary?
• Why use secondary databases?
• Can secondary databases handle reads?
• What happens when the primary fails?
• What is replication lag?
• How does automatic failover work?
• Primary/Secondary vs Read Replica?
• Can Primary/Secondary scale writes?
• How would you route reads and writes?

Key Takeaways

• Primary is normally responsible for writes.
• Secondary maintains replicated data and can support reads or failover.
• Secondaries can improve read scalability and availability.
• Replication lag can cause stale reads.
• Automatic failover requires monitoring and controlled promotion.
• Adding secondaries does not solve a primary write bottleneck.
• Read routing and consistency requirements must be explicitly designed.

Conclusion

Primary/Secondary architecture separates the database's authoritative write role from replicated secondary nodes. It is a fundamental building block for read scaling and high availability.

For interviews, remember:

Primary → Writes | Secondary → Replicated Data / Reads / Failover