partitioning

name	partitioning
description	Design and implement table partitioning strategies for massive datasets
shortcut	part

Database Partition Manager

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');

CREATE TABLE sensor_readings_2024_02 PARTITION OF sensor_readings
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE sensor_readings_2024_03 PARTITION OF sensor_readings
    FOR VALUES  ()  ();


 sensor_readings_default   sensor_readings ;


  REPLACE  create_monthly_partitions(
    p_table_name TEXT,
    p_months_ahead   
)
 VOID  $$

    v_start_date ;
    v_end_date ;
    v_partition_name TEXT;
    v_sql TEXT;
    v_month ;

    
     v_month  .p_months_ahead LOOP
        v_start_date : DATE_TRUNC(,   (v_month  )::);
        v_end_date : v_start_date   ;
        v_partition_name : p_table_name    TO_CHAR(v_start_date, );

        
        IF   (
               pg_class
             relname  v_partition_name
        ) 
            v_sql : FORMAT(
                ,
                v_partition_name,
                p_table_name,
                v_start_date,
                v_end_date
            );

            RAISE NOTICE , v_partition_name;
             v_sql;

            
             FORMAT(, v_partition_name);
         IF;
     LOOP;
;
$$  plpgsql;


  REPLACE  archive_old_partitions(
    p_table_name TEXT,
    p_retention_months   ,
    p_archive_table TEXT  
)
 VOID  $$

    v_partition RECORD;
    v_cutoff_date ;
    v_sql TEXT;

    v_cutoff_date : DATE_TRUNC(,   (p_retention_months  )::);

     v_partition 
        
            c.relname  partition_name,
            pg_get_expr(c.relpartbound, c.oid)  partition_bounds
         pg_class c
         pg_inherits i  i.inhrelid  c.oid
         pg_class p  p.oid  i.inhparent
         p.relname  p_table_name
           c.relname  p_table_name  
           c.relname  p_table_name  
          c.relname
    LOOP
        
        IF v_partition.partition_name   
            
                v_partition_date ;
            
                v_partition_date : TO_DATE(
                    (v_partition.partition_name  ),
                    
                );

                IF v_partition_date  v_cutoff_date 
                    RAISE NOTICE , v_partition.partition_name;

                    IF p_archive_table   
                        
                        v_sql : FORMAT(
                            ,
                            p_archive_table,
                            v_partition.partition_name
                        );
                         v_sql;
                     IF;

                    
                    v_sql : FORMAT(
                        ,
                        p_table_name,
                        v_partition.partition_name
                    );
                     v_sql;

                    v_sql : FORMAT(, v_partition.partition_name);
                     v_sql;

                    RAISE NOTICE , v_partition.partition_name;
                 IF;
            ;
         IF;
     LOOP;
;
$$  plpgsql;


  REPLACE  partition_health 

    schemaname,
    tablename  partition_name,
    pg_size_pretty(pg_total_relation_size(schemanametablename))  total_size,
    pg_size_pretty(pg_relation_size(schemanametablename))  table_size,
    pg_size_pretty(pg_total_relation_size(schemanametablename) 
                  pg_relation_size(schemanametablename))  index_size,
    n_live_tup  row_count,
    n_dead_tup  dead_rows,
    ROUND(  n_dead_tup  (n_live_tup  n_dead_tup, ), )  dead_row_percent,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
 pg_stat_user_tables
 tablename    
  schemaname, tablename;


  REPLACE  explain_partition_pruning(p_query TEXT)
  (plan_line TEXT)  $$

     QUERY    p_query;
;
$$  plpgsql;

#!/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
create_partitions() {
    log "Creating partitions for next $CREATE_AHEAD_MONTHS months..."

    psql -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -v ON_ERROR_STOP=1 <<EOF
        SELECT create_monthly_partitions('sensor_readings', $CREATE_AHEAD_MONTHS);
        SELECT create_monthly_partitions('audit_logs', $CREATE_AHEAD_MONTHS);
        SELECT create_monthly_partitions('user_events', $CREATE_AHEAD_MONTHS);
EOF

    log "Partition creation completed"
}

# Archive and cleanup old partitions
cleanup_partitions() {
    log "Archiving partitions older than $RETENTION_MONTHS months..."

    psql -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -v ON_ERROR_STOP=1 <<EOF
        SELECT archive_old_partitions('sensor_readings', $RETENTION_MONTHS, 'sensor_readings_archive');
        SELECT archive_old_partitions('audit_logs', $RETENTION_MONTHS, 'audit_logs_archive');
        SELECT archive_old_partitions('user_events', $RETENTION_MONTHS, NULL);  -- No archival, just drop
EOF

     
}


() {
     

    psql -h  -U  -d  -v ON_ERROR_STOP=1 <<

     
}


() {
     

    psql -h  -U  -d  -v ON_ERROR_STOP=1 <<

     
}


() {
     

    create_partitions
    cleanup_partitions
    analyze_partitions
    health_report

     
}

main 

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);

CREATE TABLE orders_asia PARTITION OF orders
    FOR VALUES IN ('JP', 'CN', 'IN', 'SG')
    PARTITION BY HASH (customer_id);


 orders_us_0   orders_us    (MODULUS , REMAINDER );
 orders_us_1   orders_us    (MODULUS , REMAINDER );
 orders_us_2   orders_us    (MODULUS , REMAINDER );
 orders_us_3   orders_us    (MODULUS , REMAINDER );

 orders_eu_0   orders_eu    (MODULUS , REMAINDER );
 orders_eu_1   orders_eu    (MODULUS , REMAINDER );
 orders_eu_2   orders_eu    (MODULUS , REMAINDER );
 orders_eu_3   orders_eu    (MODULUS , REMAINDER );

 orders_asia_0   orders_asia    (MODULUS , REMAINDER );
 orders_asia_1   orders_asia    (MODULUS , REMAINDER );
 orders_asia_2   orders_asia    (MODULUS , REMAINDER );
 orders_asia_3   orders_asia    (MODULUS , REMAINDER );


 enable_partitionwise_join  ;
 enable_partitionwise_aggregate  ;


EXPLAIN (ANALYZE, BUFFERS)
 customer_id, (total_amount)  total_spent
 orders
 region  
   order_date  
   order_date  
  customer_id
  total_spent 
LIMIT ;

# scripts/partition_migration.py - Zero-downtime partition migration
import psycopg2
from psycopg2 import sql
import logging
import time
from datetime import datetime, timedelta

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class PartitionMigrator:
    """Migrate existing table to partitioned table with minimal downtime."""

    def __init__(self, connection_string: str):
        self.conn_string = connection_string

    def connect(self):
        return psycopg2.connect(self.conn_string)

    def migrate_to_partitioned(
        self,
        table_name: str,
        partition_column: str,
        partition_type: str = 'RANGE',
        partition_interval: str = 'MONTHLY'
    ):
        """
        Migrate table to partitioned table with zero downtime.

        Strategy:
        1. Create new partitioned table
        2. Copy existing data in batches
        3. Rename tables atomically
        4. Update application to use new table
        """
        conn = self.connect()
        conn.autocommit = False

        try:
            with conn.cursor() as cur:
                # Step 1: Create new partitioned table
                logger.info(f"Creating partitioned table {table_name}_new...")

                cur.execute()

                
                logger.info()

                 partition_interval == :
                    cur.execute()

                    min_date, max_date = cur.fetchone()
                    logger.info()

                    current_date = min_date
                     current_date <= max_date:
                        next_date = current_date + timedelta(days=)
                        next_date = next_date.replace(day=)  

                        partition_name = 

                        cur.execute(sql.SQL().(
                            sql.Identifier(partition_name),
                            sql.Identifier()
                        ), (current_date, next_date))

                        logger.info()
                        current_date = next_date

                
                logger.info()

                batch_size = 
                offset = 

                 :
                    cur.execute()

                    rows_copied = cur.rowcount
                     rows_copied == :
                        

                    offset += batch_size
                    logger.info()

                    
                    conn.commit()

                
                logger.info()

                cur.execute()
                original_count = cur.fetchone()[]

                cur.execute()
                new_count = cur.fetchone()[]

                 original_count != new_count:
                     Exception(
                        
                    )

                logger.info()

                
                logger.info()

                cur.execute()

                logger.info()
                logger.info()

            conn.commit()

         Exception  e:
            conn.rollback()
            logger.error()
            
        :
            conn.close()

     ():
        
        conn = .connect()

        :
             conn.cursor()  cur:
                cur.execute()
                plan = cur.fetchone()[][]

                
                pruned = ._count_pruned_partitions(plan)

                logger.info()
                logger.info()
                logger.info()
                logger.info()
                logger.info()

                 pruned

        :
            conn.close()

     () -> :
        
        total = 
        scanned = 

         ():
             total, scanned

               node    node[]:
                total += 
                   node  node.get(, ) > :
                    scanned += 

               node:
                 child  node[]:
                    traverse(child)

        traverse(plan[])

        pruned = total - scanned
        effectiveness = (pruned / total * )  total >   

         {
            : total,
            : scanned,
            : pruned,
            : effectiveness
        }


 __name__ == :
    migrator = PartitionMigrator(
        
    )

    
    migrator.migrate_to_partitioned(
        table_name=,
        partition_column=,
        partition_type=,
        partition_interval=
    )

    
    test_query = 

    migrator.verify_partition_pruning(test_query)

Error Handling

Error	Cause	Solution
"No partition of relation ... found for row"	Data outside partition ranges	Create default partition or extend partition range
"Partition constraint violated"	Invalid data for partition	Fix data or adjust partition bounds
"Cannot create partition of temporary table"	Partitioning temp tables unsupported	Use regular tables or application-level sharding
"Too many partitions (>1000)"	Excessive partition count	Increase partition interval (daily → weekly → monthly)
"Constraint exclusion not working"	Query doesn't filter by partition key	Rewrite query to include partition key in WHERE clause

Configuration Options

Partition Planning

• partition_type: RANGE (dates), LIST (categories), HASH (distribution)
• partition_interval: DAILY, WEEKLY, MONTHLY, YEARLY
• retention_policy: How long to keep old partitions
• partition_size_target: Target 10-50GB per partition

Query Optimization

• enable_partition_pruning = on: Enable automatic partition elimination
• constraint_exclusion = partition: Enable constraint-based pruning
• enable_partitionwise_join = on: Join matching partitions directly
• enable_partitionwise_aggregate = on: Aggregate per-partition then combine

Best Practices

DO:

• Always include partition key in WHERE clauses for pruning
• Target 10-50GB per partition (not too large, not too small)
• Use RANGE partitioning for time-series data
• Use LIST partitioning for categorical data (regions, types)
• Use HASH partitioning for even distribution without natural key
• Automate partition creation 3+ months ahead
• Monitor partition sizes and adjust strategy if needed

DON'T:

• Create thousands of tiny partitions (overhead > benefit)
• Partition tables < 10GB (overhead not justified)
• Use partition key that changes over time
• Query without partition key filter (scans all partitions)
• Forget to analyze partitions after bulk inserts
• Mix partition strategies without clear reason

Performance Considerations

• Partition pruning can reduce query time by 90%+ on large tables
• Each partition adds ~8KB overhead in PostgreSQL catalogs
• INSERT performance unchanged for single-row inserts
• Bulk INSERT benefits from partition-wise parallelism
• VACUUM and ANALYZE run faster on smaller partitions
• Index creation can be parallelized across partitions

Related Commands

• /database-migration-manager - Schema migrations with partition support
• /database-backup-automator - Per-partition backup strategies
• /database-index-advisor - Optimize indexes for partitioned tables
• /sql-query-optimizer - Ensure queries leverage partition pruning

Version History

• v1.0.0 (2024-10): Initial implementation with PostgreSQL declarative partitioning
• Planned v1.1.0: Add MySQL partitioning support and automated partition rebalancing