How Database Partitioning Works
IntermediatePartitioning splits one large table into smaller pieces (partitions) that the database manages as a single logical table. Queries touch only the relevant partitions (partition pruning), maintenance like archiving old data becomes cheap, and indexes stay smaller. Partitioning happens within one database server, which distinguishes it from sharding (across servers). Common strategies are range, hash, and list partitioning, chosen to match your query and retention patterns.
Think of partitioning as splitting a ledger by month
A single giant ledger for ten years of transactions is slow to search and unwieldy to store. Split it into one book per month and it is still one logical ledger, but now finding a March entry means opening only the March book — you skip the other 119. Archiving last year is as easy as boxing up those books. That is partitioning: one logical table, many physical slices you can target and manage independently.
Step by Step
Key Concepts
Range / Hash / List Partitioning
Range splits by value intervals (dates, IDs); hash spreads rows evenly by a hash of the key; list groups explicit values (regions). Choose by whether you need range scans, even distribution, or grouping.
Partition Pruning
The optimiser skipping partitions that cannot contain matching rows, based on a filter over the partition key. It is the main performance benefit — queries scan only relevant slices.
Partitioning vs Sharding
Partitioning divides a table within one database server; sharding distributes data across multiple servers. Partitioning improves manageability and query speed; sharding adds cross-server scale (and complexity).
Horizontal vs Vertical Partitioning
Horizontal partitioning splits rows across partitions (the usual meaning). Vertical partitioning splits columns — moving rarely-used or large columns into a separate table.
Key Facts
- Partitioning stays within one server; when you outgrow a single machine you need sharding, which partitions across servers.
- The biggest wins are partition pruning (faster queries) and instant data retention (drop a partition instead of a huge DELETE).
- Queries that do not filter on the partition key gain little — and may even be slower — so the partition key must align with your access patterns.
Real-World Applications
Time-series and logs
Partitioning an events table by month means dashboards querying recent data scan only a partition or two, and dropping data older than a year is a single fast DROP PARTITION.
Multi-region data
List partitioning by region keeps each region rows together, so region-scoped queries prune to one partition and data-residency rules are easier to enforce.
Frequently Asked Questions
What is the difference between partitioning and sharding?
Partitioning splits a table into smaller pieces within a single database server, managed as one logical table. Sharding distributes data across multiple database servers. Partitioning improves query performance and manageability on one machine; sharding scales beyond one machine but introduces cross-server queries and coordination complexity.
What is partition pruning?
It is the query optimiser skipping partitions that cannot contain matching rows, based on a filter over the partition key. For example, a query for last month data on a table partitioned by month reads only that month partition, scanning far less data than a single monolithic table.
What are the main partitioning strategies?
Range partitioning splits by value ranges such as dates or ID ranges (great for time-series and archiving). Hash partitioning spreads rows evenly by hashing the key (good for balanced distribution). List partitioning groups specific values like regions or categories. You choose based on your query and retention patterns.
When should I partition a table?
Partition when a table grows very large and your queries or maintenance suffer — especially time-series data where you filter by recent ranges and periodically drop old data. If queries filter on a natural key like date or region, partitioning by that key gives pruning and cheap retention. If they do not, the benefit is limited.