Range Partitioning by Month - 15x Query Speedup

advanced

Orders table (1B rows, 500GB) with queries filtering by date. Non-partitioned table scans all 1B rows, takes 5 minutes. Partition by month (12 partitions). Query for January scans only January partition (83M rows, 8% of data), takes 20 seconds (15x faster). Archive old months by dropping partitions (instant vs 6 hours DELETE).

-- Current state: Non-partitioned table

CREATE TABLE orders (

order_id BIGSERIAL PRIMARY KEY,

customer_id BIGINT NOT NULL,

created_at TIMESTAMP NOT NULL,

total DECIMAL(10,2)

);

-- 1B rows, 500GB

-- Query performance (non-partitioned)

EXPLAIN ANALYZE

SELECT customer_id, SUM(total) as total_spent

FROM orders

WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

GROUP BY customer_id;

-- Seq Scan on orders (cost=0..5000000 rows=83000000)

-- Filter: (created_at >= '2024-01-01' AND created_at < '2024-02-01')

-- Rows Removed by Filter: 917000000

-- Planning Time: 0.5 ms

-- Execution Time: 300000 ms (5 minutes)

-- Scans all 1B rows, filters to 83M

-- Solution: Partition by month

-- Step 1: Create partitioned table

CREATE TABLE orders_partitioned (

order_id BIGSERIAL,

customer_id BIGINT NOT NULL,

created_at TIMESTAMP NOT NULL,

total DECIMAL(10,2),

PRIMARY KEY (order_id, created_at)

) PARTITION BY RANGE (created_at);

-- Step 2: Create monthly partitions

CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned

FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned

FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_2024_03 PARTITION OF orders_partitioned

FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

CREATE TABLE orders_2024_04 PARTITION OF orders_partitioned