Add a Read Replica to a Database

This page explains how to get a read-only replica of a managed database, how your application should use it, and what it costs.

A read replica is a second database instance that continuously copies every change from your primary database and accepts only reads. It is available for managed RDS PostgreSQL, MySQL and MariaDB on AWS, and for Cloud SQL PostgreSQL and MySQL on GCP.

When a replica is the right answer

A replica helps when read traffic competes with your application for the primary's capacity:

• reporting, analytics or BI tools running long queries
• a new internal service that only reads production data
• read-heavy application paths that can tolerate data a few seconds old

A replica does not help with:

• Write load. All writes still go to the primary.
• Too many connections. A replica has its own connection limit, but it only relieves the primary if you actually move read connections to it. See Database Refuses Connections first.
• High availability or backups. A replica is not a failover target you should rely on, and it is not a backup. Automatic failover comes from a Multi-AZ primary, and point-in-time recovery from backups — see RDS Backup Retention.
• Change data capture. Debezium needs the primary. See Debezium CDC Connector.

Request a replica

Adding a replica changes how the database itself is provisioned, so it is not an option in the SVIEW Catalog form. Raise a request in your dedicated Slack channel with:

• the database name exactly as SVIEW shows it, and its Environment
• how many replicas you need (up to three per database)
• the instance size you want for the replica, or the workload it will serve so we can size it
• whether anyone needs direct access to the replica through Teleport, in addition to your applications

We add the replica to the database's configuration and confirm when it is available. The initial copy takes longer for larger databases.

Connect your application to the replica

The replica has its own endpoint, separate from the primary. Once it exists, its hostname is published with the rest of the database's connection details, as replica_endpoint_1 (then replica_endpoint_2 and replica_endpoint_3 for further replicas).

A replica copies everything on the primary, including its database users, so the same usernames and passwords work on the replica. You add one more key to the database dependency block you already use for the primary — see Attach a Database to Your Application:

dependency:
  db:
    type: rds-postgres
    instance: <db-name>
    namespace: <namespace>
    user: app_user
    keys:
      # keep the username, password and host keys you already have
      - secretKey: replica_endpoint_1
        envKey: DB_READ_HOST

For the full list of keys and how the block works, see Inject Database Credentials.

Your application then opens two connection pools: one to DB_HOST for writes and reads that must be current, and one to DB_READ_HOST for everything else. The platform does not split traffic for you — your code or ORM decides which pool each query uses.

Replication lag and read-your-writes

Replication is asynchronous. A row written to the primary appears on the replica shortly afterwards, usually within a second, but longer during heavy write bursts, large migrations or maintenance.

• Read your own writes from the primary. If a user saves something and the next page reloads it, read it from the primary, or the user may see the old value.
• Keep transactions on one connection. Do not read from the replica inside a transaction that writes to the primary.
• Writes on the replica fail. Any INSERT, UPDATE, DDL or migration against the replica is rejected with a read-only error. Point migrations at the primary.

To see the current lag, connect to the replica and run:

-- PostgreSQL: seconds since the last replayed change
SELECT EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) AS lag_seconds;

-- MySQL and MariaDB: see Seconds_Behind_Master (or Seconds_Behind_Source)
SHOW REPLICA STATUS\G

On MySQL and MariaDB versions older than 8.0.22, use SHOW SLAVE STATUS\G. On a quiet PostgreSQL primary the value keeps rising even though nothing is behind, because there is no new change to replay.

Cost

A replica is a full database instance and is billed as one: its instance size, its own storage, and its share of backups and monitoring. Two replicas of the same size as the primary roughly triple the instance cost of that database. Sizing the replica for its actual workload, rather than matching the primary, is the main way to keep this down.

Things to know before you add one

• Engine upgrades. Replicas must be upgraded before the primary. See RDS MySQL Version Upgrade.
• Scheduled suspension. Neither AWS RDS nor GCP Cloud SQL will stop an instance that has a read replica, so a suspension schedule cannot stop that database. In a non-production Environment, keep the database running or remove the replica. See Suspending Non-Production Environments and Databases.
• Non-production Environments. A replica in a dev or QA Environment is only worth its cost if you are testing the read and write split itself. Otherwise, point the read pool at the primary there.

Related articles

• Data Services — provisioning databases and finding connection details
• Attach a Database to Your Application — the dependency block
• Database Refuses Connections With "Too Many Connections" or "Too Many Clients" — connection limits and pool sizing
• Database User Privileges — read-only users on the primary
• RDS MySQL Version Upgrade — upgrading replicas first
• Suspending Non-Production Environments and Databases