Practice — Data Warehouses & Lakehouses (6 questions)

Your company runs a Postgres OLTP database for the production application. To avoid buying a warehouse, analysts have been pointed at a read replica of that same Postgres database and write their dashboard queries directly against it. At first this worked fine, but as the orders table crossed 200 million rows, a dashboard query like:

SELECT region, DATE_TRUNC('month', order_date) AS month,
       SUM(revenue), COUNT(*)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region, month;

went from 2 seconds to over 90 seconds, and it's now also slowing down the production replica enough that other read traffic is affected.

1. Explain, mechanistically, why this specific query degrades so badly on a row-oriented OLTP engine as the table grows, even with an index on order_date.
2. Propose an architecture that fixes this properly, and explain why it fixes the mechanism you identified in part 1, not just the symptom.
3. A teammate suggests "just add more indexes on the replica" as a faster fix than standing up a new system. Evaluate that proposal.

A 2-billion-row events table is clustered/partitioned on event_date, a native DATE column. To pull the last 7 days, an analyst — wanting to be extra careful about types — writes:

WHERE DATE(event_date) >= CURRENT_DATE - 7

instead of the equivalent, simpler:

WHERE event_date >= CURRENT_DATE - 7

DATE() applied to an already-DATE column is semantically a no-op. Compared to the second query, what does the first one actually cost?