[Solved] You want to speed up a complex report query that aggregates sales data by month.... Which approach best keeps monthly_sales up to date with minimal performance impact? | PostgreSQL

PostgreSQL - Views and Materialized Views

You want to speed up a complex report query that aggregates sales data by month. You create a materialized view monthly_sales with the aggregation. However, the sales data updates daily. Which approach best keeps monthly_sales up to date with minimal performance impact?

AManually drop and recreate the materialized view after each data change

BSchedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day

CUse a normal view instead of a materialized view for real-time data

DSet the materialized view to auto-refresh on every query

Step-by-Step Solution

Solution:

Step 1: Consider update frequency and performance
Since sales data updates daily, refreshing the materialized view nightly balances freshness and performance.
Step 2: Evaluate other options
Dropping and recreating is costly and manual. Normal views are slow for complex queries. Auto-refresh on every query is not supported and would hurt performance.
Final Answer:
Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day -> Option B
Quick Check:
Nightly refresh = best balance for daily updates [OK]

Quick Trick: Use scheduled refresh for daily updated materialized views [OK]

Common Mistakes:

Trying to auto-refresh materialized views on every query
Dropping and recreating instead of refreshing
Using normal views for heavy aggregation queries

Master "Views and Materialized Views" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Which approach best keeps monthly_sales up to date with minimal performance impact?

Step 1: Consider update frequency and performance

Step 2: Evaluate other options

Final Answer:

Quick Check:

Want More Practice?