You have a huge table sensor_data with a timestamp column mostly in ascending order. You want to speed up queries filtering by timestamp ranges but keep index size minimal. Which approach is best?
hard📝 Application Q15 of 15
PostgreSQL - Indexing Strategies
You have a huge table sensor_data with a timestamp column mostly in ascending order. You want to speed up queries filtering by timestamp ranges but keep index size minimal. Which approach is best?
ACreate a B-tree index on <code>timestamp</code> and vacuum the table daily.
BCreate a hash index on <code>timestamp</code> for faster equality searches.
CCreate a BRIN index on <code>timestamp</code> and periodically cluster the table by <code>timestamp</code>.
DCreate no index and rely on sequential scans for simplicity.
Step-by-Step Solution
Solution:
Step 1: Identify index type for large, mostly ordered data
BRIN indexes are ideal for large tables with mostly sequential data to keep index size small.
Step 2: Consider table clustering
Clustering the table by timestamp keeps data physically ordered, improving BRIN index effectiveness.
Step 3: Evaluate other options
B-tree indexes use more space; hash indexes don't support range queries; no index is slow for large data.
Final Answer:
Create a BRIN index on timestamp and periodically cluster the table by timestamp. -> Option C
Quick Check:
BRIN + clustering = efficient, small index [OK]
Quick Trick:Use BRIN plus clustering for big, ordered data [OK]
Common Mistakes:
Choosing B-tree despite large size
Using hash index for range queries
Skipping indexing on large tables
Master "Indexing Strategies" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
