Partition_management

Chapter 40: Partition Management

Ongoing Partition Maintenance

---

40.1 Automated Partition Creation

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
    p_year INTEGER,
    p_month INTEGER
) RETURNS VOID AS $$
DECLARE
    v_partition_name TEXT;
    v_start_date DATE;
    v_end_date DATE;
BEGIN
    v_start_date := make_date(p_year, p_month, 1);
    v_end_date := v_start_date + INTERVAL '1 month';
    v_partition_name := 'orders_' || p_year || '_' || LPAD(p_month::TEXT, 2, '0');

    EXECUTE format(
        'CREATE TABLE %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
        v_partition_name, v_start_date, v_end_date
    );

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

-- Create partitions for next 12 months
SELECT create_monthly_partition(2024, 1);
SELECT create_monthly_partition(2024, 2);
-- Continue...

-- Or use a loop in a function
CREATE OR REPLACE FUNCTION create_year_partitions(p_year INTEGER)
RETURNS VOID AS $$
DECLARE
    v_month INTEGER;
BEGIN
    FOR v_month IN 1..12 LOOP
        PERFORM create_monthly_partition(p_year, v_month);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT create_year_partitions(2025);

---

40.2 Partition Pruning

PostgreSQL automatically prunes partitions to speed up queries.

-- Enable constraint_exclusion
SET constraint_exclusion = partition;

-- Check query plan for partition pruning
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-06-15';

-- The query should only scan the relevant partition
-- "Partition pruning" in EXPLAIN output

-- Force constraint exclusion
ALTER TABLE orders SET (constraint_exclusion = partition);

---

40.3 Partition Indexes

-- Create indexes on partitions
CREATE INDEX ON orders_2024_q1 (order_date);
CREATE INDEX ON orders_2024_q1 (user_id);

-- Or create on main table (propagates to partitions)
CREATE INDEX ON orders (order_date);
CREATE INDEX ON orders (user_id);

-- Partial indexes on partitions
CREATE INDEX ON orders_2024_q1 (order_date)
    WHERE status = 'completed';

---

Summary

Task	Command
Create partition	CREATE TABLE … PARTITION OF
Attach partition	ALTER TABLE … ATTACH PARTITION
Detach partition	ALTER TABLE … DETACH PARTITION
Enable pruning	SET constraint_exclusion = partition

---

Next: Chapter 41: Streaming Replication