# Database Performance Engineering Report ## PHASE 1: EXECUTION PLAN & BOTTLENECK AUDIT - Root Cause: The query is performing a sequential scan on the 'transactions' table (10M+ rows) because the 'created_at' column lacks an index. - Contention: Heavy read-locks during report generation are causing blocking delays on write operations. ## PHASE 2: INDEXING & QUERY OPTIMIZATION - Optimized Query: Use CTEs to pre-filter data before performing the JOIN. - Indexing Strategy: Create a B-Tree composite index on (merchant_id, created_at) to support the filtering and sorting pattern. ## PHASE 3: SCHEMA & DATA DISTRIBUTION STRATEGY - Strategy: Implement Table Partitioning by range (monthly) to limit the scan scope. - Read Delegation: Route report generation to a read-replica to decouple analytical load from the primary write-node. ## PHASE 4: MONITORING & REGRESSION GUARDRAILS - Risk 1: Index bloat slowing down concurrent writes. Mitigation: Schedule regular 'VACUUM ANALYZE' maintenance. - Risk 2: Memory exhaustion. Mitigation: Set 'statement_timeout' for all report queries to kill runaway processes.