Database Replication in Practice: Emulating a Production Environment with PostgreSQL, HAProxy, and TypeScript

Introduction

If you have ever worked on a system with high read volume, you have probably heard of database replication. But there is a large gap between understanding the concept and seeing it actually work. This article closes that gap: you will understand what replication is, how PostgreSQL implements it internally through the WAL, and how to set up a complete local environment with Docker, HAProxy, and three different ways to consume it all from TypeScript (raw SQL, Prisma, and Drizzle).

---

What Is Database Replication?

Replication is the process of keeping synchronized copies of a database across multiple servers. The server that accepts writes is called the primary. The servers that receive and apply changes from the primary are called replicas (or standbys).

The two main goals are:

1. Read scalability: distribute SELECT queries across multiple replicas so the primary is not overloaded.
2. High availability: if the primary goes down, a replica can be promoted to take its place.

In most applications, reads are far more frequent than writes. An e-commerce page, for example, makes dozens of SELECT statements to display products, categories, and reviews, but only one INSERT when the user places an order. Replication allows those reads to be absorbed by multiple servers in parallel.

---

How PostgreSQL Replicates: The WAL

Under the hood, PostgreSQL uses the Write-Ahead Log (WAL) as the central replication mechanism. The WAL is a sequence of transactional logs stored in file segments where every data modification is recorded before being applied to the database. This serves both crash recovery and replication.

The flow works like this:

Client → INSERT/UPDATE/DELETE
           ↓
     Modification written to memory (WAL buffer + shared buffers)
           ↓
     WAL durably flushed to disk (at COMMIT)
           ↓
     ✓ response to client (COMMIT confirmed)
           ↓
     WAL sender streams records to replicas (asynchronous)
           ↓
     Replicas receive and apply (WAL receiver + startup process)

     [in the background, independently of the COMMIT]
     Checkpointer/background writer flush shared buffers
     to data pages on disk

Replicas stay in recovery mode: they continuously receive and apply WAL records from the primary. While in this mode, they effectively operate as read-only databases for persistent application data — any write attempt is rejected. You can verify this with:

SELECT pg_is_in_recovery();
-- t  → in recovery (replica)
-- f  → primary

Physical vs. Logical Replication

PostgreSQL supports two types of replication:

• Physical (streaming replication): replicates the binary state of the data files. This is what we use in this project. It is simple, reliable, and replicates everything — including table structure.
• Logical: replicates changes at the row level, allowing you to select which tables to replicate. More flexible, but more complex to operate.

This project uses streaming replication, which is the standard for read replicas in production.

Replication Slots

When a replica connects to the primary via streaming, it needs WAL segments to not be recycled before it consumes them. This is where replication slots come in.

A replication slot is a persistent cursor on the primary that tracks how far each replica has consumed the WAL. As long as a slot is active, PostgreSQL will not discard WAL segments that have not yet been consumed by it.

The important caveat is that slow or disconnected replicas can cause the primary to accumulate WAL indefinitely, consuming disk. In production, monitor retained WAL with:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

The Bitnami image creates slots automatically. In manual setups without slots (using wal_keep_size), a very lagged replica may miss WAL segments and need to be rebuilt from scratch.

---

Asynchronous vs. Synchronous Replication

By default, PostgreSQL uses asynchronous replication: the primary confirms the COMMIT to the client before guaranteeing that replicas have received the change. This is fast, but it means an immediate read from a replica may return slightly stale data.

Asynchronous write:

Client  →  COMMIT  →  Primary confirms  →  ✓ response to client
                             ↓
                       (later, in the background)
                       Replica receives and applies

For cases where you need read-after-write consistency on replicas — guaranteeing that a read after a write sees the change — PostgreSQL offers synchronous_commit:

Mode	Behavior
off	Does not wait for the WAL to flush at all (not even locally)
local	Waits only for the local WAL flush, ignores synchronous replicas
remote_write	Waits for the replica to confirm it received and wrote the WAL to the OS buffer
on	Waits for the level configured by synchronous replication — this is the default
remote_apply	Waits for the replica to apply the WAL (data visible on replica)

In setups without synchronous replication configured (like this project), on behaves in practice the same as local.

With remote_apply, the COMMIT only returns to the client once the replicas configured as synchronous have applied the WAL and made the data visible for reading. It is the strongest mode, but it adds latency proportional to the round-trip to the replicas.

---

Project Architecture

The project sets up the following stack with Docker Compose:

┌─────────────────────────────────────────────────────┐
│                    Application                      │
│                                                     │
│  DATABASE_WRITE_URL           DATABASE_READ_URL     │
│  localhost:6432               localhost:6433        │
└──────────────┬────────────────────────┬─────────────┘
               │                        │
               ▼                        ▼
        ┌─────────────────────────────────────┐
        │             HAProxy                 │
        │  :5432 → primary  :5433 → replicas  │
        └──────┬──────────────────┬───────────┘
               │                  │ (round-robin)
               ▼                  ├──────────────────┐
     ┌──────────────────┐         ▼                  ▼
     │  postgres-primary│   ┌──────────┐     ┌──────────┐
     │  (read/write)    │   │ replica-1│     │ replica-2│
     └──────────────────┘   │(readonly)│     │(readonly)│
              │             └──────────┘     └──────────┘
              │                   ▲                 ▲
              │    WAL streaming  │                 │
              └───────────────────┴─────────────────┘

HAProxy: Router, Not Replicator

One important point: HAProxy does not replicate data. It only routes connections. Replication happens directly between the PostgreSQL containers via WAL streaming.

HAProxy exposes two ports:

• :5432 (mapped to localhost:6432): always forwards to the primary. Used for writes.
• :5433 (mapped to localhost:6433): load-balances in round-robin across the two replicas. Used for reads.

Load balancing happens per TCP connection, not per individual query — a persistent connection continues routing to the same replica until it is closed. In applications with a connection pool, the observed distribution depends on how many connections are open and for how long.

The HAProxy configuration is straightforward:

frontend postgres_write
  bind *:5432
  default_backend postgres_primary

backend postgres_primary
  server primary postgres-primary:5432 check

frontend postgres_read
  bind *:5433
  default_backend postgres_replicas

backend postgres_replicas
  balance roundrobin
  server replica1 postgres-replica-1:5432 check
  server replica2 postgres-replica-2:5432 check

It also exposes a status page at localhost:8404 that shows which backends are online in real time — useful for debugging.

The health checks used here (check) are simple TCP checks — HAProxy only confirms that the port is accepting connections, without distinguishing whether the server is a primary or a replica. In production, PostgreSQL-specific health checks are typically used to avoid unexpected behavior during failover.

Note: this setup does not implement automatic failover. If the primary goes down, the write port becomes unavailable until a replica is manually promoted and the routing is updated. In production, that promotion is typically managed by tools like Patroni, repmgr, or pg_auto_failover.

---

Configuring Replication in Docker Compose

The Bitnami PostgreSQL image makes configuration very easy through environment variables. On the primary:

postgres-primary:
  image: bitnami/postgresql:latest
  environment:
    POSTGRESQL_REPLICATION_MODE: master
    POSTGRESQL_REPLICATION_USER: replicator
    POSTGRESQL_REPLICATION_PASSWORD: replicator_password

On each replica:

postgres-replica-1:
  image: bitnami/postgresql:latest
  depends_on:
    postgres-primary:
      condition: service_healthy
  environment:
    POSTGRESQL_REPLICATION_MODE: slave
    POSTGRESQL_MASTER_HOST: postgres-primary
    POSTGRESQL_MASTER_PORT_NUMBER: 5432
    POSTGRESQL_REPLICATION_USER: replicator
    POSTGRESQL_REPLICATION_PASSWORD: replicator_password

The depends_on with condition: service_healthy ensures that replicas only start after the primary is ready to accept connections — necessary because replicas need to connect to the primary at startup to perform the initial data copy before they start following the WAL.

---

Consuming Replication: Three TypeScript Approaches

1. Raw SQL with pg

The most explicit approach. You create two separate connection pools and manually choose which one to use:

// projects/raw/db.ts
export const primaryPool = new pg.Pool({
  connectionString: process.env.DATABASE_WRITE_URL
});

export const replicaPool = new pg.Pool({
  connectionString: process.env.DATABASE_READ_URL
});

Writes go to the primary:

// write/create-user.ts
await primaryPool.query(
  `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *`,
  [`Raw User ${suffix}`, `raw-${suffix}@example.com`]
);

Reads go to the replicas:

// read/list-users.ts
const result = await replicaPool.query(
  `SELECT id, name, email FROM users ORDER BY id DESC LIMIT 10`
);

When to use: when you want full control and maximum clarity over what is happening. Great for understanding the mechanism or in systems where routing is critical.

---

2. Prisma with @prisma/extension-read-replicas

Prisma has an official extension that handles routing automatically. You configure a write URL (via schema.prisma) and a read URL (via the extension), and the extension automatically routes most operations:

• read operations like findMany, findFirst, count → replica (in most contexts)
• create, update, delete, transactions → primary always

// projects/prisma/db.ts
export const prisma = new PrismaClient().$extends(
  readReplicas({
    url: process.env.DATABASE_READ_URL!
  })
);

Application code looks identical — no changes to how you call Prisma:

// write/create-user.ts — automatically goes to the primary
const user = await prisma.user.create({
  data: { name: `Prisma User ${suffix}`, email: `...` }
});

// read/list-users.ts — automatically goes to the replica
const users = await prisma.user.findMany({
  orderBy: { id: "desc" },
  take: 10
});

When to use: when you are already on Prisma and want automatic routing without changing your application interface. The extension is official and well maintained.

---

3. Drizzle with Two Explicit db Objects

Drizzle does not have a built-in read replicas abstraction, but the idiomatic pattern is to export a db object with two named connections:

// projects/drizzle/db.ts
export const db = {
  write: drizzle(primaryPool),
  read: drizzle(replicaPool)
};

Application code makes the intent explicit:

// write/create-user.ts
const [user] = await db.write
  .insert(users)
  .values({ name: `Drizzle User ${suffix}`, email: `...` })
  .returning();

// read/list-users.ts
const rows = await db.read
  .select()
  .from(users)
  .orderBy(desc(users.id))
  .limit(10);

When to use: when you want the code itself to declare which database is being used. db.write vs db.read makes it immediately clear to any reader of the code where each operation goes.

---

Verifying That Replication Is Working

After bringing up the stack with npm run docker:up, you can confirm the replication state directly:

On the primary — see connected replicas:

docker exec -it postgres-primary psql -U app -d app -c \
  "SELECT application_name, state, sync_state, client_addr FROM pg_stat_replication;"

You will see something like:

 application_name |   state   | sync_state |  client_addr
------------------+-----------+------------+--------------
 walreceiver      | streaming | async      | 172.18.0.3
 walreceiver      | streaming | async      | 172.18.0.4

Since this project uses asynchronous replication, sync_state shows as async. When configuring synchronous replicas via synchronous_standby_names, that value would change to sync.

On a replica — confirm read-only mode:

docker exec -it postgres-replica-1 psql -U app -d app -c \
  "SELECT pg_is_in_recovery();"

 pg_is_in_recovery
-------------------
 t

t = in recovery = a working replica.

Verifying load balancing — confirming round-robin across replicas:

Run this command several times in a row against the read endpoint:

psql "postgresql://app:app@localhost:6433/app" \
  -c "SELECT inet_server_addr() AS replica, pg_is_in_recovery() AS is_replica;"

You will see the IP addresses alternating between the two replicas on each call:

  replica   | is_replica
------------+------------
 172.18.0.3 | t
(1 row)

  replica   | is_replica
------------+------------
 172.18.0.4 | t
(1 row)

This confirms that HAProxy is distributing connections in round-robin between replica-1 and replica-2, and that both are in recovery mode.

---

The Fundamental Trade-off: Consistency vs. Availability

Asynchronous replication (the default in this project) gives you:

• Low write latency: COMMIT returns as soon as the primary confirms.
• High availability: slow or offline replicas do not block writes.
• Eventual consistency: reads on replicas may see slightly stale data.

If you INSERT on the primary and immediately read from a replica, it is possible — very rare locally in normal conditions, but possible in production — to get a result without the just-inserted row. For those cases, read from DATABASE_WRITE_URL.

In real environments, the gap between primary and replica (replication lag) can grow under heavy write load, unstable network, or overloaded replicas. The larger the lag, the more stale the data returned by replicas. Monitoring lag is essential in production — the pg_stat_replication view exposes write_lag, flush_lag, and replay_lag for each connected replica.

Synchronous replication with remote_apply solves the consistency concern, but every write now depends on the network round-trip to the replicas. In cloud environments with replicas in different availability zones, this can add tens of milliseconds to each write operation.

There is no "right" configuration — only the trade-off that makes sense for your use case.

---

Running the Project

# Clone and configure the environment
cp .env.example .env

# Install dependencies
npm run raw:install
npm run prisma:install
npm run drizzle:install

# Start PostgreSQL and HAProxy
npm run docker:up

# Create tables via Prisma
npm run prisma:generate
npm run prisma:db:push

# Test each approach
npm run raw:write && npm run raw:read
npm run prisma:write && npm run prisma:read
npm run drizzle:write && npm run drizzle:read

# When done
npm run docker:down

---

Conclusion

Database replication is not magic — it is WAL streaming, recovery mode, and connection routing. This project dismantles each layer:

• PostgreSQL handles synchronization via WAL streaming.
• HAProxy handles routing application connections.
• The application consciously decides where to send each operation.

The three TypeScript approaches show that the underlying pattern is always the same — two endpoints, one for writes and one for reads — and that the difference lies in the level of abstraction you want to expose in your code: explicit with raw SQL and Drizzle, or automatic with Prisma.

The full code is available in the repository, with instructions to bring everything up with a single command.

Code: Github