PostgreSQL Partitioning Best Practices

Master PostgreSQL table partitioning to dramatically improve query performance, simplify data management, and efficiently handle billions of rows in your database.

Why Partition Your Tables?

Table partitioning divides large tables into smaller, more manageable pieces while maintaining the appearance of a single table to applications.

🚀 Performance Impact: Properly partitioned tables can see query performance improvements of 10x to 100x for time-series and large datasets.

Key Benefits

Partitioning Strategies

PostgreSQL supports three partitioning methods:

1. Range Partitioning

Best for time-series data or sequential values:

-- Create partitioned table by date range
CREATE TABLE sales (
    id BIGSERIAL,
    sale_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- Create monthly partitions
CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

💡 Note: The upper bound is exclusive - values FROM '2025-01-01' TO '2025-02-01' includes January but not February 1st.

2. List Partitioning

Best for categorical data with discrete values:

-- Create partitioned table by region
CREATE TABLE customers (
    id SERIAL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    region VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY LIST (region);

-- Create partitions for each region
CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('North', 'Northeast', 'Northwest');

CREATE TABLE customers_south PARTITION OF customers
    FOR VALUES IN ('South', 'Southeast', 'Southwest');

CREATE TABLE customers_central PARTITION OF customers
    FOR VALUES IN ('Central', 'Midwest');

-- Default partition for unknown regions
CREATE TABLE customers_other PARTITION OF customers
    DEFAULT;

3. Hash Partitioning

Best for evenly distributing data when no natural partition key exists:

-- Create hash-partitioned table
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY,
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH (session_id);

-- Create 4 hash partitions
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);

🔧 Composite (Multi-Level) Partitioning

Combine strategies for complex requirements:

-- First level: Range by year
CREATE TABLE events (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB
) PARTITION BY RANGE (event_date);

-- Second level: List by event type
CREATE TABLE events_2025 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
    PARTITION BY LIST (event_type);

-- Create sub-partitions
CREATE TABLE events_2025_clicks PARTITION OF events_2025
    FOR VALUES IN ('click', 'double_click', 'hover');

CREATE TABLE events_2025_transactions PARTITION OF events_2025
    FOR VALUES IN ('purchase', 'refund', 'subscription');

CREATE TABLE events_2025_other PARTITION OF events_2025
    DEFAULT;

Automatic Partition Creation

Using pg_partman Extension

-- Install pg_partman extension
CREATE EXTENSION pg_partman;

-- Create parent table
CREATE TABLE logs (
    id BIGSERIAL,
    log_time TIMESTAMP NOT NULL,
    level VARCHAR(10),
    message TEXT
) PARTITION BY RANGE (log_time);

-- Configure automatic partition management
SELECT partman.create_parent(
    p_parent_table := 'public.logs',
    p_control := 'log_time',
    p_type := 'native',
    p_interval := 'daily',
    p_premake := 7  -- Create 7 days in advance
);

-- Enable automatic maintenance
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false
WHERE parent_table = 'public.logs';

Manual Partition Creation Function

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
    parent_table TEXT,
    partition_date DATE
)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := date_trunc('month', partition_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := parent_table || '_' || to_char(start_date, 'YYYY_MM');

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name,
        parent_table,
        start_date,
        end_date
    );

    RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- Create partitions for the next 12 months
DO $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 0..11 LOOP
        PERFORM create_monthly_partition(
            'sales',
            CURRENT_DATE + (i || ' months')::INTERVAL
        );
    END LOOP;
END $$;

🚀 Performance Optimization

Partition Pruning

PostgreSQL automatically eliminates irrelevant partitions:

-- Enable partition pruning (on by default)
SET enable_partition_pruning = on;

-- This query only scans sales_2025_01
EXPLAIN ANALYZE
SELECT * FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-31';

Output:

Append  (cost=0.00..21.50 rows=5 width=40)
  Subplans Removed: 11  -- Other partitions pruned!
  ->  Seq Scan on sales_2025_01
        Filter: (sale_date >= '2025-01-01' AND sale_date <= '2025-01-31')

Partition-Wise Joins

-- Enable partition-wise joins
SET enable_partitionwise_join = on;

-- Both tables partitioned by the same key
SELECT s.*, c.name
FROM sales s
JOIN customers c ON s.region = c.region
WHERE s.sale_date >= '2025-01-01';

Partition-Wise Aggregation

-- Enable partition-wise aggregation
SET enable_partitionwise_aggregate = on;

-- Aggregations computed per partition, then combined
SELECT region, SUM(amount)
FROM sales
WHERE sale_date >= '2025-01-01'
GROUP BY region;

Indexing Strategies

Partition-Level Indexes

-- Indexes must be created on each partition
-- Or use index inheritance on the parent table

-- Create index on parent (automatically creates on all partitions)
CREATE INDEX idx_sales_customer ON sales (customer_id);

-- Create unique index (must include partition key)
CREATE UNIQUE INDEX idx_sales_id ON sales (id, sale_date);

⚠️ Important: Unique constraints must include the partition key column(s).

Partial Indexes Per Partition

-- Create optimized index for specific partition
CREATE INDEX idx_sales_2025_01_high_value
ON sales_2025_01 (customer_id, amount)
WHERE amount > 1000;

Data Migration to Partitioned Tables

Strategy 1: Create New and Migrate

-- 1. Create new partitioned table
CREATE TABLE sales_new (LIKE sales INCLUDING ALL)
PARTITION BY RANGE (sale_date);

-- 2. Create partitions
CREATE TABLE sales_new_2025_01 PARTITION OF sales_new
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... create more partitions

-- 3. Migrate data in batches
INSERT INTO sales_new
SELECT * FROM sales
WHERE sale_date >= '2025-01-01' AND sale_date < '2025-02-01';

-- 4. Rename tables
ALTER TABLE sales RENAME TO sales_old;
ALTER TABLE sales_new RENAME TO sales;

-- 5. Verify and drop old table
DROP TABLE sales_old;

Strategy 2: Attach Existing Tables

-- Create parent partitioned table
CREATE TABLE sales_partitioned (
    id BIGSERIAL,
    sale_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- Attach existing table as partition
ALTER TABLE sales_partitioned
    ATTACH PARTITION sales_2025_01
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

💡 Tip: Use ATTACH PARTITION ... NOT VALID to skip constraint validation, then validate later during off-peak hours.

Maintenance Operations

Dropping Old Partitions

-- Instantly remove old data
DROP TABLE sales_2024_01;

-- Or detach first (keeps data accessible)
ALTER TABLE sales DETACH PARTITION sales_2024_01;

-- Archive detached partition
ALTER TABLE sales_2024_01 SET TABLESPACE archive_storage;

Partition Maintenance Script

#!/bin/bash
# Automated partition maintenance

# Create future partitions
psql -d mydb << EOF
SELECT create_monthly_partition('sales', CURRENT_DATE + INTERVAL '3 months');
SELECT create_monthly_partition('sales', CURRENT_DATE + INTERVAL '4 months');
EOF

# Drop partitions older than 2 years
psql -d mydb << EOF
DO \$\$
DECLARE
    partition_name TEXT;
    cutoff_date DATE := CURRENT_DATE - INTERVAL '2 years';
BEGIN
    FOR partition_name IN
        SELECT tablename FROM pg_tables
        WHERE tablename LIKE 'sales_%'
          AND tablename ~ '^sales_[0-9]{4}_[0-9]{2}$'
    LOOP
        IF to_date(substring(partition_name from 7), 'YYYY_MM') < cutoff_date THEN
            EXECUTE 'DROP TABLE ' || partition_name;
            RAISE NOTICE 'Dropped partition: %', partition_name;
        END IF;
    END LOOP;
END \$\$;
EOF

📊 Monitoring Partitions

List All Partitions

-- View partition hierarchy
SELECT
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'sales'
ORDER BY child.relname;

Partition Size Analysis

-- Size of each partition
SELECT
    child.relname AS partition_name,
    pg_size_pretty(pg_relation_size(child.oid)) AS size,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS total_size,
    (SELECT count(*) FROM sales WHERE sale_date >=
        (pg_get_expr(child.relpartbound, child.oid)::text)::date) AS row_estimate
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'sales';

Monitor Partition Pruning

-- Check if queries are pruning correctly
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT COUNT(*) FROM sales
WHERE sale_date = '2025-02-15';

✅ Best Practices

Partition Key Selection

1. Choose high-selectivity columns - Date ranges, region codes
2. Include in WHERE clauses - Enables partition pruning
3. Consider query patterns - Match partitions to access patterns
4. Avoid over-partitioning - Hundreds of partitions add overhead

Partition Size Guidelines

Schema Design

-- Good: Partition key included in constraints
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- Bad: Unique constraint without partition key
-- This will fail!
-- CONSTRAINT pk_orders PRIMARY KEY (id)

⚠️ Common Pitfalls

Missing Default Partition

-- Always create a default partition to catch unexpected values
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

-- Query to find rows in default partition
SELECT * FROM sales_default LIMIT 100;

Foreign Key Limitations

-- Foreign keys TO partitioned tables require careful planning
-- The referenced table must have a matching index

-- This works
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    product_id INTEGER,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Ensure referenced table has matching unique index
CREATE UNIQUE INDEX ON orders (id, order_date);

Constraint Exclusion vs Partition Pruning

-- Modern PostgreSQL (10+): Use partition pruning
SET enable_partition_pruning = on;

-- Legacy: Constraint exclusion (slower)
SET constraint_exclusion = partition;

Advanced Techniques

Online Partition Addition

-- Add new partition without blocking writes
CREATE TABLE sales_2025_04 PARTITION OF sales
    FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

Partition Splitting

-- Split large partition into smaller ones
-- 1. Detach the partition
ALTER TABLE sales DETACH PARTITION sales_2025_q1;

-- 2. Create new finer-grained partitions
CREATE TABLE sales_2025_01 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE sales_2025_02 PARTITION OF sales
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE sales_2025_03 PARTITION OF sales
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- 3. Migrate data
INSERT INTO sales SELECT * FROM sales_2025_q1
WHERE sale_date >= '2025-01-01' AND sale_date < '2025-04-01';

-- 4. Drop old partition
DROP TABLE sales_2025_q1;

Declarative Partition Bounds

-- Check partition bounds
SELECT
    c.relname,
    pg_get_expr(c.relpartbound, c.oid, true) as partition_bound
FROM pg_class c
JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class p ON i.inhparent = p.oid
WHERE p.relname = 'sales';

Performance Comparison

Query Performance: Partitioned vs Non-Partitioned

Conclusion

PostgreSQL table partitioning is a powerful optimization technique for managing large datasets. By choosing the right partitioning strategy and following best practices, you can achieve dramatic performance improvements and simplified data lifecycle management.

Quick Decision Guide

Use Range Partitioning when:

• Time-series data (logs, events, transactions)
• Sequential numeric ranges
• Date-based archival requirements

Use List Partitioning when:

• Geographic regions
• Product categories
• Discrete status values

Use Hash Partitioning when:

• No natural partition key
• Need even data distribution
• Random access patterns

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in PostgreSQL, SQL optimization, and enterprise database solutions.

Related Articles

• PostgreSQL Table Inheritance vs Partitioning
• PostgreSQL Index Types: When to Use Each
• PostgreSQL Vacuum and Autovacuum Optimization

Need help with PostgreSQL optimization? Contact us for expert database consulting.