Read-Write Separation Cluster
Overview
In production environments with heavy read workloads, routing all queries to a single primary node can create CPU and I/O bottlenecks. A read-write separation cluster pairs an IvorySQL streaming replication topology (one primary node and one or more standby nodes) with a database middleware proxy that inspects incoming SQL requests, sends write transactions to the primary node, and distributes read-only SELECT queries across standby nodes.
Pgpool-II is a widely used open-source middleware for PostgreSQL and IvorySQL that provides connection pooling, streaming-replication awareness, automatic load balancing, and health checking without requiring application-level sharding logic.
-
Write routing:
INSERT,UPDATE,DELETE, DDL statements, and write transactions are routed to the primary node. -
Read load balancing: Read-only
SELECTstatements outside write transactions are distributed across healthy nodes according to configuredbackend_weightvalues. -
Compatibility modes: IvorySQL exposes PostgreSQL mode (default port
5432) and Oracle-compatible mode (default port1521). ConfigurePgpool-IIbackend ports consistently for the compatibility mode used by your applications.
For setting up the underlying IvorySQL primary and standby nodes, see Cluster Setup.
Deployment Steps
Prerequisites
-
Deploy a working IvorySQL streaming replication cluster (at least one primary node and one standby node). Refer to Cluster Setup for primary/standby initialization.
-
Verify replication is active on the primary node:
SELECT client_addr, state, sync_state FROM pg_stat_replication; -
Ensure network connectivity and
pg_hba.confrules on all IvorySQL nodes allow connections from thePgpool-IIhost.
Installing Pgpool-II
Install Pgpool-II on the middleware host (or a dedicated proxy node) using your system package manager or from source:
# Example on RHEL / Rocky Linux / CentOS
sudo dnf install -y pgpool-II
# Example on Debian / Ubuntu
sudo apt-get update && sudo apt-get install -y pgpool2
Configuring Backend Nodes and Load Balancing
Edit pgpool.conf (typically under /etc/pgpool-II/pgpool.conf or /usr/local/etc/pgpool.conf) to define the IvorySQL primary and standby backends, streaming replication checks, and load balancing:
# Listen settings
listen_addresses = '*'
port = 9999
# Backend mode
backend_clustering_mode = 'streaming_replication'
# Primary node (node 0)
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Standby node (node 1)
backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
# Enable read-write separation / load balancing
load_balance_mode = on
# Streaming replication delay and health checks
sr_check_period = 10
sr_check_user = 'ivorysql'
sr_check_database = 'postgres'
health_check_period = 10
health_check_user = 'ivorysql'
health_check_database = 'postgres'
|
Setting If your applications connect in IvorySQL Oracle-compatible mode, set |
Configuring Authentication and Starting Pgpool-II
-
Configure
pool_hba.confon thePgpool-IIhost to allow client connections (setenable_pool_hba = oninpgpool.conf):host all all 0.0.0.0/0 scram-sha-256 -
Register database user credentials in
pool_passwdusingpg_encsoPgpool-IIcan authenticate clients against IvorySQL:pg_enc -m -f /etc/pgpool-II/pgpool.conf -u ivorysql -p -
Start and enable
Pgpool-II:sudo systemctl enable --now pgpool
Usage Configuration
Verifying Node Roles and Routing
Connect through the Pgpool-II port (9999) using psql and inspect node status:
psql -h <pgpool_host> -p 9999 -U ivorysql -d postgres -c "SHOW pool_nodes;"
Confirm that node 0 reports role = primary and node 1 reports role = standby, both with status = up.
You can also verify routing behavior with read and write queries:
-- Read query: routed to standby (when backend_weight0 = 0)
SELECT inet_server_addr(), pg_is_in_recovery();
-- Write query: routed to primary
CREATE TABLE rw_test (id int, created_at timestamp DEFAULT now());
INSERT INTO rw_test (id) VALUES (1);
Tuning Read-Write Routing Behavior
Configure the following parameters in pgpool.conf based on your application’s consistency requirements:
-
disable_load_balance_on_write: Controls howSELECTqueries are routed after a write inside the same transaction or session.-
transaction(default): After a write, subsequent reads in the same transaction go to the primary; reads in a new transaction can return to standby nodes. -
trans_transaction/dml_adaptive/always: Keeps reads on the primary longer when strict read-your-own-writes consistency is needed.
-
-
delay_threshold: Maximum WAL replay lag (in bytes) permitted on a standby beforePgpool-IItemporarily stops routing read queries to that standby and falls back to the primary. -
write_function_list/read_only_function_list: Classify stored functions or procedures that modify data soPgpool-IIroutesSELECT my_write_func()to the primary node rather than a standby.