Cost-Optimized Database Solutions (Task 4.3)

Cost-Optimized Database Solutions

Source: https://docs.aws.amazon.com/wellarchitected/latest/cost-optimization-pillar/

Database cost has four dials: the ENGINE (license + fit), the CAPACITY MODEL (provisioned vs serverless vs on-demand), the CONNECTION/READ topology (replicas, proxies, caches), and RETENTION (how long data stays in the expensive live tier). Wrong settings on any dial can 3x the bill without any user noticing.

Engine Choice = Cost Choice

How to reason it: the first question is USAGE SHAPE, not features. Intermittent/unknown → pay-per-use models (DynamoDB on-demand, Aurora Serverless) — you pay a premium per unit but ZERO for idle. Steady 24/7 → provisioned + commitments — the premium reverses: on-demand costs 3–5x per unit at steady volume. Analytics on an OLTP engine is the classic double waste (wrong engine at wrong scale) — columnar or serverless-S3 query wins.

Aurora Serverless: ACUs scale to zero-ish; you pay for storage + per-second compute while the database is ACTIVE — the answer for "database used a few hours daily" or unpredictable dev environments.

Real use-case: An internal tools database (used 9–18h weekdays) ran a db.r5.large 24×7. Aurora Serverless v2 billed only active ACU-hours: idle elimination saved $1.8k/mo — the database is simply OFF when nobody logs in, with cold-start too fast for users to notice.

Gotchas & interview notes: on-demand DynamoDB is ~3–5x per-unit vs provisioned at STEADY volume — spiky/unpredictable is its home, not steady. "Eliminate Oracle/SQL Server license fees" (the biggest line item in legacy DB bills) → migrate to Aurora PostgreSQL (SCT + DMS).

Capacity and Connection Economics

• Rightsize the instance class: db.m5.2xlarge → db.m5.large when CPU sits at 8%
• Read replicas replicate COST too — add only when read load justifies; point BI/reporting at a replica instead of growing the primary
• RDS Proxy pooling avoids sizing the DB up just for connection counts (Lambda storms) — cheaper than vertical scaling
• Storage/IOPS: gp3-based RDS storage + storage autoscaling avoids over-provisioning IOPS
• ElastiCache in front of hot reads: cache hit costs a fraction of a DB scale-up
• Tune automated backup retention window (7→fewer days) where business allows
• Manual snapshot sprawl: tag + expire (AWS Backup/DLM lifecycle)
• Cross-Region snapshot copies only where DR demands (each copy bills in the target Region)
• Archive old OLTP data out of the expensive live DB into S3 (Glacier) — retention policies that DELETE expired data are a first-class cost control
• Heterogeneous (Oracle → Aurora PostgreSQL): SCT + DMS — eliminate license fees (the biggest line item)
• Warehouse: unload cold data from Redshift to S3 (Spectrum/Athena) instead of keeping it in nodes
• Time-series/format choice: right tool (Timestream/columnar) avoids over-paying general-purpose engines
• $18k/month: Oracle on RDS (licenses) + db.r5.8xlarge (sized for month-end reports)
• Migrate to Aurora PostgreSQL via SCT+DMS → license cost $0 (−$6k)
• Month-end reporting isolated: add ONE read replica used 4 days/month; downsize primary to db.r5.2xlarge (−$4k)
• Session catalog (simple key-value, wild traffic): move to DynamoDB on-demand (−$1.5k of RDS footprint)
• Internal tools DB used 9–18h weekdays → Aurora Serverless v2 (−$1.8k idle elimination)
• Retention: 5-year-old orders offloaded to S3 Glacier via DMS → smaller primary storage (−$0.9k)
• Result ≈ 60%+ reduction

How the read-load ladder works (cheapest first): (1) a CACHE absorbs repeated reads at ~1/10 the cost of a replica; (2) a READ REPLICA adds a full database's price but serves arbitrary fresh queries; (3) UPGRADING the primary is the most expensive answer per unit of read capacity. Scale up the LADDER only when the lower rung fails the requirement (freshness, complexity, fan-out).

Real use-case: Month-end reporting made a db.r5.8xlarge necessary — but only 4 days/month. The fix: downsize the primary to db.r5.2xlarge and add ONE read replica used by BI those 4 days. Read capacity where and when it is needed: −$4k/mo for identical month-end performance.

Gotchas & interview notes: "Lambda functions keep opening too many connections" → RDS Proxy (cheap) NOT a bigger instance (expensive). Read replicas are async — financial-accuracy reads stay on the primary. Storage autoscaling prevents both out-of-space incidents AND over-provisioned storage.

Backup and Retention Policies (Direct Savings)

• Rightsize the instance class: db.m5.2xlarge → db.m5.large when CPU sits at 8%
• Read replicas replicate COST too — add only when read load justifies; point BI/reporting at a replica instead of growing the primary
• RDS Proxy pooling avoids sizing the DB up just for connection counts (Lambda storms) — cheaper than vertical scaling
• Storage/IOPS: gp3-based RDS storage + storage autoscaling avoids over-provisioning IOPS
• ElastiCache in front of hot reads: cache hit costs a fraction of a DB scale-up
• Tune automated backup retention window (7→fewer days) where business allows
• Manual snapshot sprawl: tag + expire (AWS Backup/DLM lifecycle)
• Cross-Region snapshot copies only where DR demands (each copy bills in the target Region)
• Archive old OLTP data out of the expensive live DB into S3 (Glacier) — retention policies that DELETE expired data are a first-class cost control
• Heterogeneous (Oracle → Aurora PostgreSQL): SCT + DMS — eliminate license fees (the biggest line item)
• Warehouse: unload cold data from Redshift to S3 (Spectrum/Athena) instead of keeping it in nodes
• Time-series/format choice: right tool (Timestream/columnar) avoids over-paying general-purpose engines
• $18k/month: Oracle on RDS (licenses) + db.r5.8xlarge (sized for month-end reports)
• Migrate to Aurora PostgreSQL via SCT+DMS → license cost $0 (−$6k)
• Month-end reporting isolated: add ONE read replica used 4 days/month; downsize primary to db.r5.2xlarge (−$4k)
• Session catalog (simple key-value, wild traffic): move to DynamoDB on-demand (−$1.5k of RDS footprint)
• Internal tools DB used 9–18h weekdays → Aurora Serverless v2 (−$1.8k idle elimination)
• Retention: 5-year-old orders offloaded to S3 Glacier via DMS → smaller primary storage (−$0.9k)
• Result ≈ 60%+ reduction

How retention math compounds: daily snapshots × 365-day retention on a 2 TB database = ~60+ TB of backup storage (with incrementals, less — but still the largest hidden line in many DB bills). Tiered retention (daily-7d, weekly-30d, monthly-1y) delivers the same restore granularity at a fraction of the copies. And EXPIRING old data from the live tier shrinks storage, backup, AND index/scan time — one policy, three savings.

Real use-case: Five-year-old orders (40% of rows, 0.01% of queries) offloaded from RDS to S3 Glacier via DMS: primary storage shrank (−$0.9k/mo), backups got smaller in proportion, and queries on live data ran FASTER — retention was a performance control disguised as a cost control.

Gotchas & interview notes: "compliance requires 7-year retention" does NOT mean 7 years in the live DB — archive to Glacier (with Object Lock where mandated) and keep the OLTP tier lean.

Migrations and Formats

• Rightsize the instance class: db.m5.2xlarge → db.m5.large when CPU sits at 8%
• Read replicas replicate COST too — add only when read load justifies; point BI/reporting at a replica instead of growing the primary
• RDS Proxy pooling avoids sizing the DB up just for connection counts (Lambda storms) — cheaper than vertical scaling
• Storage/IOPS: gp3-based RDS storage + storage autoscaling avoids over-provisioning IOPS
• ElastiCache in front of hot reads: cache hit costs a fraction of a DB scale-up
• Tune automated backup retention window (7→fewer days) where business allows
• Manual snapshot sprawl: tag + expire (AWS Backup/DLM lifecycle)
• Cross-Region snapshot copies only where DR demands (each copy bills in the target Region)
• Archive old OLTP data out of the expensive live DB into S3 (Glacier) — retention policies that DELETE expired data are a first-class cost control
• Heterogeneous (Oracle → Aurora PostgreSQL): SCT + DMS — eliminate license fees (the biggest line item)
• Warehouse: unload cold data from Redshift to S3 (Spectrum/Athena) instead of keeping it in nodes
• Time-series/format choice: right tool (Timestream/columnar) avoids over-paying general-purpose engines
• $18k/month: Oracle on RDS (licenses) + db.r5.8xlarge (sized for month-end reports)
• Migrate to Aurora PostgreSQL via SCT+DMS → license cost $0 (−$6k)
• Month-end reporting isolated: add ONE read replica used 4 days/month; downsize primary to db.r5.2xlarge (−$4k)
• Session catalog (simple key-value, wild traffic): move to DynamoDB on-demand (−$1.5k of RDS footprint)
• Internal tools DB used 9–18h weekdays → Aurora Serverless v2 (−$1.8k idle elimination)
• Retention: 5-year-old orders offloaded to S3 Glacier via DMS → smaller primary storage (−$0.9k)
• Result ≈ 60%+ reduction

Worked Example: Database Bill Audit

• Rightsize the instance class: db.m5.2xlarge → db.m5.large when CPU sits at 8%
• Read replicas replicate COST too — add only when read load justifies; point BI/reporting at a replica instead of growing the primary
• RDS Proxy pooling avoids sizing the DB up just for connection counts (Lambda storms) — cheaper than vertical scaling
• Storage/IOPS: gp3-based RDS storage + storage autoscaling avoids over-provisioning IOPS
• ElastiCache in front of hot reads: cache hit costs a fraction of a DB scale-up
• Tune automated backup retention window (7→fewer days) where business allows
• Manual snapshot sprawl: tag + expire (AWS Backup/DLM lifecycle)
• Cross-Region snapshot copies only where DR demands (each copy bills in the target Region)
• Archive old OLTP data out of the expensive live DB into S3 (Glacier) — retention policies that DELETE expired data are a first-class cost control
• Heterogeneous (Oracle → Aurora PostgreSQL): SCT + DMS — eliminate license fees (the biggest line item)
• Warehouse: unload cold data from Redshift to S3 (Spectrum/Athena) instead of keeping it in nodes
• Time-series/format choice: right tool (Timestream/columnar) avoids over-paying general-purpose engines
• $18k/month: Oracle on RDS (licenses) + db.r5.8xlarge (sized for month-end reports)
• Migrate to Aurora PostgreSQL via SCT+DMS → license cost $0 (−$6k)
• Month-end reporting isolated: add ONE read replica used 4 days/month; downsize primary to db.r5.2xlarge (−$4k)
• Session catalog (simple key-value, wild traffic): move to DynamoDB on-demand (−$1.5k of RDS footprint)
• Internal tools DB used 9–18h weekdays → Aurora Serverless v2 (−$1.8k idle elimination)
• Retention: 5-year-old orders offloaded to S3 Glacier via DMS → smaller primary storage (−$0.9k)
• Result ≈ 60%+ reduction

The senior summary: match the capacity model to the usage shape, buy read capacity up the ladder (cache → replica → resize), pool connections instead of growing them, and treat data retention as a live architectural decision — every dial compounds.