Sharding and partitioning in DBMS Theory - Mini Project: Build & Apply

Understanding Sharding and Partitioning in Databases

📖 Scenario: You are working with a large online store database that holds millions of customer orders. To improve performance and manage data efficiently, the database team wants to organize the data using sharding and partitioning techniques.

🎯 Goal: Build a simple conceptual model that shows how data can be divided using partitioning and sharding. You will create data groups, set rules for dividing data, and apply the main logic to separate data into shards and partitions.

📋 What You'll Learn

Create a data structure representing customer orders with order IDs and customer regions

Add a configuration variable to define the partitioning key (e.g., region)

Write logic to assign each order to a partition based on the region

Add a final step to assign each partition to a shard based on a shard ID

💡 Why This Matters

🌍 Real World

Sharding and partitioning help large databases handle huge amounts of data by splitting it into manageable pieces. This improves speed and reliability for online stores, social networks, and other big data systems.

💼 Career

Database administrators and backend engineers use sharding and partitioning to design scalable systems that can grow with user demand and keep data organized.

1

Create the initial data structure for orders

Create a dictionary called orders with these exact entries: 101: 'North', 102: 'South', 103: 'East', 104: 'West', and 105: 'North'. Each key is an order ID and each value is the customer region.

DBMS Theory

# Create the orders dictionary with order IDs and regions
# Your code here

Hint

Use curly braces to create a dictionary with order IDs as keys and regions as values.

2

Define the partitioning key

Create a variable called partition_key and set it to the string 'region'. This will represent the attribute used to divide data into partitions.

DBMS Theory

orders = {101: 'North', 102: 'South', 103: 'East', 104: 'West', 105: 'North'}
# Define the partitioning key
# Your code here

Hint

Assign the string 'region' to the variable partition_key.

3

Assign orders to partitions based on region

Create a dictionary called partitions where keys are region names and values are lists of order IDs from orders that belong to that region. Use a for loop with variables order_id and region to iterate over orders.items().

DBMS Theory

orders = {101: 'North', 102: 'South', 103: 'East', 104: 'West', 105: 'North'}
partition_key = 'region'
# Create partitions dictionary and assign orders to regions
# Your code here

Hint

Use a loop to check each order's region and add the order ID to the correct list in partitions.

4

Assign each partition to a shard

Create a dictionary called shards that assigns each region partition to a shard ID. Use these exact mappings: 'North': 1, 'South': 2, 'East': 1, 'West': 2. Then create a dictionary called sharded_data where keys are shard IDs and values are lists of order IDs from all partitions assigned to that shard.

DBMS Theory

orders = {101: 'North', 102: 'South', 103: 'East', 104: 'West', 105: 'North'}
partition_key = 'region'
partitions = {}
for order_id, region in orders.items():
    if region not in partitions:
        partitions[region] = []
    partitions[region].append(order_id)
# Assign partitions to shards and group orders by shard
# Your code here

Hint

Map each region to a shard ID and combine orders from partitions into the correct shard lists.

Practice

1. What is the main difference between sharding and partitioning in databases?

easy

A. Sharding divides data within one database; partitioning spreads data across multiple servers.

B. Partitioning divides data within one database; sharding spreads data across multiple servers.

C. Both sharding and partitioning mean the same and are used interchangeably.

D. Partitioning is used only for backups, while sharding is for data security.

Solution

1. Step 1: Understand partitioning

   Partitioning splits data inside a single database into smaller parts for easier management and faster queries.

2. Step 2: Understand sharding

   Sharding spreads data across multiple servers or machines to handle very large datasets and improve performance.

3. Final Answer:

   Partitioning divides data within one database; sharding spreads data across multiple servers. -> Option B

4. Quick Check:

   Partitioning = single database, Sharding = multiple servers [OK]

Hint: Partitioning = one DB; Sharding = many servers [OK]

Common Mistakes:

• Confusing sharding with partitioning
• Thinking both are the same
• Assuming partitioning involves multiple servers

2. Which of the following is a correct way to describe horizontal partitioning in a database?

easy

A. Splitting a table into multiple tables with the same columns but different rows.

B. Splitting a table into multiple tables with different columns but same rows.

C. Combining multiple tables into one large table.

D. Backing up the entire database to a separate server.

Solution

1. Step 1: Define horizontal partitioning

   Horizontal partitioning means dividing a table by rows, so each partition has the same columns but different sets of rows.

2. Step 2: Check options

   Splitting a table into multiple tables with the same columns but different rows. matches this definition exactly, while others describe different concepts or unrelated actions.

3. Final Answer:

   Splitting a table into multiple tables with the same columns but different rows. -> Option A

4. Quick Check:

   Horizontal partitioning = split rows [OK]

Hint: Horizontal partitioning splits rows, not columns [OK]

Common Mistakes:

• Mixing horizontal with vertical partitioning
• Thinking partitioning means backup
• Confusing rows with columns

3. Consider a database sharded by user ID across three servers: Server 1 stores users with IDs ending in 0-3, Server 2 stores 4-6, and Server 3 stores 7-9. If a query requests data for user ID 27, which server will handle the request?

medium

A. Server 3

B. Server 2

C. Server 1

D. All servers

Solution

1. Step 1: Identify the shard key and ranges

   The sharding is based on the last digit of user ID: 0-3 on Server 1, 4-6 on Server 2, 7-9 on Server 3.

2. Step 2: Find the last digit of user ID 27

   The last digit of 27 is 7, which falls in the 7-9 range assigned to Server 3.

3. Final Answer:

   Server 3 -> Option A

4. Quick Check:

   User ID 27 ends with 7, so Server 3 [OK]

Hint: Check last digit of ID to find server [OK]

Common Mistakes:

• Ignoring the last digit and guessing server
• Choosing all servers instead of one
• Mixing up the shard ranges

4. A database administrator tries to shard a database but notices that some shards have much more data than others, causing slow queries. What is the most likely problem?

medium

A. The backup process is running during queries.

B. The database is not partitioned vertically.

C. The database server hardware is outdated.

D. The shard key is not chosen properly, causing uneven data distribution.

Solution

1. Step 1: Understand shard key role

   The shard key determines how data is split across shards. A poor choice can cause uneven data distribution.

2. Step 2: Analyze the problem

   Uneven shard sizes causing slow queries usually mean the shard key is not distributing data evenly.

3. Final Answer:

   The shard key is not chosen properly, causing uneven data distribution. -> Option D

4. Quick Check:

   Uneven shards = bad shard key choice [OK]

Hint: Uneven shards? Check shard key choice [OK]

Common Mistakes:

• Blaming hardware without checking shard key
• Confusing sharding with partitioning issues
• Ignoring data distribution patterns

5. You have a large customer database that is partitioned by region within a single server. To improve performance and handle growth, you want to shard the data across multiple servers. Which approach best combines partitioning and sharding?

hard

A. Use only partitioning by region on one server; sharding is unnecessary.

B. Partition the database by customer type across servers, and shard data by region within each server.

C. Shard the database by region across servers, and within each server, partition data by customer type.

D. Backup the database regularly instead of sharding or partitioning.

Solution

1. Step 1: Understand combining sharding and partitioning

   Sharding splits data across servers; partitioning splits data inside each server for better management.

2. Step 2: Analyze the best approach

   Sharding by region spreads data geographically, and partitioning by customer type inside each shard improves query speed and organization.

3. Final Answer:

   Shard the database by region across servers, and within each server, partition data by customer type. -> Option C

4. Quick Check:

   Shard by region, partition by type inside servers [OK]

Hint: Shard first, then partition inside shards [OK]

Common Mistakes:

• Mixing up shard and partition levels
• Ignoring partitioning after sharding
• Thinking backup replaces sharding