12.5 Table Partitioning (RANGE, LIST, HASH)

Level: 12 | Version: v1.1 | Author: Meptrasoft

Overview

Table partitioning divides a large table into smaller partitions based on column values.

This can improve query performance because the database may read only the relevant partitions instead of the entire table.

LARGE TABLE
    ↓
PARTITIONS
    ↓
SMALLER DATA CHUNKS

Partitioning → Divide a large table into smaller parts.

Table Partitioning — RANGE, LIST, HASH Strategies — Figure 1: Table partitioning divides a large table into smaller partitions using range, list, or hash rules.

RANGE Partitioning

RANGE partitioning divides rows according to a range of values.

It is commonly used for dates.

Example:

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

Conceptually:

2024 → Partition 2024
2025 → Partition 2025
2026 → Partition 2026

RANGE → Divide by value ranges.

LIST Partitioning

LIST partitioning divides rows based on specific values.

For example, data can be divided by country:

India → Partition 1
USA   → Partition 2
UK    → Partition 3

This is useful when the partitioning column contains a known set of categories.

LIST → Divide by specific values.

HASH Partitioning

HASH partitioning distributes rows using a hash function.

Rows
 ↓
HASH FUNCTION
 ↓
Partition 1
Partition 2
Partition 3
Partition 4

Unlike range or list partitioning, the rows are distributed based on the hash result rather than a meaningful business category.

HASH → Distribute rows across partitions.

Partition Pruning

One important benefit of partitioning is partition pruning.

Suppose an orders table is partitioned by year and the query asks for 2026 orders:

SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

The database may be able to access only the relevant 2026 partition rather than scanning every partition.

Query
 ↓
Partition Key
 ↓
Relevant Partition
 ↓
Read Data

Partition pruning → Skip partitions that cannot contain the requested rows.

RANGE vs LIST vs HASH

Type	Divides Data By	Example
RANGE	Value ranges	Year / Month
LIST	Specific values	Country / Region
HASH	Hash result	Even distribution

Easy Memory Trick

RANGE → RANGE
LIST  → VALUES
HASH  → DISTRIBUTE

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
Partitioning creates unrelated tables	Partitions belong to a logical parent table
RANGE is always better than HASH	The suitable strategy depends on the workload
Partitioning automatically makes every query faster	Queries benefit mainly when the partitioning strategy matches the workload
HASH partitions have business meaning	Hash partitioning is primarily for distribution
More partitions always means better performance	Too many partitions can increase planning and management overhead

Placement Quick Points

RANGE
→ VALUE RANGES

LIST
→ SPECIFIC VALUES

HASH
→ HASH DISTRIBUTION

Partitioning divides a large table into smaller partitions.
RANGE is commonly used for dates and numeric ranges.
LIST is useful for categories such as country or region.
HASH distributes rows using a hash function.
Partition pruning can reduce the amount of data scanned.
Partitioning should be designed based on query and data-access patterns.

Interview Questions

What is table partitioning?: Partitioning divides a large logical table into smaller partitions based on a partitioning rule.
What is RANGE partitioning?: It divides rows according to value ranges, commonly dates.
What is LIST partitioning?: It divides rows according to specific values or categories.
What is HASH partitioning?: It distributes rows across partitions using a hash function.
What is partition pruning?: Partition pruning is the process of skipping partitions that cannot contain the rows needed by a query.
Which partitioning type is commonly used for date-based data?: RANGE partitioning.

Practice & Hands-On Exercises

Identify a suitable partitioning method for sales data by year.
Identify a suitable method for country-based data.
Explain how HASH partitioning distributes rows.
Explain partition pruning in your own words.
Compare RANGE, LIST, and HASH.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

RANGE → VALUE RANGES
LIST  → SPECIFIC VALUES
HASH  → DISTRIBUTION

Table partitioning divides large tables into smaller partitions, and the right partitioning strategy can reduce the amount of data a query needs to scan.

End of Table Partitioning (RANGE, LIST, HASH)