Trade-off Analysis

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Calculate storage overhead of denormalization
 
-- Option 1: Estimate from column sizes
-- Adding customer_name (VARCHAR(100)) and customer_tier (VARCHAR(20))
-- to orders table with 10 million rows
 
-- Average bytes per column (including overhead):
-- customer_name: ~50 bytes average (assuming ~40 char average + overhead)
-- customer_tier: ~15 bytes average
 
-- Total additional storage:
-- 10,000,000 rows × (50 + 15) bytes = 650,000,000 bytes ≈ 620 MB
 
-- Option 2: Measure empirically before/after
SELECT 
    pg_size_pretty(pg_total_relation_size('orders')) AS normalized_size,
    pg_size_pretty(pg_total_relation_size('orders_denormalized')) AS denorm_size,
    pg_total_relation_size('orders_denormalized') - 
    pg_total_relation_size('orders') AS overhead_bytes,
    pg_size_pretty(
        pg_total_relation_size('orders_denormalized') - 
        pg_total_relation_size('orders')
    ) AS overhead_human;
 
-- Calculate monthly cost (assuming $0.023/GB/month for cloud storage)
SELECT 
    (pg_total_relation_size('orders_denormalized') - 
     pg_total_relation_size('orders')) / 1024.0 / 1024.0 / 1024.0 * 0.023 
    AS monthly_cost_usd;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

LATENCY BENEFIT ANALYSIS
========================
 
Baseline (Normalized):
- Query latency: 45ms average
- Query volume: 100,000 queries/day
 
After Denormalization:
- Query latency: 8ms average  
- Improvement: 45ms - 8ms = 37ms per query
 
Daily Time Savings:
- 100,000 queries × 37ms = 3,700,000 ms = 3,700 seconds = ~62 minutes
 
Annual Time Savings:
- 62 minutes × 365 days = 22,630 minutes = ~377 hours
 
Server Time Value:
- If compute costs $0.10/hour
- Annual compute savings: 377 × $0.10 = $37.70
 
User Experience Value:
- Studies show each 100ms of latency costs ~1% conversion
- 37ms improvement ≈ 0.37% conversion improvement
- If daily revenue is $100,000, 0.37% = $370/day = $135,000/year
 
TOTAL ANNUAL BENEFIT: ~$135,000 + $38 ≈ $135,000
(User experience dominates compute savings)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

CROSSOVER POINT ANALYSIS
========================
 
Question: At what scale does denormalization become worthwhile?
 
Variables:
- N = number of orders
- J = join cost in ms (measured as ~0.00003 * N)
- W = write overhead in ms (constant: 10ms per write)
- R = read:write ratio (measured: 50:1)
 
Total Query Time (Normalized):
T_norm = N_reads × J(N) = N_reads × 0.00003 × N
 
Total Query Time (Denormalized):
T_denorm = N_writes × W = N_writes × 10
 
Break-even when T_norm × benefit_ratio = T_denorm:
N_reads × 0.00003 × N × 0.8 = N_writes × 10
(50 × N_writes) × 0.00003 × N × 0.8 = N_writes × 10
0.0012 × N = 10
N = 8,333 orders
 
Result: Denormalization becomes favorable above ~8,300 orders.
 
For large-scale system with 10M orders:
- Join overhead: 10M × 0.00003 = 300ms per read
- With 50:1 ratio and 1000 qps:
  - Read savings: 980 × 300ms = 294 seconds/second (impossible!)
  - This means normalized query takes >300ms, unacceptable
- Denormalization is essential at this scale

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

# Architecture Decision Record: Order Customer Denormalization
 
## Status
Accepted (2024-03-15)
 
## Context
The order listing page requires joining orders with customers table.
Current performance: 45ms average, 180ms p99
Target performance: <10ms average, <30ms p99
Read:Write ratio measured at 500:1
 
## Decision
Embed customer_name and customer_tier columns directly in orders table.
Maintain consistency via database trigger on customers.customer_name UPDATE.
 
## Consequences
### Positive
- Query latency reduced to 8ms average (measured in staging)
- Eliminated orders-customers join for primary use case
- Application code simplified (no join logic)
 
### Negative
- 120MB additional storage for 10M orders
- 5ms overhead on customer name updates (rare: ~100/day)
- New trigger requires testing during migrations
 
## Normalized Reference
The canonical customer data remains in customers table.
customers.customer_name is the source of truth.
orders.customer_name is a performance copy, maintained by trigger.
 
## Consistency Mechanism
```sql
CREATE TRIGGER sync_customer_name_to_orders
AFTER UPDATE OF customer_name ON customers
FOR EACH ROW
EXECUTE FUNCTION propagate_customer_name_change();
```
 
## Monitoring
- Daily reconciliation job compares orders.customer_name with customers.customer_name
- Alert if divergence exceeds 0.01%
 
## Review Schedule
Review annually or when orders table exceeds 100M rows.