Course contentsShow
← Cloud and Infrastructure
Lesson 455 of 2,9898. Managed DatabasesFree lesson

Partitioning and Sharding Strategies

Learn horizontal and vertical partitioning to distribute data and improve query performance.

Partitioning and Sharding Strategies

What you'll learn: How to split large databases horizontally or vertically to distribute data and speed up queries.

The Core Problem

When a database grows too large, a single server struggles to handle all the data and queries efficiently. Partitioning and sharding are strategies that divide your data across multiple storage units to improve performance and scalability.

Vertical Partitioning

Vertical partitioning splits a table by columns. Think of it like organizing a filing cabinet where customer contact info goes in one drawer and purchase history goes in another.

For example, if you have a Users table with 50 columns, you might split it into:

  • UsersCore: frequently accessed fields (username, email, status)
  • UsersProfile: rarely queried fields (bio, preferences, metadata)

This reduces I/O when queries only need certain columns and can improve cache efficiency.

Horizontal Partitioning (Sharding)

Horizontal partitioning (often called sharding) splits a table by rows. Imagine dividing a phone book alphabetically: A-M in one volume, N-Z in another.

Common sharding strategies:

  • Range-based: Orders from 2020-2022 on Shard A, 2023-2024 on Shard B
  • Hash-based: Apply a hash function to user IDs to distribute evenly
  • Geographic: US customers on one shard, EU customers on another

Sharding distributes query load across multiple servers and allows parallel processing.

When to Use Each

Use vertical partitioning when different columns have different access patterns. Use sharding when table row count causes performance issues or you need horizontal scalability beyond a single database instance's capacity.

Both strategies work alongside techniques you've learned like read replicas, caching, and connection pooling.

Key Takeaway: Vertical partitioning splits tables by columns for different access patterns; horizontal partitioning (sharding) splits by rows to distribute data across servers, both improving query performance at scale.