00:00

Data Partitioning in System Design

Last updated:

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

Range-partitioned orders table with partition pruning An order service sends SQL to one parent table named orders, which is partitioned by range on order_date. The parent has four partitions: September 2026, October 2026, November 2026 and a default partition. A query for orders in November 2026 is sent to the parent table; the planner scans only the November partition and skips the other three. Order serviceSQL via JPA Queryorder_date inNovember 2026 ordersparent tableRANGE (order_date) orders_2026_09skipped (pruned) orders_2026_10skipped (pruned) orders_2026_11scanned orders_defaultskipped (pruned) Only partitions whose bounds can match the WHERE clause are read.
The application talks to one table. The database stores the rows in separate partitions and scans only the ones a query can match.

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:

  1. Define the rule: the table is declared PARTITION BY RANGE (order_date), and one partition is created for each month.
  2. Write: the application inserts into orders. The database compares the row's order_date with each partition's bounds and stores the row in the matching one.
  3. Read: for a query such as "total sales in November", the query planner checks the WHERE clause against partition bounds and removes partitions that cannot contain matching rows. This is called partition pruning.
  4. 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".

Horizontal partitioning of an orders table An orders table with four rows is split by order month. The two October rows move to the orders_2026_10 partition and the two November rows move to the orders_2026_11 partition. Both partitions keep the same three columns: id, order_date and amount. orders id order_date amount 1 2026-10-02 15.00 2 2026-10-19 8.50 3 2026-11-05 120.00 4 2026-11-17 75.50 orders_2026_10 id order_date amount 1 2026-10-02 15.00 2 2026-10-19 8.50 orders_2026_11 id order_date amount 3 2026-11-05 120.00 4 2026-11-17 75.50
Both partitions keep all three columns; only the rows are divided. October rows (amber) go to one partition and November rows (green) to the other.

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.

Vertical partitioning of a users table A users table with columns id, name, email, bio and avatar is split by columns. users_core keeps id, name and email; users_profile keeps id, bio and avatar. The id column appears in both so the rows can be joined back together. users id name email bio avatar 7 Asha asha@mail.com Loves hiking a7.png 9 Ben ben@mail.com Java developer b9.png users_core id name email 7 Asha asha@mail.com 9 Ben ben@mail.com users_profile id bio avatar 7 Loves hiking a7.png 9 Java developer b9.png
The columns are divided, and the id column is repeated in both tables so rows can be joined again. Small, frequently read columns (green) stay apart from bulky ones (amber).

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.

Functional partitioning by business domain One shared database containing orders, payments and users tables is split by business domain into three databases. The order service owns the orders database, the payment service owns the payments database, and the user service owns the users database. One databaseorders · payments· users Order service orders DB Payment service payments DB User service users DB one database per domain
Each domain gets its own database owned by one service. Other services request that data through the owning service's API instead of reading its tables directly.

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".

Range partitioning on order_date Four partitions sit side by side on a timeline: September, October, November and December 2026. A new row with order_date 2026-11-05 falls into the November range, so it is stored in orders_2026_11. Sep 2026orders_2026_09 Oct 2026orders_2026_10 Nov 2026orders_2026_11 Dec 2026orders_2026_12 new row: order_date = 2026-11-05 lower bound included, upper bound excluded: Nov = [2026-11-01, 2026-12-01)
Each partition owns one month. A row is stored in the range that contains its key, so 2026-11-05 lands in the November partition.

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.

List partitioning by region Customer rows with region codes DE, FR, IN, SG and BR are routed by an explicit value list. DE and FR go to customers_eu, IN and SG go to customers_asia, and BR, which is in no list, goes to the default partition customers_rest. customers_euIN ('DE','FR','NL') customers_asiaIN ('IN','SG','JP') customers_restDEFAULT DE FR IN SG BR
Each partition names the exact values it accepts. A value that is in no list, such as BR, falls into the default partition. These routes were confirmed on PostgreSQL 15.

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.

Hash partitioning of sessions by user_id Rows for user IDs 1, 2, 3 and 4 pass through hash(user_id) with modulus 4. PostgreSQL 15 stored user 1 in sessions_p0, user 3 in sessions_p1, user 2 in sessions_p2 and user 4 in sessions_p3. user_id 1 user_id 2 user_id 3 user_id 4 hash(user_id)modulus 4remainder = partition sessions_p0user 1 sessions_p1user 3 sessions_p2user 2 sessions_p3user 4
PostgreSQL hashes the key and uses the remainder to pick a partition, so consecutive IDs scatter across partitions and load stays even. The placements shown are real results from PostgreSQL 15.

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 then hash The invoices table is range-partitioned by invoice_date into October and November 2026 partitions. Each month is then hash-partitioned by customer_id into two sub-partitions, p0 and p1, giving four leaf partitions that actually store the rows. invoicesRANGE (invoice_date) invoices_2026_10HASH (customer_id) p0stores rows p1stores rows invoices_2026_11HASH (customer_id) p0stores rows p1stores rows
Rows are split by month first and then by a hash of customer_id inside each month. Only the four leaf partitions store data, and a query on both keys reads just one leaf.
composite.sql
-- 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

MethodData distributionRange queriesTypical use
RangeCan be uneven; newest range is busiestEfficientTime-series data, logs, retention
ListDepends on category sizesNot applicableRegions, tenants, data residency
HashEvenScan all partitionsSpreading write load by user or session
CompositeTunableEfficient on the range partLarge 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.