In this lesson, you will learn how to strategically decompose your database schema through vertical versus horizontal partitioning to resolve performance bottlenecks.
As your distributed system grows, a single table often becomes a bottleneck due to either extreme row counts or massive, infrequently accessed columns. You have two primary strategies to handle this: Vertical Partitioning and Horizontal Partitioning (Sharding).
Vertical Partitioning (Normalization by Feature)
Vertical partitioning involves splitting a table by its columns. You move rarely accessed or "heavy" columns (like large TEXT or BLOB fields) into a separate table, keeping only the frequently queried columns in the "hot" table.
When to use it:
- You have a "wide" table where a few columns are read constantly while others are accessed only by administrative background tasks.
- You want to improve cache hit ratios by reducing the row size in your main table.
-- Main Table (Hot)
CREATE TABLE users (user_id UUID PRIMARY KEY, username VARCHAR(50), email VARCHAR(100));
-- Vertical Partition (Cold/Extended)
CREATE TABLE user_profiles (user_id UUID PRIMARY KEY, biography TEXT, avatar_blob BYTEA);
Horizontal Partitioning (Sharding)
Horizontal partitioning involves splitting a table by its rows. You distribute rows across different database nodes, usually based on a partition key (e.g., user_id). Unlike vertical partitioning, the schema remains identical on every node; only the data subset changes.
When to use it:
- You hit the storage or compute limit of a single physical node.
- You need to parallelize write operations across multiple servers to maintain high availability.
-- Conceptual Sharding Logic
-- Node 1: Handles user_ids 1 to 500,000
-- Node 2: Handles user_ids 500,001 to 1,000,000
SELECT * FROM users WHERE user_id = 450000; -- Routed to Node 1
Deciding the Path
Deciding between the two depends on your primary bottleneck. If your queries suffer from high I/O due to scanning unnecessary columns, vertical partitioning reduces the data footprint. If your bottleneck is due to CPU/RAM saturation on a single node because your dataset is too massive, horizontal partitioning distributes the load, allowing your distributed system to scale linearly as nodes are added. Be aware that horizontal partitioning introduces the complexity of cross-shard transactions, which often necessitates the use of protocols like Two-Phase Commit or Paxos to maintain consistency across the cluster.
Key Takeaway: Choose vertical partitioning to optimize column-access patterns and cache performance; choose horizontal partitioning to scale your storage and throughput across multiple nodes.