Scale PostgreSQL with Docker

Set up replication, connection pooling, and table partitioning locally.

Introduction

30 Second Summary

Every app you use daily (banking, social media, food delivery) stores data in a database. When thousands of users hit that database at the same time, a single server struggles to keep up.

In this project, you will build a local PostgreSQL scaling lab that demonstrates three production techniques: streaming replication for distributing reads, connection pooling for efficient resource use, and table partitioning for handling massive tables.

What You'll Build

A Docker Compose stack running a multi-node PostgreSQL cluster with replication, connection pooling, and partitioning layers you can observe and query in real time.

By the end of this project, you'll have:

  • A running PostgreSQL cluster with 1 primary and 2 streaming replicas, with real-time replication verified.
  • PgBouncer connection pooling in transaction mode with measurable connection reduction.
  • A partitioned orders table split by month with partition pruning verified via EXPLAIN ANALYZE and lifecycle operations demonstrated.
  • Secret Mission: Implement read/write splitting by configuring a second PgBouncer instance that routes read queries to replicas.

Are there any prerequisites?

You should be comfortable with basic SQL (CREATE TABLE, INSERT, SELECT) and basic Docker knowledge (containers, volumes, docker compose up/down).

No prior experience with replication, pooling, or partitioning is needed. This project teaches all three from scratch.

Set Up the PostgreSQL Cluster

Every production PostgreSQL deployment at scale relies on multiple nodes working together. A single database server can only handle so many connections and queries before it becomes a bottleneck.

In this step, you'll create a Docker Compose stack that spins up a primary PostgreSQL node, two streaming replicas, and a PgBouncer connection pooler. By the end, all four containers will be running and healthy.

In this step, get ready to:
  • Create the Docker Compose file defining all four services.
  • Write initialization scripts that configure replication automatically.
  • Start the cluster and verify all containers are healthy.
Create the project directory and Docker Compose file

The docker-compose.yml file defines your entire cluster as code. It declares the primary, two replicas, and the PgBouncer pooler as interconnected services.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir pg-scaling-lab
cd pg-scaling-lab
  • Confirm the folder exists by running:
ls ~/Desktop

You should see pg-scaling-lab in the output.

  • Create a new file called docker-compose.yml in your pg-scaling-lab folder and add the following content:
services:
  pg-primary:
    image: postgres:17
    container_name: pg-primary
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: scalinglab
    ports:
      - "5432:5432"
    volumes:
      - pg_primary_data:/var/lib/postgresql/data
      - ./init-primary.sh:/docker-entrypoint-initdb.d/init-primary.sh
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

  pg-replica-1:
    image: postgres:17
    container_name: pg-replica-1
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGUSER: postgres
    ports:
      - "5434:5432"
    volumes:
      - pg_replica1_data:/var/lib/postgresql/data
      - ./init-replica.sh:/init-replica.sh
    entrypoint: /bin/bash
    command: /init-replica.sh
    depends_on:
      pg-primary:
        condition: service_healthy

  pg-replica-2:
    image: postgres:17
    container_name: pg-replica-2
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGUSER: postgres
    ports:
      - "5435:5432"
    volumes:
      - pg_replica2_data:/var/lib/postgresql/data
      - ./init-replica.sh:/init-replica.sh
    entrypoint: /bin/bash
    command: /init-replica.sh
    depends_on:
      pg-primary:
        condition: service_healthy

  pgbouncer:
    image: edoburu/pgbouncer
    container_name: pgbouncer
    environment:
      DB_USER: postgres
      DB_PASSWORD: postgres
      DB_HOST: pg-primary
      DB_NAME: scalinglab
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 200
      DEFAULT_POOL_SIZE: 20
      AUTH_TYPE: scram-sha-256
      ADMIN_USERS: postgres
    ports:
      - "5433:5432"
    depends_on:
      pg-primary:
        condition: service_healthy
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "localhost"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pg_primary_data:
  pg_replica1_data:
  pg_replica2_data:

What does this file do?

  • pg-primary runs PostgreSQL 17 with a health check and an initialization script that configures replication on first boot.
  • pg-replica-1 and pg-replica-2 each clone the primary's data and stream changes in real time. They only start after the primary is healthy (depends_on: service_healthy).
  • pgbouncer sits in front of the primary and pools connections in transaction mode, releasing backend connections after each transaction completes.
  • Named volumes (pg_primary_data, etc.) persist database data across container restarts.

Why transaction pooling mode?

Each idle PostgreSQL backend process consumes ~5-10MB of RAM. Without a pooler, 200 application connections mean 200 backend processes eating memory even when idle.

Transaction pooling releases backend connections back to the pool after each transaction finishes. This lets 200 clients share just 20 backend connections. Session pooling only helps with connection churn, and statement pooling breaks multi-statement transactions.

  • Save docker-compose.yml.

YAML syntax errors later?

YAML is indentation-sensitive. Make sure each nested level uses exactly 2 spaces (not tabs). The services key sits at the top level, each service name is indented 2 spaces, and their properties are indented 4 spaces.

help me fix a YAML indentation error in my docker-compose.yml

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

services:
  pg-primary:
    image: postgres:17
    container_name: pg-primary
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: scalinglab
    ports:
      - "5432:5432"
    volumes:
      - pg_primary_data:/var/lib/postgresql/data
      - ./init-primary.sh:/docker-entrypoint-initdb.d/init-primary.sh
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

  pg-replica-1:
    image: postgres:17
    container_name: pg-replica-1
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGUSER: postgres
    ports:
      - "5434:5432"
    volumes:
      - pg_replica1_data:/var/lib/postgresql/data
      - ./init-replica.sh:/init-replica.sh
    entrypoint: /bin/bash
    command: /init-replica.sh
    depends_on:
      pg-primary:
        condition: service_healthy

  pg-replica-2:
    image: postgres:17
    container_name: pg-replica-2
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGUSER: postgres
    ports:
      - "5435:5432"
    volumes:
      - pg_replica2_data:/var/lib/postgresql/data
      - ./init-replica.sh:/init-replica.sh
    entrypoint: /bin/bash
    command: /init-replica.sh
    depends_on:
      pg-primary:
        condition: service_healthy

  pgbouncer:
    image: edoburu/pgbouncer
    container_name: pgbouncer
    environment:
      DB_USER: postgres
      DB_PASSWORD: postgres
      DB_HOST: pg-primary
      DB_NAME: scalinglab
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 200
      DEFAULT_POOL_SIZE: 20
      AUTH_TYPE: scram-sha-256
      ADMIN_USERS: postgres
    ports:
      - "5433:5432"
    depends_on:
      pg-primary:
        condition: service_healthy
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "localhost"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pg_primary_data:
  pg_replica1_data:
  pg_replica2_data:
Create the primary initialization script

When the primary container starts for the first time, PostgreSQL automatically runs any scripts inside /docker-entrypoint-initdb.d/. Your script will configure streaming replication parameters and create a dedicated replication user.

  • Create a new file called init-primary.sh in your pg-scaling-lab folder and add the following content:
#!/bin/bash
set -e

# Configure replication settings
cat >> "$PGDATA/postgresql.conf" <<EOF

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
listen_addresses = '*'
EOF

# Create replication user
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator_pass';
EOSQL

# Allow replication connections from any host in the Docker network
echo "host replication replicator all scram-sha-256" >> "$PGDATA/pg_hba.conf"
echo "host all all all scram-sha-256" >> "$PGDATA/pg_hba.conf"

What does this script do?

  • wal_level = replica tells PostgreSQL to write enough detail into the WAL (Write-Ahead Log) for replicas to reconstruct every change.
  • max_wal_senders = 10 and max_replication_slots = 10 set the maximum number of replicas that can stream from this primary simultaneously.
  • hot_standby = on allows replicas to serve read queries while still receiving WAL updates.
  • The replicator role is a dedicated user that replicas use to connect and stream changes.
  • The pg_hba.conf entries allow replication connections from any host in the Docker network using scram-sha-256 authentication.
  • Save init-primary.sh.

Script not running when the container starts?

Make sure the file is saved in the same directory as docker-compose.yml. The volume mount ./init-primary.sh is relative to where Docker Compose runs from.

help me debug why my PostgreSQL initialization script isn't running

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

#!/bin/bash
set -e

# Configure replication settings
cat >> "$PGDATA/postgresql.conf" <<EOF

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
listen_addresses = '*'
EOF

# Create replication user
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator_pass';
EOSQL

# Allow replication connections from any host in the Docker network
echo "host replication replicator all scram-sha-256" >> "$PGDATA/pg_hba.conf"
echo "host all all all scram-sha-256" >> "$PGDATA/pg_hba.conf"
Create the replica script and start the cluster

Each replica needs to clone the primary's data before it can start streaming changes. The pg_basebackup tool handles this by taking a full copy of the primary and configuring the replica to connect back for ongoing WAL streaming.

  • Create a new file called init-replica.sh in your pg-scaling-lab folder and add the following content:
#!/bin/bash
set -e

# Wait for primary to be ready
until PGPASSWORD=replicator_pass pg_isready -h pg-primary -U replicator -d replication; do
  echo "Waiting for primary to be ready..."
  sleep 2
done

# Only initialize if data directory is empty
if [ ! -s "$PGDATA/PG_VERSION" ]; then
  echo "Cloning primary with pg_basebackup..."
  rm -rf "$PGDATA"/*

  PGPASSWORD=replicator_pass pg_basebackup \
    -h pg-primary \
    -U replicator \
    -D "$PGDATA" \
    -R \
    -X stream \
    -C \
    -S "replica_slot_$(hostname | tr '-' '_')" \
    -c fast

  echo "Backup complete. Starting replica..."
fi

# Start PostgreSQL in the foreground
exec postgres

What does this script do?

  • The script waits in a loop until the primary is accepting connections. This prevents the replica from trying to clone before the primary is ready.
  • The -R flag creates a standby.signal file and appends primary_conninfo to postgresql.auto.conf, which is what makes the replica automatically connect to the primary for streaming.
  • The -C -S flags create a replication slot on the primary. This prevents the primary from deleting WAL segments the replica hasn't received yet.
  • exec postgres starts PostgreSQL as the main container process, so Docker can manage it properly.
  • Save init-replica.sh.
  • Confirm all three files exist by running:
ls

You should see docker-compose.yml, init-primary.sh, and init-replica.sh in the output.

  • Make both scripts executable by running this command:
chmod +x init-primary.sh init-replica.sh
  • Start the full cluster by running:
docker compose up -d

What does this command do?

docker compose up -d builds and starts all four containers in detached mode (running in the background). The primary starts first, and once its health check passes, the replicas and PgBouncer start automatically.

The first run pulls the postgres:17 and edoburu/pgbouncer images from Docker Hub. This may take 1-2 minutes depending on your internet speed.

  • Wait about 30 seconds for the replicas to clone the primary, then check all containers are running and healthy:
docker compose ps

You should see all four containers (pg-primary, pg-replica-1, pg-replica-2, pgbouncer) listed with a STATUS showing "Up" and "(healthy)".

Containers not showing as healthy?

  • If a replica shows "Restarting", the primary may not have finished initializing yet. Wait 30 more seconds and run docker compose ps again.
  • If a container shows "Exit", check its logs by running docker compose logs pg-replica-1 (replace with the failing container name). Look for permission errors or connection refused messages.
  • Make sure Docker Desktop is running. If it was just installed, restart it and try docker compose up -d again.

help me debug why my PostgreSQL containers aren't starting

✔️ Awesome, I've got everything!

Great. Make sure you've saved both script files before moving on.

ⓧ I'd like to double check the full code

#!/bin/bash
set -e

# Wait for primary to be ready
until PGPASSWORD=replicator_pass pg_isready -h pg-primary -U replicator -d replication; do
  echo "Waiting for primary to be ready..."
  sleep 2
done

# Only initialize if data directory is empty
if [ ! -s "$PGDATA/PG_VERSION" ]; then
  echo "Cloning primary with pg_basebackup..."
  rm -rf "$PGDATA"/*

  PGPASSWORD=replicator_pass pg_basebackup \
    -h pg-primary \
    -U replicator \
    -D "$PGDATA" \
    -R \
    -X stream \
    -C \
    -S "replica_slot_$(hostname | tr '-' '_')" \
    -c fast

  echo "Backup complete. Starting replica..."
fi

# Start PostgreSQL in the foreground
exec postgres

Your cluster is alive. You have a primary accepting writes, two replicas ready to stream changes, and a connection pooler sitting in front. Next up, you'll verify that replication is actually working by writing data to the primary and watching it appear on the replicas.

Verify Streaming Replication

Your cluster is running, but running containers is not the same as working replication. You need to prove that data written on the primary actually appears on both replicas automatically.

In this step, you'll write data to the primary and confirm it flows through the WAL streaming pipeline to both replicas. Then you'll query PostgreSQL's built-in replication monitoring view to observe lag in real-time.

In this step, get ready to:
  • Create test data on the primary and verify it replicates to both replicas.
  • Query replication monitoring views to observe LSN positions and lag.
  • Stress-test replication with a 10,000-row bulk insert and watch replicas catch up.
Create test data on the primary

The simplest way to verify replication is to write data on the primary and check if it shows up on the replicas without any manual intervention.

  • Connect to the primary database by running this command:
PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d scalinglab

What does this command do?

This connects to the scalinglab database on the primary container using psql. The PGPASSWORD prefix avoids an interactive password prompt. Port 5432 maps directly to the pg-primary container.

  • Create the employees table and insert sample data by running this SQL:
-- Create a test table on the primary
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

-- Insert sample employees
INSERT INTO employees (name, department, salary) VALUES
    ('Alice Chen', 'Engineering', 95000.00),
    ('Bob Smith', 'Marketing', 72000.00),
    ('Carol Davis', 'Engineering', 98000.00);

What does this code do?

  • CREATE TABLE employees defines a simple table with an auto-incrementing ID, a name, department, and salary.
  • The INSERT adds three rows so you have real data to look for on the replicas.
  • Confirm the data exists on the primary by running:
SELECT * FROM employees;
  • You should see three rows with Alice, Bob, and Carol listed.
  • Exit psql by running:
\q

Now connect to the first replica to see if the data arrived automatically.

  • Connect to pg-replica-1 by running:
PGPASSWORD=postgres psql -h localhost -p 5434 -U postgres -d scalinglab
  • Query the employees table by running:
SELECT * FROM employees;
  • You should see the same three rows. The data replicated without any manual action on your part.
  • Exit psql and connect to pg-replica-2 by running:
\q
PGPASSWORD=postgres psql -h localhost -p 5435 -U postgres -d scalinglab
  • Query the employees table again:
SELECT * FROM employees;
  • You should see the identical three rows here as well. Both replicas are receiving data from the primary via WAL streaming.
  • Exit psql by running \q.

Not seeing the employees table on a replica?

  • Make sure you connected to the correct port. Port 5434 is replica-1, port 5435 is replica-2.
  • Run docker ps to confirm all containers show a healthy status. If a replica restarted, it may need a few seconds to finish cloning.
  • help me troubleshoot why my PostgreSQL replicas don't have the data I inserted on the primary
Monitor replication with pg_stat_replication

PostgreSQL provides a built-in view called pg_stat_replication that shows you exactly what each replica is doing. This is the same view production teams use to monitor replication health and detect problems before they become outages.

  • Connect back to the primary by running:
PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d scalinglab
  • Query the replication status view by running:
-- Check active replication connections and lag
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

What do these columns mean?

  • state shows whether the replica is actively streaming. You want to see streaming here.
  • sent_lsn is the WAL position the primary has sent to the replica.
  • write_lsn is the position the replica has written to disk.
  • flush_lsn is the position the replica has flushed (made durable on disk).
  • replay_lsn is the position the replica has applied and made visible to queries.
  • write_lag, flush_lag, and replay_lag show the time delay at each stage. When the system is idle, these may show NULL because there is no active WAL to measure against.

In production, you would alert when replay_lag exceeds your acceptable threshold. For an asynchronous standby, this column approximates how far behind the replica is before recent transactions become visible to queries.

  • You should see two rows, both with state showing streaming. The LSN columns should show matching or very close values since the system is idle.

Seeing zero rows in pg_stat_replication?

  • This means no replicas are connected to the primary. Run docker ps to check if pg-replica-1 and pg-replica-2 are running.
  • Check replica logs with docker logs pg-replica-1 to see if the replication connection failed.
  • help me troubleshoot why pg_stat_replication shows no rows for my PostgreSQL streaming replicas
Stress-test replication with a bulk insert

With a few rows, replication is nearly instant. To see replication lag in action, you need to generate enough write pressure that the replicas fall measurably behind, even if only for a moment.

  • Still connected to the primary, insert 10,000 rows by running:
-- Generate 10,000 rows to create write pressure
INSERT INTO employees (name, department, salary)
SELECT
    'Employee_' || i,
    CASE (i % 4)
        WHEN 0 THEN 'Engineering'
        WHEN 1 THEN 'Marketing'
        WHEN 2 THEN 'Sales'
        WHEN 3 THEN 'Support'
    END,
    40000 + (random() * 60000)::NUMERIC(10, 2)
FROM generate_series(1, 10000) AS i;

What does this code do?

  • generate_series(1, 10000) creates 10,000 numbered rows in a single statement.
  • The CASE expression distributes employees across four departments using the modulo operator.
  • random() * 60000 generates a random salary between 40,000 and 100,000.
  • All 10,000 rows are written in one transaction, which generates a burst of WAL records that the replicas must stream and apply.
  • Immediately after the insert completes, check the replication lag by running:
-- Check replication lag after bulk insert
SELECT
    client_addr,
    state,
    sent_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;
  • You may see small non-NULL values in the lag columns if you run this quickly enough after the insert.
  • Wait a few seconds and run the same query again:
-- Run again after a few seconds to confirm replicas caught up
SELECT
    client_addr,
    state,
    sent_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;
  • The lag columns should now show NULL or very small values, confirming the replicas caught up within seconds.
  • Verify the replicas have all 10,003 rows (3 original + 10,000 new) by running:
-- Confirm total row count on primary
SELECT COUNT(*) FROM employees;
  • You should see 10003 as the count.
  • Exit psql and verify the count on a replica by running:
\q
PGPASSWORD=postgres psql -h localhost -p 5434 -U postgres -d scalinglab -c "SELECT COUNT(*) FROM employees;"
  • You should see 10003 here as well, proving all 10,000 rows replicated successfully.

Why does lag show NULL when replicas are caught up?

The lag columns measure the time between the primary flushing WAL and receiving confirmation from the replica. When there is no active write traffic, PostgreSQL has no new WAL to measure against. After a short idle period, the lag columns revert to NULL to avoid showing stale values.

This does not mean replication is broken. It means the replicas are fully caught up and waiting for new data.

Replica count doesn't match the primary?

  • Wait 5-10 seconds and try again. Locally, replication is fast but not instantaneous for large inserts.
  • Check pg_stat_replication on the primary. If state shows catchup instead of streaming, the replica is still processing the backlog.
  • help me understand why my PostgreSQL replica has fewer rows than my primary after a bulk insert

Your replication pipeline is verified and working under load. Next up, you'll put PgBouncer to work by routing connections through the pooler and observing how it multiplexes hundreds of client connections into a small pool of backend connections.

Add Connection Pooling with PgBouncer

Your replicas are streaming data in real-time. But there is another bottleneck hiding in plain sight: connections.

Every time a client connects to PostgreSQL, the server forks a dedicated backend process consuming around 5-10MB of RAM. With hundreds of application servers connecting directly, you can exhaust memory before you exhaust CPU. PgBouncer sits between your application and PostgreSQL, multiplexing many client connections through a small pool of backend connections.

In this step, get ready to:
  • Connect to your database through PgBouncer and verify queries work normally.
  • Explore the PgBouncer admin console to observe connection multiplexing metrics.
  • Simulate connection pressure and confirm PgBouncer limits backend connections.
Verify PgBouncer and connect through the pooler

PgBouncer is already running as part of your Docker Compose stack. It is configured in transaction pooling mode, which releases server connections back to the pool after each transaction completes.

  • Confirm the PgBouncer container is healthy by running:
docker ps --filter name=pgbouncer --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}"

You should see the pgbouncer container with a status of Up and port 5433->5432/tcp in the output.

  • Connect to your database through PgBouncer by running:
psql -h localhost -p 5433 -U postgres -d scalinglab

Enter postgres when prompted for the password.

  • Run a query to confirm you can read data through the pooler:
-- Count rows in the employees table through PgBouncer
SELECT COUNT(*) FROM employees;

What is happening here?

Your psql client connected to PgBouncer on host port 5433. PgBouncer then opened (or reused) a connection to the pg-primary container on its behalf.

From your application's perspective, the query works exactly like a direct PostgreSQL connection. The pooling is completely transparent.

You should see the count of rows you inserted in the previous step (10,000+).

  • Exit the psql session:
\q

Connection refused on port 5433?

Make sure the PgBouncer container is running with docker ps. If it shows as unhealthy or restarting, check its logs with docker logs pgbouncer.

A common cause is the primary not being ready when PgBouncer starts. Try restarting just PgBouncer with docker compose restart pgbouncer.

Still stuck? Help me troubleshoot my PgBouncer connection. I'm running docker compose with the pgbouncer service on port 5433 but getting connection refused.

Explore the PgBouncer admin console

PgBouncer exposes a special virtual database called pgbouncer that gives you access to admin commands. This is where you monitor connection pool health in production.

  • Connect to the PgBouncer admin console by running:
psql -h localhost -p 5433 -U postgres -d pgbouncer

Enter postgres when prompted for the password.

  • View the current pool status by running:
SHOW POOLS;

What do these columns mean?

The key columns in SHOW POOLS output are:

  • cl_active counts client connections currently linked to a server connection.
  • cl_waiting counts clients that sent queries but are waiting for a server connection.
  • sv_active counts server connections currently in use by a client.
  • sv_idle counts server connections established but not serving any client right now.

In transaction pooling mode, idle clients release their server connections. So sv_active stays low even when many clients are connected.

  • Check aggregate statistics by running:
SHOW STATS;

This shows cumulative counters like total transactions processed, total queries, and bytes sent/received per database.

  • View the actual backend server connections by running:
SHOW SERVERS;

Each row represents one real connection from PgBouncer to PostgreSQL. Notice how few server connections exist compared to the potential 200 client connections allowed.

  • Exit the admin console:
\q

Getting permission denied on the admin console?

The PgBouncer admin console only allows users listed in ADMIN_USERS. Your Docker Compose configuration sets ADMIN_USERS: postgres, so make sure you connect as the postgres user.

Need more help? Help me access the PgBouncer admin console

Simulate connection pressure

With one client connected, the pool metrics are not very exciting. Let's simulate what happens when many clients connect simultaneously and see how PgBouncer protects PostgreSQL from connection overload.

  • Open 30 concurrent connections through PgBouncer by running this shell loop:
# Open 30 concurrent connections that each hold a transaction for 10 seconds
for i in $(seq 1 30); do
  PGPASSWORD=postgres psql -h localhost -p 5433 -U postgres -d scalinglab -c "SELECT pg_sleep(10);" &
done

What does this loop do?

This fires off 30 background psql sessions, each executing a 10-second pg_sleep transaction through PgBouncer.

PgBouncer's pool size is set to 20, so only 20 of these can hold server connections at once. The remaining 10 must wait for a server connection to become available.

  • Quickly (within 10 seconds) connect to the admin console and check the pool status:
PGPASSWORD=postgres psql -h localhost -p 5433 -U postgres -d pgbouncer -c "SHOW POOLS;"

You should see sv_active at or near 20 (the pool size limit) and cl_waiting showing the clients queued for a server connection.

Why is this important?

Without PgBouncer, all 30 connections would each spawn a dedicated PostgreSQL backend process. With PgBouncer in transaction pooling mode, PostgreSQL never sees more than 20 connections regardless of how many clients are waiting.

This is how production systems support thousands of application instances connecting to a single database cluster without running out of memory.

  • Wait about 10 seconds for all background jobs to finish, then confirm they completed by running:
wait

All 30 connections should complete successfully. PgBouncer queued the excess clients and served them as server connections became available.

Seeing timeout errors?

If some connections fail with a timeout, your PgBouncer's default query timeout may be too short for the 10-second sleep. This is fine for the demonstration. The key observation is the SHOW POOLS output showing limited server connections.

If SHOW POOLS shows 0 for all values, you may have waited too long. Run the loop again and check SHOW POOLS within a few seconds.

Need help interpreting your output? Help me understand my SHOW POOLS output

Connection pooling is now verified and working. Next up, you will implement table partitioning to handle large tables that grow over time.

Implement Table Partitioning

Your cluster now handles connections efficiently with PgBouncer multiplexing dozens of client sessions across a small pool of backend connections.

The next scaling challenge is large tables. As tables grow into millions of rows, queries slow down because PostgreSQL must scan the entire table even when you only need one month of data. Table partitioning solves this by splitting a single logical table into smaller physical pieces. In this step, you'll create a partitioned orders table, prove that the query planner only scans the partitions it needs, and practice adding and removing partitions.

In this step, get ready to:
  • Create a partitioned orders table with monthly range partitions.
  • Verify partition pruning with EXPLAIN ANALYZE.
  • Practice partition lifecycle operations (add and detach partitions).
Create the partitioned orders table

Range partitioning splits rows based on a continuous value like a date. Each partition holds a defined range of the partition key. PostgreSQL automatically routes inserts to the correct partition based on the row's value.

  • Connect to the primary database by running this command:
psql -h localhost -p 5432 -U postgres -d scalinglab
  • Create the partitioned orders table by running this SQL:
-- Create a partitioned table using range partitioning on order_date
CREATE TABLE orders (
    order_id SERIAL,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

Why is order_date in the primary key?

PostgreSQL requires the partition key to be part of any primary key or unique constraint on a partitioned table. This ensures uniqueness can be enforced within each partition independently.

  • Create monthly partitions for January through June 2025, plus a DEFAULT partition, by running this SQL:
-- Create monthly partitions for the first half of 2025
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE orders_2025_03 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

CREATE TABLE orders_2025_04 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

CREATE TABLE orders_2025_05 PARTITION OF orders
    FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

CREATE TABLE orders_2025_06 PARTITION OF orders
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

-- Catch any rows that don't fit existing partitions
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

What does this code do?

Each CREATE TABLE ... PARTITION OF statement defines a child table that stores a specific date range. The FROM bound is inclusive and the TO bound is exclusive. So orders_2025_01 holds rows where order_date is January 1 through January 31.

The DEFAULT partition catches any inserts that don't match a defined range. This prevents insert failures if a row falls outside your planned partitions.

  • Verify the partition structure by running:
\d+ orders

You should see a list of partitions with their date ranges displayed under the table definition.

Seeing an error about the partition key?

Make sure you included order_date in the PRIMARY KEY constraint. PostgreSQL requires the partition key column to be part of any unique or primary key constraint on a partitioned table.

If you need to start over, run DROP TABLE IF EXISTS orders CASCADE; and try again.

Help me fix my partitioned table definition.

Insert data and verify partition pruning

With the table structure in place, you need data to observe how the query planner handles partitioned tables. You'll insert 100,000 rows and then use EXPLAIN ANALYZE to see exactly which partitions PostgreSQL scans for different queries.

  • Insert 100,000 sample orders distributed across the first half of 2025 by running:
-- Insert 100,000 orders spread across January to June 2025
INSERT INTO orders (customer_id, order_date, amount, status)
SELECT
    (random() * 10000)::INTEGER + 1,
    '2025-01-01'::DATE + (random() * 181)::INTEGER,
    (random() * 500 + 5)::DECIMAL(10,2),
    CASE (random() * 3)::INTEGER
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'shipped'
        WHEN 2 THEN 'delivered'
        ELSE 'pending'
    END
FROM generate_series(1, 100000);

What does this code do?

The generate_series(1, 100000) function produces 100,000 rows. For each row, the query generates a random customer ID, a random date within 181 days (covering January 1 through June 30), a random order amount, and a random status.

PostgreSQL automatically routes each insert to the correct monthly partition based on the order_date value. You don't need to specify which partition to write to.

  • Verify the row distribution across partitions by running:
-- Check how many rows landed in each partition
SELECT
    tableoid::regclass AS partition_name,
    COUNT(*) AS row_count
FROM orders
GROUP BY tableoid
ORDER BY partition_name;

You should see roughly 16,000-17,000 rows in each monthly partition. The exact numbers will vary because the dates are randomly generated.

  • Now observe partition pruning in action. Run an EXPLAIN ANALYZE query filtered to a single month:
-- Query only March 2025 orders and observe the query plan
EXPLAIN ANALYZE SELECT * FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

What should I see?

The query plan shows a Seq Scan on orders_2025_03 only. PostgreSQL's planner examined the partition boundaries and proved that no other partition could contain matching rows. This is partition pruning. Only one partition is scanned instead of all seven.

  • Compare this with a full-table scan that touches all partitions:
-- Query without a date filter to see all partitions scanned
EXPLAIN ANALYZE SELECT * FROM orders;

This time the plan shows an Append node with Seq Scan entries for every partition. Without a WHERE clause on the partition key, PostgreSQL cannot prune any partitions and must scan them all.

Why does this matter in production?

Imagine a table with 5 years of daily order data. A query for last month's orders without partitioning would scan 60 months of data. With monthly partitions, the same query scans only 1 out of 60 partitions. That is a 98% reduction in I/O.

All partitions still showing in the pruned query?

Double-check that your WHERE clause uses the partition key column (order_date) directly. Wrapping it in a function (like EXTRACT) prevents the planner from matching it against partition boundaries.

Help me understand why partition pruning isn't working on my query.

Practice partition lifecycle operations

In production, partitions are not static. You add new partitions as time advances and detach old ones for archival or deletion. This lifecycle management is one of the biggest advantages of partitioning over plain tables.

  • Simulate adding capacity for a new month by creating a July 2025 partition:
-- Add a new partition for July 2025
CREATE TABLE orders_2025_07 PARTITION OF orders
    FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
  • Verify the new partition exists by running:
\d+ orders

You should see orders_2025_07 listed among the partitions with the range FOR VALUES FROM ('2025-07-01') TO ('2025-08-01').

  • Now simulate archiving old data by detaching the January 2025 partition:
-- Detach January 2025 partition (simulates archival)
ALTER TABLE orders DETACH PARTITION orders_2025_01;

What does DETACH PARTITION do?

The detached partition continues to exist as a standalone table, but it is no longer part of the orders partitioned table. Queries against orders will no longer include January data. This is much faster than running a DELETE on millions of rows, and it avoids the VACUUM overhead that bulk deletes create.

You can still query the detached table directly with SELECT * FROM orders_2025_01; for archival or reporting purposes.

  • Verify data integrity after the detach by checking the row count on the main table:
-- Count remaining rows in the partitioned table
SELECT COUNT(*) FROM orders;
  • Confirm the detached partition still holds its data independently:
-- Count rows in the detached standalone table
SELECT COUNT(*) FROM orders_2025_01;

The sum of both counts should equal your original 100,000 rows. No data was lost during the detach operation.

DETACH command hanging?

The DETACH PARTITION command requires an ACCESS EXCLUSIVE lock on the parent table. If you have another psql session with an open transaction against the orders table, the detach will wait. Close any other sessions or commit their transactions first.

Help me figure out why my DETACH PARTITION command is hanging.

  • Exit the psql session by running:
\q

Secret mission

Implement Read/Write Splitting with a Second PgBouncer Instance

In production, applications use separate connection strings for reads and writes. Writes go to the primary. Reads go to replicas. Add a second PgBouncer container that routes read queries to your two replicas in round-robin mode, completing the full production scaling architecture.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with Docker, so there are no ongoing cloud costs.

Resources you used:

  • Docker containers: pg-primary, pg-replica-1, pg-replica-2, pgbouncer, pgbouncer-read.
  • Docker volumes: pg_primary_data, pg_replica1_data, pg_replica2_data.
  • Local project files: docker-compose.yml, init-primary.sh, init-replica.sh, pgbouncer-read.ini.

Keep everything running

No action needed. Choose this if you're still actively experimenting with the cluster.

  • Your five containers will continue running in the background.
  • The stack uses approximately 2GB of RAM while running.
  • Docker volumes consume approximately 500MB of disk space.

Pause - I'll come back to this later

Shut down running containers to free up memory. Your data volumes are preserved so you can resume later.

  • Stop all containers by running this command from your project folder:
docker compose stop

When you're ready to resume, start everything back up from the same folder:

docker compose start
  • All your data (the partitioned orders table, employees table, and replication configuration) will be intact when you resume.

Delete - I don't want to use this again

Remove all containers, volumes, and project files. All database data will be permanently lost.

  • Remove all containers and volumes by running this command from your project folder:
docker compose down -v
  • This removes all five containers and the three data volumes. Your database data is gone after this.
  • Delete the project folder (the one containing docker-compose.yml) from your Desktop using Finder (macOS) or File Explorer (Windows).
  • Confirm the containers are gone by running:
docker ps -a | grep pg-
  • You should see no output. If containers still appear, run docker compose down -v again from the project folder.

Nice Work!

Nice Work!

You've just built a production-grade PostgreSQL scaling lab from scratch. This is the same architecture pattern used by teams running PostgreSQL for hundreds of millions of users.

You've learned how to:

  • Set up streaming replication with a primary and 2 read replicas, verifying real-time WAL streaming and monitoring replication lag.
  • Configure PgBouncer connection pooling in transaction mode and observe how connection multiplexing reduces backend connections under load.
  • Implement declarative table partitioning with monthly ranges, verify partition pruning with EXPLAIN ANALYZE, and manage partition lifecycle operations.
  • Secret Mission: Implement read/write splitting with separate PgBouncer instances routing writes to the primary and reads to replicas in round-robin.

Ready to quiz yourself?