When to Add Postgres Read Replicas (and When Not To)

Add Postgres read replicas when your database is busy with reads that do not need the very latest data, and you have already fixed slow queries and missing indexes. A replica is a read-only copy that streams changes from the primary, so it can serve reports, dashboards, search pages and API reads while the primary handles writes. Replicas also help when users are far from the primary: a replica near them answers reads faster.

They do not help with write load, and they add a new problem: a replica can be a moment behind. This article covers how to tell if you need one, what to fix first, and how to route reads without serving stale data where it matters.

#How Postgres read replicas work

Postgres replicas usually use physical streaming replication. The primary sends its write-ahead log (WAL) to the replica, which replays it. The replica is a byte-for-byte copy of the whole cluster, accepts read-only queries, and rejects writes.

Replication is normally asynchronous. The primary commits without waiting for replicas, so a write is visible on the primary first and on the replica a little later. That delay is replication lag. On a healthy setup it is usually well under a second, but it grows when the replica is busy, the network is slow, or a big write (a migration, a bulk update) has to replay.

#Signs you need a replica

• CPU on the primary is high and mostly from select statements. Check pg_stat_statements: if the top queries by total time are reads, a replica can take them.
• Reporting or analytics queries slow down the app. A dashboard that scans millions of rows competes with user requests. Move it to a replica.
• Users far from the primary see slow reads. A replica in another region cuts round-trip time for reads.
• You want a warm copy for failover. Replicas are also the basis of high availability, though failover needs more than just a replica.

#Fix these first

Replicas cost money and add complexity. These are cheaper and often enough:

1. Find the slow queries. pg_stat_statements ordered by total_exec_time usually shows a handful of queries doing most of the work. On StitchDB, Query performance in Studio shows the same over days.
2. Add the missing index. A sequential scan on a large table in a hot path is the most common cause of read load.
3. Cache what does not change. Product catalogs, settings and public pages rarely need a database round trip per request.
4. Use connection pooling. If the problem is connection count rather than query load, a pooler fixes it. See Postgres Connection Pooling Explained.
5. Make the server bigger. More memory means more of the working set in cache. Sometimes a larger primary is cheaper than a replica.

-- the reads that cost the most time
select calls, round(total_exec_time) as total_ms, round(mean_exec_time, 1) as mean_ms,
       left(query, 100) as query
from pg_stat_statements
where query ilike 'select%'
order by total_exec_time desc
limit 10;

If after this the primary is still read-bound, add a replica.

#The hard part: stale reads

The classic replica bug: a user saves a form, the app redirects, and the next page reads from the replica, which has not caught up. The user sees old data and assumes the save failed.

Ways to deal with it:

• Read your own writes from the primary. After a write, send that user's next reads (for a few seconds, or for that request) to the primary.
• Route by type of query. Writes and anything in a transaction that writes go to the primary; dashboards, lists and search go to replicas.
• Pin critical paths. Checkout, account settings, balances: always primary.
• Check lag. Take a replica out of rotation when lag exceeds a threshold.

On a replica, you can check lag directly:

select now() - pg_last_xact_replay_timestamp() as replay_lag;

Note this value grows when the primary is idle too (no new transactions to replay), so interpret it alongside write activity.

#Routing reads

There are three common approaches:

#Read replicas on StitchDB

StitchDB offers read replicas as a paid add-on on Pro, Scale and Enterprise. Pro and Scale include room for one replica per project and Enterprise for two (see live values in Database limits). You add one from the project's Read replicas page; it appears as soon as it streams.

Price. $0.01 per replica-hour (about $7.30 for a full month), plus $0.10 per GB-month for database data above your plan's included size. It is paid from prepaid credit, by the minute. A replica starts only when your balance covers 24 hours of it and the month's usage is under your spending cap; if the balance runs out, replicas are removed and the database keeps working on the primary.

Routing through the project URL. The project URL is answered by StitchDB's own DNS, which sends each visitor to the nearest healthy server: the primary, or a replica when it is clearly closer (more than about 500 km). On a replica server, GET and HEAD requests to /rest/v1/ are answered locally; everything else, including writes, RPC POST calls, Auth, Storage and Realtime, is forwarded to the primary over a private network. A replica that falls more than 10 seconds behind is taken out of routing automatically.

A read-only endpoint. To send reads to replicas explicitly:

import { createClient } from 'stitchdb-js'

const reader = createClient('https://<project-ref>-ro.p.stitchdb.com', anonKey)
const { data } = await reader.from('products').select('*')

Read-your-writes. When a read must see the latest write, send the X-Stitch-Read: primary header:

// one request, right after a write
const { data } = await stitchdb
  .from('orders').select('*').eq('id', 42)
  .setHeader('X-Stitch-Read', 'primary')

// or a client that always reads from the primary
const writer = createClient('https://<project-ref>.p.stitchdb.com', anonKey, {
  global: { headers: { 'X-Stitch-Read': 'primary' } },
})

SQL access. Each replica also has read-only Postgres connection strings (session and transaction mode) on the Read replicas page, with its replication lag over the last hour. Point a BI tool or a reporting job there.

#Automatic replicas

On Scale and Enterprise, automatic read replicas add replicas when load is high and remove them when it drops. Load is the highest of the primary's CPU, connections against the plan limit, and the replicas' CPU, measured every minute. The defaults:

The rules only remove replicas they added; replicas you added by hand are never touched. Automatic replicas cost the same as manual ones and follow the same balance and spending cap rules. Details are in Read replicas.

#Replicas are not backups

A replica copies mistakes instantly. A drop table on the primary drops it on the replica a moment later. Keep real backups and point-in-time recovery; see Postgres Point-in-Time Recovery.

Replicas are also not high availability on their own. Failover needs health checks, promotion and connection rerouting. On StitchDB, the High availability card on the Read replicas page shows whether automatic failover is on for your project's primary server; it is turned on per server by StitchDB.

#A checklist before you add one

• You have looked at pg_stat_statements and fixed the top queries.
• Most of the load is reads.
• You know which reads can tolerate a second of lag and which cannot.
• Your app or platform can route reads, and send read-your-writes to the primary.
• You have alerts on replica lag.
• You know the monthly cost.

#FAQ

#How many replicas should I start with?

One. Measure how much load moves to it before adding more.

#Do replicas help with write-heavy workloads?

No. Every replica replays every write. For write load, optimize queries, batch writes, partition large tables or move to a bigger server.

#Can I write to a replica?

No. Streaming replicas are read-only. On StitchDB, writes that reach a replica server through the project URL are forwarded to the primary.

#What lag is normal?

Usually under a second on a healthy setup. Watch for spikes during migrations and bulk updates.

Want replicas without running them yourself? See read replica pricing on the pricing page, or start a project and add one when you need it.