SQL 96 🛢️ Table Partitioning: Range, List, and Hash - KandZ

⏱️ Estimated read: 12 min | |

Table partitioning is the practice of splitting one large logical table into smaller physical pieces called partitions, while keeping it accessible as a single table to the application. The motivation is not raw query speed—a well-indexed table can be fast without partitioning. The motivation is manageability at scale: a table with billions of rows becomes difficult to back up, difficult to archive, and difficult to maintain. Partitioning lets you operate on one slice at a time: drop a month of old data by dropping a partition instead of running a massive DELETE, rebuild an index on one partition instead of the whole table, or let the query planner skip partitions that cannot contain matching rows .

The three primary strategies differ in how they assign a row to a partition. Range partitioning assigns rows based on whether a value falls within contiguous, ordered boundaries—dates and numeric IDs are the most common keys. List partitioning assigns rows based on whether a value belongs to one of a set of discrete values—regions, status codes, categories. Hash partitioning assigns rows by applying a hash function to a value and dividing by the number of partitions—it is used to spread data evenly when no natural range or list key exists . The choice of strategy is determined by the data’s shape and the operations you need to perform on it.

Key point: Range partitioning is for time-series and ordered data and enables partition pruning on range queries; list partitioning is for discrete categories and enables fast bulk removal of a category by dropping or truncating its partition; hash partitioning is for even distribution when no natural partition key exists, but it prevents range-based pruning because the hash scatters related values across partitions.

Why partitioning exists as a manageability tool

The maintenance window problem. A DELETE that removes a year of rows from a billion-row table locks rows, writes to the transaction log, and can run for hours. Dropping a partition that contains that year’s data is a metadata operation that completes in milliseconds . The difference between row-by-row deletion and partition drop is the difference between a maintenance window measured in hours and one measured in seconds.

The query pruning problem. When a query filters on the partition key, the planner can exclude partitions that cannot contain matching rows. A query with WHERE order_date >= '2025-01-01' on a table partitioned by month only scans the partitions for January onward, not the entire table . This is partition pruning, and it is the primary performance benefit of range partitioning. Hash partitioning does not enable range pruning because the hash scatters ordered values across partitions.

The index management problem. Rebuilding an index on a billion-row table is expensive. Rebuilding an index on one partition of that table is proportionally cheaper. Partitioning allows index maintenance to be scoped to the partition that received new data, while leaving the historical partitions untouched .

The storage tiering problem. Older partitions can be stored on slower, cheaper storage while recent partitions stay on fast storage. The table remains one logical entity, but the physical placement of its parts reflects their access frequency.

a. Range partitioning

Range partitioning divides rows by contiguous boundaries. Each partition is defined by an upper bound, and rows whose key value falls below that bound (and above the previous partition’s bound) belong to that partition. The partitions must be defined in order from lowest to highest .

The classic use case is time-series data. A table partitioned by month has a boundary for each month, and each row’s date determines which partition it lands in. A query for a specific month touches only that partition .

-- MySQL range partitioning by year of hire date
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY RANGE (YEAR(hired)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

The MAXVALUE catch-all is essential. Without it, a row whose key exceeds the highest defined boundary has no partition to go to, and the insert fails . The catch-all ensures that future data always has a home.

The RANGE LEFT versus RANGE RIGHT distinction matters in SQL Server. RANGE RIGHT means the boundary value belongs to the partition on the right, which is the correct choice for date partitioning because the first day of a month stays with that month’s rows. RANGE LEFT means the boundary belongs to the partition on the left, which is easier for upper-bound reasoning but often wrong for dates .

b. List partitioning

List partitioning divides rows by discrete values. Instead of a contiguous range, each partition is defined by an explicit list of values that belong to it . The partitions do not need to be in any particular order, and the key can be a non-integer column in some engines.

The classic use case is geographic or categorical data. A table of employees partitioned by store region assigns each store ID to the partition for its region. When a region is sold or closed, the entire partition can be truncated or dropped in one operation .

-- MySQL list partitioning by store region
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3, 5, 6, 9, 17),
    PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20),
    PARTITION pWest VALUES IN (4, 12, 13, 14, 18),
    PARTITION pCentral VALUES IN (7, 8, 15, 16)
);

The operational advantage is the same as range partitioning: if the West region is sold, ALTER TABLE employees TRUNCATE PARTITION pWest removes the data in one metadata operation, far more efficient than a DELETE WHERE store_id IN (...) . The difference from range is that the values are explicit and unordered, so the partition assignment is a membership test rather than a comparison.

List partitioning does not support partition pruning on ranges because there is no inherent ordering. A query for store_id > 10 cannot eliminate partitions by examining their boundaries; it must check the actual values in each partition.

c. Hash partitioning

Hash partitioning divides rows by applying a hash function to the key and assigning the result to a partition based on the remainder. The number of partitions is specified explicitly, and the hash function is applied by the database engine . The goal is even distribution, not ordered grouping.

The classic use case is spreading write contention. If a table’s primary key is monotonically increasing, all inserts go to the “last” partition in a range scheme, creating a hot spot. Hash partitioning scatters those inserts across all partitions, distributing the write load .

-- MySQL hash partitioning by store ID into 4 partitions
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

The partition assignment is deterministic but opaque. A row with store_id = 7 goes to partition 7 % 4 = 3. A row with store_id = 8 goes to partition 0. There is no way to know which partition a value belongs to without computing the hash, and the hash function is internal to the engine .

The trade-off is that hash partitioning provides no partition pruning for range queries. A query for store_id BETWEEN 5 AND 10 must scan every partition, because the hash scatters those values across all of them. The benefit is even distribution and reduced contention on inserts, not query optimization.

Complete Example Session

The following session demonstrates the three strategies on the same logical table, showing how each assigns rows to partitions.

-- ============================================
-- PART 1: RANGE PARTITIONING BY DATE
-- ============================================
-- Rows are assigned by date range
CREATE TABLE orders_range (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- Query for 2024 data prunes to p2024
SELECT * FROM orders_range WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- ============================================
-- PART 2: LIST PARTITIONING BY REGION
-- ============================================
-- Rows are assigned by explicit store ID lists
CREATE TABLE orders_list (
    id INT NOT NULL,
    store_id INT NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (1, 2, 3),
    PARTITION pSouth VALUES IN (4, 5, 6),
    PARTITION pEast VALUES IN (7, 8, 9),
    PARTITION pWest VALUES IN (10, 11, 12)
);

-- Dropping the South region removes all its rows instantly
ALTER TABLE orders_list TRUNCATE PARTITION pSouth;

-- ============================================
-- PART 3: HASH PARTITIONING BY ID
-- ============================================
-- Rows are distributed evenly across 4 partitions
CREATE TABLE orders_hash (
    id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY HASH(customer_id)
PARTITIONS 4;

-- Query for a range of customer IDs must scan all partitions
-- because the hash scatters those values
SELECT * FROM orders_hash WHERE customer_id BETWEEN 100 AND 200;

The three parts demonstrate the assignment mechanism of each strategy. Range assigns by comparison to boundaries, list assigns by membership in a value set, and hash assigns by the remainder of a hash computation. The range table can prune a date query to one partition. The list table can drop a region’s data with a truncate. The hash table distributes writes evenly but cannot prune a range query.

Quick Reference

Strategy Comparison

Aspect	Range	List	Hash
Assignment	Contiguous boundaries	Explicit value set	Hash function
Key type	Ordered (date, number)	Discrete (category, region)	Any
Partition pruning	Yes, on range queries	No, on range queries	No
Bulk removal	Drop oldest partition	Drop category partition	Drop partition (random)
Even distribution	No (can be skewed)	No (by design)	Yes
Primary use	Time-series, archival	Categories, regions	Write distribution

Common Syntax Elements

Element	Meaning
`PARTITION BY RANGE (expr)`	Range partitioning
`VALUES LESS THAN (n)`	Upper bound for range
`MAXVALUE`	Catch-all upper bound
`PARTITION BY LIST (expr)`	List partitioning
`VALUES IN (a, b, c)`	Explicit values for list
`PARTITION BY HASH (expr)`	Hash partitioning
`PARTITIONS n`	Number of hash partitions

Engine Support Notes

Engine	Range	List	Hash
MySQL	Yes	Yes	Yes
PostgreSQL	Yes (declarative)	Yes	Yes
Oracle	Yes	Yes	Yes
SQL Server	Yes (native)	No (workaround)	No (workaround)

SQL Server supports only range partitioning natively. List and hash strategies require workarounds such as computed bucket columns or separate tables .

Best Practices

✅ Do This:

-- Use range for time-series data
PARTITION BY RANGE (YEAR(order_date))

-- Include a MAXVALUE catch-all
PARTITION pmax VALUES LESS THAN MAXVALUE

-- Use list for categorical data
PARTITION BY LIST(region_id) (PARTITION p1 VALUES IN (1, 2, 3))

-- Use hash to distribute write contention
PARTITION BY HASH(customer_id) PARTITIONS 8

❌ Don’t Do This:

-- Range without catch-all
PARTITION BY RANGE (YEAR(d)) (PARTITION p1 VALUES LESS THAN (2024)) -- future rows fail

-- Hash for range queries
PARTITION BY HASH(store_id) -- cannot prune WHERE store_id BETWEEN 5 AND 10

-- List for ordered data
PARTITION BY LIST(order_date) -- impractical, one partition per date

Common Pitfalls

Pitfall	Why It Happens	Fix
Insert fails on range table	No `MAXVALUE` partition	Add catch-all
Range query scans all partitions	Hash partitioning	Use range for ordered data
Uneven partition sizes	Hash with skewed key	Choose a higher-cardinality key
Cannot drop partition	Foreign keys or indexes	Check dependencies
Query does not prune	Filter does not use partition key	Filter on the partitioning column

Real-World Examples

1. Monthly range partition

PARTITION BY RANGE (TO_DAYS(order_date)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01'))
)

2. List partition by status

PARTITION BY LIST(status) (
  PARTITION active VALUES IN ('active', 'pending'),
  PARTITION archived VALUES IN ('closed', 'cancelled')
)

3. Hash partition for even distribution

PARTITION BY HASH(user_id) PARTITIONS 16

4. Drop old range partition

ALTER TABLE logs DROP PARTITION p202401;

5. Truncate list partition

ALTER TABLE orders TRUNCATE PARTITION pWest;

6. Range with catch-all

PARTITION pmax VALUES LESS THAN MAXVALUE

7. SQL Server range function

CREATE PARTITION FUNCTION pf (date) AS RANGE RIGHT FOR VALUES ('2025-01-01')

8. PostgreSQL declarative range

CREATE TABLE orders (id int, d date) PARTITION BY RANGE (d);
CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

9. Verify pruning

EXPLAIN SELECT * FROM orders WHERE order_date >= '2025-06-01';

10. Check partition sizes

SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'orders';

Visual

Range Partitioning

┌──────────────────────────────────────────────┐
│  RANGE by YEAR(order_date)                   │
│                                              │
│  p2023:  < 2024     ████████                 │
│  p2024:  2024       ████████████████         │
│  p2025:  2025       ██████████               │
│  pmax:   >= 2026    ███                      │
│                                              │
│  Query: WHERE year = 2024                    │
│  → scans only p2024                          │
└──────────────────────────────────────────────┘

List Partitioning

┌──────────────────────────────────────────────┐
│  LIST by store_id                            │
│                                              │
│  pNorth: 1, 2, 3     ████████                │
│  pSouth: 4, 5, 6     ████████████            │
│  pEast:  7, 8, 9     ██████                  │
│  pWest:  10, 11, 12  ██████████████          │
│                                              │
│  TRUNCATE PARTITION pSouth                   │
│  → removes all pSouth rows instantly         │
└──────────────────────────────────────────────┘

Hash Partitioning

┌──────────────────────────────────────────────┐
│  HASH by customer_id, 4 partitions           │
│                                              │
│  customer_id = 7  → 7 % 4 = 3 → p3           │
│  customer_id = 8  → 8 % 4 = 0 → p0           │
│  customer_id = 9  → 9 % 4 = 1 → p1           │
│  customer_id = 10 → 10 % 4 = 2 → p2          │
│                                              │
│  Values are scattered evenly                 │
│  Query BETWEEN 5 AND 10 scans ALL partitions │
└──────────────────────────────────────────────┘

Strategy Decision

┌──────────────────────────────────────────────┐
│  Is the key ordered (date, number)?          │
│  ├─ YES → Need range pruning or archival?    │
│  │        ├─ YES → RANGE                     │
│  │        └─ NO → HASH (write distribution)  │
│  └─ NO → Is the key categorical?             │
│          ├─ YES → LIST                       │
│          └─ NO → HASH                        │
└──────────────────────────────────────────────┘

Summary

Item	Value
Range	Contiguous boundaries, ordered key
List	Explicit value sets, categorical key
Hash	Hash function, even distribution
Range pruning	Yes, on range queries
List pruning	No, on range queries
Hash pruning	No, on range queries
Range bulk removal	Drop oldest partition
List bulk removal	Truncate category partition
Hash bulk removal	Drop partition (random data)
SQL Server native	Range only

Key takeaways:

Partitioning is a manageability tool, not a query accelerator. The primary benefits are partition pruning, instant bulk removal, and scoped index maintenance, not raw speed .
Range is for ordered data. It assigns rows by contiguous boundaries, enables partition pruning on range queries, and supports rolling window archival by dropping old partitions .
List is for discrete categories. It assigns rows by explicit value sets, and it allows a category’s data to be removed with a single truncate or drop .
Hash is for even distribution. It scatters rows across partitions to reduce write contention, but it prevents range-based pruning because related values are spread across partitions .
A MAXVALUE catch-all is mandatory for range. Without it, a row whose key exceeds the highest boundary has no partition and the insert fails .
SQL Server supports only range natively. List and hash strategies require workarounds such as computed bucket columns or separate tables .

Remember: Table partitioning is not about making a single query faster. It is about making a large table manageable. Range partitioning is the right choice for time-series data because it enables pruning and archival by date. List partitioning is the right choice for categorical data because it allows a category to be removed in one operation. Hash partitioning is the right choice when no natural partition key exists and the goal is to spread writes evenly. The strategy determines what operations are efficient: range prunes, list drops, hash distributes. Choose the strategy that matches the operations you need to perform, not the one that sounds most sophisticated.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!