🔹 Partitioning in Databases
Partitioning means dividing a large table into smaller, more manageable pieces for performance, scalability, and easier maintenance.
There are two common types of partitioning: Horizontal and Vertical.
1. Horizontal Partitioning (Sharding)
- Definition: Splits a table into rows across multiple partitions based on some condition (like range, list, or hash).
- How it works: Each partition stores different rows, but with the same columns.
- Use Case: Useful when the dataset is huge and queries often deal with subsets of rows.
Example:
Table: Customers(id, name, city, phone)
Horizontal Partitioning by city:
- Partition 1: Customers where
city = 'Delhi' - Partition 2: Customers where
city = 'Mumbai' - Partition 3: Customers where
city = 'Bangalore'
👉 Each partition has the same schema (columns), but different rows.
2. Vertical Partitioning
- Definition: Splits a table into columns across multiple partitions.
- How it works: Each partition stores different columns, but with the same rows (primary key used as reference).
- Use Case: Useful when some columns are accessed frequently while others are rarely used (e.g., separating frequently accessed user info vs. large text fields).
Example:
Table: Customers(id, name, city, phone, address, profile_picture)
Vertical Partitioning:
- Partition 1:
(id, name, city, phone)→ frequently used columns - Partition 2:
(id, address, profile_picture)→ rarely used, large-size columns
👉 Each partition has different schema (columns), but same rows identified by primary key.
🔑 Key Differences
| Feature | Horizontal Partitioning | Vertical Partitioning |
|---|---|---|
| Basis of Split | Rows (data records) | Columns (attributes) |
| Schema | Same schema in all partitions | Different schema in each partition |
| Use Case | Large data volume across rows (scaling, sharding) | Reduce I/O by splitting frequently vs rarely used columns |
| Example | Split Customers by city | Split Customers into personal info vs. media info |
| Performance Gain | Query performance for row-based filtering | Query performance by reducing unnecessary column reads |
✅ Summary:
- Horizontal Partitioning = splitting by rows (like distributing customers by region).
- Vertical Partitioning = splitting by columns (like separating personal info from media files).