Reads, writes, replicas and sharding: scaling a database past one server.

O problema e a escadaThe problem and the ladder

Um banco em um servidor só cresce até o limite da máquina: CPU, memória, disco, conexões. Depois disso há duas perguntas diferentes, e elas têm respostas diferentes. Leitura demais? Réplicas copiam os dados para outras máquinas que só respondem consultas. Escrita demais, ou dados demais para um disco? Só dividir os dados entre servidores (sharding) resolve, porque toda escrita de uma linha ainda passa por um único primário.A database on one server grows until the machine's limit: CPU, memory, disk, connections. Past that there are two different questions with different answers. Too many reads? Replicas copy the data to other machines that only answer queries. Too many writes, or too much data for one disk? Only splitting the data across servers (sharding) solves it, because every write to a row still goes through a single primary.

1. Primário, WAL e réplicas1. Primary, WAL and replicas

Todo banco relacional grava as mudanças primeiro em um log (no Postgres, o WAL, write-ahead log) e só depois nos arquivos de dados. A streaming replication do Postgres aproveita isso: o primário envia o WAL a um standby assim que ele é gerado, sem esperar o segmento de 16 MB encher, e o standby reaplica as mudanças. Em modo assíncrono ele costuma ficar menos de um segundo atrás.Every relational database writes changes first to a log (in Postgres, the WAL, write-ahead log) and only then to the data files. Postgres streaming replication builds on that: the primary sends the WAL to a standby as soon as it is generated, without waiting for the 16 MB segment to fill, and the standby replays the changes. In asynchronous mode it is usually under a second behind.

Montar na mão (Postgres)Setting it up by hand (Postgres)

É o que o laboratório da seção 6 faz, e é o que o RDS faz por você. No primário, um usuário de replicação, uma linha no pg_hba.conf e max_wal_senders suficiente. No standby, um backup base, um arquivo standby.signal vazio e o primary_conninfo. A opção -R do pg_basebackup grava os dois últimos.This is what the section 6 lab does, and what RDS does for you. On the primary, a replication user, a line in pg_hba.conf and enough max_wal_senders. On the standby, a base backup, an empty standby.signal file and primary_conninfo. The -R option of pg_basebackup writes the last two.

# primário: pg_hba.conf
host  replication  replicator  192.168.1.100/32  scram-sha-256

# standby: backup base + standby.signal + primary_conninfo (e um slot de replicação)
pg_basebackup -h 192.168.1.50 -U replicator -D /var/lib/postgresql/data \
  -R -X stream -C -S replica1

# monitorar, no primário
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

# promover o standby a primário (failover)
SELECT pg_promote();

Assíncrona ou síncronaAsynchronous or synchronous

O padrão é assíncrono: o COMMIT não espera o standby, então uma queda do primário pode perder as últimas transações confirmadas. No modo síncrono (synchronous_standby_names não vazio) o commit espera, e synchronous_commit escolhe até onde:The default is asynchronous: COMMIT does not wait for the standby, so a primary crash can lose the last committed transactions. In synchronous mode (synchronous_standby_names non-empty) the commit waits, and synchronous_commit chooses how far:

O synchronous_commit pode ser definido por usuário, banco ou transação, então dá para pagar a latência só nas escritas que importam. Também há quórum: ANY 2 (s1, s2, s3) espera dois quaisquer.synchronous_commit can be set per user, database or transaction, so you can pay the latency only on the writes that matter. There is also quorum: ANY 2 (s1, s2, s3) waits for any two.

O que a réplica aceitaWhat a replica accepts

Uma réplica que aceita consultas se chama hot standby. Ela é somente leitura de verdade: transaction_read_only é sempre verdadeiro e não dá para mudar. Confirmei no laboratório:A replica that accepts queries is called a hot standby. It is truly read-only: transaction_read_only is always true and cannot be changed. I confirmed it in the lab:

$ psql -h replica -c "INSERT INTO orders(tenant_id,customer,total) VALUES ('x','y',1)"
ERROR:  cannot execute INSERT in a read-only transaction

Consequência de design: uma transação que lê para depois escrever, ou que usa FOR UPDATE, tem que ir inteira ao primário. Só roteie para a réplica o que é leitura de ponta a ponta.Design consequence: a transaction that reads and then writes, or that uses FOR UPDATE, must go entirely to the primary. Only route to the replica what is read-only end to end.

2. Lidar com réplicas: lag, consistência, conflitos2. Living with replicas: lag, consistency, conflicts

Adicionar a réplica é a parte fácil. O difícil é que ela sempre está um pouco no passado, e o app precisa ser desenhado para isso.Adding the replica is the easy part. The hard part is that it is always a little in the past, and the app must be designed for that.

Medir o lagMeasuring lag

• Em bytes, no primário: pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) em pg_stat_replication. Se a distância até sent_lsn é grande, o primário está carregado; entre sent e replay, é rede ou réplica lenta.In bytes, on the primary: pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) from pg_stat_replication. A large gap up to sent_lsn means a loaded primary; between sent and replay, network or a slow replica.
• Em tempo, na réplica: now() - pg_last_xact_replay_timestamp(). Cuidado: num primário parado esse valor cresce mesmo com a réplica em dia. É o mesmo cálculo da métrica ReplicaLag do RDS, que por isso pode mostrar até 5 minutos de lag sem transações.In time, on the replica: now() - pg_last_xact_replay_timestamp(). Careful: on an idle primary this value grows even with the replica caught up. It is the same calculation as RDS's ReplicaLag metric, which is why it can show up to 5 minutes of lag with no transactions.

As causas típicas de lag, segundo a AWS: escrita muito concorrente ou lotes grandes no primário; consultas pesadas ou lentas na própria réplica, que disputam recurso com a reaplicação; e transações enormes ou DDL, porque a ordem dos commits é preservada.Typical lag causes, per AWS: highly concurrent writes or big batches on the primary; heavy or slow queries on the replica itself, competing with replay; and huge transactions or DDL, because commit order is preserved.

Consistência: o que o usuário percebeConsistency: what the user notices

Com réplica assíncrona, três anomalias aparecem para o usuário, e todas têm a mesma raiz:With an asynchronous replica, three anomalies reach the user, all with the same root:

As estratégias, da mais simples à mais precisa:The strategies, from simplest to most precise:

Conflitos: a réplica cancela consultasConflicts: the replica cancels queries

O primário não enxerga as consultas da réplica. Quando a reaplicação do WAL entra em conflito com uma consulta que está rodando na réplica (por exemplo, o VACUUM do primário removeu linhas que a consulta ainda via, ou um DROP TABLE pegou um lock exclusivo), a réplica espera ou cancela a consulta. Os controles:The primary cannot see the replica's queries. When WAL replay conflicts with a query running on the replica (for example, the primary's VACUUM removed rows the query still saw, or a DROP TABLE took an exclusive lock), the replica waits or cancels the query. The controls:

Duas regras práticas: separe a réplica de relatório da réplica de failover, porque elas querem configurações opostas; e trate o cancelamento como erro normal, com nova tentativa em outra transação. A visão pg_stat_database_conflicts, na réplica, mostra os cancelamentos por motivo.Two practical rules: keep the reporting replica separate from the failover replica, because they want opposite settings; and treat cancellation as a normal error, with a retry in a new transaction. The pg_stat_database_conflicts view, on the replica, shows cancellations by reason.

Rotear no app, no pool ou no DNSRouting in the app, the pool or DNS

3. Na AWS: o que existe e quando usar3. On AWS: what exists and when to use it

A AWS tem várias formas de ter “mais de uma cópia”, e elas resolvem problemas diferentes. A confusão mais comum é tratar Multi-AZ (disponibilidade) como réplica de leitura (escala de leitura). A documentação separa as duas.AWS has several ways to have “more than one copy”, and they solve different problems. The most common confusion is treating Multi-AZ (availability) as a read replica (read scaling). The documentation keeps them separate.

Uma topologia típicaA typical topology

• Endpoints do Aurora: o cluster endpoint sempre aponta para o writer (muda sozinho no failover); o reader endpoint balanceia conexões entre os readers (não balanceia consultas: para espalhar cada consulta, abra uma conexão por consulta); o custom endpoint agrupa um subconjunto de instâncias (por exemplo, readers maiores para relatórios); o instance endpoint serve para diagnóstico. Se o cluster só tem o writer, o reader endpoint aponta para ele.Aurora endpoints: the cluster endpoint always points to the writer (it changes by itself on failover); the reader endpoint balances connections among the readers (it does not balance queries: to spread each query, open a connection per query); the custom endpoint groups a subset of instances (for example, larger readers for reports); the instance endpoint is for diagnosis. If the cluster has only the writer, the reader endpoint points to it.
• RDS Proxy: mantém um pool de conexões e as multiplexa (todas as operações de uma transação usam uma conexão do banco), o que protege o banco de rajadas, e reconecta a um standby no failover preservando as conexões do app. Para Aurora e Multi-AZ DB cluster você pode criar endpoints somente leitura (TargetRole = READ_ONLY), que reagem a um reader que cai sem esperar o cache de DNS. Já para uma instância RDS com réplicas, o proxy só se associa ao writer, não a uma réplica de leitura.RDS Proxy: keeps a connection pool and multiplexes it (all operations of a transaction use one database connection), which protects the database from bursts, and reconnects to a standby on failover while preserving app connections. For Aurora and Multi-AZ DB clusters you can create read-only endpoints (TargetRole = READ_ONLY), which react to a reader dying without waiting for the DNS cache. For an RDS instance with replicas, however, the proxy only associates with the writer, not a read replica.
• Sem autoscaling de réplica no RDS: o RDS não adiciona nem remove réplicas conforme a demanda; você cria e apaga à mão. Se apagar o primário sem apagar as réplicas na mesma Região, cada réplica é promovida a instância independente.No replica autoscaling on RDS: RDS does not add or remove replicas as demand changes; you create and delete them by hand. If you delete the primary without deleting its replicas in the same Region, each replica is promoted to a standalone instance.
• Lag e failover: no Multi-AZ DB cluster com PostgreSQL, o tempo de failover depende do menor lag entre os dois readers, porque o escolhido precisa aplicar o que falta antes de virar writer. Por isso a AWS recomenda alarmar o lag.Lag and failover: in a Multi-AZ DB cluster with PostgreSQL, failover time depends on the lowest lag of the two readers, because the chosen one must apply what is missing before becoming writer. That is why AWS recommends alarming on lag.

Como fazer (AWS CLI)How to do it (AWS CLI)

# 1. réplica de leitura de uma instância RDS (mesma Região)
aws rds create-db-instance-read-replica \
  --db-instance-identifier shop-replica-1 \
  --source-db-instance-identifier shop \
  --availability-zone us-east-1b

# promover a réplica a instância independente (irreversível)
aws rds promote-read-replica --db-instance-identifier shop-replica-1

# 2. Multi-AZ DB cluster (writer + 2 readers em 3 AZs)
aws rds create-db-cluster \
  --db-cluster-identifier shop-cluster --engine postgres \
  --master-username postgres --manage-master-user-password \
  --db-subnet-group-name private-3az --backup-retention-period 1 \
  --allocated-storage 400 --storage-type io1 --iops 3000 \
  --db-cluster-instance-class db.m5d.xlarge

# 3. Aurora PostgreSQL: o cluster e depois uma instância por papel
aws rds create-db-cluster --db-cluster-identifier shop-aurora \
  --engine aurora-postgresql --master-username postgres --manage-master-user-password \
  --db-subnet-group-name private-3az
aws rds create-db-instance --db-instance-identifier shop-aurora-writer \
  --db-cluster-identifier shop-aurora --engine aurora-postgresql --db-instance-class db.r6g.large
aws rds create-db-instance --db-instance-identifier shop-aurora-reader-1 \
  --db-cluster-identifier shop-aurora --engine aurora-postgresql --db-instance-class db.r6g.large

# onde estão os endpoints do cluster
aws rds describe-db-clusters --query '*[].{Writer:Endpoint,Reader:ReaderEndpoint}'

# 4. alarme de lag em instância RDS (métrica ReplicaLag, em segundos)
aws cloudwatch put-metric-alarm --alarm-name shop-replica-lag \
  --namespace AWS/RDS --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=shop-replica-1 \
  --statistic Maximum --period 60 --evaluation-periods 5 \
  --threshold 30 --comparison-operator GreaterThanThreshold \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

4. Sharding: dividir os dados entre servidores4. Sharding: splitting data across servers

No sharding cada servidor (shard) guarda um pedaço das linhas, e cada shard é o primário dos seus dados, com as suas próprias réplicas. A escrita escala porque deixa de passar por um único nó. O preço é que o banco deixa de ser “um banco”: o que era trivial (um join, uma transação, um COUNT) vira problema distribuído.In sharding each server (shard) holds a slice of the rows, and each shard is the primary of its own data, with its own replicas. Writes scale because they no longer go through a single node. The price is that the database stops being “one database”: what was trivial (a join, a transaction, a COUNT) becomes a distributed problem.

Particionar não é fazer shardPartitioning is not sharding

O Postgres tem particionamento declarativo (range, list e hash), que divide uma tabela em pedaços dentro do mesmo servidor. Ele ajuda com poda de partições (partition pruning), com manutenção (apagar uma partição antiga é quase instantâneo) e com localidade de cache, mas CPU, memória e disco continuam sendo de uma máquina só. A documentação sugere considerar particionar quando a tabela passa da memória do servidor. Sharding é o passo seguinte: os pedaços ficam em máquinas diferentes.Postgres has declarative partitioning (range, list and hash), which splits a table into pieces inside the same server. It helps with partition pruning, with maintenance (dropping an old partition is nearly instant) and with cache locality, but CPU, memory and disk still belong to one machine. The docs suggest considering partitioning when the table exceeds the server's memory. Sharding is the next step: the pieces live on different machines.

CREATE TABLE measurement (
    city_id int NOT NULL, logdate date NOT NULL, peaktemp int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2026m10 PARTITION OF measurement
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- Hash: espalha por uma chave (útil quando não há um eixo natural de tempo)
CREATE TABLE m_p0 PARTITION OF measurement FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Duas restrições que importam: uma chave primária ou UNIQUE numa tabela particionada precisa incluir todas as colunas da chave de partição, e partições demais aumentam o tempo de planejamento.Two restrictions that matter: a primary key or UNIQUE on a partitioned table must include all the partition key columns, and too many partitions increase planning time.

Escolher a chave de shardChoosing the shard key

É a decisão mais difícil de desfazer. Uma boa chave: (1) aparece em quase toda consulta, para que ela caia em um shard só; (2) distribui bem, sem shards quentes; (3) mantém junto o que é consultado junto. Em SaaS multi-tenant, tenant_id costuma ser a escolha natural.It is the hardest decision to undo. A good key: (1) appears in almost every query, so it lands on a single shard; (2) distributes evenly, with no hot shards; (3) keeps together what is queried together. In multi-tenant SaaS, tenant_id is usually the natural choice.

O truque do desenho acima é o que torna o resharding viável: shards lógicos (buckets) fixos, shards físicos variáveis. A chave nunca muda de bucket; para crescer, você move alguns buckets de um servidor para outro e atualiza o mapa. Sem isso, hash % N com N novo obriga a mover quase todas as linhas.The trick in the drawing above is what makes resharding viable: fixed logical shards (buckets), variable physical shards. A key never changes bucket; to grow, you move some buckets from one server to another and update the map. Without it, hash % N with a new N forces moving almost every row.

O que quebraWhat breaks

Três jeitos de fazerThree ways to do it

Em todos, a lição é a mesma da escolha da chave: o que for consultado e transacionado junto precisa ter a mesma chave de shard.In all of them the lesson is the same as for key choice: what is queried and transacted together must share the same shard key.

5. Uma requisição ponta a ponta5. One request, end to end

O exemplo: um endpoint POST /orders cria um pedido e o cliente imediatamente faz GET /orders/{id}. É o caso em que a leitura na réplica mais dói. O desenho abaixo é o que o laboratório da seção 6 implementa.The example: a POST /orders endpoint creates an order and the client immediately does GET /orders/{id}. It is the case where reading from the replica hurts most. The drawing below is what the section 6 lab implements.

1. POST /orders. O router sabe que isto é escrita e abre conexão com o primário.POST /orders. The router knows this is a write and opens a connection to the primary.
2. INSERT ... RETURNING id. O primário grava no WAL e confirma o commit. Assíncrono: não espera a réplica.INSERT ... RETURNING id. The primary writes to the WAL and acknowledges the commit. Asynchronous: it does not wait for the replica.
3. Token. Logo depois do commit, a API lê pg_current_wal_lsn(). Como foi lido depois, é maior ou igual à posição do commit. A resposta carrega o id e o cabeçalho X-Min-LSN.Token. Right after the commit, the API reads pg_current_wal_lsn(). Since it was read after, it is greater than or equal to the commit's position. The response carries the id and the X-Min-LSN header.
4. Replicação. Em paralelo, o walsender envia o WAL; a réplica o grava e reaplica. Milissegundos, mas não zero.Replication. In parallel, the walsender ships the WAL; the replica writes and replays it. Milliseconds, but not zero.
5. GET /orders/{id} com X-Min-LSN. O router quer usar a réplica, para poupar o primário.GET /orders/{id} with X-Min-LSN. The router wants to use the replica, to spare the primary.
6. Checagem. Na réplica, SELECT pg_last_wal_replay_lsn() >= token. Se verdadeiro, a réplica já enxerga o pedido e responde (served_by: replica). Se falso, ou se a réplica estiver fora do ar, a API lê do primário (served_by: primary (...)). A posição de reaplicação só avança, então a checagem não tem condição de corrida.Check. On the replica, SELECT pg_last_wal_replay_lsn() >= token. If true, the replica already sees the order and answers (served_by: replica). If false, or if the replica is down, the API reads from the primary (served_by: primary (...)). The replay position only advances, so the check has no race condition.

6. Laboratório em Docker: primário, réplica, API e shards6. Docker lab: primary, replica, API and shards

O laboratório sobe um Postgres 18 primário, uma réplica de verdade (feita com pg_basebackup -R, em streaming replication), uma API em Python que separa leitura e escrita com o token da seção 5 e, num perfil à parte, dois shards do roteamento por buckets da seção 4. Só a API tem dependência externa (psycopg).The lab starts a primary Postgres 18, a real replica (made with pg_basebackup -R, in streaming replication), a Python API that splits reads and writes using the section 5 token and, under a separate profile, two shards with the section 4 bucket routing. Only the API has an external dependency (psycopg).

EstruturaLayout

db-lab/
├── compose.yaml
├── replica.sh              # pg_basebackup -R e sobe o standby
├── initdb/
│   ├── 00-replication.sh   # usuário de replicação + pg_hba
│   └── 10-schema.sql       # tabela orders
├── app/
│   ├── Dockerfile
│   └── app.py              # API: split leitura/escrita + sharding
└── demo.sh                 # o cenário ponta a ponta

compose.yamlcompose.yaml

x-pg: &pg
  image: postgres:18.6-alpine
  environment:
    POSTGRES_USER: app
    POSTGRES_PASSWORD: secret
    POSTGRES_DB: shop

services:
  primary:
    <<: *pg
    command: ["postgres", "-c", "wal_level=replica", "-c", "max_wal_senders=5", "-c", "max_slot_wal_keep_size=256MB"]
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d shop"]
      interval: 2s
      retries: 30

  replica:
    <<: *pg
    user: postgres
    entrypoint: ["/replica.sh"]
    volumes:
      - ./replica.sh:/replica.sh:ro
      - replica-data:/var/lib/postgresql
    depends_on:
      primary: { condition: service_healthy }

  api:
    build: ./app
    environment:
      PRIMARY_DSN: postgresql://app:secret@primary/shop
      REPLICA_DSN: postgresql://app:secret@replica/shop
      SHARD_DSNS: postgresql://app:secret@shard0/shop,postgresql://app:secret@shard1/shop
    ports: ["8080:8080"]
    depends_on: [replica]

  shard0:
    <<: *pg
    profiles: [shard]
    volumes: ["./initdb/10-schema.sql:/docker-entrypoint-initdb.d/10-schema.sql:ro"]
  shard1:
    <<: *pg
    profiles: [shard]
    volumes: ["./initdb/10-schema.sql:/docker-entrypoint-initdb.d/10-schema.sql:ro"]

volumes:
  replica-data:

initdb/00-replication.shinitdb/00-replication.sh

#!/bin/sh
set -e
psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
  -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replpass'"
echo "host replication replicator all scram-sha-256" >> "$PGDATA/pg_hba.conf"

initdb/10-schema.sqlinitdb/10-schema.sql

CREATE TABLE orders (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id  text        NOT NULL,
    customer   text        NOT NULL,
    total      numeric(10,2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_tenant_idx ON orders (tenant_id, created_at DESC);

replica.shreplica.sh

#!/bin/sh
set -e
if [ ! -s "$PGDATA/PG_VERSION" ]; then
  PGPASSWORD=replpass pg_basebackup -h primary -U replicator -D "$PGDATA" \
    -R -X stream -C -S replica1
  chmod 700 "$PGDATA"
fi
exec postgres -c hot_standby=on -c hot_standby_feedback=on

app/Dockerfileapp/Dockerfile

FROM python:3.12-slim
RUN pip install --no-cache-dir "psycopg[binary]==3.2.*"
WORKDIR /app
COPY app.py .
USER 10001
CMD ["python", "-u", "app.py"]

A API inteira cabe em um arquivo. Leia create_order e read_order (token e fallback) e depois bucket_of e shard_of (sharding):The whole API fits in one file. Read create_order and read_order (token and fallback) and then bucket_of and shard_of (sharding):

app/app.pyapp/app.py

import hashlib
import json
import os
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer

import psycopg

PRIMARY = os.environ["PRIMARY_DSN"]
REPLICA = os.environ["REPLICA_DSN"]
SHARDS = os.environ["SHARD_DSNS"].split(",")
BUCKETS = 64  # logical shards: fixed forever; only the bucket -> server map changes


def connect(dsn):
    return psycopg.connect(dsn, autocommit=True, connect_timeout=2)


# ---- read/write split with read-your-writes --------------------------------
def create_order(tenant, customer, total):
    with connect(PRIMARY) as db:
        order_id = db.execute(
            "INSERT INTO orders (tenant_id, customer, total) VALUES (%s, %s, %s) RETURNING id",
            (tenant, customer, total),
        ).fetchone()[0]
        # taken AFTER the commit, so it is >= the commit's position in the WAL
        lsn = db.execute("SELECT pg_current_wal_lsn()::text").fetchone()[0]
    return {"id": str(order_id), "min_lsn": lsn, "served_by": "primary"}


def read_order(order_id, min_lsn):
    reason = "no token"
    try:
        with connect(REPLICA) as db:
            fresh = min_lsn is None or db.execute(
                "SELECT pg_last_wal_replay_lsn() >= %s::pg_lsn", (min_lsn,)
            ).fetchone()[0]
            if fresh:
                row = db.execute(
                    "SELECT id, customer, total FROM orders WHERE id = %s", (order_id,)
                ).fetchone()
                return row, "replica"
            reason = "replica behind the token"
    except psycopg.OperationalError:
        reason = "replica unreachable"
    with connect(PRIMARY) as db:
        row = db.execute("SELECT id, customer, total FROM orders WHERE id = %s", (order_id,)).fetchone()
    return row, f"primary ({reason})"


def lag():
    with connect(PRIMARY) as db:
        return db.execute(
            "SELECT application_name, state, sync_state, "
            "pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)::bigint AS replay_lag_bytes "
            "FROM pg_stat_replication"
        ).fetchall()


# ---- sharding by tenant -----------------------------------------------------
def bucket_of(tenant):
    return int.from_bytes(hashlib.sha256(tenant.encode()).digest()[:8], "big") % BUCKETS


def shard_of(tenant):
    return bucket_of(tenant) * len(SHARDS) // BUCKETS  # buckets 0..31 -> shard 0, 32..63 -> shard 1


def shard_create(tenant, customer, total):
    with connect(SHARDS[shard_of(tenant)]) as db:
        order_id = db.execute(
            "INSERT INTO orders (tenant_id, customer, total) VALUES (%s, %s, %s) RETURNING id",
            (tenant, customer, total),
        ).fetchone()[0]
    return {"id": str(order_id), "tenant": tenant, "bucket": bucket_of(tenant), "shard": shard_of(tenant)}


def shard_list(tenant):
    with connect(SHARDS[shard_of(tenant)]) as db:  # single-shard query: tenant_id is in the WHERE
        rows = db.execute(
            "SELECT customer, total FROM orders WHERE tenant_id = %s ORDER BY created_at DESC", (tenant,)
        ).fetchall()
    return {"tenant": tenant, "shard": shard_of(tenant), "orders": [[c, str(t)] for c, t in rows]}


def shard_stats():
    out = []  # scatter-gather: touches every shard
    for i, dsn in enumerate(SHARDS):
        with connect(dsn) as db:
            n, s = db.execute("SELECT count(*), coalesce(sum(total), 0) FROM orders").fetchone()
        out.append({"shard": i, "orders": n, "revenue": str(s)})
    return {"shards": out, "total_orders": sum(x["orders"] for x in out)}


class Handler(BaseHTTPRequestHandler):
    def reply(self, status, body, headers=()):
        data = json.dumps(body).encode()
        self.send_response(status)
        self.send_header("Content-Type", "application/json")
        for k, v in headers:
            self.send_header(k, v)
        self.end_headers()
        self.wfile.write(data)

    def body(self):
        return json.loads(self.rfile.read(int(self.headers.get("Content-Length", 0))) or "{}")

    def do_POST(self):
        parts = self.path.strip("/").split("/")
        data = self.body()
        if parts == ["orders"]:
            out = create_order(data.get("tenant", "acme"), data["customer"], data["total"])
            return self.reply(201, out, [("X-Min-LSN", out["min_lsn"])])
        if len(parts) == 3 and parts[0] == "t" and parts[2] == "orders":
            return self.reply(201, shard_create(parts[1], data["customer"], data["total"]))
        self.reply(404, {"error": "not found"})

    def do_GET(self):
        parts = self.path.strip("/").split("/")
        if len(parts) == 2 and parts[0] == "orders":
            row, served_by = read_order(parts[1], self.headers.get("X-Min-LSN"))
            if row is None:
                return self.reply(404, {"error": "order not found", "served_by": served_by})
            return self.reply(200, {"id": str(row[0]), "customer": row[1], "total": str(row[2]), "served_by": served_by})
        if parts == ["lag"]:
            return self.reply(200, [dict(zip(("replica", "state", "sync", "replay_lag_bytes"), r)) for r in lag()])
        if len(parts) == 3 and parts[0] == "t" and parts[2] == "orders":
            return self.reply(200, shard_list(parts[1]))
        if parts == ["stats"]:
            return self.reply(200, shard_stats())
        self.reply(404, {"error": "not found"})


ThreadingHTTPServer(("0.0.0.0", 8080), Handler).serve_forever()

Cenário 1: réplica saudável, atrasada e fora do arScenario 1: replica healthy, lagging and down

docker compose up -d --build primary replica api
docker compose exec primary psql -U app -d shop -c "select application_name,state,sync_state from pg_stat_replication"
#  application_name |   state   | sync_state
#  walreceiver      | streaming | async
./demo.sh

demo.shdemo.sh

#!/bin/sh
# end-to-end: one write and its reads, with the replica healthy, lagging and down
api=http://localhost:8080
sql() { docker compose exec -T replica psql -U app -d shop -tAc "$1"; }
post() { curl -s -D /tmp/h -X POST $api/orders -d "{\"customer\":\"$1\",\"total\":$2}"; }

echo "== 1. write goes to the primary and returns a token (WAL position)"
out=$(post ana 49.90); echo "$out"
id=$(echo "$out" | sed 's/.*"id": "\([^"]*\)".*/\1/'); lsn=$(grep -i x-min-lsn /tmp/h | tr -d '\r' | cut -d' ' -f2)
sleep 1
echo "== 2. read with the token: replica has already replayed it"
curl -s -H "X-Min-LSN: $lsn" $api/orders/$id; echo

echo "== 3. freeze replay on the replica (simulates lag)"
sql "SELECT pg_wal_replay_pause()" >/dev/null
out=$(post bruno 120.00); echo "$out"
id=$(echo "$out" | sed 's/.*"id": "\([^"]*\)".*/\1/'); lsn=$(grep -i x-min-lsn /tmp/h | tr -d '\r' | cut -d' ' -f2)
echo "-- read WITHOUT token (naive split): stale read"
curl -s $api/orders/$id; echo
echo "-- read WITH token: falls back to the primary"
curl -s -H "X-Min-LSN: $lsn" $api/orders/$id; echo
echo "-- lag as seen by the primary"
curl -s $api/lag; echo

echo "== 4. resume replay: the same read goes back to the replica"
sql "SELECT pg_wal_replay_resume()" >/dev/null; sleep 1
curl -s -H "X-Min-LSN: $lsn" $api/orders/$id; echo

echo "== 5. replica down: reads survive on the primary"
docker compose stop -t 1 replica >/dev/null 2>&1
curl -s -H "X-Min-LSN: $lsn" $api/orders/$id; echo
docker compose start replica >/dev/null 2>&1

Saída real (o pg_wal_replay_pause() congela a reaplicação na réplica, simulando lag):Real output (pg_wal_replay_pause() freezes replay on the replica, simulating lag):

== 1. write goes to the primary and returns a token (WAL position)
{"id": "92c500b3-...", "min_lsn": "0/3047348", "served_by": "primary"}
== 2. read with the token: replica has already replayed it
{"id": "92c500b3-...", "customer": "ana", "total": "49.90", "served_by": "replica"}
== 3. freeze replay on the replica (simulates lag)
{"id": "6dc4dfd0-...", "min_lsn": "0/3047460", "served_by": "primary"}
-- read WITHOUT token (naive split): stale read
{"error": "order not found", "served_by": "replica"}
-- read WITH token: falls back to the primary
{"id": "6dc4dfd0-...", "customer": "bruno", "total": "120.00", "served_by": "primary (replica behind the token)"}
-- lag as seen by the primary
[{"replica": "walreceiver", "state": "streaming", "sync": "async", "replay_lag_bytes": 280}]
== 4. resume replay: the same read goes back to the replica
{"id": "6dc4dfd0-...", "customer": "bruno", "total": "120.00", "served_by": "replica"}
== 5. replica down: reads survive on the primary
{"id": "6dc4dfd0-...", "customer": "bruno", "total": "120.00", "served_by": "primary (replica unreachable)"}

Repare no passo 3: a leitura sem o token devolveu um 404 de um pedido que acabou de ser criado. É a anomalia de read-your-writes reproduzida, com a réplica respondendo com toda a confiança.Note step 3: the read without the token returned a 404 for an order that had just been created. It is the read-your-writes anomaly reproduced, with the replica answering with full confidence.

Cenário 2: sharding por tenantScenario 2: sharding by tenant

docker compose --profile shard up -d shard0 shard1
docker compose up -d api          # recria a API já com os shards no ar

# 8 tenants x 3 pedidos cada
curl -X POST localhost:8080/t/acme/orders -d '{"customer":"zed","total":5}'
# {"id": "ebdca5ae-...", "tenant": "acme", "bucket": 32, "shard": 1}

curl localhost:8080/t/acme/orders      # consulta de UM shard (tem a chave)
# {"tenant": "acme", "shard": 1, "orders": [["zed","5.00"],["c3","30.00"],["c2","20.00"],["c1","10.00"]]}

curl localhost:8080/stats              # scatter-gather: consulta TODOS os shards
# {"shards": [{"shard": 0, "orders": 12, "revenue": "240.00"},
#             {"shard": 1, "orders": 13, "revenue": "245.00"}], "total_orders": 25}

# onde cada tenant caiu
# acme 32->1  globex 39->1  initech 3->0  umbrella 32->1
# hooli 11->0 stark 14->0   wayne 31->0   wonka 62->1

A distribuição ficou equilibrada (12 e 13 pedidos) com só 8 tenants. Note também que acme e umbrella caíram no mesmo bucket (32), o que é normal com 64 buckets: o bucket é uma unidade de movimentação, não de isolamento.The distribution came out balanced (12 and 13 orders) with only 8 tenants. Also note that acme and umbrella landed in the same bucket (32), which is normal with 64 buckets: the bucket is a unit of movement, not of isolation.

Limpar e exercíciosCleanup and exercises

docker compose --profile shard down -v

1. Coloque synchronous_standby_names = 'replica1' e synchronous_commit = remote_apply no primário. Repita o cenário 1: o passo 3 trava o POST? Por quê? (Dica: a réplica está com a reaplicação pausada.)Set synchronous_standby_names = 'replica1' and synchronous_commit = remote_apply on the primary. Repeat scenario 1: does step 3 block the POST? Why? (Hint: the replica has replay paused.)
2. Desligue o hot_standby_feedback, rode um pg_sleep(60) numa transação na réplica e um UPDATE mais VACUUM no primário. Veja a consulta ser cancelada e consulte pg_stat_database_conflicts.Turn off hot_standby_feedback, run a pg_sleep(60) in a transaction on the replica and an UPDATE plus VACUUM on the primary. Watch the query get cancelled and check pg_stat_database_conflicts.
3. Pare o primário e promova a réplica com SELECT pg_promote(). O que a API faz? O que precisa mudar para o failover ser automático?Stop the primary and promote the replica with SELECT pg_promote(). What does the API do? What has to change for failover to be automatic?
4. Adicione um terceiro shard: mude shard_of para usar um mapa explícito bucket → shard, mova um bucket à mão (INSERT ... SELECT entre os dois bancos) e troque o mapa.Add a third shard: change shard_of to use an explicit bucket → shard map, move one bucket by hand (INSERT ... SELECT between the two databases) and switch the map.

7. Decisão e checklist7. Decision and checklist

ChecklistChecklist

• Meça antes: qual é o gargalo? CPU, IO, conexões, leitura, escrita?Measure first: what is the bottleneck? CPU, IO, connections, reads, writes?
• Cada leitura classificada: pode ser velha? Se não, vai ao primário ou usa token.Every read classified: may it be stale? If not, it goes to the primary or uses a token.
• Transação que escreve (ou usa FOR UPDATE) inteira no primário.A transaction that writes (or uses FOR UPDATE) entirely on the primary.
• Alarme de lag, de WAL retido e de conexões; fallback para o primário se a réplica falhar.Alarms on lag, retained WAL and connections; fallback to the primary if the replica fails.
• Réplica de relatório com max_standby_streaming_delay alto, separada da de failover.Reporting replica with a high max_standby_streaming_delay, separate from the failover one.
• Failover testado de verdade, com a aplicação reconectando (DNS e pool).Failover tested for real, with the application reconnecting (DNS and pool).
• Se for fazer shard: chave que aparece em quase toda consulta, buckets fixos, UUIDs, migrações versionadas.If you shard: a key that appears in almost every query, fixed buckets, UUIDs, versioned migrations.
• Cada shard com as suas réplicas e o seu backup.Each shard with its own replicas and backup.