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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

-- Comprehensive rightsizing analysis queries
 
-- CPU Rightsizing Assessment
WITH hourly_cpu AS (
    SELECT 
        DATE_TRUNC('hour', collection_timestamp) AS hour_bucket,
        AVG(cpu_utilization_pct) AS avg_cpu,
        MAX(cpu_utilization_pct) AS max_cpu,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY cpu_utilization_pct) AS p95_cpu
    FROM system_metrics
    WHERE collection_timestamp >= NOW() - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('hour', collection_timestamp)
),
cpu_summary AS (
    SELECT 
        MAX(p95_cpu) AS peak_p95_cpu,
        AVG(avg_cpu) AS overall_avg_cpu,
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY max_cpu) AS p99_peak_cpu
    FROM hourly_cpu
)
SELECT 
    16 AS current_cores,
    ROUND(overall_avg_cpu, 1) AS avg_utilization_pct,
    ROUND(peak_p95_cpu, 1) AS peak_p95_pct,
    ROUND(p99_peak_cpu, 1) AS p99_peak_pct,
    -- Rightsizing calculation: target 70% at peak
    CEILING(16.0 * p99_peak_cpu / 70) AS recommended_cores,
    CASE 
        WHEN p99_peak_cpu < 35 THEN 'OVERSIZED - Consider 50% reduction'
        WHEN p99_peak_cpu < 50 THEN 'SOMEWHAT OVERSIZED - Consider 25% reduction'
        WHEN p99_peak_cpu < 70 THEN 'ACCEPTABLE - Minor optimization possible'
        WHEN p99_peak_cpu < 85 THEN 'OPTIMAL - Well-sized'
        ELSE 'UNDERSIZED - Consider upgrade'
    END AS cpu_assessment
FROM cpu_summary;
 
-- Memory Rightsizing Assessment  
WITH memory_metrics AS (
    SELECT 
        DATE_TRUNC('hour', collection_timestamp) AS hour_bucket,
        AVG(memory_used_pct) AS avg_memory,
        MAX(memory_used_pct) AS max_memory,
        AVG(buffer_cache_hit_ratio) AS avg_cache_hit
    FROM system_metrics
    WHERE collection_timestamp >= NOW() - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('hour', collection_timestamp)
)
SELECT 
    128 AS current_memory_gb,
    ROUND(AVG(avg_memory), 1) AS avg_memory_pct,
    ROUND(MAX(max_memory), 1) AS peak_memory_pct,
    ROUND(AVG(avg_cache_hit) * 100, 2) AS avg_cache_hit_pct,
    CASE 
        WHEN MAX(max_memory) < 50 AND AVG(avg_cache_hit) > 0.98 
            THEN 'OVERSIZED - Can likely reduce 30-50%'
        WHEN MAX(max_memory) < 70 AND AVG(avg_cache_hit) > 0.95 
            THEN 'SOMEWHAT OVERSIZED - Can reduce 10-20%'
        WHEN AVG(avg_cache_hit) > 0.97 
            THEN 'OPTIMAL - Good balance'
        WHEN AVG(avg_cache_hit) < 0.90 
            THEN 'UNDERSIZED - Cache pressure affecting performance'
        ELSE 'ACCEPTABLE'
    END AS memory_assessment
FROM memory_metrics;
 
-- Storage Rightsizing Assessment
WITH storage_usage AS (
    SELECT 
        database_name,
        ROUND(total_size_gb, 1) AS total_size_gb,
        ROUND(used_size_gb, 1) AS used_size_gb,
        ROUND(100.0 * used_size_gb / NULLIF(total_size_gb, 0), 1) AS utilization_pct,
        monthly_growth_rate_pct
    FROM storage_allocation
)
SELECT 
    database_name,
    total_size_gb || ' GB' AS allocated,
    used_size_gb || ' GB' AS used,
    utilization_pct || '%' AS utilization,
    -- Months until full at current growth
    CASE 
        WHEN monthly_growth_rate_pct > 0 
        THEN ROUND(LN((100.0 - utilization_pct) / 100.0 + 1) / 
                   LN(1 + monthly_growth_rate_pct / 100.0), 1)
        ELSE NULL
    END AS months_to_full,
    CASE 
        WHEN utilization_pct < 30 THEN 'HIGHLY OVERSIZED - Reduce allocation 50%+'
        WHEN utilization_pct < 50 THEN 'OVERSIZED - Reduce allocation 20-30%'
        WHEN utilization_pct < 70 THEN 'ACCEPTABLE'
        WHEN utilization_pct < 85 THEN 'OPTIMAL'
        ELSE 'NEEDS EXPANSION'
    END AS recommendation
FROM storage_usage
ORDER BY utilization_pct;
 
-- Combined savings estimate
WITH potential_savings AS (
    SELECT 
        -- Assume current monthly cost of $8000
        8000 AS current_monthly_cost,
        -- Component-specific potential savings (based on analysis above)
        0.20 AS cpu_savings_potential,     -- 20% from CPU rightsizing
        0.15 AS memory_savings_potential,  -- 15% from memory rightsizing  
        0.25 AS storage_savings_potential  -- 25% from storage rightsizing
)
SELECT 
    current_monthly_cost AS current_cost,
    ROUND(current_monthly_cost * 0.35 * cpu_savings_potential, 0) AS cpu_savings,
    ROUND(current_monthly_cost * 0.25 * memory_savings_potential, 0) AS memory_savings,
    ROUND(current_monthly_cost * 0.20 * storage_savings_potential, 0) AS storage_savings,
    ROUND(current_monthly_cost * (0.35 * cpu_savings_potential + 
                                   0.25 * memory_savings_potential + 
                                   0.20 * storage_savings_potential), 0) AS total_monthly_savings,
    ROUND(current_monthly_cost * (0.35 * cpu_savings_potential + 
                                   0.25 * memory_savings_potential + 
                                   0.20 * storage_savings_potential) * 12, 0) AS annual_savings
FROM potential_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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206

"""
Cloud Database Cost Optimization Analysis
 
Analyzes cloud database resources for cost optimization opportunities.
Supports AWS RDS, Azure SQL, and GCP Cloud SQL patterns.
"""
 
from dataclasses import dataclass
from typing import List, Dict, Optional
from datetime import datetime, timedelta
 
 
@dataclass
class DatabaseInstance:
    """Represents a cloud database instance"""
    instance_id: str
    instance_class: str
    engine: str
    region: str
    multi_az: bool
    storage_type: str
    storage_gb: int
    iops_provisioned: Optional[int]
    monthly_cost: float
    avg_cpu_utilization: float
    avg_memory_utilization: float
    avg_iops_utilized: Optional[int]
    avg_connections: int
    environment: str  # production, staging, development
 
 
class CloudCostOptimizer:
    """Analyzes and recommends cloud database cost optimizations"""
    
    def __init__(self, instances: List[DatabaseInstance]):
        self.instances = instances
    
    def analyze_all(self) -> Dict:
        """Run all optimization analyses"""
        return {
            'rightsizing': self.analyze_rightsizing(),
            'reserved_instances': self.analyze_ri_opportunities(),
            'storage_optimization': self.analyze_storage(),
            'idle_resources': self.find_idle_resources(),
            'multi_az_review': self.review_multi_az(),
            'total_current_cost': sum(i.monthly_cost for i in self.instances),
            'estimated_savings': self._calculate_total_savings()
        }
    
    def analyze_rightsizing(self) -> List[Dict]:
        """Identify instances that can be downsized"""
        recommendations = []
        
        for instance in self.instances:
            if instance.avg_cpu_utilization < 20 and instance.avg_memory_utilization < 40:
                # Significantly underutilized
                recommendations.append({
                    'instance_id': instance.instance_id,
                    'current_class': instance.instance_class,
                    'recommendation': 'Downsize by 50%',
                    'reason': f'CPU: {instance.avg_cpu_utilization}%, Memory: {instance.avg_memory_utilization}%',
                    'estimated_savings_pct': 45,
                    'risk': 'Low' if instance.environment != 'production' else 'Medium'
                })
            elif instance.avg_cpu_utilization < 40 and instance.avg_memory_utilization < 60:
                # Moderately underutilized
                recommendations.append({
                    'instance_id': instance.instance_id,
                    'current_class': instance.instance_class,
                    'recommendation': 'Downsize by 25%',
                    'reason': f'CPU: {instance.avg_cpu_utilization}%, Memory: {instance.avg_memory_utilization}%',
                    'estimated_savings_pct': 20,
                    'risk': 'Low' if instance.environment != 'production' else 'Medium'
                })
        
        return recommendations
    
    def analyze_ri_opportunities(self) -> Dict:
        """Identify reserved instance purchase opportunities"""
        production_instances = [i for i in self.instances if i.environment == 'production']
        
        on_demand_cost = sum(i.monthly_cost for i in production_instances)
        ri_1yr_cost = on_demand_cost * 0.65  # ~35% savings
        ri_3yr_cost = on_demand_cost * 0.45  # ~55% savings
        
        return {
            'production_instance_count': len(production_instances),
            'current_monthly_cost': on_demand_cost,
            'ri_1yr_monthly_cost': ri_1yr_cost,
            'ri_1yr_annual_savings': (on_demand_cost - ri_1yr_cost) * 12,
            'ri_3yr_monthly_cost': ri_3yr_cost,
            'ri_3yr_annual_savings': (on_demand_cost - ri_3yr_cost) * 12,
            'recommendation': f'Consider 1-year RIs for {len(production_instances)} stable production instances'
        }
    
    def analyze_storage(self) -> List[Dict]:
        """Identify storage optimization opportunities"""
        recommendations = []
        
        for instance in self.instances:
            # Check for over-provisioned IOPS
            if instance.iops_provisioned and instance.avg_iops_utilized:
                iops_utilization = instance.avg_iops_utilized / instance.iops_provisioned
                if iops_utilization < 0.3:
                    recommendations.append({
                        'instance_id': instance.instance_id,
                        'type': 'IOPS',
                        'recommendation': 'Reduce provisioned IOPS or switch to gp3',
                        'detail': f'Using only {iops_utilization*100:.0f}% of provisioned IOPS',
                        'estimated_savings': instance.monthly_cost * 0.10
                    })
            
            # Check for storage type optimization
            if instance.storage_type == 'io1' and instance.avg_iops_utilized and instance.avg_iops_utilized < 3000:
                recommendations.append({
                    'instance_id': instance.instance_id,
                    'type': 'Storage Type',
                    'recommendation': 'Switch from io1 to gp3',
                    'detail': 'gp3 provides 3000 IOPS baseline at lower cost',
                    'estimated_savings': instance.monthly_cost * 0.15
                })
        
        return recommendations
    
    def find_idle_resources(self) -> List[Dict]:
        """Find databases with minimal or no activity"""
        idle_instances = []
        
        for instance in self.instances:
            if instance.avg_connections < 5 and instance.avg_cpu_utilization < 5:
                idle_instances.append({
                    'instance_id': instance.instance_id,
                    'environment': instance.environment,
                    'monthly_cost': instance.monthly_cost,
                    'recommendation': 'Review necessity - appears unused',
                    'metrics': f'Avg connections: {instance.avg_connections}, CPU: {instance.avg_cpu_utilization}%'
                })
        
        return idle_instances
    
    def review_multi_az(self) -> List[Dict]:
        """Review Multi-AZ configurations for appropriateness"""
        recommendations = []
        
        for instance in self.instances:
            if instance.multi_az and instance.environment in ['development', 'staging']:
                recommendations.append({
                    'instance_id': instance.instance_id,
                    'environment': instance.environment,
                    'recommendation': 'Disable Multi-AZ for non-production',
                    'estimated_savings': instance.monthly_cost * 0.45  # Multi-AZ roughly doubles cost
                })
        
        return recommendations
    
    def _calculate_total_savings(self) -> Dict:
        """Calculate total potential savings across all categories"""
        rightsizing = sum(
            i.monthly_cost * (r.get('estimated_savings_pct', 0) / 100)
            for i in self.instances
            for r in self.analyze_rightsizing()
            if r['instance_id'] == i.instance_id
        )
        
        storage = sum(r.get('estimated_savings', 0) for r in self.analyze_storage())
        multi_az = sum(r.get('estimated_savings', 0) for r in self.review_multi_az())
        idle = sum(r['monthly_cost'] for r in self.find_idle_resources())
        
        ri_savings = self.analyze_ri_opportunities()['ri_1yr_annual_savings'] / 12
        
        return {
            'rightsizing_monthly': rightsizing,
            'storage_monthly': storage,
            'multi_az_monthly': multi_az,
            'idle_resources_monthly': idle,
            'reserved_instances_monthly': ri_savings,
            'total_monthly': rightsizing + storage + multi_az + idle + ri_savings,
            'total_annual': (rightsizing + storage + multi_az + idle + ri_savings) * 12
        }
 
 
# Example usage
def demonstrate_cost_analysis():
    instances = [
        DatabaseInstance('prod-db-1', 'db.r5.2xlarge', 'postgres', 'us-east-1',
                        True, 'io1', 500, 5000, 1850, 35, 45, 1200, 150, 'production'),
        DatabaseInstance('prod-db-2', 'db.r5.xlarge', 'postgres', 'us-east-1',
                        True, 'gp3', 200, None, 850, 55, 60, None, 80, 'production'),
        DatabaseInstance('staging-db', 'db.r5.large', 'postgres', 'us-east-1',
                        True, 'gp2', 100, None, 325, 15, 25, None, 10, 'staging'),
        DatabaseInstance('dev-db-1', 'db.r5.large', 'postgres', 'us-east-1',
                        False, 'gp2', 50, None, 165, 5, 10, None, 2, 'development'),
        DatabaseInstance('unused-db', 'db.t3.medium', 'mysql', 'us-east-1',
                        False, 'gp2', 20, None, 85, 1, 5, None, 0, 'development'),
    ]
    
    optimizer = CloudCostOptimizer(instances)
    analysis = optimizer.analyze_all()
    
    print("Cloud Database Cost Optimization Analysis")
    print("=" * 50)
    print(f"Total Current Monthly Cost: ${analysis['total_current_cost']:, .0f
                        }")
    print(f"Estimated Monthly Savings: ${analysis['estimated_savings']['total_monthly']:,.0f}")
    print(f"Estimated Annual Savings: ${analysis['estimated_savings']['total_annual']:,.0f}")
    print(f"Savings Percentage: {analysis['estimated_savings']['total_monthly']/analysis['total_current_cost']*100:.1f}%")

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

-- Storage optimization opportunity analysis
 
--Identify tables by size and growth for archival candidates
WITH table_sizes AS(
                            SELECT 
        schemaname,
                            tablename,
                            pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
                pg_total_relation_size(schemaname || '.' || tablename) AS size_bytes,
                n_live_tup AS row_count,
                COALESCE(last_vacuum, last_autovacuum) AS last_maintenance
    FROM pg_stat_user_tables
),
        table_analysis AS(
            SELECT 
        schemaname,
            tablename,
            total_size,
            size_bytes,
            row_count,
            ROUND(size_bytes:: numeric / NULLIF(row_count, 0), 0) AS bytes_per_row,
            100.0 * size_bytes / SUM(size_bytes) OVER() AS pct_total_storage
    FROM table_sizes
        )
    SELECT
    schemaname || '.' || tablename AS table_name,
        total_size,
        row_count,
        bytes_per_row || ' bytes/row' AS row_efficiency,
            ROUND(pct_total_storage, 1) || '%' AS pct_storage,
                CASE 
        WHEN row_count > 10000000 THEN 'Consider partitioning/archival'
        WHEN bytes_per_row > 2000 THEN 'Review large columns'
        WHEN pct_total_storage > 20 THEN 'Major storage consumer - optimize first'
        ELSE 'Normal'
    END AS recommendation
FROM table_analysis
ORDER BY size_bytes DESC
LIMIT 20;
 
    --Compression opportunity analysis(PostgreSQL with pg_column_compression)
    SELECT
    schemaname || '.' || tablename AS table_name,
        pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS current_size,
            --Estimate compressed size based on typical compression ratios
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) * 0.4) AS estimated_compressed,
        pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) * 0.6) AS estimated_savings
FROM pg_stat_user_tables
WHERE pg_total_relation_size(schemaname || '.' || tablename) > 1073741824  -- > 1GB
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
 
    --Unused index detection(candidates for removal)
        SELECT
    schemaname || '.' || indexrelname AS index_name,
        schemaname || '.' || relname AS table_name,
            pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
                idx_scan AS times_used,
                    idx_tup_read AS tuples_read,
                        CASE 
        WHEN idx_scan = 0 THEN 'UNUSED - Safe to drop'
        WHEN idx_scan < 100 THEN 'RARELY USED - Review necessity'
        ELSE 'ACTIVE'
    END AS recommendation
FROM pg_stat_user_indexes
WHERE NOT indexrelname LIKE '%_pkey'  -- Exclude primary keys
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
 
    --Duplicte index detection  
WITH index_columns AS(
        SELECT 
        i.indexrelid,
        i.indrelid,
        i.indexrelid:: regclass AS index_name,
        i.indrelid:: regclass AS table_name,
        array_agg(a.attname ORDER BY ord) AS columns,
        pg_relation_size(i.indexrelid) AS index_size
    FROM pg_index i
    JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS c(attnum, ord) ON true
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = c.attnum
    GROUP BY i.indexrelid, i.indrelid
    )
    SELECT
    a.table_name,
        a.index_name AS index_to_drop,
            pg_size_pretty(a.index_size) AS index_size,
                b.index_name AS superseding_index,
                    a.columns AS dropped_columns,
                        b.columns AS kept_columns
FROM index_columns a
JOIN index_columns b ON a.indrelid = b.indrelid 
    AND a.indexrelid != b.indexrelid
    AND a.columns < @b.columns-- a's columns are subset of b's
    AND a.index_size <= b.index_size
ORDER BY a.index_size DESC
LIMIT 10; 

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

# Database Cost Allocation Tagging Strategy
# Tags applied to all database resources for accurate cost attribution
 
required_tags:
  # Ownership and accountability
        - name: owner
    description: "Team or individual responsible for this resource"
    valid_values: ["platform-team", "payments-team", "search-team", "analytics-team"]
 
        - name: cost_center
    description: "Finance cost center for chargeback"
    pattern: "^CC-[0-9]{6}$"
 
        - name: project
    description: "Project or product this supports"
    examples: ["checkout-v2", "search-reindex", "core-platform"]
 
        - name: environment
    description: "Deployment environment"
    valid_values: ["production", "staging", "development", "testing"]
 
# Environment - based defaults and limits
    environment_policies:
    production:
    multi_az: required
    backup_retention_days: 35
    max_auto_approval_cost: null  # All production requires approval
 
    staging:
    multi_az: optional
    backup_retention_days: 7
    max_auto_approval_cost: 500  # Per month
 
    development:
    multi_az: disabled
    backup_retention_days: 3
    max_auto_approval_cost: 200  # Per month
    auto_shutdown:
    enabled: true
    schedule: "weekdays 8am-8pm"  # Only run during business hours
 
    testing:
    multi_az: disabled
    backup_retention_days: 1
    max_auto_approval_cost: 100  # Per month
    lifetime_limit_hours: 72  # Auto - delete after 72 hours
 
# Cost reporting configuration
    cost_reports:
    daily:
    recipients: ["dba-team@company.com"]
    format: summary
    threshold_alert: 10 %  # Alert if daily spend 10 % + over average
 
    weekly:
    recipients: ["engineering-leads@company.com"]
    format: detailed_by_team
    include_optimization_recommendations: true
 
    monthly:
    recipients: ["engineering-all@company.com", "finance@company.com"]
    format: executive_summary
    include_trends: true
    include_forecast: true
 
# Anomaly detection
    anomaly_alerts:
    - condition: "daily_cost > 2x 30day_average"
    severity: high
    notify: ["dba-oncall", "owner"]
 
        - condition: "new_resource_cost > $500/month"
    severity: medium
    notify: ["owner", "finance-approvers"]
 
        - condition: "resource_idle > 7_days"
    severity: low
    notify: ["owner"]
    action: auto_stop_if_non_production