Query without partition key in WHERE clause: SELECT * FROM orders WHERE customer_id = 123

Include partition key: SELECT * FROM orders WHERE customer_id = 123 AND created_at >= '2024-01-01' AND created_at < '2024-02-01'

Partition pruning only works when WHERE clause filters on partition key. Without partition key filter, optimizer must scan all partitions to find matching rows. This defeats the purpose of partitioning. Always include partition key in WHERE clause, even if you need to add date range to existing filters.

Check EXPLAIN output. If it shows "Append" with all partitions listed, pruning failed. Add partition key to WHERE clause. For application queries, enforce partition key filter at application layer.

Critical

Scans all 12 partitions instead of 1. No pruning benefit. Query takes 5 minutes instead of 20 seconds (15x slower)

Using function on partition key: WHERE YEAR(created_at) = 2024 OR WHERE DATE_TRUNC('month', created_at) = '2024-01-01'

Use range comparison: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

When you apply a function to the partition key (YEAR, DATE_TRUNC, EXTRACT), the optimizer cannot determine which partitions to scan. It must evaluate the function for every partition. Solution: rewrite as range comparison without functions. created_at >= '2024-01-01' AND created_at < '2025-01-01' enables pruning.

Never use functions on partition key in WHERE clause. Rewrite as range comparisons. Check EXPLAIN - if all partitions scanned, look for functions on partition key.

Critical

Function on partition key breaks pruning. Scans all partitions. Query 15x slower

Creating 1000+ partitions: daily partitions for 3 years = 1095 partitions

Use monthly or quarterly partitions: 12-36 partitions for 3 years

Each partition is a separate table with metadata. Too many partitions cause: (1) Long planning time - optimizer checks all partitions, (2) Metadata bloat - pg_class, pg_attribute grow large, (3) Overhead > benefit - planning time exceeds time saved by pruning. Sweet spot: 10-100 partitions. For daily granularity, use monthly partitions + index on date.

Monitor planning time with EXPLAIN. If >100ms, too many partitions. Consolidate: daily → weekly, weekly → monthly. Rule: partition count should be <100 for most workloads.

High

Planning time: 5 seconds per query. Metadata bloat. pg_class has 1000+ entries. Query overhead > pruning benefit

Forgetting to create new partitions - INSERT fails: "no partition of relation orders for row"

Automate partition creation with cron job or trigger. Create partitions in advance (next 3 months)

When you INSERT data that doesn't match any partition range, PostgreSQL throws error. This happens when: (1) Forgot to create next month's partition, (2) Data arrives earlier than expected, (3) Clock skew. Solution: automate partition creation. Create partitions 3-6 months in advance. Set up monitoring to alert if partition missing.

Create cron job: "0 0 1 * * psql -c CREATE TABLE orders_$(date +%Y_%m) PARTITION OF orders FOR VALUES FROM (...)". Create partitions for next 3 months. Monitor for missing partitions: check if partition exists for current_date + 30 days.

High

Application INSERT fails. Data loss if not caught. Manual intervention required. Downtime

Partitioning small table (10GB) with no clear query pattern

Only partition tables >100GB with queries that filter on partition key

Partitioning has overhead: planning time, metadata, constraint checking. For small tables (<100GB), this overhead exceeds pruning benefit. Also, if queries don't filter on partition key, no pruning happens. Result: slower queries, more complexity, no benefit. Only partition when: (1) Table >100GB, (2) Queries filter on partition key, (3) Clear archiving needs.

Before partitioning, analyze query patterns. Do most queries filter on a specific column? Is table >100GB? Will it grow? If no to any, don't partition. Use indexes instead.

Medium

Overhead > benefit. Planning time increases. Maintenance complexity. No performance gain