How to Implement Read/Write Splitting in Spring Data JPA

Step-by-step configuration guide for routing read and write operations across primary and replica databases using Spring Boot, AbstractRoutingDataSource, and AOP interceptors. This runbook targets production deployments requiring deterministic query routing, strict transaction boundary enforcement, and zero-downtime failover capabilities.

Architectural Prerequisites & Topology Mapping

Define primary (writer) and replica (reader) endpoints before touching application code. Establish baseline connection pool sizing, replication lag thresholds (< 500ms for synchronous reads, > 2s triggers circuit breaker), and failover policies. Review foundational Connection Routing & Pooling Strategies to align infrastructure constraints with application-level routing logic before modifying ORM configurations.

Infrastructure Checklist:

• Primary endpoint: jdbc:postgresql://db-primary.internal:5432/appdb
• Replica endpoint(s): jdbc:postgresql://db-replica-01.internal:5432/appdb
• Network: Ensure read replicas are on isolated subnets with dedicated security groups.
• DNS TTL: Set to 30s for rapid endpoint rotation during failover.
• Replication Mode: Streaming replication with synchronous_commit = on (if strict consistency required) or off (for performance).
• Read/Write Ratio Baseline: Document expected traffic split (typically 85/15 to 95/5) to size connection pools accurately.

Step 1: Configure Dual DataSource Beans

Implement HikariCP configurations for both primary and replica databases in application.yml. Define @Primary for the write DataSource and annotate the read DataSource with @Qualifier("readDataSource"). Set distinct pool sizes, connection timeouts, and validation queries to isolate traffic paths at the JDBC driver level.

spring:
 datasource:
 writer:
 jdbc-url: jdbc:postgresql://db-primary.internal:5432/appdb
 username: ${DB_WRITER_USER}
 password: ${DB_WRITER_PASS}
 hikari:
 maximum-pool-size: 20
 minimum-idle: 5
 connection-timeout: 3000
 validation-timeout: 2000
 idle-timeout: 300000
 max-lifetime: 1800000
 connection-test-query: SELECT 1
 reader:
 jdbc-url: jdbc:postgresql://db-replica-01.internal:5432/appdb
 username: ${DB_READER_USER}
 password: ${DB_READER_PASS}
 hikari:
 maximum-pool-size: 40
 minimum-idle: 10
 connection-timeout: 2000
 validation-timeout: 1500
 idle-timeout: 300000
 max-lifetime: 1800000
 connection-test-query: SELECT 1

Failure Mode Warning: Identical maximum-pool-size across writer and reader pools will cause connection starvation on the primary during write-heavy bursts. Size reader pools 2–3x larger than writer pools based on baseline traffic ratios.

Step 2: Implement AbstractRoutingDataSource

Extend AbstractRoutingDataSource to override determineCurrentLookupKey(). Create a ThreadLocal context holder to store routing keys (READ or WRITE). Map the configured DataSources to a target map, set the default target to the primary writer, and register the routing bean as the EntityManagerFactory’s DataSource.

public enum RoutingKey { WRITE, READ }

public class RoutingContextHolder {
 private static final ThreadLocal<RoutingKey> CONTEXT = ThreadLocal.withInitial(() -> RoutingKey.WRITE);
 public static void set(RoutingKey key) { CONTEXT.set(key); }
 public static RoutingKey get() { return CONTEXT.get(); }
 public static void clear() { CONTEXT.remove(); }
}

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
 @Override
 protected Object determineCurrentLookupKey() {
 return RoutingContextHolder.get();
 }
}

Bean Registration:

@Configuration
@EnableJpaRepositories(basePackages = "com.app.repository")
public class DataSourceConfig {

 @Bean
 @ConfigurationProperties("spring.datasource.writer")
 public DataSourceProperties writerProperties() { return new DataSourceProperties(); }

 @Bean
 @ConfigurationProperties("spring.datasource.reader")
 public DataSourceProperties readerProperties() { return new DataSourceProperties(); }

 @Bean
 public DataSource routingDataSource() {
 DynamicRoutingDataSource routingDS = new DynamicRoutingDataSource();
 Map<Object, Object> targetDataSources = new HashMap<>();
 targetDataSources.put(RoutingKey.WRITE, writerProperties().initializeDataSourceBuilder().build());
 targetDataSources.put(RoutingKey.READ, readerProperties().initializeDataSourceBuilder().build());
 routingDS.setTargetDataSources(targetDataSources);
 routingDS.setDefaultTargetDataSource(targetDataSources.get(RoutingKey.WRITE));
 routingDS.afterPropertiesSet();
 return routingDS;
 }
}

Step 3: Automate Routing with AOP Interceptors

Develop an AspectJ interceptor targeting @Transactional(readOnly = true) methods. Push READ or WRITE keys into the ThreadLocal context before method execution. Ensure context clearance in @After advice to prevent thread pool contamination. Compare this ORM-level approach with Implementing Read/Write Splitting at the Proxy Layer to evaluate latency trade-offs and operational complexity.

@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class RoutingAspect {

 @Pointcut("@annotation(org.springframework.transaction.annotation.Transactional)")
 public void transactionalMethod() {}

 @Before("transactionalMethod()")
 public void beforeExecution(JoinPoint joinPoint) {
 MethodSignature signature = (MethodSignature) joinPoint.getSignature();
 Transactional tx = signature.getMethod().getAnnotation(Transactional.class);
 if (tx != null && tx.readOnly()) {
 RoutingContextHolder.set(RoutingKey.READ);
 } else {
 RoutingContextHolder.set(RoutingKey.WRITE);
 }
 }

 @After("transactionalMethod()")
 public void afterExecution() {
 RoutingContextHolder.clear();
 }

 @AfterThrowing(pointcut = "transactionalMethod()", throwing = "ex")
 public void afterThrowing(Throwable ex) {
 RoutingContextHolder.clear();
 }
}

Critical Guard: Always set @Order(Ordered.HIGHEST_PRECEDENCE) on the aspect. If transaction management executes before routing context injection, the connection will bind to the default (writer) pool, defeating the split.

Step 4: Transaction Boundary & Propagation Alignment

Configure @Transactional propagation rules to prevent nested read transactions from inheriting write routing keys. Set readOnly = true explicitly on service methods that only execute SELECT queries. Validate that JPA EntityManager flush behavior does not trigger unintended write routing during read-heavy workflows.

Enforced Transaction Semantics:

• Use @Transactional(isolation = Isolation.READ_COMMITTED, readOnly = true, propagation = Propagation.REQUIRES_NEW) for explicit read boundaries. This isolates replica reads from parent transactional contexts and prevents phantom reads without SERIALIZABLE overhead.
• Avoid Propagation.NESTED or Propagation.SUPPORTS on read methods; they inherit the parent thread’s routing key, causing write-bound connections to service SELECT queries.
• Disable auto-flush on read-only transactions by setting spring.jpa.properties.hibernate.flushMode=COMMIT globally, or explicitly call entityManager.setFlushMode(FlushMode.MANUAL) in read services.

Failure Mode: If readOnly = true is omitted, Hibernate triggers EntityManager.flush() before query execution, acquiring a write connection and routing to the primary. This causes primary pool exhaustion under read-heavy load.

Troubleshooting Runbook: Symptom Identification & Resolution

Explicit Rollback Procedure:

1. Disable the routing aspect via Spring profile: SPRING_PROFILES_ACTIVE=legacy-routing
2. Revert DataSourceConfig to a single @Primary HikariCP bean pointing to the primary endpoint.
3. Remove AbstractRoutingDataSource and RoutingContextHolder beans from the context.
4. Restart the application: systemctl restart app-service or kubectl rollout restart deployment/app
5. Verify baseline connectivity via curl -s http://localhost:8080/actuator/health | jq .components.db.status

Validation, Monitoring & Production Tuning

Deploy integration tests asserting read/write routing via mock DataSource proxies. Expose Micrometer metrics for active connections, routing key distribution, and query latency. Tune HikariCP maximumPoolSize and idleTimeout based on observed read/write ratios. Implement circuit breakers for replica failover scenarios and configure health checks to dynamically adjust routing weights.

Required Metrics & Alerts:

• hikaricp_connections_active{pool="writer"} / hikaricp_connections_active{pool="reader"}
• routing_context_switches_total{target="READ"} vs {target="WRITE"}
• db_replication_lag_seconds (custom gauge from replica health check)
• Alert Threshold: hikaricp_connections_active{pool="reader"} > 0.85 * maximumPoolSize → Trigger connection pool scale-up or circuit breaker trip.

Production Tuning Checklist:

• Set spring.jpa.open-in-view=false to prevent lazy-loading queries from bypassing routing context.
• Configure spring.datasource.hikari.leak-detection-threshold=30000 to catch unclosed connections.
• Implement a ReplicaCircuitBreaker using Resilience4j: trip after 3 consecutive ConnectionTimeoutException or ReplicationLagException, route all traffic to writer until half-open state validates replica recovery.
• Validate routing distribution in staging with pg_stat_activity queries to confirm client_addr matches expected replica endpoints.