🗄️ Introduction: The Oversized Drawer
Imagine you have a single physical filing cabinet drawer where you keep every invoice your business has ever sent over the last 10 years:
- The Problem: The drawer is bulging. To find an invoice from March 2025, you have to pull open the heavy drawer and search through 50,000 files.
- The Solution: You buy a cabinet with 12 drawers, one for each month.
- You file all January invoices in Drawer 1, February in Drawer 2, and so on.
- When searching for the March 2025 invoice, you go straight to Drawer 3. You don't even touch or look at the other 11 drawers!
In databases, this is Partitioning! It is the process of splitting one giant logical table into smaller, more manageable physical sub-tables (partitions) on the same disk server.
🏗️ Horizontal vs. Vertical Partitioning
There are two primary ways to slice a giant table:
1. Horizontal Partitioning (Shredding Rows)
Splitting a table by Rows. This is what most people mean by partitioning.
- Example: Splitting a
salestable intosales_2024,sales_2025, andsales_2026based on the date.
UNPARTITIONED TABLE PARTITIONED TABLE
+-----+------------+--------+ +---------------------------------+
| ID | Date | Amount | | PARTITION MASTER (Sales) |
+-----+------------+--------+ +---------------------------------+
| 1 | 2024-05-12 | $50 | / | \
| 2 | 2025-02-20 | $120 | v v v
| 3 | 2026-01-10 | $80 | [Sales_2024] [Sales_2025] [Sales_2026]
+-----+------------+--------+ | ID: 1 | ID: 2 | ID: 3 |
+------------+------------+------------+
2. Vertical Partitioning (Shredding Columns)
Splitting a table by Columns.
- Example: Splitting a
userstable with large binary images into:users_core(ID, Name, Email) - Small size, fast to search.users_blobs(ID, Profile_Picture_Raw) - Large size, rarely queried.
Partition Pruning (The Speed Secret)
When you query a partitioned table:
SELECT * FROM sales WHERE sale_date >= '2025-01-01' AND sale_date <= '2025-12-31';The database engine automatically ignores the sales_2024 and sales_2026 partitions. It reads data only from the sales_2025 partition. This bypass is called Partition Pruning!
💻 Code Examples
Let's see how we define partitioned tables in SQL and simulate range partitions in code.
SQL Setup (PostgreSQL Range Partitioning)
-- Create parent table
CREATE TABLE sales (
id INT,
amount DECIMAL,
sale_date DATE
) PARTITION BY RANGE (sale_date);
-- Create individual partitions
CREATE TABLE sales_y2025 PARTITION OF sales
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sales_y2026 PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');Multi-Language Execution
⚠️ Common Mistakes
1. Choosing the Wrong Partition Key
Partitioning on a column that is rarely filtered in your query WHERE clauses (e.g. partitioning by user_id but running queries like WHERE country = 'USA'). The database cannot prune partitions and has to search every single partition anyway, making the query slower than a standard table query!
2. Over-partitioning
Splitting your table into thousands of daily partitions. This forces the database to maintain thousands of active file descriptors, depleting memory and slowing down general connection queries.
🔍 Interview Corner
Q1: What is "Partition Pruning"?
Partition Pruning is a performance optimization technique where the database query optimizer analyzes the WHERE clause conditions and ignores any physical partitions that do not contain matching data, reducing disk I/O.
Q2: What is the difference between Horizontal and Vertical Partitioning?
- Horizontal Partitioning splits a table's rows across multiple sub-tables (e.g. grouping by year).
- Vertical Partitioning splits a table's columns across multiple sub-tables (e.g. separating frequently queried text columns from heavy binary image columns).
📝 Summary
- Partitioning splits a giant table into smaller sub-tables on the same server.
- Horizontal partitioning splits rows; Vertical partitioning splits columns.
- Partition Pruning speeds up queries by reading only matching partitions.