Data Partitioning in System Design
An orders table that grows by millions of rows a month eventually becomes painful. Queries slow down, indexes stop fitting in memory, and deleting last year's data locks the table for hours. Data partitioning solves this by splitting one large dataset into smaller, independent pieces that the database can manage separately.
This tutorial explains the types and methods, and shows how partition pruning speeds up queries. It pairs well with Database Sharding, which applies the same ideas across many servers.
1. What Is Data Partitioning?
Data partitioning is the practice of dividing a large dataset, such as a table, an index or a message stream, into smaller parts called partitions. Each partition holds a subset of the data according to a rule based on a partition key, and the system routes reads and writes to the right partition automatically.
Applications usually still see one logical table. The database decides where each row lives. When the partitions are spread across separate servers, the technique is called sharding; when they stay inside one database server, it is usually just called partitioning.
2. How a Partitioned Table Is Organised
The parent table holds no rows itself; every row is stored in exactly one child partition. A default partition catches rows that match no other partition.
3. How Data Partitioning Works
Consider an online store that partitions its orders table by month on order_date:
- Define the rule: the table is declared
PARTITION BY RANGE (order_date), and one partition is created for each month. - Write: the application inserts into
orders. The database compares the row'sorder_datewith each partition's bounds and stores the row in the matching one. - Read: for a query such as "total sales in November", the query planner checks the
WHEREclause against partition bounds and removes partitions that cannot contain matching rows. This is called partition pruning. - Maintain: each month a new partition is added, and old ones are archived or dropped as a single fast operation instead of a slow
DELETE.
Pruning works only when queries filter on the partition key.
4. Types of Partitioning and Partitioning Methods
Partitioning has two dimensions. The type describes what is split: rows, columns or whole domains. The method describes how rows are assigned to partitions: by range, by list, by hash or by a combination.
Horizontal partitioning
Splits a table by rows. Every partition has the same columns but holds a different subset of records, such as orders by month or customers by region. This is the most common form and what most databases mean by "partitioning".
Vertical partitioning
Splits a table by columns. Small, frequently read columns stay in one table, while large or rarely used columns such as biographies or image paths move to another table that shares the same key. Queries that need only the core columns read less data and keep more of it in memory.
Functional partitioning
Splits data by business domain. Orders, payments and users each get their own database, which is the data side of microservices: every service owns its store and changes it independently. See Microservices Architecture.
Range partitioning
Each partition owns one unbroken interval of the key, for example one calendar month or IDs 1 to 1,000,000. It is ideal for time-series data and retention because old ranges can be dropped whole, but the newest range receives all new writes and can become "hot".
List partitioning
Each partition holds an explicit list of values, such as the country codes DE, FR and NL for a European partition. It suits data that falls into known categories, and it is a simple way to keep regional data in the right place for data-residency rules.
Hash partitioning
A hash of the key decides the partition, which spreads rows evenly even when key values are skewed or sequential. It balances load well, but range queries must visit every partition, and changing the number of partitions means moving data.
Composite partitioning
Methods can be combined in levels. A common design partitions by month for easy retention and then by a hash of the customer inside each month to spread writes. Note that the primary key must then include the partition columns of every level. Distributed databases often use consistent hashing as their top level instead; see Load Balancing Algorithms.
-- Composite partitioning: range by month, then hash by customer inside each month
CREATE TABLE invoices (
invoice_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
invoice_date DATE NOT NULL,
total NUMERIC(10,2) NOT NULL,
PRIMARY KEY (invoice_id, invoice_date, customer_id) -- every partition key column
) PARTITION BY RANGE (invoice_date);
CREATE TABLE invoices_2026_11 PARTITION OF invoices
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01')
PARTITION BY HASH (customer_id);
CREATE TABLE invoices_2026_11_p0 PARTITION OF invoices_2026_11
FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE invoices_2026_11_p1 PARTITION OF invoices_2026_11
FOR VALUES WITH (MODULUS 2, REMAINDER 1);
Output:
invoice_id | customer_id | partition
------------+-------------+---------------------
1 | 101 | invoices_2026_11_p0
2 | 102 | invoices_2026_11_p0
3 | 103 | invoices_2026_11_p1
4 | 104 | invoices_2026_11_p0
These results came from PostgreSQL 15. All four invoices fall in the November range and are then spread across the two hash sub-partitions. A query that filters on both invoice_date and customer_id was planned as a single index scan on invoices_2026_11_p0.
5. Advantages and Disadvantages
Advantages
- Faster queries through partition pruning when filters use the partition key.
- Cheap data retention: dropping a partition is instant, while deleting millions of rows is slow and bloats the table.
- Smaller indexes per partition that are more likely to fit in memory.
- Easier maintenance and a path to sharding later.
Disadvantages
- Queries without the partition key scan every partition and can become slower.
- Primary keys and unique constraints must include the partition key.
- A poor partition key causes hot partitions and uneven load.
- New partitions must exist before data arrives, and too many small ones slow query planning.
6. Partitioning Methods Compared
| Method | Data distribution | Range queries | Typical use |
|---|---|---|---|
| Range | Can be uneven; newest range is busiest | Efficient | Time-series data, logs, retention |
| List | Depends on category sizes | Not applicable | Regions, tenants, data residency |
| Hash | Even | Scan all partitions | Spreading write load by user or session |
| Composite | Tunable | Efficient on the range part | Large multi-tenant time-series |
For the distributed side, see Database Sharding and Database Replication.
7. Real-World Use Cases
- Event and audit logs: monthly range partitions keep recent data fast and let old months be archived to cheap storage.
- Multi-tenant SaaS: list or hash partitioning by tenant isolates large customers and simplifies per-tenant exports.
- Messaging: Kafka divides every topic into partitions and assigns a record to a partition by hashing its key, so the events of a single order are read back in the order they were written.
- Wide-column stores: in Cassandra, the partition key decides which node stores a row and keeps related rows together on disk.
- Data residency: list partitions per region keep EU data in EU storage.
8. Best Practices
- Choose the key from your queries: partition by the column that most frequent and most expensive queries filter on, usually a date or tenant.
- Avoid hot partitions: if all writes hit one partition, add a hash component or a finer range.
- Create partitions ahead of time and keep a default partition, but monitor it: rows in the default partition usually mean a missing partition.
- Prefer fewer, larger partitions over thousands of tiny ones, and remember that an index on the parent is created on every partition.
- Apply retention on schedule by dropping or archiving old partitions.
- Test plans with EXPLAIN to confirm that important queries prune partitions as expected.
9. Summary
- Data partitioning splits a large dataset into smaller parts using a partition key.
- Horizontal partitioning splits rows, vertical splits columns, and functional splits by domain.
- Range, list, hash and composite methods decide which partition each row belongs to.
- Partition pruning makes queries faster only when they filter on the partition key.
- In PostgreSQL, primary keys must include the partition key.
- Partitions across multiple servers are shards; partitioning on one server is the first step.
Next, learn where large files such as images and backups belong in Object Storage.
Data Partitioning: Top Interview Questions
These are the data partitioning questions most often asked in system design interviews, with short answers you can explain in your own words.
Q1. What is the difference between partitioning and sharding?
Partitioning splits a dataset into parts using a partition key; the parts often live on the same database server. Sharding is partitioning where the parts are placed on different servers, so it also adds distributed concerns such as routing, cross-shard queries and rebalancing.
Q2. Why must a primary key include the partition key in PostgreSQL?
PostgreSQL enforces uniqueness inside each partition separately and has no global index across partitions. Including the partition key guarantees that two rows with the same key value would land in the same partition, so the local check is enough to keep the key unique across the whole table.
Q3. What is partition pruning, and when does it not happen?
Pruning is when the planner skips partitions that cannot contain rows matching the query. It does not happen when the query does not filter on the partition key, or when the filter cannot be compared with partition bounds, for example when a function is applied to the key column.
Q4. How do you choose a good partition key?
Pick the column that your most frequent and expensive queries filter on, that spreads data and traffic reasonably evenly, and that rarely changes. Dates suit time-series and retention; tenant or user IDs suit multi-tenant workloads.
Q5. What is a hot partition and how can you fix it?
A hot partition receives far more reads or writes than the others, for example the current month in a date-range scheme or one very large tenant. Fixes include adding a hash component to the key, using smaller ranges, or giving very large tenants their own partition.