Feature Request: scale out of PostgreSQL performance by splitting reads/writes

Overview of the Feature Request

Postgres has no native support for multiple primary nodes, but relies on people using read-only replicas. This fits well with the usual pattern of much more read than write traffic for a website. JPA/EclipseLink provides read/write partitioning of SQL queries without code changes. This means we can take advantage of scaling out the database with read replicas and "only" need to give EclipseLink the necessary DataSource to do it for us.

What kind of user is the feature intended for?
Sysadmin

What inspired the request?
No longer wanting to run a non-HA architecture for the database.

What existing behavior do you want changed?
Enable configuring read-only replicas and split reads and writes to a primary and these replicas.
See also https://jdbc.postgresql.org/documentation/use/#connection-parameters parameter "loadBalanceHosts"

Any brand new behavior do you want to add to Dataverse?
Not really brand new, no.

Any open or closed issues related to this feature request?
Not sure.

Are you thinking about creating a pull request for this feature?
Yes.

Here's some code for the idea:

1. Add this to DataSourceProducer (plus some nice logging for init if splitting is on, etc):

// Read replica database (reads)
    // For single database deployments, just use default localhost - will create a separate pool but to same DB
    // For production, configure multiple replica hosts separated by commas
    @DataSourceDefinition(
            name = "java:app/jdbc/dataverse-read",
            className = "org.postgresql.ds.PGConnectionPoolDataSource",
            user = "${MPCONFIG=dataverse.db.read.user:dataverse}",
            password = "${MPCONFIG=dataverse.db.read.password}",
            // Multi-host support: Use targetServerType=preferSecondary to prefer replicas
            // loadBalanceHosts=true randomizes connection order for better distribution across pool
            url = "jdbc:postgresql://${MPCONFIG=dataverse.db.read.hosts:localhost:5432}/${MPCONFIG=dataverse.db.read.name:dataverse}?targetServerType=preferSecondary&loadBalanceHosts=true&${MPCONFIG=dataverse.db.read.parameters:}",
            // Read pool can be larger since we expect more read traffic
            minPoolSize = 10,
            maxPoolSize = 150,
            maxIdleTime = 300,
            properties = {
                // Enable connection validation for read replicas (recommended for production)
                "fish.payara.is-connection-validation-required=${MPCONFIG=dataverse.db.read.is-connection-validation-required:false}",
                "fish.payara.connection-validation-method=${MPCONFIG=dataverse.db.read.connection-validation-method:}",
                "fish.payara.validation-table-name=${MPCONFIG=dataverse.db.read.validation-table-name:}",
                "fish.payara.validation-classname=${MPCONFIG=dataverse.db.read.validation-classname:}",
                "fish.payara.validate-atmost-once-period-in-seconds=${MPCONFIG=dataverse.db.read.validate-atmost-once-period-in-seconds:0}",
                "fish.payara.connection-leak-timeout-in-seconds=${MPCONFIG=dataverse.db.read.connection-leak-timeout-in-seconds:0}",
                "fish.payara.connection-leak-reclaim=${MPCONFIG=dataverse.db.read.connection-leak-reclaim:false}",
                "fish.payara.statement-leak-timeout-in-seconds=${MPCONFIG=dataverse.db.read.statement-leak-timeout-in-seconds:0}",
                "fish.payara.statement-leak-reclaim=${MPCONFIG=dataverse.db.read.statement-leak-reclaim:false}",
                "fish.payara.statement-timeout-in-seconds=${MPCONFIG=dataverse.db.read.statement-timeout-in-seconds:-1}",
                "fish.payara.slow-query-threshold-in-seconds=${MPCONFIG=dataverse.db.read.slow-query-threshold-in-seconds:-1}",
                "fish.payara.log-jdbc-calls=${MPCONFIG=dataverse.db.read.log-jdbc-calls:false}"
            })

2. Add this to persistence.xml:

<!-- Enable replication partitioning (controlled by dataverse.db.read.enabled config) -->
            <property name="eclipselink.partitioning" value="Replication"/>
            <property name="eclipselink.partitioning.callback" value="edu.harvard.iq.dataverse.util.DataverseReplicationPartitioningPolicy"/>

3. Create the custom policy:

/**
 * Custom replication partitioning policy for Dataverse.
 * Only performs read/write splitting if configured via dataverse.db.read.enabled=true
 * Otherwise, all operations use the primary database.
 */
public class DataverseReplicationPartitioningPolicy extends ReplicationPartitioningPolicy {
    
    private static final Logger logger = Logger.getLogger(DataverseReplicationPartitioningPolicy.class.getName());
    private static final String READ_DATASOURCE_JNDI = "java:app/jdbc/dataverse-read";
    
    private boolean readReplicaEnabled = false;
    
    public DataverseReplicationPartitioningPolicy() {
        super();
        setName("DataverseReplication");
        
        // Check if read replica is enabled
        try {
            InitialContext ctx = new InitialContext();
            DataSourceProducer producer = (DataSourceProducer) ctx.lookup("java:module/DataSourceProducer");
            readReplicaEnabled = producer.isReadReplicaEnabled();
            
            if (readReplicaEnabled) {
                logger.info("Initialized replication partitioning - read/write splitting is ENABLED");
                
                // Configure the replica connection pool
                List<String> replicaPools = new ArrayList<>();
                replicaPools.add(READ_DATASOURCE_JNDI);
                setReplicatedConnectionPools(replicaPools);
            } else {
                logger.info("Replication partitioning is DISABLED - all operations will use primary database");
            }
        } catch (Exception e) {
            logger.warning("Failed to initialize read replica configuration: " + e.getMessage() + 
                    ". Read/write splitting is DISABLED.");
            readReplicaEnabled = false;
        }
    }
    
    @Override
    public List<Accessor> getConnectionsForQuery(AbstractSession session, DatabaseQuery query, AbstractRecord arguments) {
        // If read replica is not enabled, always use primary
        if (!readReplicaEnabled) {
            return session.getAccessors();
        }
        
        // Otherwise, use the parent's replication logic
        // (reads go to replica, writes and transactional reads go to primary)
        return super.getConnectionsForQuery(session, query, arguments);
    }
}

Start by inspecting DataSourceProducer and persistence.xml, then review the proposed DataverseReplicationPartitioningPolicy alongside EclipseLink's ReplicationPartitioningPolicy. The work is done when read replicas can be configured for read/write splitting, while disabled or failed initialization keeps operations on the primary database; no tests are named in the issue.