Partitioning — Explained with Examples

Database partitioning divides a large table into smaller, manageable segments while keeping them under the same database server for improved performance.

Partitioning splits a single table into multiple physical segments (partitions) based on a partitioning key. Each partition stores a subset of rows. Queries can scan only relevant partitions instead of the entire table, dramatically improving performance.

Types of Partitioning

Horizontal Partitioning — splits rows across partitions (e.g., orders from 2023 in partition A, 2024 in partition B).

Vertical Partitioning — splits columns across partitions (e.g., frequently accessed columns in one partition, large blobs in another).

Range Partitioning — divides by ranges (e.g., date ranges, salary brackets).

List Partitioning — divides by discrete values (e.g., region: US, EU, APAC).

Hash Partitioning — divides by a hash function for even distribution.

Why Partitioning Matters

Tables with billions of rows are slow even with indexes — indexes also become huge. Partitioning enables partition pruning (skipping irrelevant partitions), easier bulk data removal (drop a partition instead of DELETE), and parallel query execution across partitions.

Real-World Analogy

Instead of one giant filing cabinet where every document is stuffed randomly, you organize by year (range partitioning). To find a 2024 document, you open only the “2024” drawer, ignoring all others. Less scanning, faster retrieval.

Example: Range Partitioning in PostgreSQL

CREATE TABLE orders (
    id SERIAL,
    order_date DATE,
    customer_id INT,
    amount DECIMAL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Query only scans the relevant partition
SELECT * FROM orders WHERE order_date = '2024-06-15';
-- PostgreSQL prunes orders_2023 partition automatically

Related Terms

Sharding, Replication, Database Index, Connection Pooling