Read-Only Query Offloading

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.

1 When to Use

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:

  • Reporting, BI and ad-hoc read-only workloads exhaust connections on the master while CPU and IO on the primary segments stay low;
  • The master is clearly busier than the data nodes during peak hours, and queries queue during dispatch;
  • Writes and maintenance are infrequent, and there is no hard requirement to read a commit on the standby immediately; see remote_apply in section 6 if there is;
  • The cluster already has a standby coordinator. If not, add one with mxinitstandby.

Cases where it does not fit:

  • A single large query is slow — that is an execution problem, and moving it to the standby will not make it faster;
  • Read-only traffic is small and the master is far from connection saturation; enabling it only adds configuration to maintain;
  • The application cannot change its connection string and has no retry logic — a failover interrupts queries on the standby, see section 5.

2 How It Works

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:

  1. The standby only offloads coordination; it holds no business data shards. What it saves is connections, parsing and dispatch. Query execution still happens on the data nodes, so primary segment load rises while master load falls.
  2. Where a distributed query received by the standby runs is controlled by 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.

3 Before You Start

Confirm the following four items in order to avoid rework after the restart:

  1. Confirm a standby exists. Run on the master:
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.

  1. Check the current parameter values.
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.

  1. 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.

  2. 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.

4 Enabling Hot Standby

4.1 Set the Parameters

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 -s still reports off right after the set: it reads the running value, not the value in the configuration file. The value becomes on after the restart, so do not mistake this for a failed change — the file already contains hot_standby=on.

4.2 Restart the Cluster

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, mxstop uses 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 legacy mxstop -arf form inherited from the gpstop era has no -f in the 6.8 mxstop --help; use the explicit form above.

The cluster is unavailable during the restart, so run it off-peak.

4.3 Verify the Change

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.

4.4 Point Read-Only Applications at the Standby

  • Change the connection address to the standby's hostname and port, using the values from step 1 of section 3. Write and maintenance applications keep connecting to the master.
  • Allow the client network. Add a rule for the client subnet to 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.

  • Confirm privileges. Users and roles are replicated to the standby, but the read-only user still needs SELECT on the target tables.

5 What to Expect After Enabling

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.

6 Related Parameters

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:

  • If the business requires that a commit returned by the master be visible to a new transaction on the standby immediately, set 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.
  • If the log shows replay-conflict cancellations of standby queries, first raise 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.

7 Disable and Roll Back

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.

8 FAQ

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.

See Also