Intro

This is a conversation with Gemini how to split DB queries between Primary database and the Replica. The idea is to route database transaction in RW mode to primary server and RO transaction (which is used for analytical queries) to the replica.

It is necessary to verify the claims.

1. Conversation

1.1. Question

Q: In my app are also long running analysis queries. They require read only access. I would like to run them on replica to distribute load. Is there any solution for it?

1.2. Answer

A: To separate your heavy analysis queries from your primary transaction traffic, you should implement Read-Write Splitting. Since your "db2" is a streaming replica, it is in hot standby mode, meaning it is open for read-only connections.

The most elegant way to do this in Spring Boot is using an AbstractRoutingDataSource. This allows you to automatically route traffic to the replica whenever you use @Transactional(readOnly = true).

1.2.1. Define Two DataSources in application.yml

First, stop using the multi-host JDBC URL for this specific task, as that is meant for failover, not load distribution. Instead, define them explicitly:

spring:
  datasource:
    primary:
      jdbc-url: jdbc:postgresql://localhost:5411/stage
      username: pdm_control
      password: xxx
      driver-class-name: org.postgresql.Driver
    replica:
      jdbc-url: jdbc:postgresql://localhost:5412/stage
      username: pdm_control
      password: xxx
      driver-class-name: org.postgresql.Driver

1.2.2. Create a Routing Logic

You need a class to decide which "key" to use based on the transaction context.

public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
               ? "replica" : "primary";
    }
}

1.2.3. Configure the Beans

You need to wrap these DataSources so Spring can switch between them dynamically.

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.replica")
    public DataSource replicaDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dataSource() {
        ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("primary", primaryDataSource());
        dataSourceMap.put("replica", replicaDataSource());

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(primaryDataSource());
        return routingDataSource;
    }
}

1.2.4. Usage in Service Layer

Now, your code stays clean. Spring will handle the routing based on your annotation:

@Service
public class AnalysisService {

    // This will run on db1 (Primary)
    @Transactional
    public void updateData() {
        // write operations
    }

    // This will automatically route to db2 (Replica)
    // Perfect for long-running analysis
    @Transactional(readOnly = true)
    public List<Report> runHeavyAnalysis() {
        return repository.performComplexQuery();
    }
}

Critical "DevOps" Tip for Analysis If your analysis queries are truly "long-running" (minutes), you might encounter an error on the replica called "User query might have needed to see row versions that are must be removed." To fix this, run these commands on db1 (Primary):

ALTER SYSTEM SET max_standby_archive_delay = '300s'; -- Allow standby 5 mins to finish queries
ALTER SYSTEM SET max_standby_streaming_delay = '300s';
SELECT pg_reload_conf();

Alternatively, enable Hot Standby Feedback on the replica (db2) so it tells the primary not to delete data it is currently reading:

-- On db2
ALTER SYSTEM SET hot_standby_feedback = on;

Would you like to see how to monitor the "Replication Lag" to ensure your analysis isn’t reading data that is too old?

1.3. Question

My analytic queries work on time range of days or months. It does not make any difference if last minute is missing.

1.4. Answer

That makes your life much easier! If you can tolerate a few minutes of lag, you don’t have to worry about the complex "Hot Standby Feedback" loop which can sometimes bloat the primary’s storage.

Since your analysis is focused on historical data (days/months), here is the optimized setup for your specific use case:

1.4.1. The "Lazy" Replica Config

To prevent your long-running analysis queries from being killed when the primary sends over new updates (VACUUM cleanups), you should adjust the standby to prioritize your queries over immediate synchronization.

Run this on db2 (the Replica):

-- Allow the replica to delay applying WAL logs for up to 30 minutes
-- if a local query is currently reading those data blocks.
ALTER SYSTEM SET max_standby_streaming_delay = '30min';
ALTER SYSTEM SET max_standby_archive_delay = '30min';
-- Reload the configuration
SELECT pg_reload_conf();

1.4.2. Spring Boot "Double-Check" Failover

Even though you are using db2 for analysis, there is a risk that db2 might go down for maintenance. To keep your app resilient, you can modify the replica JDBC URL to "fall back" to the primary if the replica is unavailable:

# Inside your replica datasource config
replica:
  jdbc-url: jdbc:postgresql://localhost:5412,localhost:5411/stage?targetServerType=preferSecondary

How it works: It will try the replica (5412) first. If it’s down, it hits the primary (5411). Because of preferSecondary, it will always go back to the replica once it’s healthy.

1.4.3. Monitoring the "Lag"

Since you are doing time-range analysis, you might occasionally want to check how far behind the replica is. You can run this on db1 (Primary) to see the lag in bytes and time:

SELECT
    application_name,
    client_addr,
    state,
    (pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) / 1024)::int as pending_kb,
    (pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024)::int as total_lag_kb,
    replay_lag as time_lag
FROM pg_stat_replication;

Summary of Benefits

  • No Interference: Your heavy JOINs on months of data won’t slow down the users creating new records on db1.

  • Stability: By setting the max_standby_streaming_delay to 30 minutes, your analysis won’t crash even if the primary is heavily updating the database.

  • Structure: Your Spring Boot code remains clean using @Transactional(readOnly = true).

Are you ready to test the data flow by inserting a record into db1 and selecting it from your "Analysis" service on db2?