This document describes how to use hot standby so that the standby coordinator serves read-only queries from reporting and BI workloads, offloading connection handling, SQL parsing and query dispatch from the master. It applies to YMatrix 6.8 and later.
Reporting, BI and analytics workloads usually open many connections but run light queries. The pressure lands on the coordinator: connections queue up, and SQL parsing plus query dispatch saturate the master CPU while the data nodes stay idle. Hot standby moves part of that pressure to the standby coordinator.
Signs that hot standby fits:
remote_apply in section 6 if there is;Cases where it does not fit:
A YMatrix cluster has two kinds of nodes with different responsibilities:
| Node | Role | Responsibility |
|---|---|---|
| master | Primary coordinator | Accepts connections, builds plans, dispatches queries |
| standby | Standby coordinator | Stays in sync with the master; accepts read-only connections once hot standby is enabled |
| primary segment | Primary data node | Stores data, executes queries |
| mirror | Mirror data node | Copy of a primary, takes over when the primary fails |
After it is enabled, connections are distributed roughly like this:
read-only apps writes, maintenance
│ │
▼ ▼
standby coordinator ←—— repl ——→ master coordinator
│ │
└─────────────┬───────────────┘
▼
primary segments, execute queries
│ │
mirror mirror
Two conclusions drive every caveat below:
mx_hot_standby_use_primary_qe. When the parameter is on, queries go to the current primary segments, so an unavailable mirror does not make the query read incomplete data. Enabling it together is recommended, see section 4.hot_standby is off by default. It only controls whether a recovery instance accepts read-only connections, and it does not change the cluster failover mechanism.
Confirm the following four items in order to avoid rework after the restart:
select content, role, hostname, port, status
from gp_segment_configuration
where content = -1;
Two rows are returned: role = p is the master, role = m is the standby. status = u means the instance is healthy. Note the standby's hostname and port; section 4.4 needs them.
Connecting to the standby at this point fails with FATAL: the database system is in recovery mode. That is the normal response while hot standby is off and does not mean the instance is broken.
gpconfig -s hot_standby
gpconfig -s mx_hot_standby_use_primary_qe
If they are already on, hot standby was enabled earlier; skip to section 4.3 to verify.
Schedule a maintenance window. hot_standby is a postmaster-level parameter with setting class restart, so the cluster must be restarted. The restart drops all connections and rolls back uncommitted transactions.
Prepare the application side. Read-only applications must be able to change the connection address and retry when a connection breaks. Without this step, the workload cannot connect after the restart.
Run on the master as the installation user, usually mxadmin:
gpconfig -c hot_standby -v on
gpconfig -c mx_hot_standby_use_primary_qe -v on
gpconfig writes the parameter to postgresql.conf on all instances: master, standby, data nodes and mirrors, so there is no need to edit each node.
The two parameters take effect differently:
hot_standby is a postmaster parameter with setting class restart and requires a restart;mx_hot_standby_use_primary_qe is a session-level parameter with setting class session; connections created after the restart pick up the new value.Note!
gpconfig -sstill reportsoffright after the set: it reads the running value, not the value in the configuration file. The value becomesonafter the restart, so do not mistake this for a failed change — the file already containshot_standby=on.
mxstop -a -r -M fast
The flags mean: -a skips the confirmation prompt, -r restarts after stopping, and -M fast interrupts and rolls back running transactions.
Note!
Without-M fast,mxstopuses smart mode by default: it refuses to stop while any connection is active. This is the most common reason a restart appears to hang.
The legacymxstop -arfform inherited from the gpstop era has no-fin the 6.8mxstop --help; use the explicit form above.
The cluster is unavailable during the restart, so run it off-peak.
Once the cluster is up, check the parameters:
gpconfig -s hot_standby
gpconfig -s mx_hot_standby_use_primary_qe
Both should report on:
Values on all segments are consistent
GUC : hot_standby
Coordinator value: on
Segment value: on
Then connect to the standby and confirm its state:
psql -h <standby host> -p <standby port> -d <database>
-- Returns t: the standby is in recovery, which is expected
select pg_is_in_recovery();
show hot_standby; -- expect on
show mx_hot_standby_use_primary_qe; -- expect on
Finally run a read-only query on the standby, for example select count(*) from public.orders; with your own table name, and confirm it returns rows. Then try a write: it must fail with cannot execute INSERT in a read-only transaction. That error is expected and proves the instance is read-only.
hostname and port, using the values from step 1 of section 3. Write and maintenance applications keep connecting to the master.pg_hba.conf on the standby host, then run:mxstop -u
-u reloads pg_hba.conf on the master, the standby and all data nodes, along with runtime parameters in postgresql.conf, without stopping the database.
SELECT on the target tables.The following three behaviours are inherent to hot standby, not faults. Knowing them in advance prevents misdiagnosis:
1. Read queries contend with maintenance for locks. When a query on the standby runs on the primaries, the usual lock rules apply. For example, while a query reads a partition holding AccessShareLock, a TRUNCATE on the same partition from the master, which needs AccessExclusiveLock, must wait for the query to finish. Conversely, maintenance can be held up by read-only queries. Schedule operations that need exclusive locks, such as TRUNCATE and VACUUM FULL, away from reporting peaks.
2. Failover interrupts queries. hot_standby does not change the failover mechanism. When a failover happens, queries running on the standby are interrupted, and after the standby is promoted to master, the read-only connections that pointed at it must be re-established. This is why section 3 requires applications to have retry logic.
3. Metadata visibility depends on replay progress. With synchronous_commit = on, if a synchronous standby is listed in synchronous_standby_names, a commit waits until the WAL reaches durable storage on those standbys, but it does not wait for replay. When that list is empty (the YMatrix default), on behaves like local: the commit only waits for the local flush and has nothing to do with the standby. That said, with the recommended mx_hot_standby_use_primary_qe = on, row data is read straight from the primary segments, so newly inserted rows are not affected by standby replay lag.
What is affected is metadata: while replay lags, the standby still has an old system catalog, so new tables and new columns are not visible there — queries fail with relation "..." does not exist until replay catches up. After running DDL, wait for replay before using the object on the standby.
| Parameter | Default | Takes effect | Behaviour and trade-off |
|---|---|---|---|
hot_standby |
off | restart | Whether a recovery instance accepts read-only connections |
mx_hot_standby_use_primary_qe |
off | session | Whether distributed queries received by the standby go to the current primary segments. Boolean; list it with gpconfig -l |
synchronous_commit |
on | session | With a synchronous standby configured: on waits until the commit WAL reaches durable storage on the standby, without waiting for replay; remote_apply waits for replay. With an empty synchronous_standby_names, on, remote_write, remote_apply and local all wait only for the local flush |
max_standby_streaming_delay |
30s | reload | Time budget by which replay may be delayed to avoid query conflicts; conflicting queries are cancelled once it is exhausted. -1 waits indefinitely |
hot_standby_feedback |
off | reload | The standby reports the old row versions its queries need, reducing query cancellations caused by cleanup on the primary, at the cost of table bloat there |
wal_receiver_status_interval |
10s | reload | Upper bound on how often feedback is sent; hot_standby_feedback feedback is no more frequent than this |
Check gpconfig -l for the valid range of each parameter.
Tuning advice: keep the defaults for a while and adjust only when the need is real:
synchronous_commit = remote_apply; the commit then waits for replay to finish. The cost is that a standby query blocking replay also blocks commits on the master. This only works if the standby is listed in synchronous_standby_names; otherwise remote_apply only waits for the local flush, exactly like on.max_standby_streaming_delay, or turn on hot_standby_feedback. The former increases replay lag; the latter increases bloat on the primary. Either way the cost moves elsewhere.max_standby_streaming_delay is not a per-query timeout, and it does not govern ordinary lock waits on the master. Do not use it as a query timeout.Set the parameters back to their defaults and restart to return to the previous state:
gpconfig -c hot_standby -v off
gpconfig -c mx_hot_standby_use_primary_qe -v off
mxstop -a -r -M fast
The standby then becomes a pure standby again and stops accepting read-only connections. Remember to point the read-only applications back at the master.
Q: Hot standby is on, but applications cannot connect to the standby.
Check in order: whether the standby status is u in gp_segment_configuration; whether pg_hba.conf on the standby host allows the client subnet and whether mxstop -u was run afterwards; whether the port is the standby's own; whether the database user exists and has privileges.
Q: Queries on the standby are slower than on the master.
First confirm mx_hot_standby_use_primary_qe is on. Reading through mirrors adds overhead and reduces availability; primary segments are the recommended path. Also note that the standby only offloads coordination, so a single large query does not become faster just because it moved there.
Q: A query on the standby fails with primary segments can only process MPP protocol messages from primary QD.
The session has mx_hot_standby_use_primary_qe turned off: the standby tries to read through mirrors, and on a cluster without mirrors that error is inevitable. Confirm the parameter is on and reconnect.
Q: The restart command does nothing and keeps waiting.
Most likely -M fast was omitted: smart mode refuses to stop while connections are active. Once the workload can be dropped, rerun mxstop -a -r -M fast.
Q: TRUNCATE or VACUUM FULL hangs during the maintenance window.
A read-only query on the standby holds a shared lock on the table on the master, and the exclusive operation must wait for it. Locate the session holding AccessShareLock with pg_stat_activity and pg_locks, and cancel it with pg_cancel_backend if needed. In the long run, schedule maintenance off-peak and set lock_timeout on the maintenance side so the operation fails instead of waiting forever.
Q: Applications report errors after a failover.
Expected. All connections to the promoted standby are gone, so applications must reconnect, and the read-only address must be updated to the new master, or the new standby.
hot_standby, hot_standby_feedback and related parameterssynchronous_commit