Connect a read replica

Point QueryDesk at a read replica and a slow or heavy query lands on the replica, not on the primary your application writes to. This guide shows where to find a replica's address on each engine QueryDesk supports.

Add the replica to QueryDesk as its own database, with its own name, such as Orders (replica). Keep the primary as a separate database if your team also needs to write. A replica that accepts no writes still keeps a write from running, but use a read-only user on it as well: some replicas accept writes, and a replica can be promoted to primary.

On QueryDesk Cloud the replica has to accept connections from 35.224.237.12, or be reached through a Tunnel in its network.


PostgreSQL

These steps also cover TimescaleDB.

  • Amazon RDS: each read replica is its own instance. In the RDS console, open the replica under Databases and copy the Endpoint from Connectivity & security.
  • Amazon Aurora: use the cluster's Reader endpoint, which contains cluster-ro-. It spreads connections across the cluster's replicas.
  • Google Cloud SQL: a read replica is its own instance with its own IP address, listed under the primary on the Instances page.
  • Azure Database for PostgreSQL: each read replica is its own server, with its own Server name on its Overview.
  • Neon: add a Read replica compute to the branch and copy its connection string. Turn Connection pooling off before you copy it.
  • Self-managed: use the host and port of a standby. CloudNativePG creates a <cluster>-ro service for its replicas, and Patroni's HAProxy template serves them on port 5001.

To confirm you reached a replica, run SELECT pg_is_in_recovery();. It returns true on a replica.

Poolers that route by role

A pooler such as PgDog can send a connection to a replica based on a parameter the connection opens with. QueryDesk sends one when you set it on the database:

  1. Connect the database at the pooler's address.
  2. On the Databases page, click the gear icon next to the database.
  3. Under Connection parameters, add the name pgdog.role with the value replica. Only a super admin can change connection parameters.
  4. Click Test Connection on a credential.

Every credential on the database opens its connections with that parameter. To pass it through PostgreSQL's options parameter instead, use the name options and the value -c pgdog.role=replica.

MySQL

These steps also cover MariaDB.

  • Amazon RDS: each read replica is its own instance. Copy its Endpoint from Connectivity & security.
  • Amazon Aurora: use the cluster's Reader endpoint, which contains cluster-ro-.
  • Google Cloud SQL: a read replica is its own instance with its own IP address.
  • Azure Database for MySQL: each read replica is its own server, with its own Server name.
  • Self-managed: use the replica's host and port.

A replica refuses writes only with read_only on, and on MySQL, admin users can still write unless super_read_only is on too. To confirm you reached a replica, run SELECT @@read_only;. It returns 1 on a replica set to refuse writes.

SQL Server

QueryDesk connects to the address you give and doesn't send ApplicationIntent=ReadOnly, so connect to the replica's own address rather than to a listener that routes by intent.

  • Always On availability groups: use the host of a readable secondary. Set its secondary role to ALLOW_CONNECTIONS = ALL: a secondary set to READ_ONLY accepts only connections that send ApplicationIntent=ReadOnly.
  • Amazon RDS for SQL Server: each read replica is its own instance. Copy its Endpoint from Connectivity & security.
  • Azure SQL Database: use a geo-replica or a Hyperscale named replica, at its own server and database name. Read scale-out on the primary's own address needs ApplicationIntent=ReadOnly.
  • Azure SQL Managed Instance: use a failover group's secondary instance, at its own host.

To confirm you reached a replica, run SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');. It returns READ_ONLY on a readable secondary.

ClickHouse

Every replica of a replicated table accepts writes, so a ClickHouse replica doesn't refuse them. Use a read-only user whichever node you connect to.

  • ClickHouse Cloud: create a second service in the same warehouse and make it read-only. It reads the same data on its own compute, and has its own endpoint under Connect.
  • Self-managed: use the host of one replica, or of a load balancer in front of the replicas you set aside for reads. Connect to a node that holds the tables, or a node with Distributed tables over them.

Oracle

  • Active Data Guard: open the physical standby read-only and connect to the standby's host, with the service name you created for read-only work as the Database name.
  • Amazon RDS for Oracle: each read replica is its own instance. Copy its Endpoint and Port from Connectivity & security.

To confirm you reached a standby, run SELECT database_role, open_mode FROM v$database; as a user that can read v$database. It returns PHYSICAL STANDBY and READ ONLY WITH APPLY.


Add the replica to QueryDesk

  1. On the Databases page, click New database.
  2. Under Database Configuration, give it a Display name that says it is a replica, such as Orders (replica), and enter the replica's host, port and database name.
  3. In the Credentials section, click + Add Credential and enter a read-only user's Username and Password.
  4. Click Test Connection under the credential.

Was this page helpful?