What is Database Partitioning?
Understand database partitioning, range/list/hash strategies, partition pruning, and how it improves query performance and maintenance.
Expected Interview Answer
Database partitioning is splitting a large table into smaller, more manageable pieces called partitions, while the table is still queried as one logical unit.
Partitioning can be done by range (dates or ID ranges), list (fixed category values), or hash (an even spread based on a hash function). Each partition can be stored, indexed, and queried independently, so a query that only needs recent data can skip scanning older partitions entirely, a technique called partition pruning. This improves query performance, simplifies maintenance like archiving old data, and helps manage very large tables that would otherwise be unwieldy.
- Faster queries via partition pruning
- Easier archiving and bulk deletes
- Improved maintenance on large tables
- Better parallelism across partitions
AI Mentor Explanation
A stadium groundstaff does not store every season’s pitch report in one giant folder; they file reports by year, so pulling up "2024 reports" means opening just that year’s folder instead of searching decades of records. Database partitioning works the same way: a huge table is split into smaller chunks, often by date range, so a query for recent rows only scans the relevant partition instead of the entire table.
Step-by-Step Explanation
Step 1
Choose a partition key
Pick a column like date, region, or ID to split the table by.
Step 2
Select a partitioning strategy
Use range, list, or hash partitioning based on data access patterns.
Step 3
Create partitions
The engine physically stores each partition as a separate segment.
Step 4
Query with pruning
Queries with a filter on the partition key only scan matching partitions.
What Interviewer Expects
- Understanding of range, list, and hash partitioning
- Knowledge of partition pruning and its performance benefit
- Awareness of maintenance benefits like easy archiving
- Ability to distinguish partitioning from sharding
Common Mistakes
- Confusing partitioning with database sharding across servers
- Not mentioning partition pruning as the performance win
- Forgetting that a poorly chosen partition key hurts performance
- Assuming partitioning changes the logical table structure for users
Best Answer (HR Friendly)
“Database partitioning means splitting a very large table into smaller pieces based on a column like date or region, while it still behaves as one table to anyone querying it. It makes queries faster because the database can skip partitions that do not match the filter, and it makes maintenance tasks like archiving old data much easier.”
Code Example
CREATE TABLE orders (
order_id INT,
order_date DATE NOT NULL,
amount NUMERIC
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
-- Only scans the orders_2026 partition
SELECT * FROM orders WHERE order_date >= '2026-01-01';Follow-up Questions
- What is the difference between partitioning and sharding?
- When would you choose hash partitioning over range partitioning?
- What is partition pruning and how does it improve performance?
- How does partitioning affect indexes on a table?
MCQ Practice
1. Database partitioning primarily helps with?
Partitioning divides one large table into smaller partitions, each queryable and maintainable independently.
2. Which partitioning strategy divides rows by fixed category values?
List partitioning assigns rows to partitions based on a matching set of discrete values, like country codes.
3. What does partition pruning do?
Partition pruning lets the query planner skip partitions that the WHERE clause rules out, speeding up execution.
Flash Cards
What is database partitioning? — Splitting a large table into smaller partitions while it is still queried as one logical table.
What is range partitioning? — Splitting rows into partitions based on value ranges, like dates or IDs.
What is partition pruning? — The query planner skips partitions that cannot contain matching rows, speeding up queries.
How does partitioning differ from sharding? — Partitioning splits a table within one database instance; sharding spreads data across multiple servers.