Core Principles of Read-Write Separation

Database read-write splitting distributes query and modification operations across separate database instances to enhance system performance and scalability. This architectural pattern directs write operations to a primary instance while routing read queries to one or multiple replica instances.

Key Components

• Primary Node: Handles all data modification operations (INSERT, UPDATE, DELETE) and maintains data consistency as the authoritative source.
• Replica Nodes: Mirror data from the primary node and process read-only queries. Multiple replicas can be configured to distribute read load.

Implementasion Approach

1. Replication Setup: Configure data synchronization between primary and replica nodes using built-in database features like MySQL's replication mechanism.
2. Query Routing: Implement logic at the application or middleware layer to direct operations to appropriate database instances.
3. Consistency Management: Address replication lag to ansure acceptable data freshness for read operations.

Java-Based Implementation Example

The following demonstrates read-write splitting using Spring Framework with Hikari connection pooling.

Configuration Properties

# application.yml
database:
  primary:
    endpoint: jdbc:mysql://primary-server:3306/appdata
    credentials:
      user: admin
      pass: secret
    pool:
      max-size: 15
  
  secondaries:
    - endpoint: jdbc:mysql://replica-one:3306/appdata
      credentials:
        user: admin
        pass: secret
    - endpoint: jdbc:mysql://replica-two:3306/appdata
      credentials:
        user: admin
        pass: secret

Connection Pool Configuraton

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Configuration
public class DatabaseConnectionConfig {

    private final DatabaseProperties dbProps;
    
    public DatabaseConnectionConfig(DatabaseProperties properties) {
        this.dbProps = properties;
    }

    @Bean
    public DataSource routingDataSource() {
        DynamicDataSource router = new DynamicDataSource();
        
        HikariDataSource primarySource = createDataSource(
            dbProps.getPrimary().getEndpoint(),
            dbProps.getPrimary().getCredentials()
        );
        
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("write", primarySource);
        
        List<SecondaryConfig> replicas = dbProps.getSecondaries();
        for (int idx = 0; idx < replicas.size(); idx++) {
            SecondaryConfig replica = replicas.get(idx);
            HikariDataSource replicaSource = createDataSource(
                replica.getEndpoint(), 
                replica.getCredentials()
            );
            dataSourceMap.put("read" + idx, replicaSource);
        }
        
        router.setTargetDataSources(dataSourceMap);
        router.setDefaultTargetDataSource(primarySource);
        
        return router;
    }
    
    private HikariDataSource createDataSource(String url, Credentials creds) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(creds.getUser());
        config.setPassword(creds.getPass());
        return new HikariDataSource(config);
    }
}

Dynamic Data Source Router

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    
    private static final ThreadLocal<String> operationType = new ThreadLocal<>();
    
    public static void setCurrentOperation(String type) {
        operationType.set(type);
    }
    
    public static void resetOperation() {
        operationType.remove();
    }
    
    @Override
    protected Object determineCurrentLookupKey() {
        return operationType.get();
    }
}

Operation Interceptor

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DatabaseOperationInterceptor {
    
    @Before("@annotation(com.app.annotations.ModifyData)")
    public void routeToPrimary() {
        DynamicDataSource.setCurrentOperation("write");
    }
    
    @Before("@annotation(com.app.annotations.ReadData) || execution(* com.app.repository..*get*(..))")
    public void routeToReplica() {
        DynamicDataSource.setCurrentOperation("read0");
    }
}

Custom Annotations

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ModifyData {}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ReadData {}

Service Layer Usage

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class AccountService {
    
    @Autowired
    private AccountRepository repository;
    
    @ModifyData
    @Transactional
    public void createAccount(Account account) {
        repository.persist(account);
    }
    
    @ReadData
    public Account getAccount(Long accountId) {
        return repository.retrieve(accountId);
    }
}