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>-roservice 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:
- Connect the database at the pooler's address.
- On the Databases page, click the gear icon next to the database.
- Under Connection parameters, add the name
pgdog.rolewith the valuereplica. Only a super admin can change connection parameters. - 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 toREAD_ONLYaccepts only connections that sendApplicationIntent=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
Distributedtables 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
- On the Databases page, click New database.
- 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. - In the Credentials section, click + Add Credential and enter a read-only user's Username and Password.
- Click Test Connection under the credential.