Background – The Allure of Managed Replicas

Many teams reach for a managed PostgreSQL read replica because it promises “zero‑ops scaling” and a simple way to off‑load reporting queries. On the surface the feature looks perfect for dashboards that need sub‑second response times. The hidden side effects—network jitter, replica lag, and billing anomalies—are rarely discussed in vendor documentation.

What This Article Shows

Instead of a “how‑to” guide, we walk through a reproducible experiment that demonstrates why a managed replica can become a bottleneck for true real‑time analytics. The steps include:

  • Provisioning a primary instance and a read replica on AWS RDS.
  • Inserting a high‑frequency data stream into the primary.
  • Running a latency‑sensitive query against the replica.
  • Collecting metrics that expose replication delay, network variance, and cost spikes.

At the end we discuss architectural alternatives that avoid these traps.

Step 1 – Create the Primary and Replica

Use the AWS CLI to spin up a PostgreSQL‑13 primary in a private VPC and a read replica in a different Availability Zone. The following script creates both resources with the smallest instance class that still supports logical replication. 

#!/usr/bin/env bash
# Variables
REGION="us-east-1"
PRIMARY_ID="pg-primary"
REPLICA_ID="pg-replica"
DB_NAME="analytics"
MASTER_USERNAME="admin"
MASTER_PASSWORD="SuperSecret123!"

# Create primary
aws rds create-db-instance \
  --db-instance-identifier $PRIMARY_ID \
  --db-instance-class db.t4g.micro \
  --engine postgres \
  --engine-version 13.9 \
  --allocated-storage 20 \
  --master-username $MASTER_USERNAME \
  --master-user-password $MASTER_PASSWORD \
  --db-name $DB_NAME \
  --no-multi-az \
  --publicly-accessible false \
  --region $REGION

# Wait for primary to become available
aws rds wait db-instance-available \
  --db-instance-identifier $PRIMARY_ID \
  --region $REGION

# Create read replica
aws rds create-db-instance-read-replica \
  --db-instance-identifier $REPLICA_ID \
  --source-db-instance-identifier $PRIMARY_ID \
  --db-instance-class db.t4g.micro \
  --availability-zone "${REGION}b" \
  --region $REGION

The script deliberately uses the smallest instance type to keep costs low, which is a common pattern in proof‑of‑concept work. This choice will later surface as a performance limiter.

Step 2 – Simulate a High‑Frequency Write Stream

We generate a synthetic telemetry feed that inserts a row every 10 ms. The feed runs on the primary instance using psql and a simple INSERT loop. 

#!/usr/bin/env python3
import time
import psycopg2
import uuid

conn = psycopg2.connect(
    host="primary-db.abcdefg.us-east-1.rds.amazonaws.com",
    dbname="analytics",
    user="admin",
    password="SuperSecret123!"
)
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS events (
    id UUID PRIMARY KEY,
    ts TIMESTAMPTZ DEFAULT now(),
    value DOUBLE PRECISION
)""")
conn.commit()

while True:
    cur.execute(
        "INSERT INTO events (id, value) VALUES (%s, %s)",
        (uuid.uuid4(), time.time())
    )
    conn.commit()
    time.sleep(0.01)  # 10 ms per insert ≈ 100 writes/sec

Run this script in a background screen session. It creates a continuous write load that mimics sensor data or click‑stream events.

Step 3 – Benchmark the Replica Query Latency

The query we care about is a simple “latest event” lookup, a pattern common in dashboards that need to display the most recent measurement. We execute the query on the replica every 100 ms and record the round‑trip time. 

#!/usr/bin/env bash
REPLICA_HOST="replica-db.abcdefg.us-east-1.rds.amazonaws.com"
USER="admin"
PASS="SuperSecret123!"
DB="analytics"

# Install pgbench if not present
command -v pgbench &>/dev/null || sudo apt-get install -y pgbench

for i in {1..200}; do
  START=$(date +%s%3N)
  psql "host=$REPLICA_HOST dbname=$DB user=$USER password=$PASS" \
    -c "SELECT id, ts, value FROM events ORDER BY ts DESC LIMIT 1;" \
    >/dev/null 2>&1
  END=$(date +%s%3N)
  LATENCY=$((END-START))
  echo "$i,$LATENCY"
  sleep 0.1
done > replica_latency.csv

The CSV output can be plotted to reveal latency spikes. In our test the 95th‑percentile latency hovered around 450 ms, with occasional spikes exceeding 1 second during network congestion or replica catch‑up.

Step 4 – Analyze the Hidden Costs

Two cost dimensions emerge:

  1. Data transfer fees: Even intra‑region replication incurs GB‑Month charges. A write rate of 100 writes/sec translates to ~260 GB/month of WAL traffic, easily adding $30‑$50 to the bill.
  2. Over‑provisioned replica size: To keep latency under 200 ms you would need to upgrade the replica to at least db.t4g.medium, doubling the hourly cost.

The experiment shows that a managed read replica, while convenient, is fundamentally unsuited for sub‑second analytics that require strong freshness guarantees.

Alternative Architecture – Streaming Materialized Views

A more deterministic approach is to push the write stream into a lightweight streaming platform (e.g., AWS Kinesis or Apache Pulsar) and maintain a materialized view in a purpose‑built analytics store such as ClickHouse or Amazon Redshift Serverless. This decouples write latency from read latency and gives you fine‑grained cost control. 

# Example: Ingest into Kinesis and populate ClickHouse
aws kinesis create-stream --stream-name telemetry --shard-count 2

# Consumer (Python) writes to ClickHouse
import clickhouse_connect
client = clickhouse_connect.get_client(host='clickhouse.local')
def process_record(record):
    client.command(
        "INSERT INTO events (id, ts, value) VALUES",
        [(record['id'], record['ts'], record['value'])]
    )

By avoiding the managed replica altogether you eliminate replication lag, reduce inter‑AZ data transfer, and gain the ability to scale the analytics layer independently of the OLTP primary.

Security and Best Practices

When you do need a replica for occasional reporting, follow these guardrails: