丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Database Cost Optimization: Instance Sizing, Reserved Instances, Storage Tiering

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Database Cost Optimization: Instance Sizing, Reserved Instances, Storage Tiering

Database costs are often the largest infrastructure expense for data-intensive applications. This article covers strategies to optimize database spending without sacrificing performance or reliability.

Right-Sizing Instances

Over-provisioning is the most common cause of wasted database spend. Most teams provision for peak load and never scale down.

Measuring Utilization

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- CPU utilization (via pg_stat_statements)

SELECT ROUND(AVG(extract(epoch FROM total_exec_time) / calls), 2) AS avg_query_time

FROM pg_stat_statements;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Connection utilization

SELECT count(*) AS connections, setting AS max_connections

FROM pg_settings, pg_stat_activity

WHERE name = 'max_connections'

GROUP BY setting;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Disk IOPS

SELECT schemaname, tablename,

seq_tup_read, idx_tup_fetch,

seq_scan, idx_scan

FROM pg_stat_user_tables

ORDER BY seq_scan DESC;

In cloud environments, monitor CloudWatch (AWS), Azure Monitor, or GCP Cloud Monitoring metrics:

CPU Utilization : If consistently below 20%, downsize.
Database Connections : If below 30% of max, reduce max_connections or instance size.
Read/Write IOPS : Provisioned IOPS that are never used are pure waste.
Storage Used : Wasted storage costs money; reclaim unused space.

The Sizing Process

Before: 8 vCPU, 32 GB RAM, 1000 GB gp2 ($700/month)

After monitoring for 2 weeks:

Peak CPU: 25%, average CPU: 12%

Peak connections: 40 of 200

Storage used: 120 GB

Optimized: 4 vCPU, 16 GB RAM, 150 GB gp3 ($200/month - 71% savings)

Reserved Instances

Cloud providers offer significant discounts for committing to 1-year or 3-year terms:

| Commitment | AWS RDS Discount | Azure SQL Discount | GCP Cloud SQL | |------------|-----------------|-------------------|---------------| | 1-year no upfront | ~30% | ~30% | ~25% | | 1-year partial upfront | ~35% | ~35% | ~30% | | 3-year all upfront | ~60% | ~55% | ~50% |

When to Reserve

Reserve when : Workload is predictable and runs 24/7. Production databases are ideal.
Do not reserve when : Development/staging instances that run only during business hours. Short-lived project databases.

Using Reserved Instances with Auto-Scaling

If you use read replicas that scale dynamically, consider a mixed strategy:

Production primary: 3-year reserved instance (60% savings)

Read replicas (fixed): 1-year reserved (30% savings)

Read replicas (auto-scaling): On-demand (flexibility premium)

Storage Tiering

Different data needs different storage performance:

AWS RDS Storage Options

| Type | IOPS/GB | Max IOPS | Cost/GB/Month | Use Case | |------|---------|----------|---------------|----------| | gp2 | 3 baseline | 16,000 | $0.10 | Development, low-throughput workloads | | gp3 | 3,000 baseline | 16,000 | $0.08 | General purpose (cheaper than gp2) | | io1 | Provisioned | 256,000 | $0.125 + IOPS | High-throughput OLTP | | io2 | Provisioned | 256,000 | $0.125 + IOPS | Mission-critical, 99.999% durability |

Storage Tiering Strategy

\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Cost Optimization: Instance Sizing, Reserved Instances, Storage Tiering

Database Cost Optimization: Instance Sizing, Reserved Instances, Storage Tiering