Database Partitioning Strategies

When a table grows to hundreds of millions of rows, even indexed queries slow down, maintenance operations take hours, and vacuum cannot keep up. Partitioning divides a single logical table into multiple physical segments. Queries that include the partition key scan only the relevant partition — turning a full-table scan into a targeted lookup.

Partitioning Strategies

-- Range Partitioning (most common)
-- Best for time-series data: logs, events, orders
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Query automatically goes to the right partition:
SELECT * FROM events WHERE created_at >= '2026-02-15'
                       AND created_at < '2026-03-01';
-- Only scans events_2026_02, skips everything else!


-- List Partitioning
-- Best for categorical data: region, tenant, status
CREATE TABLE orders (
    id BIGSERIAL,
    region TEXT NOT NULL,
    total NUMERIC,
    created_at TIMESTAMPTZ
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('ap-south', 'ap-east');


-- Hash Partitioning
-- Best for even distribution when no natural range/list
CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    user_id BIGINT NOT NULL,
    data JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Maintenance

class PartitionManager:
    """Automate partition lifecycle management."""
    
    def create_future_partitions(self, table: str, months_ahead: int = 3):
        """Pre-create partitions for upcoming months."""
        for i in range(months_ahead):
            start = self.next_month(i)
            end = self.next_month(i + 1)
            partition_name = f"{table}_{start.strftime('%Y_%m')}"
            
            self.execute(f"""
                CREATE TABLE IF NOT EXISTS {partition_name}
                PARTITION OF {table}
                FOR VALUES FROM ('{start}') TO ('{end}');
            """)
    
    def detach_old_partitions(self, table: str, retention_months: int = 12):
        """Detach and archive partitions older than retention."""
        cutoff = self.months_ago(retention_months)
        
        for partition in self.get_partitions_before(table, cutoff):
            # Detach (instant, non-blocking)
            self.execute(f"""
                ALTER TABLE {table} DETACH PARTITION {partition.name};
            """)
            
            # Archive to cold storage
            self.archive_to_s3(partition.name)
            
            # Drop the detached table
            self.execute(f"DROP TABLE {partition.name};")

Anti-Patterns

Partitioning is not a silver bullet — it is a scalpel. Choose the right partition key, ensure your queries use it, automate maintenance, and monitor partition sizes. Done right, partitioning turns an unmanageable table into a well-organized filing system.