Break the Billion-View Counter

Simulate counter overflow, sharding, and idempotent retries in PostgreSQL.

Introduction

30 Second Summary

A viral video's view count looks like one simple number. At enormous scale, that number can outgrow its storage or become a traffic jam for updates.

In this project, you will build a local viral-video counter lab that exposes the signed 32-bit limit in PostgreSQL. You will evolve it into a sharded counter that handles concurrent writes across multiple rows.

What You'll Build

You will watch a valid view reach the counter's limit, see the next update fail, migrate beyond that boundary, then compare one hot row with 16 counter shards under the same concurrent workload.

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

  • A reproducible overflow demo that reaches the maximum four-byte signed integer before visibly rejecting the next view.
  • A value-preserving migration that lets the same counter advance to 2147483648.
  • A concurrent benchmark that reports attempted transactions, recorded views, elapsed time, and throughput for single-row and 16-shard designs.
  • Secret Mission: Send the same view event hundreds of times concurrently while recording exactly one event and one view.

Are there any prerequisites?

This lab assumes familiarity with SQL plus distributed systems concepts. You also need Docker Desktop and Visual Studio Code installed on a Mac.

Before We Start

This step is your moment to commit to a fully local counter-failure lab that isolates numeric overflow, hot-row contention, sharding trade-offs, and duplicate retries as separate concerns. Reproducing each failure gives you evidence for choosing the smallest appropriate design change.

Launch the Local Counter Lab

Your counter lab needs failures you can trust. Machine-specific version drift would blur whether a problem comes from the counter design or the environment.

Docker Compose gives every experiment the same PostgreSQL version in a container. A second image pins Python for the benchmark.

In this step, get ready to:
  • Create the local files that define the lab environment.
  • Start the PostgreSQL database through Docker Compose.
  • Verify the pinned database and client-library versions.
Create the lab structure

Visual Studio Code keeps the container definitions beside the files they mount. The `sql` folder holds database scripts in later steps. The `benchmark` folder becomes the working directory for the benchmark container.

  • Press Cmd+Space to open macOS search.
  • Type `Visual Studio Code` into the search field.
  • Press Enter to open Visual Studio Code.
  • Select File from the top menu.
  • Select Open Folder.
  • Select Desktop in the folder picker.

The folder picker now points to your Desktop. This keeps the lab in a predictable location for the terminal commands ahead.

  • Click New Folder in the folder picker.
  • Enter `gangnam-counter-lab` as the folder name.
  • Create the folder with the confirmation control in the folder picker.
  • Open `gangnam-counter-lab` in Visual Studio Code.

Your lab now has a dedicated home on the Desktop. The Explorer sidebar shows `gangnam-counter-lab` as the open folder.

  • Create `sql` inside `gangnam-counter-lab` with the Explorer sidebar's New Folder control.
  • Create `benchmark` inside `gangnam-counter-lab` with the same control.

The Explorer sidebar now shows the empty `sql` folder beside the empty `benchmark` folder.

  • Create `compose.yaml` inside `gangnam-counter-lab` with the Explorer sidebar's New File control.
  • Define the database service by pasting this code into `compose.yaml`:
services:
  db:
    image: postgres:18.6-bookworm
    environment:
      POSTGRES_USER: counter_user
      POSTGRES_PASSWORD: counter_password
      POSTGRES_DB: counter_lab
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U counter_user -d counter_lab"]
      interval: 2s
      timeout: 5s
      retries: 15

volumes:
  postgres-data:

What does this configuration do?

  • The `db` service pins the database image to `postgres:18.6-bookworm`.
  • The environment values create `counter_user` for the `counter_lab` database.
  • The `postgres-data` named volume preserves the local database files.
  • The `healthcheck` waits for PostgreSQL to accept connections through `pg_isready`.
  • Save `compose.yaml`.
  • Confirm that `compose.yaml` appears beside the two folders in the Explorer sidebar.

The first runnable service definition is now stored on disk. Its read-only SQL mount points at the `sql` folder you created.

Is the database definition showing an error?

Check the indentation under `services`, `db`, `environment`, `volumes`, and `healthcheck`. YAML uses spaces to preserve nesting.

Still stuck? Help me compare my database service with the required compose.yaml structure.

  • In `compose.yaml`, locate the top-level `volumes:` block.
  • Insert the benchmark service immediately above that block by pasting this code:
  benchmark:
    build:
      context: .
      dockerfile: Dockerfile
    environment:
      DATABASE_URL: "postgresql://counter_user:counter_password@db:5432/counter_lab"
    volumes:
      - ./benchmark:/app:ro
    depends_on:
      db:
        condition: service_healthy

How does the benchmark service connect?

  • The build uses the `Dockerfile` in `gangnam-counter-lab`.
  • The `DATABASE_URL` targets the `db` service through Docker Compose networking.
  • The `/app` mount exposes your local `benchmark` folder inside the container.
  • The health dependency delays the benchmark container until the database is ready.
  • Save `compose.yaml`.
  • Confirm that `benchmark:` remains indented beneath `services:`.

Your Compose file now defines both containers. The named volume remains at the top level beneath the service definitions.

Is the benchmark service misaligned?

Make sure `benchmark:` lines up with `db:`. Keep the final `volumes:` block aligned with `services:`.

Need another pair of eyes? Help me find the indentation problem in my benchmark service.

✔️ Awesome, I've got everything!

Great. Save `compose.yaml` before moving to the benchmark image.

ⓧ I'd like to double check the full code

services:
  db:
    image: postgres:18.6-bookworm
    environment:
      POSTGRES_USER: counter_user
      POSTGRES_PASSWORD: counter_password
      POSTGRES_DB: counter_lab
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U counter_user -d counter_lab"]
      interval: 2s
      timeout: 5s
      retries: 15

  benchmark:
    build:
      context: .
      dockerfile: Dockerfile
    environment:
      DATABASE_URL: "postgresql://counter_user:counter_password@db:5432/counter_lab"
    volumes:
      - ./benchmark:/app:ro
    depends_on:
      db:
        condition: service_healthy

volumes:
  postgres-data:
  • Create `requirements.txt` inside `gangnam-counter-lab` with the Explorer sidebar's New File control.
  • Pin the benchmark dependency by pasting this line into `requirements.txt`:
psycopg[binary]==3.3.6

Why pin the dependency?

Psycopg connects the Python benchmark to PostgreSQL. The `3.3.6` pin makes every image build install the same client-library version.

  • Save `requirements.txt`.
  • Confirm that `requirements.txt` appears in the Explorer sidebar.

The benchmark dependency is now explicit. The image build can reproduce it without installing Psycopg on macOS.

Does the dependency line look different?

Check that the package name is lowercase. Keep `[binary]`, both equals signs, and `3.3.6` together with no spaces.

Need help checking the pin? Help me verify my Psycopg requirement.

✔️ Awesome, I've got everything!

Your dependency pin is ready for the image build.

ⓧ I'd like to double check the full code

psycopg[binary]==3.3.6
  • Create `Dockerfile` inside `gangnam-counter-lab` with the Explorer sidebar's New File control.
  • Define the benchmark image by pasting this code into `Dockerfile`:
FROM python:3.14.8-slim-bookworm

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

ENTRYPOINT ["python"]

What does this image contain?

  • The base image pins Python to `3.14.8` on the slim Bookworm variant.
  • The working directory places benchmark commands inside `/app`.
  • The install layer reads `requirements.txt` to add Psycopg.
  • The entry point makes one-off benchmark commands run through Python.
  • Save `Dockerfile`.
  • Confirm that `Dockerfile` appears beside `compose.yaml` and `requirements.txt`.

The Explorer sidebar now shows all three environment files. The two empty subfolders are ready for the lab code added in later steps.

Is the Dockerfile named incorrectly?

Use the exact filename `Dockerfile` with no extension. Check that `requirements.txt` sits beside it inside `gangnam-counter-lab`.

Still unsure? Help me check the files used to build my benchmark image.

✔️ Awesome, I've got everything!

Your benchmark image definition is complete. Save every open file.

ⓧ I'd like to double check the full code

FROM python:3.14.8-slim-bookworm

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

ENTRYPOINT ["python"]
Start the healthy database

Docker Desktop runs the containers defined by your Compose file. The database health check keeps later commands from racing a PostgreSQL server that is still starting.

  • Press Cmd+Space to open macOS search.
  • Type `Docker Desktop` into the search field.
  • Press Enter to start Docker Desktop.
  • Wait until Docker Desktop reports that its engine is running.
  • Press Cmd+Space to reopen macOS search.
  • Type `Terminal` into the search field.
  • Press Enter to open macOS Terminal.
  • Move Terminal into the `gangnam-counter-lab` folder by running this command:
cd ~/Desktop/gangnam-counter-lab

What does this command do?

The `~` shortcut represents your macOS home folder. The command makes the Desktop copy of `gangnam-counter-lab` the current folder for every Compose command that follows.

The first database start can take a few minutes while Docker downloads the pinned image. A short wait during this first run is expected.

  • Start the database in the background by running this command:
docker compose up -d --wait db

What does this command do?

  • The `up` command creates the database container plus its network.
  • The `-d` option leaves the service running in the background.
  • The `--wait` option keeps the command active until the service passes its health check.
  • The `db` argument limits this start to the PostgreSQL service.

When the command returns successfully, the `db` service has accepted the readiness check. The `postgres-data` named volume now holds its local database files.

Did the database fail to become healthy?

Confirm that Docker Desktop is running. Check that Terminal is inside `~/Desktop/gangnam-counter-lab`.

A Compose parsing error usually points to indentation in `compose.yaml`. Help me troubleshoot why my db service did not become healthy.

Build and verify the benchmark environment

The benchmark image installs its own pinned Python runtime plus client library. Building it now proves that the local files can reproduce the environment without changing macOS.

The first image build can take a few minutes while Docker downloads layers. The command finishes by importing Psycopg inside a temporary container.

  • Build the benchmark image by running this command:
docker compose run --rm --build benchmark -c "import psycopg; print(psycopg.__version__)"

What does this command prove?

  • The `--build` option creates the benchmark image from `Dockerfile`.
  • The `--rm` option removes the one-off container after the check.
  • The Python expression imports Psycopg from the built image.
  • The printed version confirms which library the image installed.

The final output includes `3.3.6`. This confirms that the benchmark container can import the pinned Psycopg version.

Did the benchmark image fail to build?

Check that `Dockerfile` and `requirements.txt` are saved inside `gangnam-counter-lab`. Confirm that the dependency line contains `psycopg[binary]==3.3.6`.

Need help with the build output? Help me diagnose my benchmark image build.

Before you run both checks, predict whether both outputs will match the versions pinned in your files.

  • Verify PostgreSQL plus Psycopg from the running environment by running these commands:
docker compose exec db psql -U counter_user -d counter_lab -c "SELECT version();"
docker compose run --rm benchmark -c "import psycopg; print(psycopg.__version__)"

What do these checks inspect?

The first command asks the running database server for its version. The second command starts a one-off benchmark container to print the installed Psycopg version.

You'll see PostgreSQL `18.6` in the first result. You'll see `3.3.6` in the second result.

That is your first environment proof: the database plus benchmark client are running at the pinned versions. Future failures now come from the lab design you intentionally exercise.

Do the version checks fail?

A database connection failure can mean the `db` service is not healthy. A missing Psycopg import can mean the benchmark image was not rebuilt after saving `requirements.txt`.

Still blocked? Help me diagnose my PostgreSQL and Psycopg version checks.

Your local counter lab is reproducible and ready. Next, you will drive its first counter to the edge of the four-byte integer range.

Make the View Counter Overflow

Your local PostgreSQL lab is ready. In the last step, you proved that the database environment is healthy inside Docker Compose.

Now you can isolate the counter's first boundary risk. This step tests what happens when a valid increment reaches the edge of a signed integer's representable range.

In this step, get ready to:
  • Create a PostgreSQL table with a four-byte view counter.
  • Advance the counter to its maximum signed integer value.
  • Observe PostgreSQL's response to one increment beyond the boundary.
Create the overflow script

A boundary test starts close to the limit so the result is quick to reproduce. This SQL script seeds one row at 2147483646, one step below the largest integer value.

  • Switch back to Visual Studio Code from earlier.
  • Create sql/01-overflow.sql inside gangnam-counter-lab by using the new-file button above the file list.

You'll see the empty 01-overflow.sql file under the sql folder in the file sidebar.

  • Add the table definition and seed row to sql/01-overflow.sql by pasting this code:
CREATE TABLE video_counters (
    video_id bigint PRIMARY KEY,
    title text NOT NULL,
    views integer NOT NULL
);

INSERT INTO video_counters (video_id, title, views)
VALUES (1, 'Gangnam Style counter simulation', 2147483646);

What does this code do?

  • The CREATE TABLE statement defines the counter's three columns.
  • The video_id column acts as the primary key for each video.
  • The views column uses PostgreSQL's four-byte integer type.
  • The INSERT statement creates the Gangnam Style counter simulation row at 2147483646 views.
  • Save sql/01-overflow.sql.
  • Inspect the final value in the editor.

You'll see the seed row starts at 2147483646.

Is the file in the wrong folder?

  • Confirm 01-overflow.sql appears inside sql in the file sidebar.
  • Move the file into sql if it appears beside that folder.

Help me place sql/01-overflow.sql correctly.

The row now begins immediately below the boundary. The final chunk performs an atomic increment so the database changes the value without a separate read.

  • Append the final valid increment below the existing INSERT INTO video_counters statement by pasting this code:
UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
RETURNING video_id, title, views;

How does the increment work?

  • The UPDATE statement increments the stored value inside PostgreSQL.
  • The WHERE video_id = 1 condition targets the simulation row.
  • The RETURNING clause displays the updated row immediately.
  • Save sql/01-overflow.sql.
  • Inspect the final statement in the editor.

Your script now ends with a RETURNING video_id, title, views clause.

Does the final statement look different?

  • Check that SET views = views + 1 appears directly below the UPDATE video_counters line.
  • Check that every SQL statement ends with a semicolon.

Help me compare sql/01-overflow.sql with the expected script.

✔️ Awesome, I've got everything!

Your sql/01-overflow.sql file now contains the table definition, seed row, and final valid increment.

ⓧ I'd like to double check the full code

CREATE TABLE video_counters (
    video_id bigint PRIMARY KEY,
    title text NOT NULL,
    views integer NOT NULL
);

INSERT INTO video_counters (video_id, title, views)
VALUES (1, 'Gangnam Style counter simulation', 2147483646);

UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
RETURNING video_id, title, views;

How should the full file work?

  • The first statement defines the counter table with an integer view column.
  • The second statement seeds the counter one view below the boundary.
  • The final statement performs the last representable increment.
Run the final valid increment

The local sql folder is mounted into the running db service at /work/sql. Running the file through psql proves that PostgreSQL accepts the last value inside the column's range.

  • Switch back to the macOS Terminal window from earlier.
  • Execute the SQL file in the running db service by running this command:
docker compose exec db psql -U counter_user -d counter_lab -f /work/sql/01-overflow.sql

What does this command do?

  • The exec subcommand runs a command inside the existing db container.
  • The psql client connects as counter_user to counter_lab.
  • The -f option executes /work/sql/01-overflow.sql.

Good, your returned row shows 2147483647 views. The counter now sits at the highest value PostgreSQL's integer type can store.

Did the script fail to run?

  • Confirm sql/01-overflow.sql exists in the local folder mounted into the running container.
  • Confirm the db service from the previous step is still running.

Ask for help with the script execution error if the command still fails.

Trigger the next increment

The stored value is now exactly at the boundary. The next statement repeats the same atomic increment and asks the database to represent one more view.

Before you run this, do you expect the write to succeed or be rejected?

  • Attempt the next increment by running this command:
docker compose exec db psql -U counter_user -d counter_lab -c "UPDATE video_counters SET views = views + 1 WHERE video_id = 1;"

What just happened?

PostgreSQL rejects the write because integer cannot represent 2147483648.

The failed statement leaves the stored value at 2147483647. A one-row table can still hit a numeric capacity limit.

You have reproduced the boundary cleanly. A valid increment now fails because the next number falls outside the column's range.

Did the increment succeed?

  • Confirm the previous script run returned 2147483647 before repeating the increment.
  • Compare sql/01-overflow.sql with the full-file reference to confirm views still uses integer.
  • Run the script from the previous substep if the command reports a missing table.

Help me diagnose why the overflow command did not produce the expected range failure.

Your counter now fails at a real storage boundary on demand. Next, you'll widen the column while preserving the value that already reached the limit.

Migrate the Counter to bigint

Your PostgreSQL counter has reached 2147483647. The next valid view had nowhere to go inside its four-byte integer column.

That rejection isolated numeric capacity as the current problem. A schema migration widens views to bigint while preserving the stored count.

The same increment can then reach 2147483648 successfully.

In this step, get ready to:
  • Create a migration that widens the views column in video_counters.
  • Run the migration to advance the counter beyond its previous limit.
  • Inspect the table schema to confirm the new column type.
Write the widening migration

The failed write left the existing count untouched. The migration changes only the column type before retrying that increment.

  • Create sql/02-migrate.sql in Visual Studio Code's file sidebar by pasting the code below:
ALTER TABLE video_counters
ALTER COLUMN views TYPE bigint;

UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
RETURNING video_id, title, views;

What does this migration do?

  • The ALTER TABLE statement changes only the views column to bigint.
  • The UPDATE statement retries the increment that failed at the previous boundary.
  • The RETURNING clause displays the stored row after the increment.
  • Save sql/02-migrate.sql.

You should see 02-migrate.sql inside the sql folder in Visual Studio Code.

Migration File Missing?

Confirm that the file is named 02-migrate.sql. Check that it sits inside the existing sql folder.

Still cannot find the file? Help me check the location of my SQL migration file.

✔️ Awesome, I've got everything!

Your migration file is saved. It is ready to widen the counter before retrying the increment.

ⓧ I'd like to double check the full code

ALTER TABLE video_counters
ALTER COLUMN views TYPE bigint;

UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
RETURNING video_id, title, views;
Run the migration

The database still stores the valid value from the previous step. Running the migration applies the wider type before the next increment executes.

Before you run the migration, do you think the counter can now cross its previous limit?

  • Switch back to the macOS Terminal from earlier.
  • Apply sql/02-migrate.sql to the running database by executing this command:
docker compose exec db psql -U counter_user -d counter_lab -f /work/sql/02-migrate.sql

What does this command do?

  • The command runs psql inside the existing db service.
  • The -f option executes the mounted migration file against counter_lab.

You'll see the returned row with views set to 2147483648.

That is the boundary crossed. Your original count survived the migration.

Count Higher Than Expected?

A value above 2147483648 means the migration script has already run. Each rerun executes the increment again.

If the migration file cannot be found, confirm that sql still contains 02-migrate.sql.

Need help interpreting the result? Help me troubleshoot my counter migration output.

Confirm the new column type

The successful increment proves the value now fits. Inspecting the schema confirms that the storage type itself changed.

Before you inspect the table, which type do you expect to see beside views?

  • Inspect the video_counters schema by running this command:
docker compose exec db psql -U counter_user -d counter_lab -c "\d video_counters"

What does this command show?

The \d video_counters instruction displays the table's columns. It also displays the type assigned to each column.

In video_counters, you'll see views listed as bigint.

Still Seeing integer?

Confirm that the earlier migration command completed successfully. A failed migration leaves the column unchanged.

Check that the inspection command targets counter_lab and video_counters.

Still seeing the old type? Help me verify why views is still an integer.

The lab table is tiny, so this change completes quickly.

What Changes for a Large Table?

PostgreSQL generally takes an ACCESS EXCLUSIVE lock for ALTER TABLE. Changing an existing column type normally rewrites the table plus its indexes.

Production migrations need a planned lock window. They also need enough time for the rewrite. A safe rollout includes a rollback path.

Your counter now reaches 2147483648 with its original value intact. Next, you'll measure what happens when 32 clients target that one row.

Benchmark the Single Hot Row

Your PostgreSQL counter now stores values beyond the old four-byte integer limit. Numeric capacity is solved.

Next, you'll use Python with Psycopg to send concurrent increments at one counter row. The result gives you a baseline for the sharded design you'll test next.

A wider integer leaves a separate bottleneck called row-lock contention. When one transaction holds the row lock, competing transactions wait before applying their updates.

In this step, get ready to:
  • Build a benchmark that sends concurrent transactions to one counter row.
  • Measure elapsed time plus recorded views for the single-row design.
  • Save the throughput result as a baseline for the sharded benchmark.
Define the concurrent workload

The benchmark needs a fixed workload so later comparisons stay fair. It uses 32 clients with 10 transactions each.

  • Use the new-file control beside the benchmark folder in Visual Studio Code to create benchmark.py.

You should now see benchmark.py inside the benchmark folder.

  • Add the benchmark imports plus its fixed workload settings by copying this code into benchmark.py:
import os
import random
import sys
import time
from concurrent.futures import ThreadPoolExecutor

import psycopg

CLIENTS = 32
TRANSACTIONS_PER_CLIENT = 10
SHARD_COUNT = 16
LOCK_HOLD_SECONDS = 0.01
DSN = os.environ["DATABASE_URL"]

SINGLE_UPDATE = """
UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
"""

SHARDED_UPDATE = """
UPDATE counter_shards
SET views = views + 1
WHERE video_id = 1 AND shard_id = %s
"""

What does this setup define?

  • The imports provide database access plus timing. They also provide concurrent worker execution.
  • CLIENTS and TRANSACTIONS_PER_CLIENT create a fixed workload of 320 attempted transactions.
  • LOCK_HOLD_SECONDS keeps each acquired lock for 0.01 seconds. This intentional delay makes waiting visible during a short local test.
  • DSN reads the existing DATABASE_URL from the benchmark container.
  • SINGLE_UPDATE performs an atomic increment against the single counter row.
  • SHARDED_UPDATE prepares the same script for the next benchmark mode.
  • Save benchmark.py.
  • Confirm the file currently ends with the closing triple quotes for SHARDED_UPDATE.

File or constants missing?

Check that benchmark.py sits inside the existing benchmark folder. Confirm each constant uses the same capitalization shown above.

Still stuck? Help me check the imports and workload settings in benchmark.py.

Each benchmark run starts from a known count. The reset function makes the recorded total directly comparable with the number of attempted transactions.

  • Append the counter reset function to benchmark.py by copying this code below SHARDED_UPDATE:
def reset_counter(mode):
    with psycopg.connect(DSN) as conn:
        if mode == "single":
            conn.execute(
                "UPDATE video_counters SET views = 0 WHERE video_id = 1"
            )
        else:
            conn.execute(
                "UPDATE counter_shards SET views = 0 WHERE video_id = 1"
            )

What does this function do?

  • reset_counter() opens a database connection using DSN.
  • The single branch resets the existing video_counters row to zero before timing begins.
  • The other branch supports the sharded mode introduced in the next step.
  • Save benchmark.py.
  • Confirm the editor shows reset_counter as a defined function.

Reset function looks incomplete?

Check that both database updates remain inside the connection context. Match the indentation of each branch to the reference.

Need another pair of eyes? Help me check the reset_counter function and its indentation.

A worker represents one concurrent client. Every worker opens its own connection before committing ten transactions.

  • Append the client worker function below reset_counter() by copying this code:
def run_client(mode):
    with psycopg.connect(DSN) as conn:
        for _ in range(TRANSACTIONS_PER_CLIENT):
            with conn.transaction():
                if mode == "single":
                    conn.execute(SINGLE_UPDATE)
                else:
                    shard_id = random.randrange(SHARD_COUNT)
                    conn.execute(SHARDED_UPDATE, (shard_id,))
                time.sleep(LOCK_HOLD_SECONDS)
    return TRANSACTIONS_PER_CLIENT

How does one client behave?

  • run_client() opens an independent database connection for one worker.
  • The loop performs 10 committed transactions through that connection.
  • The single branch sends every transaction to the same row through SINGLE_UPDATE.
  • time.sleep() extends the lock hold. Competing workers therefore spend more time waiting on the shared row.
  • The return value reports how many transactions that worker attempted.
  • Save benchmark.py.
  • Confirm the editor shows run_client beneath reset_counter.

Worker function marked as invalid?

Check that return TRANSACTIONS_PER_CLIENT aligns with the connection context. Confirm the sleep remains inside the transaction context.

Need help finding the mismatch? Help me debug the run_client function in my benchmark script.

Add result reading and reporting

Attempt counts only describe what the workers tried to do. The benchmark also reads the persisted value so it can prove whether every increment reached the database.

  • Append the persisted-total reader below run_client() by copying this code:
def read_total(mode):
    with psycopg.connect(DSN) as conn:
        if mode == "single":
            row = conn.execute(
                "SELECT views FROM video_counters WHERE video_id = 1"
            ).fetchone()
        else:
            row = conn.execute(
                "SELECT SUM(views) FROM counter_shards WHERE video_id = 1"
            ).fetchone()
    return int(row[0])

Why read the total afterward?

  • read_total() queries the database after every worker has finished.
  • The single branch reads the value stored in video_counters.
  • The sharded branch is ready to aggregate the shard rows in the next step.
  • The integer return value lets the report compare recorded views with attempted transactions.
  • Save benchmark.py.
  • Confirm the editor shows read_total beneath run_client.

Total reader marked as invalid?

Check that each query ends with fetchone(). Confirm return int(row[0]) sits outside the connection context.

Still seeing a problem? Help me debug the read_total function in benchmark.py.

The final function coordinates the reset plus the concurrent workload. It measures the complete run before printing correctness plus throughput.

  • Complete benchmark.py by appending this code below read_total().
def main():
    if len(sys.argv) != 2 or sys.argv[1] not in {"single", "sharded"}:
        raise SystemExit("Usage: python benchmark.py [single|sharded]")

    mode = sys.argv[1]
    reset_counter(mode)

    started = time.perf_counter()
    with ThreadPoolExecutor(max_workers=CLIENTS) as executor:
        completed = sum(executor.map(run_client, [mode] * CLIENTS))
    elapsed = time.perf_counter() - started

    recorded = read_total(mode)
    print(f"mode: {mode}")
    print(f"concurrent clients: {CLIENTS}")
    print(f"attempted transactions: {completed}")
    print(f"recorded views: {recorded}")
    print(f"elapsed seconds: {elapsed:.3f}")
    print(f"views per second: {recorded / elapsed:.1f}")
    print(f"all increments recorded: {recorded == completed}")


if __name__ == "__main__":
    main()

How does the report come together?

  • main() accepts either the single mode or the later sharded mode.
  • reset_counter() removes the previous count before the timer starts.
  • ThreadPoolExecutor launches a thread pool with 32 workers.
  • time.perf_counter() measures the elapsed duration of the concurrent workload.
  • The final comparison proves whether the persisted total matches every attempted transaction.
  • Save the completed benchmark.py file.
  • Compare your file with the complete reference below.

Script structure looks different?

Confirm main() appears after every helper function. Check that the final call remains inside the __main__ condition.

Need help comparing the complete file? Help me identify differences in my benchmark.py file.

✔️ Awesome, I've got everything!

Your benchmark.py file now contains the workload plus its correctness report. Make sure the file is saved before you run it.

ⓧ I'd like to double check the full code

import os
import random
import sys
import time
from concurrent.futures import ThreadPoolExecutor

import psycopg

CLIENTS = 32
TRANSACTIONS_PER_CLIENT = 10
SHARD_COUNT = 16
LOCK_HOLD_SECONDS = 0.01
DSN = os.environ["DATABASE_URL"]

SINGLE_UPDATE = """
UPDATE video_counters
SET views = views + 1
WHERE video_id = 1
"""

SHARDED_UPDATE = """
UPDATE counter_shards
SET views = views + 1
WHERE video_id = 1 AND shard_id = %s
"""


def reset_counter(mode):
    with psycopg.connect(DSN) as conn:
        if mode == "single":
            conn.execute(
                "UPDATE video_counters SET views = 0 WHERE video_id = 1"
            )
        else:
            conn.execute(
                "UPDATE counter_shards SET views = 0 WHERE video_id = 1"
            )


def run_client(mode):
    with psycopg.connect(DSN) as conn:
        for _ in range(TRANSACTIONS_PER_CLIENT):
            with conn.transaction():
                if mode == "single":
                    conn.execute(SINGLE_UPDATE)
                else:
                    shard_id = random.randrange(SHARD_COUNT)
                    conn.execute(SHARDED_UPDATE, (shard_id,))
                time.sleep(LOCK_HOLD_SECONDS)
    return TRANSACTIONS_PER_CLIENT


def read_total(mode):
    with psycopg.connect(DSN) as conn:
        if mode == "single":
            row = conn.execute(
                "SELECT views FROM video_counters WHERE video_id = 1"
            ).fetchone()
        else:
            row = conn.execute(
                "SELECT SUM(views) FROM counter_shards WHERE video_id = 1"
            ).fetchone()
    return int(row[0])


def main():
    if len(sys.argv) != 2 or sys.argv[1] not in {"single", "sharded"}:
        raise SystemExit("Usage: python benchmark.py [single|sharded]")

    mode = sys.argv[1]
    reset_counter(mode)

    started = time.perf_counter()
    with ThreadPoolExecutor(max_workers=CLIENTS) as executor:
        completed = sum(executor.map(run_client, [mode] * CLIENTS))
    elapsed = time.perf_counter() - started

    recorded = read_total(mode)
    print(f"mode: {mode}")
    print(f"concurrent clients: {CLIENTS}")
    print(f"attempted transactions: {completed}")
    print(f"recorded views: {recorded}")
    print(f"elapsed seconds: {elapsed:.3f}")
    print(f"views per second: {recorded / elapsed:.1f}")
    print(f"all increments recorded: {recorded == completed}")


if __name__ == "__main__":
    main()

How to use this reference

Compare the imports plus constants first. Then compare each function in order.

Pay close attention to indentation inside the connection contexts plus transaction context. Python uses that indentation to define which statements belong together.

Run the single-row benchmark

The existing Docker Compose service runs the script inside the prepared benchmark image. Every worker targets the same database row in single mode.

  • Predict whether all 320 transactions can be recorded while the workers wait on one row.
  • Run the single-row benchmark from the existing gangnam-counter-lab Terminal by running this command:
docker compose run --rm benchmark benchmark.py single

What does this command do?

The command starts a one-off benchmark container with the existing service configuration. The single argument selects the hot-row workload.

The script resets the counter before starting 32 concurrent workers. Docker Compose removes the one-off container after the report finishes.

Your report shows concurrent clients: 32.

You should see attempted transactions: 320.

You should also see recorded views: 320.

The final line reads all increments recorded: True.

Your elapsed time plus views per second depend on your local machine. Those two values form the baseline for the next step.

That's your single-row baseline captured. The atomic updates preserved every view while the workers competed for one lock.

Benchmark command failed?

Confirm benchmark.py is saved inside the mounted benchmark folder. Check that the PostgreSQL database service is still healthy.

Seeing a Python error? Help me diagnose my single-row benchmark failure.

  • Record the displayed elapsed time here: your single-row elapsed seconds.
  • Record the displayed throughput here: your single-row views per second.

How should you interpret this result?

The single counter row acts as a hot key. Its updates remain correct because each transaction waits for access to the row.

The deliberate 0.01 second lock hold amplifies that waiting. Treat the result as a contention demonstration.

Local hardware affects the exact throughput. Docker resource limits can also affect the result.

You now have a correct single-row baseline with visible lock contention. Next, you'll distribute the same workload across 16 shards to measure how independent rows change write progress.

Shard the Counter and Measure the Difference

The widened counter now handles values beyond the 32-bit limit. Your single-row baseline also proved that 32 clients must queue behind one PostgreSQL row.

A sharded counter spreads increments across independent rows. More writes can make progress at the same time.

The current count now comes from SUM(views) across all 16 shards. This aggregate step introduces read amplification.

In this step, get ready to:
  • Create 16 independent shard rows for video 1.
  • Run the 320-transaction workload against those shards.
  • Evaluate the write-throughput and aggregate-read trade-off.
Create the 16 shard rows

Each row stores one slice of the logical view count. A composite primary key keeps each video_id and shard_id pair unique.

  • Switch back to Visual Studio Code.
  • Select the sql folder in the file sidebar.
  • Use the new-file control at the top of the sidebar.
  • Enter 03-shards.sql as the file name.

You should see an empty sql/03-shards.sql file inside the sql folder.

  • Populate sql/03-shards.sql by pasting this SQL:
CREATE TABLE counter_shards (
    video_id bigint NOT NULL,
    shard_id smallint NOT NULL,
    views bigint NOT NULL,
    PRIMARY KEY (video_id, shard_id)
);

INSERT INTO counter_shards (video_id, shard_id, views) VALUES
    (1, 0, 0),
    (1, 1, 0),
    (1, 2, 0),
    (1, 3, 0),
    (1, 4, 0),
    (1, 5, 0),
    (1, 6, 0),
    (1, 7, 0),
    (1, 8, 0),
    (1, 9, 0),
    (1, 10, 0),
    (1, 11, 0),
    (1, 12, 0),
    (1, 13, 0),
    (1, 14, 0),
    (1, 15, 0);

SELECT COUNT(*) AS shard_count,
       SUM(views) AS total_views
FROM counter_shards
WHERE video_id = 1;

What Does This SQL Do?

  • The composite key gives every video a unique identity for each shard.
  • The 16 INSERT rows establish shard IDs from 0 through 15.
  • Each views value starts at 0.
  • The final query checks the shard count and the aggregate starting total.
  • Save sql/03-shards.sql.
  • Return to the macOS Terminal from earlier.
  • Create the shard table and inspect its starting total by running:
docker compose exec db psql -U counter_user -d counter_lab -f /work/sql/03-shards.sql

What Does This Command Do?

Docker Compose executes psql inside the running database service. The -f option loads the mounted SQL file.

You should see shard_count equal 16. The total_views value should equal 0.

Good progress. The database now holds 16 independent write targets for the same video.

Shard Table Not Ready?

  • Confirm the file is named 03-shards.sql inside the sql folder.
  • Confirm compose.yaml still mounts ./sql at /work/sql.
  • Check that every inserted shard_id appears once from 0 through 15.

Help me diagnose why the shard table or its 16 rows were not created.

✔️ Awesome, I've got everything!

Your saved file creates all 16 shards and confirms their aggregate starting value.

ⓧ I'd like to double check the full code

CREATE TABLE counter_shards (
    video_id bigint NOT NULL,
    shard_id smallint NOT NULL,
    views bigint NOT NULL,
    PRIMARY KEY (video_id, shard_id)
);

INSERT INTO counter_shards (video_id, shard_id, views) VALUES
    (1, 0, 0),
    (1, 1, 0),
    (1, 2, 0),
    (1, 3, 0),
    (1, 4, 0),
    (1, 5, 0),
    (1, 6, 0),
    (1, 7, 0),
    (1, 8, 0),
    (1, 9, 0),
    (1, 10, 0),
    (1, 11, 0),
    (1, 12, 0),
    (1, 13, 0),
    (1, 14, 0),
    (1, 15, 0);

SELECT COUNT(*) AS shard_count,
       SUM(views) AS total_views
FROM counter_shards
WHERE video_id = 1;

Compare this reference with your saved file before continuing.

Run and compare the sharded workload

The benchmark.py file from earlier already has a sharded path. Each transaction chooses one shard with random.randrange(SHARD_COUNT).

The deliberate lock hold remains 0.01 seconds for every transaction. This keeps the workload comparable with your single-row baseline.

Why Shard the Counter?

A single counter row gives every transaction the same lock target. Independent shard rows give workers multiple lock targets.

The trade-off moves to the read path. SUM(views) must aggregate the shard values to recover the current total.

Before you run the sharded mode, do you expect its throughput to land above or below your single-row baseline?

  • Run the same 320-transaction workload across 16 shards by running:
docker compose run --rm benchmark benchmark.py sharded

How to Read the Report

  • The first line identifies mode: sharded.
  • The report shows attempted transactions: 320.
  • The persisted total appears as recorded views: 320.
  • The final correctness check reads all increments recorded: True.
  • Compare the sharded elapsed seconds value with the value from Step 4.
  • Compare the sharded views per second value with your single-row baseline.

The sharded run should show a shorter elapsed time in this deliberately amplified lab. Its throughput should be higher than the single-row baseline.

Interpret the Result Carefully

The 0.01 second lock hold intentionally amplifies contention. This makes the serialization difference visible during a short local run.

Treat the measured multiplier as local evidence. Docker resource limits can change it.

Background load can change the result. Random shard collisions also add variation.

  • Complete the correctness check by finding recorded views: 320 in the report.
  • Confirm the final line reads all increments recorded: True.

That's the comparison captured. All 320 increments survived while the workload gained 16 possible lock targets.

Sharded Benchmark Not Completing?

  • Return to the first substep if the benchmark cannot find the shard table.
  • Confirm sql/03-shards.sql reported 16 shards before retrying.
  • Close other heavy local workloads if the throughput result fluctuates sharply between runs.

Help me troubleshoot why my sharded benchmark did not record all 320 increments.

Your counter now preserves all 320 increments across 16 write targets. You can explain why lower write contention comes with an aggregate read cost.

Secret mission

Make Duplicate Retries Idempotent

Your sharded counter can absorb concurrent writes, but a retry can still count the same view twice. Add an event ledger that accepts one event identity once. Then attack it with 320 duplicate attempts and prove the counter moves by one.

Clean Up Your Resources

Clean Up Your Resources

Your counter lab runs entirely on your Mac, so there are no cloud resources or ongoing charges. Choose whether to keep the database running, pause it for later, or remove the local resources.

Resources you used:

  • The Docker Compose stack for gangnam-counter-lab with its PostgreSQL 18.6 db container.
  • The postgres-data named volume holding the counter_lab database.
  • The local gangnam-counter-lab folder containing your SQL scripts, benchmark code, Dockerfile, and Compose configuration.

Keep everything running

No action needed. Choose this if you plan to continue experimenting with overflow, contention, sharding, or idempotency.

  • Keep Docker Desktop running when you want the db container to remain available.
  • Retain the postgres-data volume so your tables and benchmark data remain available.
  • Reuse the gangnam-counter-lab files for more counter experiments.

Pause - I'll come back to this later

Shut down the running database container to free local memory. Your project files and database volume stay intact for your return.

  • Switch back to macOS Terminal in the gangnam-counter-lab folder.
  • Stop the running Compose services by running this command:
docker compose stop

What Does This Command Do?

The stop command shuts down the project services without removing their containers. The postgres-data volume keeps the database contents available.

You'll see Docker report that the db service has stopped. The postgres-data volume remains available.

  • Restart the database when you return by running this command:
docker compose up -d --wait db

How Does the Restart Work?

This starts the db service in the background. The --wait option waits until PostgreSQL passes its health check.

Delete - I don't want to use this again

Remove the Compose runtime, stored database data, and local project files if you want to start fresh.

Deletion Removes the Database

The cleanup command permanently removes the local lab database. Your SQL and Python files remain available until you delete the gangnam-counter-lab folder separately.

  • Switch back to macOS Terminal in the gangnam-counter-lab folder.
  • Remove the project containers, network, and named database volume by running this command:
docker compose down --volumes --remove-orphans

What Does This Cleanup Remove?

This command removes the project containers and network. It also removes the postgres-data volume containing video_counters, counter_shards, and view_events.

You'll see Docker report that the project containers and network were removed. The named volume holding counter_lab is removed during the same cleanup.

  • Use Finder to locate the gangnam-counter-lab folder at its existing saved location.
  • Move the gangnam-counter-lab folder to the Trash.
  • Empty the Trash to remove the local project files.
  • Confirm the gangnam-counter-lab folder no longer appears at its saved location.

Docker may retain the locally built benchmark image in its build cache. The cached image is inactive and creates no ongoing charge.

Nice Work!

Nice Work!

You did it! Your local PostgreSQL lab now demonstrates the evolution from numeric overflow to retry-safe counting with evidence you can rerun.

You've learned how to:

  • Reproduce a signed integer overflow at the four-byte boundary. Migrate the counter to bigint while preserving its value.
  • Benchmark one hot row with 32 concurrent clients. Identify row-lock contention as an independent throughput bottleneck.
  • Distribute writes across a 16-row sharded counter. Read the current total with an aggregate sum. Explain the resulting read amplification.
  • Secret Mission: Use idempotency to suppress 320 concurrent duplicate retries. Store one event. Record one view.

Ready to quiz yourself?