Skip to content

AWS Updated 2025-09-20

Read replicas in a multi-tenant SaaS — per-tenant reads, lag traps, and when to promote

Routing tenant reads to a replica scales the writer, until a dashboard reload shows a stale value and the customer opens a ticket. Here is the lag-visibility checklist, the read-your-own-writes pattern, and the threshold where promoting a replica to a dedicated tenant DB makes sense.

The SaaS has 400 tenants on a shared Postgres on RDS. The writer is at 70% CPU during business hours, mostly on read queries against the dashboard view. The proposal is to add two read replicas, route the dashboard query to them, and free the writer for actual writes. Six weeks in, a customer pages support: they invoiced a client, refreshed the invoice list, and the new invoice is missing. They refresh again ten seconds later and it appears. The application is reading from a replica that lagged by 8 seconds at that moment, and the customer assumes they lost their data. The replica strategy is correct, the implementation needs three patterns to not generate support tickets.

What’s happening

A read replica is an asynchronous copy of the writer’s data. The replication mechanism on Postgres RDS is streaming replication, on Aurora it is cluster-volume sharing with millisecond lag. The replica is fresh enough for most queries, and stale enough to be wrong for a specific class of query: the read that follows the user’s own write.

The patterns that go wrong:

  • Dashboard reload after an action. User clicks “create invoice,” the application writes to the primary, then the user navigates to the invoice list which reads from the replica. The list is sourced from a connection that has not yet seen the write. The invoice is missing.
  • List/detail mismatch. The list query reads from the replica (showing the cached state); the detail query for an item the user just edited reads from the primary (showing the new state). The list shows one number, the detail shows another, the user opens a ticket.
  • Aggregation queries with a write. A monthly report runs against the replica; the user clicks “include today’s data,” which writes a flag and re-queries the aggregation. The aggregation does not include the flag yet because replication has not caught up.

Replication lag is also non-uniform across replicas. A workload that runs a big sequential scan on one replica can lag that replica significantly while another replica stays fresh. Routing decisions that assume “any replica is fine” ignore this.

The correct mental model: a read replica is a cache. It is approximately consistent. The application has to know which reads tolerate approximate consistency and which do not. Per-tenant routing makes the problem worse, not better — if all of tenant A’s reads go to replica-1 and replica-1 is lagging, tenant A sees stale data on every page.

When read replicas are the right answer for multi-tenant SaaS:

  • The read-to-write ratio is high (most workloads — 80/20 or more).
  • The application can identify which reads are safe to serve stale (most dashboard queries, reports, list views past the first page).
  • The team is willing to mark certain endpoints as “primary-only.”

When read replicas are not enough and per-tenant database promotion makes sense:

  • A single tenant generates more than 20% of the database’s write load.
  • A tenant has data residency requirements that the shared database does not meet.
  • A tenant runs heavy analytical queries that affect the writer’s buffer cache.
  • The blast radius of a single noisy tenant is unacceptable.

How to verify

Measure the lag continuously. The metric to watch on RDS Postgres is ReplicaLag (seconds), on Aurora it is AuroraReplicaLag (milliseconds). The lag is published per-replica:

aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=prod-db-1-replica-1 \
  --start-time $(date -u -v-7d +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 \
  --statistics Maximum \
  --query 'Datapoints[].Maximum' \
  --output text | sort -n | tail -5

The maximum over a week is the number to plan against. If it is 30+ seconds, the application has to treat the replica as approximate-only. If it is consistently under 1 second on Aurora, the use cases that tolerate “near-real-time but not write-consistent” are most of them.

Inside Postgres, check the lag from the writer’s perspective:

SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
  EXTRACT(EPOCH FROM (now() - reply_time)) AS reply_lag_seconds
FROM pg_stat_replication;

replay_lag_bytes is more honest than reply_lag_seconds — a replica that has acknowledged a position but not yet replayed it shows zero reply lag and meaningful replay lag.

In the application, instrument the read-after-write window. A simple pattern: every write call increments a per-tenant version counter in the application’s cache (Redis, in-memory). Subsequent reads for that tenant check the cache, and if a recent write is pending, route to the primary. The threshold (e.g. “for the 10 seconds after a write, read from primary”) is workload-specific. Measure the actual replication lag, double it for safety, that is your read-from-primary window.

Confirm which tenant is generating the most write load:

SELECT 
  current_setting('app.tenant_id') AS tenant,
  COUNT(*) AS query_count,
  SUM(CASE WHEN query LIKE 'INSERT%' OR query LIKE 'UPDATE%' OR query LIKE 'DELETE%' THEN 1 ELSE 0 END) AS writes
FROM pg_stat_statements
JOIN pg_database d ON d.oid = pg_stat_statements.dbid
WHERE d.datname = 'production'
GROUP BY 1
ORDER BY writes DESC
LIMIT 10;

This assumes the application sets app.tenant_id via SET LOCAL per request, which it should for query attribution. If it does not, instrument it now.

The fix

The three patterns that make read replicas work in multi-tenant SaaS:

Read-your-own-writes routing. Track recent writes per tenant or per user. After a write, route that tenant’s reads to the primary for a configurable window (typically 5–30 seconds, depending on the worst-case replica lag). The simplest implementation: a Redis key tenant:{id}:last_write with a TTL equal to the routing window. Every write sets it; every read checks for it before choosing a connection.

In the application’s data access layer:

def get_db_connection(tenant_id, write=False):
    if write:
        redis.setex(f"tenant:{tenant_id}:last_write", 30, "1")
        return primary_connection()
    if redis.exists(f"tenant:{tenant_id}:last_write"):
        return primary_connection()
    return replica_connection()

This trades replica utilization for correctness. A workload where every request includes a write will route everything to primary, defeating the replica. That is the wrong design — the read path should not require a write for every request.

Explicit endpoint selection per query. Tag every database call site with its consistency requirement. Three tags:

  • strict (read from primary, never replica) — used for read-after-write paths the routing layer cannot detect, like webhook handlers.
  • recent (read from primary if within the read-your-own-writes window, else from replica) — most user-initiated reads.
  • eventual (always read from replica) — analytical queries, reports, list views past page 1, audit logs.

The tags live in the code, not in runtime configuration. A developer adding a new query has to think about which tag applies. Defaulting to recent is sane; the explicit tag prevents silent drift.

Per-replica health and lag awareness in the routing layer. Do not round-robin across all replicas. Use Aurora’s reader endpoint (which load-balances internally and is lag-aware), or on RDS Postgres maintain an application-side health check that excludes replicas with lag over a threshold. A simple pattern is a routing layer that polls each replica’s pg_last_wal_replay_lsn versus the primary’s pg_current_wal_lsn every 5 seconds and excludes anything more than 2 seconds behind.

When to promote a replica to a dedicated tenant database:

  • The tenant’s queries are larger than 10x the median tenant’s, by a sustained measure (rows scanned, IO, CPU time).
  • The tenant has a regulatory requirement that the shared database cannot satisfy (data residency, audit log isolation).
  • The tenant’s writes are the bottleneck for the writer instance.
  • The cost of an isolated database (instance + storage + backup) is less than the cost of upsizing the shared writer to handle the noisy tenant.

The promotion mechanics:

# Create a read replica of the shared writer, scoped to just this tenant.
# In practice, schemas-per-tenant lets you carve cleanly. Tenant-data-in-shared-tables
# requires an application-side rewrite to move the rows.

aws rds promote-read-replica \
  --db-instance-identifier prod-db-1-replica-tenant-XYZ \
  --backup-retention-period 7

After promotion, the replica becomes an independent writer. The application’s connection routing has to direct that tenant to the new endpoint. The replica’s residual schema (rows for other tenants that were also on the replica) gets dropped via an offline schema strip.

The pattern that does not work: trying to “shard the multi-tenant SaaS one tenant at a time” by promoting replicas. The promotion is mechanical; the application’s tenant-routing layer is the hard part. Build the tenant-routing layer first, then promotions become a tactical operation.

Three rules we hold across multi-tenant database operations:

Every write is also a routing event. The read after the write has to come from the right place. Build that into the data access layer or it will not be built at all.

Replica lag is a metric, not an assumption. A replica that was lag-free yesterday can lag for 30 minutes today during a vacuum. Alert on ReplicaLag above the read-your-own-writes window. Page on ReplicaLag above 60 seconds.

Promote tenants when the data tells you to, not when one customer complains. A single customer’s noise can be tuned without promotion. A pattern across multiple noisy customers is a signal that the shared model is reaching its limits.

The shared-replica model scales SaaS to roughly an order of magnitude past where a single writer would have collapsed, but only if the read-after-write pattern is treated as a first-class application concern, not an afterthought.

Stack Harbor designs and operates production RDS / Aurora topologies as part of Managed AWS and the ongoing Managed Cloud Ops layer. If your database posture is “Multi-AZ checkbox + hope,” start with an infrastructure consult.