Acme SaaS — PostgreSQL/Aurora Cost & Reliability Review

Prepared by: AKS Engineering · Engagement: Standard Review · Scope: Production Aurora PostgreSQL estate (3 clusters) · Access model: Read-only role + snapshot + 30-day metrics export

1. Executive Summary

Acme SaaS runs a customer-facing B2B platform on three Aurora PostgreSQL clusters (primary application DB, analytics DB, billing DB). Combined database spend is approximately $28,000/month, growing ~6% month-over-month — faster than user growth.

Spend concentrates in three areas: oversized instances carrying low steady-state utilization, Aurora I/O charges driven by inefficient query and index patterns, and idle/duplicate read replicas. Together these represent a quantified savings opportunity in the range of $7,000–$10,000/month, the majority of which is low-to-moderate effort and low risk (math in §4).

The review also found two material reliability risks independent of cost: failover has never been tested on the billing cluster, and backup restores are unverified across all three clusters. Finally, observability gaps mean Acme currently cannot answer several basic cost and reliability questions without manual effort — closing these is the prerequisite for sustaining any savings and is the first item in the plan.

Recommended sequence: instrument first (observability) → capture low-risk cost wins → right-size & reduce structural waste → harden reliability.

2. Environment Overview — what we reviewed

Aurora PostgreSQL 14.x, single region, Multi-AZ on app and billing. The app cluster connects directly with no pooler. pg_stat_statements and Performance Insights are enabled on acme-app only. Context from intake: the analytics cluster was sized during a one-time historical backfill 14 months ago and never re-evaluated; the billing reader was added "for reporting," but reporting now runs against analytics.

3. Top 10 Findings — how we think

Each finding carries evidence (a metric, query, or config), an impact rating, an effort estimate, and the risk of the fix itself.

4. Cost Savings Opportunities — technical issue → business impact

Each opportunity shows the reasoning. Estimates are ranges, not guarantees, and assume changes are validated in staging first.

• Right-size acme-analytics (F1): peak CPU ~14% over 30 days; one class down ≈ 50% of writer compute ≈ $2,500–$3,500/mo. Validate against month-end reporting peak.
• Reduce Aurora I/O on acme-app (F2): 3 queries dominate logical reads via sequential scans; targeted indexes + one rewrite ≈ $1,500–$2,500/mo plus latency gains.
• Remove idle billing reader (F3): negligible read traffic after reporting moved; ≈ $1,200–$1,500/mo, no functional impact after a consumer check.
• Bloat + unused-index cleanup (F6, F8): reclaim storage and write amplification ≈ $800–$1,500/mo.
• Schedule non-prod (F10): stop outside business hours ≈ $500–$900/mo.

Total quantified opportunity: ~$7,000–$10,000/month for this environment, the bulk at low-to-moderate effort and low risk. Realized savings depend on validation and execution — this is the opportunity found, not a promised outcome.

5. Reliability Risks — how we prioritize

These are addressed regardless of the cost work; they are not optional optimizations.

• R1 — Untested failover on billing (F4). The highest-consequence cluster has never exercised failover; real RTO is unknown. Run a scheduled failover test; document RTO; confirm app reconnect.
• R2 — Unverified restores (F5). Backups configured, but no restore performed. Perform and time a restore of each cluster to a scratch environment; document real RPO/RTO.
• R3 — Connection saturation on app (F7). Without a pooler, connections approach the limit at peak — a latent incident. Introduce RDS Proxy/PgBouncer; alert on saturation.

6. Observability Gaps

Acme currently cannot answer these without manual effort: query cost on analytics/billing (no pg_stat_statements), replication-lag alerting, storage-growth forecast, and connection-saturation alerting. These gaps make savings unsustainable — without baselines you can't prove a change helped or catch a regression. Closing them is step one.

7. 30/60/90-Day Action Plan

First 30 days — instrument & capture low-risk wins

• Enable pg_stat_statements + slow-query logging on analytics & billing.
• Add replication-lag, storage-runway, and connection-saturation alerts.
• Remove idle billing reader (after consumer check); schedule non-prod.
• Perform and time a restore on each cluster.

Days 31–60 — right-size & reduce structural waste

• Right-size the analytics writer, validated against month-end peak.
• Implement targeted indexes / query fix for the top I/O queries on app.
• Drop six unused indexes after confirming zero reads.

Days 61–90 — harden & sustain

• Run a scheduled failover test on billing; document RTO.
• Introduce connection pooling; alert on saturation.
• Bloat reclamation on the four high-churn tables in a maintenance window.
• Establish a monthly baseline and plan a re-review to measure realized savings.

8. Appendix

Diagnostic queries used: top queries by time/I/O (pg_stat_statements), unused/duplicate index detection, bloat estimation, sequential-scan ratio, connection and replication-lag snapshots. Metrics reviewed: 30 days of CloudWatch (CPU, memory, connections, IOPS, replication lag, storage) + Performance Insights on app. Assumptions & limitations: point-in-time over the 30-day window; list pricing for the region; all changes assumed validated in staging and applied via change management.