PostgreSQL Partition Pruning for Non-Partition Key Queries
This article addresses a common PostgreSQL optimization challenge: partition pruning normally only works when filtering by the partition key, but using check constraints you can enable pruning on non-partition columns too. The technique requires correlated data patterns, append-only tables, and explicit range constraints. The article provides SQL techniques for automating constraint generation using the gaps-and-islands approach, enabling efficient queries on columns like session_id while maintaining timestamp-based partitioning.