Partitioning — Explained with Examples

In this tutorial, you'll learn about Partitioning. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

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