Partitioning

Design, implement, and manage table partitioning strategies for massive datasets with automated partition maintenance, query optimization, and data lifecycle management.

When to Use This Command

Use /partition when you need to:

• Manage tables exceeding 100GB with slow query performance
• Implement time-series data archival strategies (IoT, logs, metrics)
• Optimize queries that filter by date ranges or specific values
• Reduce maintenance window for VACUUM, INDEX, and ANALYZE operations
• Implement efficient data retention policies (delete old partitions)
• Improve parallel query performance across multiple partitions

DON'T use this when:

• Tables are small (<10GB) and perform well
• Queries don't filter by partition key (causes partition pruning failure)
• Application can't be updated to handle partition-aware queries
• Database doesn't support native partitioning (use application-level sharding instead)

Design Decisions

This command implements declarative partitioning because:

• Native database support provides optimal query performance
• Automatic partition pruning reduces query execution time by 90%+
• Constraint exclusion ensures only relevant partitions are scanned
• Partition-wise joins improve multi-table query performance
• Automated partition management reduces operational overhead

Alternative considered: Application-level sharding

• Full control over data distribution
• Requires application code changes
• No automatic query optimization
• Recommended for multi-tenant applications with tenant-based isolation

Alternative considered: Inheritance-based partitioning (legacy)

• Available in older PostgreSQL versions (<10)
• Manual trigger maintenance required
• No automatic partition pruning
• Recommended only for legacy systems

Prerequisites

Before running this command:

1. Identify partition key (typically timestamp or category column)
2. Analyze query patterns to ensure they filter by partition key
3. Estimate partition size (target: 10-50GB per partition)
4. Plan partition retention policy (e.g., keep 90 days, archive rest)
5. Test partition migration on development database

Implementation Process

Step 1: Analyze Table and Query Patterns

Review table size, query patterns, and identify optimal partition strategy.

Step 2: Design Partition Schema

Choose partitioning method (range, list, hash) and partition key based on access patterns.

Step 3: Create Partitioned Table

Convert existing table to partitioned table with minimal downtime using pg_partman or manual migration.

Step 4: Implement Automated Partition Maintenance

Set up automated partition creation, archival, and cleanup processes.

Step 5: Optimize Queries for Partition Pruning

Ensure queries include partition key in WHERE clauses for automatic pruning.

Output Format

The command generates:

• schema/partitioned_table.sql - Partitioned table definition
• maintenance/partition_manager.sql - Automated partition management functions
• scripts/partition_maintenance.sh - Cron job for partition operations
• migration/convert_to_partitioned.sql - Zero-downtime migration script
• monitoring/partition_health.sql - Partition size and performance monitoring

Code Examples

Example 1: PostgreSQL Range Partitioning for Time-Series Data

-- Create partitioned table for time-series sensor data
CREATE TABLE sensor_readings (
    id BIGSERIAL,
    sensor_id INTEGER NOT NULL,
    reading_value NUMERIC(10,2) NOT NULL,
    reading_time TIMESTAMP NOT NULL,
    metadata JSONB,
    PRIMARY KEY (id, reading_time)
) PARTITION BY RANGE (reading_time);

-- Create indexes on partitioned table (inherited by all partitions)
CREATE INDEX idx_sensor_readings_sensor_id ON sensor_readings (sensor_id);
CREATE INDEX idx_sensor_readings_time ON sensor_readings (reading_time);
CREATE INDEX idx_sensor_readings_metadata ON sensor_readings USING GIN (metadata);

-- Create initial partitions (monthly strategy)
CREATE TABLE sensor_readings_2024_01 PARTITION OF sensor_readings
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
…

#!/bin/bash
# scripts/partition_maintenance.sh - Automated Partition Management

set -euo pipefail

# Configuration
DB_NAME="mydb"
DB_USER="postgres"
DB_HOST="localhost"
RETENTION_MONTHS=12
CREATE_AHEAD_MONTHS=3
LOG_FILE="/var/log/partition_maintenance.log"

log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Create future partitions
…

Example 2: List Partitioning by Category with Hash Sub-Partitioning

-- Multi-level partitioning: LIST (by region) → HASH (by customer_id)
CREATE TABLE orders (
    order_id BIGSERIAL,
    customer_id INTEGER NOT NULL,
    region VARCHAR(10) NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount NUMERIC(10,2),
    PRIMARY KEY (order_id, region, customer_id)
) PARTITION BY LIST (region);

-- Create regional partitions
CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US', 'CA', 'MX')
    PARTITION BY HASH (customer_id);

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('UK', 'FR', 'DE', 'ES', 'IT')
    PARTITION BY HASH (customer_id);
…