Partitioning #
The orders table that used to finish queries in 50ms now takes 4 seconds. Indexes exist. Queries are optimized. But the data keeps growing — now at 500 million rows — and there’s nothing more to do at the query level to restore performance. This is the point where partitioning becomes relevant: not as an initial optimization, but as a scalability solution when a table has grown beyond what regular indexes can handle.
Partitioning divides one logical table into several separate physical segments — called partitions. From the application’s perspective, the table still looks like one and queries are written normally. From the database’s perspective, only the relevant partitions are read, not the entire table. For a 500-million-row table partitioned by month, a query filtering one month only needs to read about 40 million rows — not half a billion.
But partitioning isn’t free. It brings new complexity: a wrong partition key actually makes queries slower, too many partitions burden the query planner, and queries that don’t include a partition key filter will scan every partition at once. Deeply understanding how partitioning works is a prerequisite for using it correctly.
How Partitioning and Partition Pruning Work #
Partitioning works by storing data in different physical segments based on the partition key value. When a query runs with a filter on the partition key, the database performs partition pruning — it determines which partitions could possibly contain the searched data, then only reads those partitions.
Without partitioning — the query scans the entire table:
Table orders (500 million rows, all in one physical segment):
┌─────────────────────────────────────────────────────────────┐
│ Jan 2023 │ Feb 2023 │ ... │ Dec 2024 │ Jan 2025 │ Feb 2025 │
└─────────────────────────────────────────────────────────────┘
← the database must scan all of this →
SELECT * FROM orders WHERE created_at >= '2025-02-01'
→ Scans 500 million rows to find ~5 million February 2025 rows
→ Wasteful I/O, slow, large and inefficient indexes
With monthly partitioning — the query only reads the relevant partition:
orders_2023_01 │ orders_2023_02 │ ... │ orders_2025_01 │ orders_2025_02
───────────────────────────────────────────────────────────────────────
↑
only this is read
SELECT * FROM orders WHERE created_at >= '2025-02-01'
→ Partition pruning: only orders_2025_02 is read
→ Scans ~5 million rows, not 500 million
→ I/O reduced by 99%, query much faster
The key to all of partitioning’s benefits is this pruning. If pruning doesn’t happen — for example, because the filter doesn’t include the partition key, or the partition key is wrapped in a function — all partitions are still read and there’s no performance benefit at all.
-- How to verify whether partition pruning happens:
EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-02-01';
-- The EXPLAIN output shows the 'partitions' column:
-- partitions: orders_2025_02 → pruning succeeded, only 1 partition
-- partitions: orders_2023_01,... → pruning failed, all partitions read
-- An example query that PREVENTS pruning (anti-pattern):
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- partitions: all partitions → the YEAR() function prevents pruning
-- Rewrite: WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
Four Partitioning Types #
Range Partitioning #
Range partitioning divides data by value ranges — most commonly used for time-based data. Each partition stores data whose partition key value falls within a certain range.
This is the most appropriate partitioning type for time-series data: logs, events, orders, transactions, audit trails. Queries almost always filter by time, and old data is easily removed by dropping a partition — an O(1) operation far faster than DELETE.
-- Range partitioning by month (MySQL)
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
total DECIMAL(15,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at) -- the partition key must be part of the primary key
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p2024_01 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
PARTITION p2024_03 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
-- ... and so on
PARTITION p2025_02 VALUES LESS THAN (UNIX_TIMESTAMP('2025-03-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE -- holds future data
);
-- PostgreSQL: range partitioning is cleaner with PARTITION BY RANGE
CREATE TABLE orders (
id BIGSERIAL,
user_id BIGINT NOT NULL,
total NUMERIC(15,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- Create monthly partitions
CREATE TABLE orders_2025_01
PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02
PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
Monthly range partitioning visualization:
orders
├── orders_2024_01 [Jan 2024: ids 1–42M]
├── orders_2024_02 [Feb 2024: ids 42M–81M]
├── orders_2024_03 [Mar 2024: ids 81M–125M]
│ ...
├── orders_2025_01 [Jan 2025: ids 450M–492M]
├── orders_2025_02 [Feb 2025: ids 492M–...] ← currently active partition
└── p_future [data after Feb 2025] ← fallback
Query: WHERE created_at >= '2025-02-01'
→ Only reads orders_2025_02 and p_future
→ 2 partitions out of 26 total → 92% pruning
Main advantage of range partitioning: deleting old data is as easy as running DROP PARTITION — no heavy DELETE, no fragmentation, no long locks. For a system with a 2-year data retention policy, for example, this is the most efficient maintenance operation.
-- Deleting old data: DROP PARTITION vs DELETE
-- Without partitioning — heavy DELETE, taking minutes to hours:
DELETE FROM orders WHERE created_at < '2024-01-01';
-- Long locks, large WAL, table fragmentation
-- With partitioning — DROP PARTITION: O(1), almost instant:
ALTER TABLE orders DROP PARTITION p2023_12;
-- No significant locks, no fragmentation, done in seconds
List Partitioning #
List partitioning divides data by known discrete values — suitable when data is grouped by fixed categories like country, region, or tenant.
-- List partitioning by region (PostgreSQL)
CREATE TABLE transactions (
id BIGSERIAL,
user_id BIGINT NOT NULL,
amount NUMERIC(15,2) NOT NULL,
region VARCHAR(10) NOT NULL, -- 'ID', 'SG', 'MY', 'TH', 'PH'
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY LIST (region);
CREATE TABLE transactions_id PARTITION OF transactions FOR VALUES IN ('ID');
CREATE TABLE transactions_sg PARTITION OF transactions FOR VALUES IN ('SG');
CREATE TABLE transactions_my PARTITION OF transactions FOR VALUES IN ('MY');
CREATE TABLE transactions_sea -- combine smaller regions
PARTITION OF transactions FOR VALUES IN ('TH', 'PH', 'VN');
CREATE TABLE transactions_other
PARTITION OF transactions DEFAULT; -- holds unlisted values
When list partitioning is appropriate vs not:
Appropriate if:
✓ The partition key values are known and relatively stable
✓ Queries almost always filter by those values
✓ There's a data isolation need per value (e.g. per-country data for compliance)
✓ The load per category is large enough to justify separation
Not appropriate if:
✗ Partition key values keep growing — every new value needs a new partition
✗ Data is uneven — one partition is far larger than the others
✗ Queries rarely filter by this column
If partition key values keep growing — for example, a city list or product categories that are constantly expanded — list partitioning can become an operational burden: every new value requires DDL to create a new partition. Consider whether range or hash is more appropriate for such cases.
Hash Partitioning #
Hash partitioning distributes data evenly based on the hash value of the partition key. Unlike range and list, there’s no semantics here — just balanced distribution.
-- Hash partitioning by user_id (MySQL) — even distribution
CREATE TABLE user_activities (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
activity VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id)
PARTITIONS 16; -- 16 partitions, data distributed evenly
-- user_id 1 → hash(1) mod 16 = partition N
-- user_id 2 → hash(2) mod 16 = partition M
-- etc — even distribution without hot spots
Data distribution comparison:
Range partitioning (by time):
Jan 2025 [━━━━━━━━━━━━━━━━━] ← all recent traffic lands here (hot partition)
Dec 2024 [━━━━━━━━━━]
Nov 2024 [━━━━━━━━]
Oct 2024 [━━━━━━━]
Hash partitioning (by user_id):
Partition 0 [━━━━━━━━━]
Partition 1 [━━━━━━━━━]
Partition 2 [━━━━━━━━━] ← uniform distribution
Partition 3 [━━━━━━━━━]
...all the same size
Hash partitioning solves the hot partition problem — a condition where one partition is accessed far more than the others. This often happens in time-based range partitioning: this month’s partition is always hotter than last month’s.
But hash partitioning can’t do range- or time-based partition pruning. A query like WHERE created_at >= '2025-02-01' must read all partitions because there’s no way to know which hash holds February’s data. Hash partitioning suits even write distribution, not time- or category-based queries.
Composite Partitioning #
Composite partitioning (or subpartitioning) combines two strategies at once — usually range at the top level and hash at the bottom level. Used for very large systems needing both: time-based pruning capability and even write distribution.
-- Composite: Range (month) + Hash (user_id) in PostgreSQL
-- Level 1: monthly partitions (for time-based pruning)
CREATE TABLE events (
id BIGSERIAL,
user_id BIGINT NOT NULL,
type VARCHAR(50) NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- Level 2: each monthly partition is subpartitioned by user_id
CREATE TABLE events_2025_02
PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01')
PARTITION BY HASH (user_id);
CREATE TABLE events_2025_02_h0 PARTITION OF events_2025_02 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2025_02_h1 PARTITION OF events_2025_02 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2025_02_h2 PARTITION OF events_2025_02 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_2025_02_h3 PARTITION OF events_2025_02 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Composite partitioning provides the most flexibility but also the most complexity. Use it only when the system is truly at a scale requiring it — hundreds of millions to billions of rows with very high write rates.
Choosing the Right Partition Key #
The partition key is the most important decision in designing partitioning. A wrong choice doesn’t just make partitioning useless — it can make the system slower than before partitioning.
A framework for choosing a partition key:
Question 1: What filter appears most often in queries?
→ If most queries filter by created_at → use created_at
→ If most queries filter by user_id → use user_id
→ If both → consider composite
Question 2: How evenly is the data distributed on that column?
→ created_at is usually even (data keeps arriving daily)
→ status is NOT even ('pending' few, 'completed' very many)
→ status is bad as a partition key
Question 3: Is there a maintenance need based on this value?
→ Want to delete data older than 2 years → created_at is ideal for range partitioning
→ Want per-tenant data isolation → tenant_id for list partitioning
Question 4: Are there hot spots to avoid?
→ If all writes go to the newest partition (range by time) → hot partition
→ Add a hash subpartition if the write rate is very high
Good partition key columns:
✓ created_at / event_date → time-series, natural pruning, easy maintenance
✓ region / country_code → natural distribution, data isolation
✓ tenant_id → multi-tenant isolation
✓ user_id (for hash) → even distribution
✗ status → low cardinality, uneven distribution
✗ boolean / is_active → only 2 values, useless
✗ columns that get changed → UPDATEs on the partition key move rows between partitions
Partition Lifecycle Automation #
Manually managed partitioning is a recipe for incidents. If next month’s partition is forgotten, every INSERT into that month fails. If old partitions are never dropped, storage grows without limit. Partition lifecycle must be automated from day one.
-- Monthly partition creation automation procedure (MySQL)
-- Stored as a stored procedure, run via the event scheduler at the start of each month
DELIMITER $$
CREATE PROCEDURE create_monthly_partition(p_year INT, p_month INT)
BEGIN
DECLARE partition_name VARCHAR(20);
DECLARE next_month_start VARCHAR(20);
DECLARE sql_stmt TEXT;
SET partition_name = CONCAT('p', p_year, '_', LPAD(p_month, 2, '0'));
SET next_month_start = DATE_FORMAT(
DATE_ADD(CONCAT(p_year, '-', p_month, '-01'), INTERVAL 1 MONTH),
'%Y-%m-%d'
);
SET sql_stmt = CONCAT(
'ALTER TABLE orders REORGANIZE PARTITION p_future INTO (',
'PARTITION ', partition_name,
' VALUES LESS THAN (UNIX_TIMESTAMP(''', next_month_start, ''')),',
'PARTITION p_future VALUES LESS THAN MAXVALUE)'
);
PREPARE stmt FROM sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- Event scheduler: create next month's partition at the start of each month
CREATE EVENT auto_create_partition
ON SCHEDULE EVERY 1 MONTH
STARTS '2025-03-01 00:00:00'
DO CALL create_monthly_partition(YEAR(DATE_ADD(NOW(), INTERVAL 1 MONTH)),
MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH)));
-- Old partition deletion procedure based on retention (MySQL)
-- Drop partitions older than 2 years
DELIMITER $$
CREATE PROCEDURE drop_old_partitions(retention_months INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE part_name VARCHAR(50);
DECLARE cutoff_date DATE;
SET cutoff_date = DATE_SUB(CURDATE(), INTERVAL retention_months MONTH);
-- Get the list of partitions past the retention limit
DECLARE partition_cursor CURSOR FOR
SELECT PARTITION_NAME
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'orders'
AND PARTITION_NAME != 'p_future'
AND STR_TO_DATE(CONCAT(
SUBSTRING(PARTITION_NAME, 2, 4), '-',
SUBSTRING(PARTITION_NAME, 7, 2), '-01'
), '%Y-%m-%d') < cutoff_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN partition_cursor;
read_loop: LOOP
FETCH partition_cursor INTO part_name;
IF done THEN LEAVE read_loop; END IF;
SET @sql = CONCAT('ALTER TABLE orders DROP PARTITION ', part_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE partition_cursor;
END$$
DELIMITER ;
The ideal partition automation timeline:
T-1 month: The script creates next month's partition
T-0: The new month begins, the partition is already ready for data
T+24 months: The script drops partitions past the 2-year retention
Alerts that should be set up:
→ Alert if next month's partition doesn't exist 3 days before the month changes
→ Alert if an INSERT fails because the partition key is out of range
→ Alert if the total partition count exceeds a threshold (e.g. > 100)
Hot Data vs Cold Data: Optimizing Access #
One of the biggest benefits of time-based partitioning is the natural ability to separate hot data from cold data. The newest data — the most frequently accessed — lives in the newest partition, which is most likely still in the buffer pool (memory). Old data lives in older partitions, tends to have fallen out of the cache, and is only read when there’s a historical query.
Data access distribution in a typical system:
orders_2025_02 [████████████████████] ← 70% of queries access this
orders_2025_01 [████████] ← 15% of queries
orders_2024_12 [████] ← 8% of queries
orders_2024_11 [██] ← 4% of queries
orders_2024_10 and older [█] ← 3% combined queries
Configuration implications:
→ InnoDB buffer pool focuses on the newest partition
→ Old partitions can move to slower but cheaper storage
→ In the cloud: new partitions on SSD, old partitions on HDD or cold storage
In PostgreSQL, this can be combined with tablespaces — storing old partitions on different (cheaper, slower) storage while keeping new partitions on fast storage:
-- Move old partitions to a cold storage tablespace (PostgreSQL)
CREATE TABLESPACE cold_storage
LOCATION '/mnt/cold-storage/postgres';
-- When creating old partitions, specify their tablespace
CREATE TABLE orders_2023_01
PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
TABLESPACE cold_storage;
-- Or move existing partitions
ALTER TABLE orders_2023_01 SET TABLESPACE cold_storage;
Limitations to Understand #
Partitioning isn’t without constraints. There are several technical limitations to understand before implementing it:
MySQL partitioning limitations:
✗ The partition key must be part of the primary key and all unique indexes
→ This often forces non-trivial schema changes
→ A primary key of (id) alone isn't enough — it must be (id, partition_key)
✗ Foreign keys aren't supported on partitioned tables
→ If there are foreign keys referencing this table, they must be removed first
→ Referential integrity must be maintained in the application layer
✗ Full-text indexes and spatial indexes aren't supported
✗ The maximum number of partitions per table is 8192 (MySQL 8.0)
→ For daily partitions over 20+ years, this can become a limitation
✗ Queries not including the partition key → all partitions read
→ This is often slower than an unpartitioned table due to metadata overhead
PostgreSQL limitations:
✗ Unique indexes and primary keys must include the partition key
→ Same as MySQL
✗ Some DDL operations must be done per partition, not on the parent table
✓ PostgreSQL is more flexible: foreign keys to partitioned tables are supported (PostgreSQL 12+)
✓ Declarative partitioning in PostgreSQL is cleaner and more powerful
Anti-Patterns to Avoid #
-- ✗ Anti-pattern 1: partitioning too early
-- A table with 100,000 rows — regular indexes are more than enough
-- Adding partitioning only adds complexity without benefit
-- ✓ Solution: partitioning only becomes relevant above tens of millions of rows
────────────────────────────────────────────────────────────────────────────────
-- ✗ Anti-pattern 2: functions on the partition key in WHERE → pruning fails
SELECT * FROM orders WHERE DATE(created_at) = '2025-02-15';
-- DATE() wraps the column → the database can't prune → all partitions read
-- ✓ Solution: query the column directly without functions
SELECT * FROM orders
WHERE created_at >= '2025-02-15' AND created_at < '2025-02-16';
────────────────────────────────────────────────────────────────────────────────
-- ✗ Anti-pattern 3: queries without any partition key filter
SELECT * FROM orders WHERE user_id = 42;
-- If the partition key is created_at, this query scans every partition
-- Possibly slower than an unpartitioned table due to metadata overhead
-- ✓ Solution: if queries often lack the partition key filter, add a regular index
-- or consider whether the chosen partition key is right
────────────────────────────────────────────────────────────────────────────────
-- ✗ Anti-pattern 4: partition keys on frequently-UPDATE'd columns
UPDATE orders SET status = 'completed', region = 'SG' -- region is the partition key!
WHERE id = 42;
-- If region changes, the row must move between partitions → an expensive operation
-- ✓ Solution: the partition key must be a column whose value never changes
-- created_at, inserted_at, user_id (immutable) → safe as partition keys
-- status, region, category → dangerous if they can change
────────────────────────────────────────────────────────────────────────────────
-- ✗ Anti-pattern 5: too many small partitions
-- Daily partitions for 10 years = 3650 partitions
-- The query planner must inspect the metadata of all partitions before pruning
-- Metadata overhead can cost more than the pruning benefit
-- ✓ Solution: the right granularity based on data volume
-- < 10 million rows/month → monthly or quarterly partitions
-- 10–100 million rows/month → monthly partitions
-- > 100 million rows/month → weekly or daily partitions
Partitioning Review Checklist #
PARTITIONING DECISIONS:
□ Data volume large enough to justify partitioning (>50 million rows)
□ Query patterns analyzed — a consistent filter exists that can be the partition key
□ Other options (indexes, query optimization) considered and found insufficient
□ The team ready for the additional operational complexity partitioning brings
PARTITION DESIGN:
□ Partition key chosen based on the most frequent query patterns, not assumptions
□ Partition key is an immutable column (its value never changes)
□ Partition granularity matches data volume (daily/weekly/monthly)
□ Total partition count reasonable (tens to hundreds, not thousands)
□ A DEFAULT or MAXVALUE partition exists to hold out-of-range data
IMPLEMENTATION:
□ Schema adjusted: the partition key included in the primary key and unique indexes
□ Incompatible foreign keys handled
□ EXPLAIN run to verify partition pruning happens
□ Queries without partition key filters identified and given alternative indexes
AUTOMATION AND MAINTENANCE:
□ New partition creation automation scripts exist and are tested
□ Next month's partition always created before the month changes (not when it changes)
□ Old partition deletion scripts exist matching the retention policy
□ Alerts set up if INSERTs fail because the partition key is out of range
MONITORING:
□ Per-partition sizes monitored to detect uneven distribution
□ Queries scanning many partitions (ineffective pruning) monitored via the slow query log
□ Total partition count monitored — not growing without limit
Summary #
- Partitioning isn’t an initial solution — it’s a scalability solution — relevant when a table has grown to tens of millions of rows and regular indexes are no longer enough to maintain performance. Don’t partition small tables.
- Partitioning’s main benefit comes from partition pruning — the database only reads the relevant partitions. Without pruning, partitioning provides no performance benefit — it can even be slower.
- Functions on the partition key in WHERE prevent pruning —
DATE(created_at),YEAR(created_at), and similar make the database read every partition. Rewrite queries so columns aren’t wrapped in functions.- Time-based range partitioning is the safest choice for most cases — fits time-series data, queries always filter by time, and maintenance (deleting old data) is very efficient with DROP PARTITION.
- DROP PARTITION is far more efficient than DELETE — O(1), almost no overhead, no fragmentation. This is one of the strongest reasons to use partitioning for data with limited retention.
- Partition keys must be immutable — UPDATEs on the partition key move rows between partitions, an expensive operation. Use columns whose values never change, like
created_atoruser_id.- Partition lifecycle automation is mandatory — manually created partitions are prone to human error. If next month’s partition is forgotten, every INSERT into that month fails.
- Hash partitioning for even distribution, range for time-based pruning — each has different use cases. If you need both, use composite partitioning.
- Too many small partitions are counterproductive — metadata overhead can cost more than the pruning benefit. The right granularity depends on data volume per period.
- Partitioning requires schema changes — in MySQL, the partition key must go into the primary key and all unique indexes. This is often a constraint on tables with existing foreign keys.