Database Partitioning

Also Known As

TL;DR

Explanation

Partitioning divides a single logical table into multiple physical storage segments. MySQL and PostgreSQL support: Range partitioning (by date range — perfect for time-series data and rolling retention), List partitioning (by discrete values — region or status), Hash partitioning (distributes rows evenly), and Composite forms. Benefits: partition pruning (a query on the last 7 days scans only recent partitions), fast archival (DROP PARTITION is instant vs DELETE on millions of rows), and parallel scan on multi-core hardware. Unlike sharding, partitioning is transparent to the application — all partitions appear as one table with no application-layer changes required.

Diagram

flowchart TD
    TABLE[(orders table<br/>100M rows)] --> PART{Partition by}
    subgraph Range_Partitioning
        PART -->|created_at| P2024[(2024 partition)]
        PART -->|created_at| P2025[(2025 partition)]
        PART -->|created_at| P2026[(2026 partition)]
    end
    subgraph List_Partitioning
        PART -->|country| PUK[(UK partition)]
        PART -->|country| PUS[(US partition)]
    end
    QUERY[SELECT WHERE created_at > 2026] -->|partition pruning| P2026
    QUERY -.->|skips| P2024
style TABLE fill:#f85149,color:#fff
style P2026 fill:#238636,color:#fff
style P2024 fill:#6e40c9,color:#fff
style QUERY fill:#1f6feb,color:#fff

Common Misconception

Why It Matters

Common Mistakes

• Partitioning tables that are not actually large enough to benefit — adds complexity with no gain.
• Choosing a partition key that most queries don't filter on — all partitions are scanned anyway.
• Not including the partition key in the primary key — required in most databases for partitioned tables.
• Assuming partitioning replaces indexing — indexes within partitions are still needed for non-partition-key filters.

Code Examples

-- Without partitioning: DELETE of old rows is slow and locks the table:
DELETE FROM events WHERE created_at < '2023-01-01'; -- Deletes millions of rows slowly

-- With range partitioning by month:
-- DROP PARTITION p_2023_01; -- Instant, no row-by-row deletion

-- PostgreSQL range partitioning by date — each partition is a separate table
CREATE TABLE events (
    id         BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Queries filtered on created_at only scan the relevant partition
-- Old partitions can be detached and archived without touching live data
ALTER TABLE events DETACH PARTITION events_2024_q1;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

Tags

Related Terms