-- Create partitioned table for time-series data
CREATE TABLE events (
    id SERIAL,
    event_time TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (event_time);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Enable compression for older partitions
ALTER TABLE events_2023_12 SET (autovacuum_enabled = true);
-- Move cold partitions to lower-cost storage via pg_dump/pg_restore or cloud storage integration

resource "aws_db_instance" "optimized" {
  engine               = "postgres"
  engine_version       = "15.4"
  instance_class       = "db.r6g.large"
  allocated_storage    = 100
  storage_type         = "gp3"          # Cost-effective vs io1/io2
  storage_encrypted    = true
  max_allocated_storage = 500           # Auto-scale storage, not compute
  backup_retention_period = 7           # Align with RPO, not default 35
}

-- Identify expensive queries
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Create targeted composite index
CREATE INDEX idx_events_time_status 
ON events(event_time, status) 
WHERE status != 'archived';

-- Analyze execution plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM events 
WHERE event_time > NOW() - INTERVAL '7 days' AND status = 'active';

[databases]
mydb = host=db-primary port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 30

r concurrency without scaling compute.

3. Architecture & Scaling Patterns

Read-heavy workloads benefit from replica consolidation and caching. Write-heavy workloads require careful IOPS provisioning and batching.

Read Replica + Redis Caching Pattern

# Python/Redis cache-aside pattern
import redis
import psycopg2

cache = redis.Redis(host='cache-node', port=6379, db=0)

def get_user_profile(user_id):
    cached = cache.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)
    
    with psycopg2.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
            result = cur.fetchone()
    
    cache.setex(f"user:{user_id}", 300, json.dumps(result))
    return result

Offloading 60–80% of read traffic to cache reduces replica count from 3–4 to 1, cutting multi-AZ replication and egress costs.

4. Cost Visibility & Automation

Without telemetry, optimization is guesswork. Implement cost tagging, query-level cost attribution, and anomaly detection.

AWS Cost Allocation Tag Query (Athena)

SELECT 
  line_item_resource_id,
  SUM(line_item_unblended_cost) as monthly_cost,
  product_instance_type,
  tags.user:Environment as env
FROM cost_and_usage
WHERE product_product_name = 'Amazon Relational Database Service'
  AND month = '2024-01'
GROUP BY line_item_resource_id, product_instance_type, tags.user:Environment
ORDER BY monthly_cost DESC;

Automated Cost Anomaly Detection (CloudWatch + Lambda)

# CloudWatch Alarm for unexpected DB CPU spikes
Alarms:
  HighCPUAnomaly:
    AlarmName: DB-CPU-Anomaly-Detection
    MetricName: CPUUtilization
    Namespace: AWS/RDS
    Statistic: Average
    Period: 300
    EvaluationPeriods: 3
    Threshold: 75
    ComparisonOperator: GreaterThanThreshold
    AlarmActions:
      - arn:aws:sns:region:account:db-cost-alerts

---

Pitfall Guide

#	Pitfall	Why It Happens	Impact	Mitigation
1	Over-compression causing CPU bottlenecks	Applying zstd/lz4 on high-write tables without testing	CPU spikes, increased latency, negated storage savings	Benchmark compression ratios vs CPU overhead; use columnar compression for analytical workloads only
2	Aggressive auto-scaling triggering cold starts	Scaling thresholds too tight, no warm-up strategy	2–5s latency spikes during scale-out, SLA violations	Implement predictive scaling, connection pre-warming, and minimum instance baselines
3	Ignoring cross-AZ/region data transfer costs	Multi-AZ replicas, backup replication, ETL pipelines	15–30% of total DB spend hidden in network egress	Co-locate replicas with consumers, use VPC endpoints, compress replication traffic, schedule ETL during off-peak
4	Premature serverless migration without workload analysis	Moving spiky, unpredictable workloads to serverless without baseline metrics	Cost increases during sustained loads, connection limits hit	Profile 30-day query patterns; use serverless only for intermittent/dev workloads or bursty APIs
5	Backup retention misconfiguration	Default 35-day retention with no tiering or lifecycle policies	Exponential storage growth, slow restores, compliance risk	Implement tiered retention (hot 7d, warm 30d, cold 1y), use incremental snapshots, automate lifecycle transitions
6	Index bloat from over-indexing	Adding indexes reactively to slow queries without monitoring	Write amplification, storage waste, vacuum overhead	Track index usage via pg_stat_user_indexes, drop unused indexes, use partial indexes, schedule regular REINDEX
7	FinOps without resource tagging	Unstructured resource naming, shared accounts, no chargeback	Inability to attribute costs, budget overruns, team friction	Enforce mandatory tags (env, team, app, cost-center), automate tagging via IaC, integrate with cost explorer dashboards

---

Production Bundle

Checklist

• Audit current DB instances: compute utilization, storage IOPS, backup retention, replica count
• Enable cost allocation tags across all database resources
• Identify top 10 expensive queries via pg_stat_statements or equivalent
• Create targeted indexes; remove unused indexes (>30 days zero usage)
• Implement table partitioning for time-series or append-only data
• Switch storage type to cost-optimized tier (e.g., gp3 over io1)
• Deploy connection pooler (PgBouncer, ProxySQL) with transaction mode
• Configure auto-scaling with minimum baseline + predictive thresholds
• Implement cache-aside layer for read-heavy endpoints
• Set backup lifecycle policy: hot/warm/cold tiers with retention limits
• Deploy cost anomaly alerts (CPU, storage growth, egress spikes)
• Document cost ownership per team/application via tagging schema

Decision Matrix

Workload Type	Recommended Techniques	Avoid	Risk Level
OLTP (high writes, low latency)	Connection pooling, gp3 storage, partial indexes, auto-scaling with baseline	Serverless, heavy compression, cross-region replicas	Low
OLAP / Analytics	Columnar compression, partitioning, read replicas, cold storage tiering	High-IOPS provisioned, frequent vacuum, synchronous replication	Medium
Microservices / API-driven	Cache-aside, transaction pooling, auto-scaling, tagged cost allocation	Over-indexing, manual scaling, long backup retention	Low
Legacy / Batch Processing	Partitioning, backup lifecycle, storage tiering, off-peak ETL	Real-time replicas, high CPU instances, internet egress	Medium–High
Dev / Staging	Serverless, minimal replicas, short retention, auto-pause	Production-grade IOPS, multi-AZ, 24/7 scaling	Low

Config Template

# cost-optimized-database.tf
variable "environment" { default = "prod" }
variable "team"        { default = "platform" }
variable "app"         { default = "user-service" }

resource "aws_db_instance" "optimized" {
  engine                  = "postgres"
  engine_version          = "15.4"
  instance_class          = "db.r6g.large"
  allocated_storage       = 100
  storage_type            = "gp3"
  max_allocated_storage   = 500
  storage_encrypted       = true
  backup_retention_period = 7
  copy_tags_to_snapshot   = true
  deletion_protection     = var.environment == "prod"

  tags = {
    Environment = var.environment
    Team        = var.team
    App         = var.app
    CostCenter  = "engineering"
    ManagedBy   = "terraform"
  }
}

resource "aws_cloudwatch_metric_alarm" "storage_growth" {
  alarm_name          = "db-storage-growth-${var.app}"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = "3"
  metric_name         = "FreeStorageSpace"
  namespace           = "AWS/RDS"
  period              = "86400"
  statistic           = "Average"
  threshold           = "50000000000" # 50GB threshold
  alarm_description   = "Storage growing faster than expected"
}

Quick Start

Week 1: Discovery & Tagging

• Export current DB inventory and cost breakdown
• Enforce mandatory tagging schema across all database resources
• Deploy pg_stat_statements or equivalent query telemetry
• Baseline CPU, storage, IOPS, and connection metrics

Week 2: Storage & Query Optimization

• Identify top 10 costly queries; create targeted indexes
• Remove unused indexes; schedule REINDEX for bloated tables
• Implement partitioning for tables >10GB with time-based access patterns
• Switch storage to cost-optimized tier; enable auto-scaling storage

Week 3: Architecture & Scaling

• Deploy connection pooler; tune pool size and timeout settings
• Implement cache-aside for read-heavy endpoints; set TTL policies
• Configure auto-scaling with predictive thresholds and minimum baselines
• Consolidate read replicas; verify cache hit ratios >60%

Week 4: Observability & Automation

• Deploy cost anomaly alerts for CPU, storage, and egress
• Implement backup lifecycle policy with tiered retention
• Integrate cost allocation tags with FinOps dashboard
• Document runbooks; schedule monthly cost review cadence

Database cost reduction is not a one-time exercise. It is a continuous feedback loop between engineering, operations, and finance. By implementing storage tiering, query optimization, architectural scaling, and cost telemetry, organizations typically achieve 30–55% reduction in database spend while improving performance and reliability. Start with telemetry, optimize incrementally, and automate guardrails. The savings compound, but only when the process becomes institutionalized.