Design Notion-Style Sharded Storage

Build a Python simulator and diagram for workspace-local database sharding.

Introduction

30 Second Summary

Behind every tidy workspace, a storage system decides where thousands of related records live. A poor routing choice can scatter one page across several databases.

In this project, you will build a Notion-inspired database sharding simulator. You will turn its routing model into an architecture diagram.

What You'll Build

Picture running your simulator to expose a four-database failure before proving that each workspace keeps a stable logical home as the physical fleet changes.

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

  • A deterministic fan-out failure demo where Acme, Beta, and Gamma each touch four databases for one workspace-level operation.
  • A workspace-local routing model that gives every block in a workspace one of 480 logical shards. You can compare the same shard assignments across 32-database and 96-database fleets.
  • An interview-ready architecture diagram for tracing an online request. The same scene shows a verified migration. A separate lane isolates offline data flows.
  • Secret Mission: Add a workload-skew detector that flags a hot workspace in both physical fleet layouts.

Are there any prerequisites?

Familiarity with basic system design concepts and simple Python functions is enough. You only need Python, Visual Studio Code, a browser, and Excalidraw.

Before We Start

This opening checkpoint locks in the reason behind the design you are about to model. Keeping a workspace's related records together avoids cross-database fan-out reads while allowing related writes to share one database transaction boundary.

Set Up the Simulator and Diagram Canvas

A sharding design has many moving parts. Running real databases now would bury the partition-key lesson under infrastructure setup.

In this step, you will create a one-line Python simulator in Visual Studio Code. You will also prepare an Excalidraw canvas for the final architecture view.

In this step, get ready to:
  • Create the local project folder and starter simulator file.
  • Validate the installed Python interpreter by running the simulator.
  • Prepare a blank diagram canvas for the architecture pass.
Create the local project

Visual Studio Code treats an opened folder as a workspace. This keeps the editor and integrated terminal anchored to the same project files.

Visual Studio Code may display a Workspace Trust prompt after opening the folder. You created this folder yourself, so you can trust its contents.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Click File in the menu bar.
  • Select Open Folder....
  • Select Desktop in the folder dialog.
  • Select New Folder.
  • Enter notion-sharding-simulator as the folder name.
  • Select Open to load the folder as your workspace.

Good start. You will see notion-sharding-simulator at the top of the Explorer sidebar.

  • Select Yes, I trust the authors if the Workspace Trust prompt appears.
  • Click New File... in the Explorer sidebar.
  • Enter shard_simulator.py as the file name.
  • Add the starter message to shard_simulator.py by pasting this code:
print("Shard simulator ready")

What does this starter do?

  • The print() call writes a fixed status message to the terminal.
  • The predictable message gives every later routing change a quick local test.
  • Save shard_simulator.py by pressing Cmd+S on macOS or Ctrl+S on Windows.
  • Confirm the Explorer sidebar lists shard_simulator.py inside notion-sharding-simulator.

Can't see the starter file?

  • Confirm the Explorer sidebar shows notion-sharding-simulator as the open folder.
  • Check that the file name ends with .py.

Help me find or create shard_simulator.py in Visual Studio Code.

✔️ Awesome, I've got everything!

Your project folder now contains the saved starter simulator. Keep shard_simulator.py open for the interpreter check.

ⓧ I'd like to double check the full code

Compare your saved shard_simulator.py with this complete starter file.

print("Shard simulator ready")

What should match?

Your file should contain this single print() call with the same capitalization and quotation marks.

Validate Python in the integrated terminal

The integrated terminal starts inside the folder that Visual Studio Code has open. Running the file here checks the interpreter against the exact simulator you just saved.

  • Click Terminal in the Visual Studio Code menu bar.
  • Select New Terminal.

Before you run the script, do you expect the terminal to print the message from your file? The next command checks your prediction.

  • Validate the installed interpreter by running this command:
python3 shard_simulator.py

What does this command do?

  • The python3 command starts the installed Python interpreter.
  • The shard_simulator.py argument tells the interpreter which file to run.

You will see Shard simulator ready in the terminal. Your first feedback loop is live because the local interpreter can run the simulator file.

Didn't see the starter message?

  • Confirm the terminal prompt is inside the notion-sharding-simulator folder.
  • Save shard_simulator.py before running the command again.
  • Check that the terminal recognizes the python3 command.

Help me debug why python3 shard_simulator.py does not print the starter message.

Open the diagram workspace

The simulator will prove routing behavior with output. The browser canvas will turn that behavior into an architecture you can trace visually.

  • Open the Excalidraw editor in your browser.
  • Keep the blank canvas open beside Visual Studio Code.

Before you test the canvas, what do you expect to change when you choose a drawing tool? The next click checks your prediction.

  • Select the rectangle tool from the top toolbar.

You will see the rectangle tool become active. This confirms the canvas is editable.

  • Press Escape to return to selection mode without drawing a shape.
  • Arrange the browser beside Visual Studio Code so the canvas and terminal are visible.

You will see Shard simulator ready in the integrated terminal. You will also see a blank Excalidraw canvas ready for the architecture diagram.

Your simulator and diagram canvas are ready. Next, you will make block-level routing fail and observe the resulting database fan-out.

Demonstrate Block-Level Fan-Out

Your local simulator already proves that Python can run from Visual Studio Code. The next question is whether a seemingly balanced partition key preserves the records that one workspace needs together.

Block-level routing spreads sequential IDs evenly across four targets. An ordinary page operation then becomes a fan-out read across every database.

In this step, get ready to:
  • Create deterministic records for Acme, Beta, and Gamma.
  • Route every block ID to one of four demo databases.
  • Expose the four-database fan-out for each workspace.
Add deterministic sample records

Random records could make the routing failure change between runs. Fixed workspaces make the result repeatable.

  • In shard_simulator.py, place your cursor on the blank line above the existing print("Shard simulator ready") line.
  • Paste this fixture block above the existing line:
NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "block_ids": [3000, 3001, 3002, 3003],
    },
]

Why use fixed records?

  • The NAIVE_DATABASE_COUNT value limits the demonstration to four database targets.
  • The WORKSPACES list gives each workspace four sequential block IDs.
  • Each block list covers every possible remainder produced by division by four. This guarantees the same routing result on every run.
  • Save shard_simulator.py.
  • Confirm Python can parse the expanded file by running this command in the integrated terminal:
python3 shard_simulator.py

What does this check prove?

The command executes shard_simulator.py with the validated interpreter. You should still see Shard simulator ready, which confirms that Python parsed the new fixtures successfully.

Seeing a syntax error?

Check that the fixture block sits above the existing print("Shard simulator ready") line. Confirm that every opening brace has a matching closing brace.

Compare the commas after each workspace dictionary with the snippet above. A missing comma can stop Python from parsing the list.

Help me find the syntax problem in my workspace fixtures.

Implement block-level routing

Modulo routing assigns each block to the database matching its remainder after division by four. Four sequential IDs therefore land on four different databases.

  • Select the existing print("Shard simulator ready") line in shard_simulator.py.
  • Replace it with the following routing program:
def naive_database_for_block(block_id):
    return block_id % NAIVE_DATABASE_COUNT


def show_naive_routing():
    print("Naive block-based routing across 4 databases")
    for workspace in WORKSPACES:
        databases = sorted(
            {naive_database_for_block(block_id) for block_id in workspace["block_ids"]}
        )
        print(
            "{0}: blocks touch databases {1} (fan-out: {2})".format(
                workspace["name"], databases, len(databases)
            )
        )


def main():
    show_naive_routing()


if __name__ == "__main__":
    main()

What does this code do?

  • The naive_database_for_block() function uses the modulo operator to return a database number from 0 through 3.
  • The set inside show_naive_routing() collects the distinct databases touched by one workspace.
  • The sorted() call keeps the database list in a stable order.
  • The main() function starts the demonstration whenever you run the file directly.
  • Save shard_simulator.py.
Run the fan-out demonstration

The simulator now has everything required to reveal the routing outcome. This run tests whether block-level balance preserves workspace locality.

Before you run it, which databases do you expect one workspace to touch? Your next run tests that prediction.

  • Run the completed block-level simulator in the integrated terminal:
python3 shard_simulator.py

What Should You See?

You should see the heading Naive block-based routing across 4 databases followed by one line for each workspace.

  • Acme, Beta, and Gamma each report databases [0, 1, 2, 3].
  • Each workspace line ends with fan-out: 4.
  • A page-level operation involving all four blocks needs four database requests. One transaction boundary cannot cover all four targets.

Still seeing the starter message?

Remove the original print("Shard simulator ready") line. Confirm that main() appears beneath show_naive_routing().

Check that if __name__ == "__main__": starts at the far left of the file. Keep the main() call indented beneath it.

Help me debug why my fan-out output does not appear.

You have exposed the intended failure. An even spread across databases has destroyed data locality for every sample workspace.

✔️ Awesome, I've got everything!

Everything is in place. Your saved simulator now reproduces the same four-database fan-out on every run.

ⓧ I'd like to double check the full code

NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "block_ids": [3000, 3001, 3002, 3003],
    },
]


def naive_database_for_block(block_id):
    return block_id % NAIVE_DATABASE_COUNT


def show_naive_routing():
    print("Naive block-based routing across 4 databases")
    for workspace in WORKSPACES:
        databases = sorted(
            {naive_database_for_block(block_id) for block_id in workspace["block_ids"]}
        )
        print(
            "{0}: blocks touch databases {1} (fan-out: {2})".format(
                workspace["name"], databases, len(databases)
            )
        )


def main():
    show_naive_routing()


if __name__ == "__main__":
    main()

What should the complete file contain?

The file contains one deterministic fixture set. It also contains one modulo router plus a reporting function that exposes the resulting fan-out.

Your simulator now proves why block-level balance creates expensive page reads. Next, you will route by workspace so every related block inherits one stable logical shard.

Route Workspaces to Logical Shards

Your simulator now proves that block-level routing causes four-way fan-out reads. An ordinary page operation reaches four databases before it can collect one workspace's blocks.

Workspace routing uses a UUID as the partition key. Every related block inherits one logical shard. This keeps the workspace inside one transaction boundary.

In this step, get ready to:
  • Assign fixed UUIDs to the three sample workspaces.
  • Convert each workspace UUID into one of 480 logical shards.
  • Verify every block in a workspace inherits one shard.
Give each workspace a fixed UUID

A fixed identifier makes every simulator run deterministic. The sample UUIDs represent positions spread across the full 128-bit UUID range.

  • Return to the open shard_simulator.py tab from earlier.
  • Replace the top data section with the following code:
import uuid


LOGICAL_SHARD_COUNT = 480
NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "workspace_id": uuid.UUID("10000000-0000-0000-0000-000000000001"),
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "workspace_id": uuid.UUID("70000000-0000-0000-0000-000000000001"),
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "workspace_id": uuid.UUID("d0000000-0000-0000-0000-000000000001"),
        "block_ids": [3000, 3001, 3002, 3003],
    },
]

What does this code do?

  • The uuid module provides the uuid.UUID class used to represent each workspace identifier.
  • LOGICAL_SHARD_COUNT fixes the logical routing space at 480 buckets.
  • workspace_id gives each sample workspace a stable routing key.
  • block_ids remains unchanged so the partition-key decision is the only meaningful difference.
  • Save shard_simulator.py.
  • Confirm the original failure case still runs by using this command:
python3 shard_simulator.py

What does this check prove?

The command runs the updated simulator with its new workspace identifiers. The unchanged naive output confirms that the original fan-out demonstration still works.

Good progress. The fixed workspace IDs are loaded while every workspace still reports four-way fan-out.

Does the simulator stop before printing?

Check that each uuid.UUID value has matching quotation marks. Confirm that every workspace dictionary ends with a comma.

Compare the top of shard_simulator.py with the code block above. A missing bracket can prevent the file from loading.

Help me fix the workspace UUID data in my shard simulator.

Map the UUID range to logical shards

Each UUID exposes its value as a 128-bit integer. Dividing that integer range into 480 equal buckets produces a deterministic logical shard number from zero through 479.

  • Find the existing naive_database_for_block function in shard_simulator.py.
  • Add the following routing function directly below it:
def logical_shard_for_workspace(workspace_id):
    return workspace_id.int * LOGICAL_SHARD_COUNT // (1 << 128)

How does the shard calculation work?

  • workspace_id.int converts the UUID into its 128-bit integer value.
  • 1 << 128 represents the size of the full UUID range.
  • Multiplying by LOGICAL_SHARD_COUNT scales the UUID's position into 480 buckets.
  • Integer division returns the bucket number that becomes the workspace's stable logical shard.
Print workspace-local routing

The final output keeps the failed block-level model visible for comparison. A second section routes each workspace once so all four blocks inherit the same destination.

  • Find the existing main function near the bottom of shard_simulator.py.
  • Replace that function through the final invocation with the following code:
def show_workspace_routing():
    print("\nWorkspace routing across 480 logical shards")
    for workspace in WORKSPACES:
        logical_shard = logical_shard_for_workspace(workspace["workspace_id"])
        print(
            "{0}: logical shard {1}; all {2} blocks stay together".format(
                workspace["name"], logical_shard, len(workspace["block_ids"])
            )
        )


def main():
    show_naive_routing()
    show_workspace_routing()


if __name__ == "__main__":
    main()

What does this code do?

  • show_workspace_routing calculates one logical shard for each workspace.
  • Every output line uses the workspace's block count to confirm that all four blocks share the result.
  • main prints the naive routing failure before printing the workspace-local result.
  • Save shard_simulator.py.

Before you run the simulator, do you expect the original block-based section to disappear or stay beside the workspace-based section?

  • Verify both routing models by running this command:
python3 shard_simulator.py

What should you see?

The naive section still reports fan-out: 4 for Acme, Beta, and Gamma. Each workspace still touches all four database indexes.

The workspace section maps Acme to logical shard 30. Beta maps to 210. Gamma maps to 390.

Each workspace reports that all four blocks stay together. One page operation now has a single logical destination.

That is the locality problem solved. Your simulator now demonstrates how a stable workspace key removes ordinary four-way fan-out.

Missing the workspace routing section?

Confirm that main calls show_workspace_routing after show_naive_routing.

If the shard numbers differ, compare each fixed UUID with the values above. A single changed hexadecimal character can select a different bucket.

Help me debug the logical shard output in my simulator.

✔️ Awesome, I've got everything!

Your saved simulator now prints the naive fan-out result followed by workspace-local routing. Acme, Beta, and Gamma remain together on logical shards 30, 210, and 390.

ⓧ I'd like to double check the full code

Compare your complete shard_simulator.py file with this reference:

import uuid


LOGICAL_SHARD_COUNT = 480
NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "workspace_id": uuid.UUID("10000000-0000-0000-0000-000000000001"),
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "workspace_id": uuid.UUID("70000000-0000-0000-0000-000000000001"),
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "workspace_id": uuid.UUID("d0000000-0000-0000-0000-000000000001"),
        "block_ids": [3000, 3001, 3002, 3003],
    },
]


def naive_database_for_block(block_id):
    return block_id % NAIVE_DATABASE_COUNT


def logical_shard_for_workspace(workspace_id):
    return workspace_id.int * LOGICAL_SHARD_COUNT // (1 << 128)


def show_naive_routing():
    print("Naive block-based routing across 4 databases")
    for workspace in WORKSPACES:
        databases = sorted(
            {naive_database_for_block(block_id) for block_id in workspace["block_ids"]}
        )
        print(
            "{0}: blocks touch databases {1} (fan-out: {2})".format(
                workspace["name"], databases, len(databases)
            )
        )


def show_workspace_routing():
    print("\nWorkspace routing across 480 logical shards")
    for workspace in WORKSPACES:
        logical_shard = logical_shard_for_workspace(workspace["workspace_id"])
        print(
            "{0}: logical shard {1}; all {2} blocks stay together".format(
                workspace["name"], logical_shard, len(workspace["block_ids"])
            )
        )


def main():
    show_naive_routing()
    show_workspace_routing()


if __name__ == "__main__":
    main()

Your workspace assignments are now stable. Next, you will place those logical shards across different physical database fleets without changing the workspace routing key.

Map Logical Shards to Physical Databases

Your simulator in Visual Studio Code now keeps every workspace's blocks on one logical shard. The next goal is to grow the physical database fleet without changing that stable assignment.

A physical placement layer translates each logical shard into a database number. This separates application-facing routing from machine placement.

Resharding changes where logical shards live. In this step, you will compare layouts with 32 databases and 96 databases while preserving every workspace's logical shard.

In this step, get ready to:
  • Add a validated logical-to-physical placement function.
  • Compare placement across two physical database fleets.
  • Verify that workspace shard assignments remain stable.
Add the physical placement function

The 480 logical shards need to divide evenly across the chosen fleet. The placement function checks that requirement before grouping contiguous logical shards onto physical databases.

  • In the shard_simulator.py tab from earlier, locate logical_shard_for_workspace().
  • Add the placement function directly below it by pasting this code:
def physical_database_for_logical_shard(logical_shard, physical_database_count):
    if physical_database_count <= 0:
        raise ValueError("Physical database count must be positive")
    if LOGICAL_SHARD_COUNT % physical_database_count != 0:
        raise ValueError("Physical database count must divide 480 evenly")
    if not 0 <= logical_shard < LOGICAL_SHARD_COUNT:
        raise ValueError("Logical shard is outside the valid range")

    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    return logical_shard // logical_shards_per_database

What does this function do?

  • The first check rejects a physical database count of zero or less.
  • The second check requires the fleet size to divide 480 evenly.
  • The range check keeps logical_shard within the valid shard space.
  • Integer division groups each contiguous shard range onto one physical database.
  • Save shard_simulator.py.
  • Check that the existing routing path still runs by using this command:
python3 shard_simulator.py

What does this check prove?

Your earlier naive routing output still reports four-way fan-out. Your workspace output still reports logical shards 30, 210, and 390.

The Python interpreter successfully parses the new function. Your existing routing behavior remains intact.

Simulator stopped after the new function?

Check that def physical_database_for_logical_shard begins at the left edge of the file. Keep every line inside the function indented consistently.

Confirm that all three closing parentheses match the target code above.

Help me debug the physical placement function.

Compare the fleet layouts

The routing report now needs a physical database count as input. It can use the same logical shard to calculate a different physical destination for each fleet layout.

  • In shard_simulator.py, locate the existing show_workspace_routing() function.
  • Replace the entire function with this version:
def show_workspace_routing(physical_database_count):
    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    print(
        "\nWorkspace routing across {0} physical databases "
        "({1} logical shards per database)".format(
            physical_database_count, logical_shards_per_database
        )
    )

    for workspace in WORKSPACES:
        logical_shard = logical_shard_for_workspace(workspace["workspace_id"])
        physical_database = physical_database_for_logical_shard(
            logical_shard, physical_database_count
        )
        print(
            "{0}: logical shard {1} -> physical database {2}; "
            "all {3} blocks stay together".format(
                workspace["name"],
                logical_shard,
                physical_database,
                len(workspace["block_ids"]),
            )
        )

How does the report change?

  • The function calculates logical_shards_per_database from the selected fleet size.
  • Each workspace keeps its result from logical_shard_for_workspace().
  • The placement function converts that stable shard into a physical database number.
  • The output shows the stable logical shard beside its current physical destination.
  • Locate the existing main() function near the bottom of shard_simulator.py.
  • Replace the entire function with this version:
def main():
    show_naive_routing()
    show_workspace_routing(32)
    show_workspace_routing(96)
    print(
        "\nThe logical shard stays stable while physical placement changes."
    )

How does the comparison run?

  • The first call preserves the naive block-routing baseline.
  • The next call tests the 32-database layout.
  • The following call tests the 96-database layout.
  • The final line states the separation between logical routing and physical placement.
Verify stable logical routing

The comparison succeeds when the fleet ratios change without moving any workspace to a different logical shard. The physical destinations can change because the number of logical shards assigned to each database changes.

  • Save shard_simulator.py.

Before the run, make your prediction about which values stay fixed when the physical database count changes.

  • Run both fleet layouts by using this command:
python3 shard_simulator.py

What should you see?

  • The 32-database layout reports 15 logical shards per database.
  • The 96-database layout reports 5 logical shards per database.
  • Acme retains logical shard 30. Beta retains logical shard 210. Gamma retains logical shard 390.
  • The final line reads The logical shard stays stable while physical placement changes.

Missing one of the fleet layouts?

Check that main() passes 32 into one call. Confirm that it passes 96 into the other call.

Compare the indentation inside show_workspace_routing() with the target function above.

Help me debug the missing fleet output.

That's the key separation working. Each workspace keeps a stable shard identity across both fleet layouts.

Keep the model honest

The contiguous grouping is a transparent teaching model based on Notion's published separation of logical shards from physical databases. Notion's full production routing implementation remains private.

✔️ Awesome, I've got everything!

Your saved shard_simulator.py now compares both physical database layouts while preserving every workspace's logical shard.

ⓧ I'd like to double check the full code

import uuid


LOGICAL_SHARD_COUNT = 480
NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "workspace_id": uuid.UUID("10000000-0000-0000-0000-000000000001"),
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "workspace_id": uuid.UUID("70000000-0000-0000-0000-000000000001"),
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "workspace_id": uuid.UUID("d0000000-0000-0000-0000-000000000001"),
        "block_ids": [3000, 3001, 3002, 3003],
    },
]


def naive_database_for_block(block_id):
    return block_id % NAIVE_DATABASE_COUNT


def logical_shard_for_workspace(workspace_id):
    return workspace_id.int * LOGICAL_SHARD_COUNT // (1 << 128)


def physical_database_for_logical_shard(logical_shard, physical_database_count):
    if physical_database_count <= 0:
        raise ValueError("Physical database count must be positive")
    if LOGICAL_SHARD_COUNT % physical_database_count != 0:
        raise ValueError("Physical database count must divide 480 evenly")
    if not 0 <= logical_shard < LOGICAL_SHARD_COUNT:
        raise ValueError("Logical shard is outside the valid range")

    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    return logical_shard // logical_shards_per_database


def show_naive_routing():
    print("Naive block-based routing across 4 databases")
    for workspace in WORKSPACES:
        databases = sorted(
            {naive_database_for_block(block_id) for block_id in workspace["block_ids"]}
        )
        print(
            "{0}: blocks touch databases {1} (fan-out: {2})".format(
                workspace["name"], databases, len(databases)
            )
        )


def show_workspace_routing(physical_database_count):
    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    print(
        "\nWorkspace routing across {0} physical databases "
        "({1} logical shards per database)".format(
            physical_database_count, logical_shards_per_database
        )
    )

    for workspace in WORKSPACES:
        logical_shard = logical_shard_for_workspace(workspace["workspace_id"])
        physical_database = physical_database_for_logical_shard(
            logical_shard, physical_database_count
        )
        print(
            "{0}: logical shard {1} -> physical database {2}; "
            "all {3} blocks stay together".format(
                workspace["name"],
                logical_shard,
                physical_database,
                len(workspace["block_ids"]),
            )
        )


def main():
    show_naive_routing()
    show_workspace_routing(32)
    show_workspace_routing(96)
    print(
        "\nThe logical shard stays stable while physical placement changes."
    )


if __name__ == "__main__":
    main()

Your simulator now separates stable workspace routing from physical placement. Next, you will diagram the online request path plus the migration and offline data flows.

Diagram Serving and Resharding Flows

Your simulator now proves that logical shards stay stable while physical placement changes. That result gives your architecture a reliable routing core.

The router explains where a request belongs. A production design also needs a safe way to move traffic.

Heavy offline jobs need their own path away from serving traffic. Your Excalidraw diagram will make all three paths traceable.

In this step, get ready to:
  • Draw the online path from a client request to its workspace tables.
  • Map replication and verification before the database cutover.
  • Separate the offline data path from latency-sensitive serving traffic.
Draw the online serving path

The online lane shows how an application request reaches the schema that owns its workspace. PgBouncer pools connections before PostgreSQL serves the correct logical schema.

  • Switch back to the blank Excalidraw canvas from earlier.
  • Create six boxes in one horizontal row.
  • Label the six boxes from left to right using Client -> Application Router -> PgBouncer -> Physical PostgreSQL Instance -> Logical Schema -> block and related tables.
  • Connect each neighboring box with a left-to-right arrow.
  • Add Online serving path above the row.
  • Add workspace UUID -> logical shard -> physical database inside the Application Router box.
  • Trace the lane from Client to block and related tables with your cursor.

That is the serving path mapped. You can now follow one workspace from the client to its related tables.

Why place PgBouncer in the path?

PgBouncer pools application connections before they reach PostgreSQL. Its shard mappings also provide a controlled point for moving traffic to a new physical database.

Is the serving lane difficult to trace?

Keep every box on one horizontal line. Make each arrow point in the same direction.

Ask for help refining the lane if the labels overlap. Help me make my Excalidraw online serving path easy to trace.

Map the migration and cutover

A stable logical shard can move between physical databases without changing the workspace partition key. Logical replication keeps the destination fleet updated during that move.

  • Add a second horizontal lane below the online serving lane.
  • Label the lane 2023 resharding path.
  • Place a group labeled Old 32-instance fleet on the left.
  • Place a group labeled New 96-instance fleet on the right.
  • Connect the old fleet to the new fleet with an arrow labeled PostgreSQL logical replication.

What does logical replication do?

PostgreSQL logical replication copies historical data to the new fleet. It continues applying new changes while the old fleet serves traffic.

  • Add a node labeled Sampled dark-read comparison below the fleet groups.
  • Connect both fleet groups to the comparison node.
  • Place a Verification passed gate after the comparison.
  • Connect the verification gate to a final PgBouncer mapping cutover box.

Why verify before cutover?

Sampled dark reads compare results from the old fleet with results from the new fleet. Application traffic still uses the established serving path during these checks.

The PgBouncer mapping changes only after the comparison passes. This makes verification a visible condition for cutover.

Before you trace this lane, which stage do you expect to sit between data copying and traffic cutover?

  • Trace the migration lane from Old 32-instance fleet to PgBouncer mapping cutover with your cursor.

You should pass through logical replication, the sampled dark-read comparison, and the verification gate before reaching cutover. You have now made the migration safety condition visible.

Does the cutover appear too early?

Move the cutover box after the verification gate. Make sure the dark-read comparison receives a connection from each fleet.

Use this prompt if the migration sequence still feels unclear. Help me check the order of my resharding diagram.

Separate offline data flows

The offline lane uses change data capture to copy PostgreSQL changes without putting analytical reads on the serving path. Debezium, Kafka, Apache Hudi, and Amazon S3 form that isolated flow.

  • Create a third horizontal lane below the migration lane.
  • Label the lane Offline workloads.
  • Create six boxes across the lane.
  • Label the six boxes from left to right using PostgreSQL -> Debezium CDC -> Kafka -> Apache Hudi -> Amazon S3.
  • Connect each neighboring box with a left-to-right arrow.
  • Annotate the lane with analytics, search, AI, and other non-serving workloads.

Why isolate the offline path?

Notion described its data lake as serving workloads that tolerate minutes to hours. A separate path keeps those workloads away from online requests with stricter latency needs.

  • Open Excalidraw's canvas menu.
  • Select Save to disk.
  • Save the editable scene as notion-sharding-system.excalidraw.
  • Confirm your browser reports a downloaded file named notion-sharding-system.excalidraw.

Cannot find the saved scene?

Check your browser's recent downloads for notion-sharding-system.excalidraw. Save the scene again if the filename or extension differs.

Use this prompt if Excalidraw does not download the editable scene. Help me save my Excalidraw scene as an editable file.

Before you perform the final trace, which path should reach the online block tables without passing through Kafka?

  • Trace the online request from Client to block and related tables.
  • Trace the migration from Old 32-instance fleet to PgBouncer mapping cutover.
  • Trace the offline flow from PostgreSQL to Amazon S3.

The online path ends at the workspace tables. The migration path reaches cutover only after verification.

The offline path ends at Amazon S3 without joining the serving lane. Your diagram now explains routing, migration safety, and workload isolation in one scene.

✔️ Awesome, I've got everything!

Your editable scene now contains three complete paths. The saved diagram matches the simulator's stable logical-to-physical routing model.

ⓧ I'd like to double check the full code

  • Confirm the online lane reads Client -> Application Router -> PgBouncer -> Physical PostgreSQL Instance -> Logical Schema -> block and related tables.
  • Confirm the router contains workspace UUID -> logical shard -> physical database.
  • Confirm the migration lane shows the old 32-instance fleet and the new 96-instance fleet.
  • Confirm the migration lane shows PostgreSQL logical replication and sampled dark-read comparison.
  • Confirm the verification gate appears before the PgBouncer mapping cutover.
  • Confirm the offline lane reads PostgreSQL -> Debezium CDC -> Kafka -> Apache Hudi -> Amazon S3.
  • Confirm the scene is saved as notion-sharding-system.excalidraw.

The simulator does not change during this step. Compare shard_simulator.py with this complete reference:

import uuid


LOGICAL_SHARD_COUNT = 480
NAIVE_DATABASE_COUNT = 4

WORKSPACES = [
    {
        "name": "Acme",
        "workspace_id": uuid.UUID("10000000-0000-0000-0000-000000000001"),
        "block_ids": [1000, 1001, 1002, 1003],
    },
    {
        "name": "Beta",
        "workspace_id": uuid.UUID("70000000-0000-0000-0000-000000000001"),
        "block_ids": [2000, 2001, 2002, 2003],
    },
    {
        "name": "Gamma",
        "workspace_id": uuid.UUID("d0000000-0000-0000-0000-000000000001"),
        "block_ids": [3000, 3001, 3002, 3003],
    },
]


def naive_database_for_block(block_id):
    return block_id % NAIVE_DATABASE_COUNT


def logical_shard_for_workspace(workspace_id):
    return workspace_id.int * LOGICAL_SHARD_COUNT // (1 << 128)


def physical_database_for_logical_shard(logical_shard, physical_database_count):
    if physical_database_count <= 0:
        raise ValueError("Physical database count must be positive")
    if LOGICAL_SHARD_COUNT % physical_database_count != 0:
        raise ValueError("Physical database count must divide 480 evenly")
    if not 0 <= logical_shard < LOGICAL_SHARD_COUNT:
        raise ValueError("Logical shard is outside the valid range")

    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    return logical_shard // logical_shards_per_database


def show_naive_routing():
    print("Naive block-based routing across 4 databases")
    for workspace in WORKSPACES:
        databases = sorted(
            {naive_database_for_block(block_id) for block_id in workspace["block_ids"]}
        )
        print(
            "{0}: blocks touch databases {1} (fan-out: {2})".format(
                workspace["name"], databases, len(databases)
            )
        )


def show_workspace_routing(physical_database_count):
    logical_shards_per_database = LOGICAL_SHARD_COUNT // physical_database_count
    print(
        "\nWorkspace routing across {0} physical databases "
        "({1} logical shards per database)".format(
            physical_database_count, logical_shards_per_database
        )
    )

    for workspace in WORKSPACES:
        logical_shard = logical_shard_for_workspace(workspace["workspace_id"])
        physical_database = physical_database_for_logical_shard(
            logical_shard, physical_database_count
        )
        print(
            "{0}: logical shard {1} -> physical database {2}; "
            "all {3} blocks stay together".format(
                workspace["name"],
                logical_shard,
                physical_database,
                len(workspace["block_ids"]),
            )
        )


def main():
    show_naive_routing()
    show_workspace_routing(32)
    show_workspace_routing(96)
    print(
        "\nThe logical shard stays stable while physical placement changes."
    )


if __name__ == "__main__":
    main()

Why is the simulator unchanged?

This step documents the serving and migration architecture around the routing model. The completed diagram adds operational context without changing the simulator's routing behavior.

Secret mission

Detect a Hot Workspace

Add synthetic write load to reveal Acme as a hotspot in both fleet layouts. Then mark a mitigation that isolates the exceptional workspace while preserving workspace-level locality.

Clean Up Your Resources

Clean Up Your Resources

Your Python simulator runs entirely on your computer. Your Excalidraw scene is also stored locally.

This project creates no cloud resources or ongoing costs. Choose the cleanup option that fits what you plan to do next.

Resources you used:

  • The local notion-sharding-simulator folder containing shard_simulator.py.
  • The saved notion-sharding-system.excalidraw architecture scene.

Keep everything running

No action is needed. Choose this if you plan to rerun the simulator or refine the architecture scene.

  • Keep the notion-sharding-simulator folder for future routing experiments.
  • Keep notion-sharding-system.excalidraw for future system design discussions.

Pause - I'll come back to this later

Close your local tools while keeping both artifacts. The simulator has no background service to stop.

  • Close the integrated terminal panel in Visual Studio Code.
  • Close Visual Studio Code.
  • Close the Excalidraw browser tab.

Your project folder and architecture scene remain available when you return.

Delete - I don't want to use this again

Remove both local project artifacts if you do not plan to revisit this design.

Deleting these files is permanent. Each platform path targets only the simulator folder and saved architecture scene.

macOS

  • Click Finder in the Dock.
  • Select Desktop in the Finder sidebar.
  • Move the notion-sharding-simulator folder to Trash.
  • Search Finder for notion-sharding-system.excalidraw.
  • Move the matching scene to Trash if it was saved outside notion-sharding-simulator.
  • Empty Trash to remove the artifacts permanently.
  • Search Finder again for notion-sharding-system.excalidraw to verify that no matching scene remains.

You should no longer see the project folder on your Desktop. Finder should return no matching architecture scene.

Windows

  • Select File Explorer from the Windows taskbar.
  • Select Desktop in the File Explorer sidebar.
  • Move the notion-sharding-simulator folder to the Recycle Bin.
  • Search File Explorer for notion-sharding-system.excalidraw.
  • Move the matching scene to the Recycle Bin if it was saved outside notion-sharding-simulator.
  • Empty the Recycle Bin to remove the artifacts permanently.
  • Search File Explorer again for notion-sharding-system.excalidraw to verify that no matching scene remains.

You should no longer see the project folder on your Desktop. File Explorer should return no matching architecture scene.

Nice Work!

Nice Work!

You did it! Your runnable Python shard simulator now turns database design choices into visible routing results. Your saved Excalidraw scene explains how the same ideas support serving, migration, verification, and offline workloads.

You've learned how to:

  • Expose four-database fan-out with deterministic block-level routing. Replace that failed partition key with workspace UUID routing that keeps related blocks together.
  • Separate 480 stable logical shards from physical placement across 32-instance and 96-instance database layouts. Show that fleet growth can preserve every workspace's logical assignment.
  • Present an interview-ready architecture diagram connecting PgBouncer, PostgreSQL logical replication, dark reads, and verified cutover. Isolate the offline change data capture path through Debezium, Kafka, Apache Hudi, and Amazon S3.
  • Secret Mission: Detect tenant-driven workload skew with HOTSPOT output in both fleet layouts. Explain how an exceptional workspace can be isolated while preserving workspace-level locality.

Ready to quiz yourself?