You have a PostgreSQL table 'logs' partitioned by RANGE on 'log_date' with daily partitions. To efficiently delete logs older than 30 days, which query approach best leverages partition pruning?

hard📝 Application Q8 of 15

PostgreSQL - Table Partitioning

ADELETE FROM logs WHERE EXTRACT(DAY FROM log_date) < 30;

BDELETE FROM logs WHERE log_date < CURRENT_DATE - INTERVAL '30 days';

CDELETE FROM logs WHERE TO_CHAR(log_date, 'YYYY-MM-DD') < '2023-01-01';

DDELETE FROM logs WHERE log_date::TEXT < '2023-01-01';

Step-by-Step Solution

Solution:

Step 1: Identify pruning-compatible filter
Partition pruning works when the WHERE clause directly compares the partition key column without wrapping functions.
Step 2: Analyze options
DELETE FROM logs WHERE log_date < CURRENT_DATE - INTERVAL '30 days'; uses a direct comparison on 'log_date' with an interval, enabling pruning. Options B, C, and D apply functions or casts, preventing pruning.
Final Answer:
DELETE FROM logs WHERE log_date < CURRENT_DATE - INTERVAL '30 days'; -> Option B
Quick Check:
Direct comparisons enable pruning, functions block it [OK]

Quick Trick: Use direct comparisons on partition keys for pruning [OK]

Common Mistakes:

Using functions on partition keys in WHERE clause
Casting partition keys to other types
Assuming any filter triggers pruning

Master "Table Partitioning" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

You have a PostgreSQL table 'logs' partitioned by RANGE on 'log_date' with daily partitions. To efficiently delete logs older than 30 days, which query approach best leverages partition pruning?

Step 1: Identify pruning-compatible filter

Step 2: Analyze options

Final Answer:

Quick Check:

Want More Practice?