Configure JDBC load balancing on multiple read-only secondary instances - Hologres

When query traffic concentrates on a single Hologres instance, you risk overloading it and losing availability if that instance fails. By listing multiple read-only secondary instance endpoints in a single JDBC connection URL, Hologres distributes query traffic randomly across instances and retries other endpoints automatically when one becomes unreachable.

This feature is available from Hologres V1.3.

How it works

A Hologres primary instance can be attached to multiple read-only secondary instances. The instances share storage but their compute resources are isolated from each other, forming a high availability (HA) deployment with read/write splitting. For more information, see Primary and secondary instances with read/write splitting (shared storage).

When you list multiple endpoints in the connection URL, JDBC provides two behaviors:

Random distribution: When loadBalanceHosts=true, JDBC randomly selects from the listed endpoints, distributing connections across instances.
Sequential failover: If a connection attempt to one endpoint fails, JDBC automatically tries the next endpoint in the URL. JDBC reports a connection failure only if all listed endpoints are unreachable.

JDBC determines whether an instance is a primary or a read-only secondary by checking the in_hot_standby Grand Unified Configuration (GUC) parameter: off means primary, on means read-only secondary. This is what allows targetServerType to route by instance role.

Prerequisites

Before you begin, ensure that you have:

Multiple Hologres read-only secondary instances attached to a primary instance. See Configure primary and secondary instances that use shared storage
PostgreSQL JDBC driver version 42.3.2 or later. See JDBC

Configure load balancing in the connection URL

List all instance endpoints in the connection URL, separated by commas. The following example shows the recommended configuration for random load balancing with a 10-second endpoint cache:

jdbc:postgresql://<Endpoint1>:<Port1>,<Endpoint2>:<Port2>,<Endpoint3>:<Port3>/<DBNAME>?user=<AccessKey ID>&password=<AccessKey secret>&targetServerType=any&loadBalanceHosts=true&hostRecheckSeconds=10

The following table describes each parameter.

Parameter	Description
`Endpoint`	The network address of the Hologres instance. Get this from the Instance Details page in the Hologres console.
`Port`	The port of the Hologres instance. Get this from the Instance Details page in the Hologres console.
`DBNAME`	The name of the database to connect to.
`user`	Your AccessKey ID. Get this from AccessKey Management.
`password`	Your AccessKey secret. Get this from AccessKey Management.
`targetServerType`	Controls which instance types accept connections. Supported values: `any` connects to any listed endpoint. The following values require Hologres V2.0.10 or later: `master` (primary instance only), `slave` (read-only secondary instances only), `preferSlave` (read-only secondary instances first; falls back to the primary instance if a connection to a read-only secondary instance fails).
`loadBalanceHosts`	Controls the connection order. `true`: randomly selects from the listed endpoints, distributing load across instances. `false` (default): tries endpoints in the order they are listed. Set to `true` for most load balancing scenarios.
`hostRecheckSeconds`	How long JDBC caches the list of reachable endpoints, in seconds. Default: `10`. If you want to change the cache duration, modify the value of the `hostRecheckSeconds` parameter.

For the full list of PostgreSQL JDBC configuration options, see the JDBC documentation.

Examples

Random load balancing across three read-only secondary instances

Connect to three read-only secondary instances with random distribution (loadBalanceHosts=true, targetServerType=any). When one instance is unavailable, JDBC automatically retries the other endpoints.

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class hatest {
    public static void main(String[] args) {
        // Read-only secondary instance 1
        String endpoint1 = "hgpostcn-cn-wxxxxxxxx01-cn-shanghai.hologres.aliyuncs.com:80";
        // Read-only secondary instance 2
        String endpoint2 = "hgpostcn-cn-wxxxxxxxx02-cn-shanghai.hologres.aliyuncs.com:80";
        // Read-only secondary instance 3
        String endpoint3 = "hgpostcn-cn-wxxxxxxxx03-cn-shanghai.hologres.aliyuncs.com:80";
        String dbname = "postgres";
        String jdbcUrl = "jdbc:postgresql://" + endpoint1 + "," + endpoint2 + "," + endpoint3 + "/" + dbname;
        Properties properties = new Properties();
        properties.setProperty("user", "xxxx");
        properties.setProperty("password", "xxxx");
        // any: connect to any listed endpoint regardless of instance role
        properties.setProperty("targetServerType", "any");
        // true: randomly select from listed endpoints to distribute load
        properties.setProperty("loadBalanceHosts", "true");
        // Cache reachable endpoints for 10 seconds before rechecking
        properties.setProperty("hostRecheckSeconds", "10");
        try {
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection(jdbcUrl, properties);
            PreparedStatement preparedStatement = connection.prepareStatement("show hg_frontend_endpoints;");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData rsmd = resultSet.getMetaData();
                int columnCount = rsmd.getColumnCount();
                Map map = new HashMap();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                }
                System.out.println(map);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

Polling between a primary instance and a read-only secondary instance

Send 100 queries across a primary instance and one read-only secondary instance. With loadBalanceHosts=true, JDBC randomly distributes connections, so connection counts on both instances should be roughly equal after the queries run.

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class hatest {
    public static void main(String[] args) {
        int x = 1;
        while (x <= 100) {
            // Primary instance
            String endpoint1 = "hgpostcn-cn-wxxxxxxxx04-cn-hangzhou.hologres.aliyuncs.com:80";
            // Read-only secondary instance
            String endpoint2 = "hgpostcn-cn-wxxxxxxxx05-cn-hangzhou.hologres.aliyuncs.com:80";
            String dbname = "postgres";
            String jdbcUrl = "jdbc:postgresql://" + endpoint1 + "," + endpoint2 + "/" + dbname;
            Properties properties = new Properties();
            properties.setProperty("user", "xxx");
            properties.setProperty("password", "xxx");
            // any: connect to any listed endpoint regardless of instance role
            properties.setProperty("targetServerType", "any");
            // true: randomly select from listed endpoints to distribute load
            properties.setProperty("loadBalanceHosts", "true");
            // Cache reachable endpoints for 10 seconds before rechecking
            properties.setProperty("hostRecheckSeconds", "10");
            try {
                Class.forName("org.postgresql.Driver");
                Connection connection = DriverManager.getConnection(jdbcUrl, properties);
                PreparedStatement preparedStatement = connection.prepareStatement("show hg_frontend_endpoints;");
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    ResultSetMetaData rsmd = resultSet.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    Map map = new HashMap();
                    for (int i = 0; i < columnCount; i++) {
                        map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                    }
                    System.out.println(map);
                }
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            x++;
        }
    }
}

To verify the distribution, check the connection count on each instance in the monitoring dashboard. The counts should be approximately equal. See View monitoring metrics for details.

Monitoring metrics for instance hgpostcn-cn-wxxxxxxxx04.
Monitoring metrics for instance hgpostcn-cn-wxxxxxxxx05.