PostgreSQL - Table Partitioning
You want to optimize queries filtering by user_id and created_at date on a large table. Which partitioning strategy is best practice?
You want to optimize queries filtering by user_id and created_at date on a large table. Which partitioning strategy is best practice?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Analyze query filters
Queries filter by user_id and created_at, so both should guide partitioning.Step 2: Choose partitioning methods
RANGE on created_at handles date ranges well; HASH subpartitioning on user_id balances data.Step 3: Evaluate other options
LIST on user_id alone is inefficient for many users; HASH on created_at is unusual; no partitioning misses benefits.Final Answer:
Use RANGE partitioning on created_at and subpartition by HASH on user_id. -> Option CQuick Check:
Combine RANGE and HASH for multi-column filtering [OK]
Quick Trick: Combine RANGE for dates and HASH for IDs for best performance [OK]
Common Mistakes:
- Using LIST for high-cardinality user_id
- Hash partitioning on date column only
- Skipping partitioning on large tables
Master "Table Partitioning" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes