You manage a large PostgreSQL table transactions partitioned monthly by transaction_date using range partitioning. To archive data older than 3 years efficiently, which method is best?
hard📝 Application Q8 of 15
PostgreSQL - Table Partitioning
You manage a large PostgreSQL table transactions partitioned monthly by transaction_date using range partitioning. To archive data older than 3 years efficiently, which method is best?
ADetach partitions older than 3 years and move them to archive storage.
BDelete rows older than 3 years using DELETE statements on the main table.
CCreate a new table and copy recent data, then drop the old table.
DUse triggers to move old data to an archive table on insert.
Step-by-Step Solution
Solution:
Step 1: Understand partition management
Range partitions by month allow easy detachment of old partitions.
Step 2: Evaluate archiving options
Detaching partitions is efficient and fast compared to row-by-row deletes.
Step 3: Confirm best practice
Detaching old partitions and moving them to archive storage is recommended for large datasets.
Final Answer:
Option A -> Option A
Quick Check:
Detaching partitions is efficient for archiving old data [OK]
Quick Trick:Detach old partitions to archive data efficiently [OK]
Common Mistakes:
Using DELETE which is slow on large data
Recreating tables unnecessarily
Using triggers which add overhead
Master "Table Partitioning" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
