Tips and Tricks #187: Partition Large Tables for Query Performance

Use table partitioning to dramatically speed up queries on large datasets.

Code Snippet

-- PostgreSQL: Create partitioned table
CREATE TABLE events (
    event_id BIGSERIAL,
    event_date DATE NOT NULL,
    event_type VARCHAR(50),
    payload JSONB
) PARTITION BY RANGE (event_date);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

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

-- Queries automatically use partition pruning
SELECT * FROM events 
WHERE event_date BETWEEN '2024-01-15' AND '2024-01-20';
-- Only scans events_2024_01 partition!

-- Automate partition creation
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
-- Dynamic partition creation logic
$$ LANGUAGE plpgsql;

Why This Helps

• Query performance 10-100x faster with partition pruning
• Easier data lifecycle management (drop old partitions)
• Parallel query execution across partitions

How to Test

• EXPLAIN ANALYZE to verify partition pruning
• Compare query times before/after partitioning

When to Use

Tables over 100M rows, time-series data, data with natural partition keys.

Performance/Security Notes

Choose partition key based on query patterns. Too many partitions can hurt performance.

References

• https://www.postgresql.org/docs/current/ddl-partitioning.html

Try this tip in your next project and share your results in the comments!