Partitioning is the technique of dividing one large table or index into smaller physical pieces, called partitions, according to the value of a partition key, while the database still presents it as a single logical table. In database performance work, partitioning matters because it lets the engine skip irrelevant partitions during queries and lets administrators load, archive or drop data in bulk instead of row by row.
Partitioning in this sense usually means horizontal partitioning within one database server. When the pieces are placed on separate servers, the technique becomes sharding, which adds distribution concerns that ordinary partitioning does not have.
How partitioning divides a table
A partitioned table consists of a parent definition and several child partitions, each storing the rows whose partition key falls into its assigned range, list or hash bucket. Inserts are routed automatically to the correct partition, and each partition has its own storage, indexes and statistics.
The central performance mechanism is partition pruning: when a query filters on the partition key, the optimizer excludes partitions that cannot contain matching rows. Pruning happens at planning time for literal values and, in modern engines, also at execution time for parameters and join values. A query that filters a three-year table to one month can then scan one partition out of 36.
Partitioning also changes maintenance. Removing an old month becomes a metadata operation, such as dropping or detaching a partition, rather than a DELETE that generates undo, log volume and index churn for millions of rows.
Partitioning methods and when each applies
The partitioning method defines how key values map to partitions, and the right choice follows the dominant query filter and the data lifecycle.
| Method | How rows are assigned | Best fit | Watch out for |
|---|---|---|---|
| Range | Contiguous key intervals, such as one month per partition | Time series, logs, orders by date | Creating future partitions in advance |
| List | Explicit sets of values, such as country codes | Regions, tenants, categories | Uneven partition sizes |
| Hash | Hash of the key modulo the partition count | Even spread without a natural range | No pruning for range filters |
| Composite | Range first, then hash or list inside each range | Very large time-based tables | Many small partitions |
Vertical partitioning, splitting columns rather than rows into separate tables, is a related design technique. It keeps wide, rarely read columns such as large text bodies away from frequently scanned columns, but it is usually done in the schema rather than by the engine’s partitioning feature.
Partitioning compared with sharding and indexing
Partitioning, sharding and indexing all reduce the amount of data a query must read, but they work at different levels and carry different costs.
| Technique | Unit of division | Main benefit | Main cost |
|---|---|---|---|
| Partitioning | Table pieces on one server | Pruning and bulk lifecycle operations | Schema complexity, key in every unique constraint |
| Sharding | Data subsets on several servers | Write and storage scale-out | Cross-shard queries, distributed transactions |
| Indexing | Sorted access structure | Fast lookups of few rows | Slower writes, extra storage |
| Query optimization | Execution plan | Better plans for the same data | Requires analysis per query |
A frequent misconception is that partitioning replaces indexes. It does not: a well-indexed table of 50 million rows often answers point lookups as fast as a partitioned one. Partitioning pays off mainly for range scans on the key, for bulk data removal, and for keeping each index small enough to stay in memory.
Designing a partitioning scheme
A good partitioning scheme starts from the most common filter and the retention policy, not from table size alone. Tables below tens of gigabytes rarely benefit, because the overhead of planning across partitions can outweigh pruning gains.
| Decision | Recommended approach | Reason |
|---|---|---|
| Partition key | The column used in most WHERE clauses | Enables pruning on typical queries |
| Partition size | Roughly 1 to 50 GB each | Balances pruning against planning overhead |
| Partition count | Tens to low thousands | Very high counts slow planning and locking |
| Future partitions | Create ahead by automated job | Avoids insert failures at period boundaries |
| Retention | Drop or detach whole partitions | Avoids large deletes and table bloat |
Engines also impose structural rules. In PostgreSQL and MySQL, a primary key or unique constraint on a partitioned table must include the partition key, because uniqueness is enforced per partition. This often changes the primary key design and should be decided early.
Pitfalls of partitioning
Partitioning can make performance worse when queries do not match the scheme. Each pitfall below is visible in the execution plan, which should be checked for every important query after partitioning.
- Queries that do not filter on the partition key scan every partition and may run slower than before.
- Wrapping the key in a function, such as converting a timestamp to text, can disable pruning.
- Too many tiny partitions inflate planning time and memory use.
- Hash partitioning on a skewed key still produces hot partitions under uneven traffic.
- Missing future range partitions cause insert errors at midnight on the first day of a new period.
A plain-language summary
Think of a company that keeps all its invoices in one enormous drawer. Finding last March’s invoices means digging through everything. Partitioning is like giving each month its own folder while still calling the collection one archive.
To find March, you open one folder. To throw away invoices older than seven years, you discard whole folders instead of picking out sheets one by one. The method helps only if you usually search by month; if you search by customer name, the folders do not save much time.
An example from practice
A payment provider stores card transaction events in a table that grows by about 40 million rows per month and must keep 24 months of history. Dashboards query the last seven days, and a nightly job deleted expired rows, which took hours and left the table bloated.
The team converts the table to monthly range partitions on the event timestamp and adds an automated job that creates partitions three months ahead. Dashboard queries now touch one or two partitions, and their median time falls from several seconds to well under one second. Expiring a month becomes a single detach and drop that finishes in moments, and the nightly delete job is retired.