You have a transactions table partitioned by transaction_date (RANGE) and sub-partitioned by store_id (LIST). To optimize queries filtering by both date and store, which strategy is best?
hard📝 Conceptual Q8 of 15
PostgreSQL - Table Partitioning
You have a transactions table partitioned by transaction_date (RANGE) and sub-partitioned by store_id (LIST). To optimize queries filtering by both date and store, which strategy is best?
ACreate partitions by date ranges and subpartitions for each store under those date partitions.
BPartition by store_id and subpartition by transaction_date ranges.
CUse only RANGE partitioning on transaction_date without subpartitioning.
DUse HASH partitioning on store_id and no subpartitioning.
Step-by-Step Solution
Solution:
Step 1: Analyze query filtering needs
Queries filter by both transaction_date and store_id.
Step 2: Choose partitioning strategy
Partitioning by date (RANGE) first allows pruning by date, then subpartitioning by store (LIST) allows pruning by store within date partitions.
Step 3: Evaluate other options
Partitioning by store first is less efficient for date range queries; no subpartitioning reduces pruning efficiency.
Final Answer:
Create partitions by date ranges and subpartitions for each store under those date partitions. best optimizes query speed for filtering by both keys.
Quick Check:
Partition by date, subpartition by store [OK]
Quick Trick:Partition by most selective filter first [OK]
Common Mistakes:
Reversing partition and subpartition keys
Ignoring subpartitioning benefits
Choosing HASH partitioning without query pattern consideration
Master "Table Partitioning" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
