Find Overprovisioned RDS Allocated Storage

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadDbInstances",
      "Effect": "Allow",
      "Action": "rds:DescribeDBInstances",
      "Resource": "*"
    },
    {
      "Sid": "ReadStorageMetrics",
      "Effect": "Allow",
      "Action": "cloudwatch:GetMetricData",
      "Resource": "*"
    }
  ]
}

// find-overprovisioned-rds-storage.ts
// Compares each RDS DB instance's allocated storage with the lowest FreeStorageSpace seen over the
// last 30 days, suggests a smaller target size, and prices the difference. Also flags gp2 and io1
// storage that could move to gp3. Report only: RDS can't shrink storage in place.
// Usage: npx tsx find-overprovisioned-rds-storage.ts [--regions us-east-1,eu-west-1] [--days 30]
import { RDSClient, paginateDescribeDBInstances, type DBInstance } from "@aws-sdk/client-rds";
import { CloudWatchClient, paginateGetMetricData, type MetricDataQuery } from "@aws-sdk/client-cloudwatch";

const args = process.argv.slice(2);
const flag = (name: string): string | undefined => {
  const i = args.indexOf(name);
  return i >= 0 ? args[i + 1] : undefined;
};
const regions = (flag("--regions") ?? process.env.AWS_REGION ?? "us-east-1").split(",").map((r) => r.trim()).filter(Boolean);
const days = Math.min(Math.max(Number(flag("--days") ?? 30), 7), 90);
const HEADROOM = 1.5; // target = peak used storage x 1.5, so the database stays well under 70% full
const GIB = 1024 ** 3;

// us-east-1 list prices per GB-month and per provisioned IOPS-month (AWS Price List, September 2026).
// Multi-AZ DB instance deployments are billed at twice these rates.
const STORAGE_PRICE: Record<string, number> = { gp2: 0.115, gp3: 0.115, io1: 0.125, io2: 0.125 };
const IOPS_PRICE: Record<string, number> = { io1: 0.1, io2: 0.1, gp3: 0.02 }; // gp3: only IOPS above the included baseline
const MIN_STORAGE: Record<string, number> = { gp2: 20, gp3: 20, io1: 100, io2: 100 };

interface Row {
  Instance: string;
  Engine: string;
  Type: string;
  AllocatedGiB: number;
  PeakUsedGiB: number | string;
  TargetGiB: number | string;
  SavePerMonth: string;
  Note: string;
}

const errorText = (err: unknown): string => (err instanceof Error ? `${err.name}: ${err.message}` : String(err));

/** Lowest FreeStorageSpace (bytes) per instance over the window. */
async function minFreeSpace(cw: CloudWatchClient, ids: string[]): Promise<Map<string, number>> {
  const end = new Date();
  const start = new Date(end.getTime() - days * 86_400_000);
  const queries: MetricDataQuery[] = ids.map((id, i) => ({
    Id: `f${i}`,
    MetricStat: {
      Metric: { Namespace: "AWS/RDS", MetricName: "FreeStorageSpace", Dimensions: [{ Name: "DBInstanceIdentifier", Value: id }] },
      Period: 86_400,
      Stat: "Minimum",
    },
  }));
  const out = new Map<string, number>();
  for (let i = 0; i < queries.length; i += 500) {
    const pages = paginateGetMetricData({ client: cw }, { MetricDataQueries: queries.slice(i, i + 500), StartTime: start, EndTime: end });
    for await (const page of pages) {
      for (const r of page.MetricDataResults ?? []) {
        const values = r.Values ?? [];
        if (!r.Id || values.length === 0) continue;
        const id = ids[Number(r.Id.slice(1))];
        out.set(id, Math.min(out.get(id) ?? Infinity, ...values));
      }
    }
  }
  return out;
}

function assess(db: DBInstance, minFree: number | undefined): Row {
  const type = db.StorageType ?? "unknown";
  const allocated = db.AllocatedStorage ?? 0;
  const multiAz = db.MultiAZ ? 2 : 1;
  const notes: string[] = [];
  if (type === "gp2") notes.push("gp2: gp3 has the same per-GB price here and a 3,000 IOPS baseline");
  if (type === "io1" && (db.Iops ?? 0) <= 3000) notes.push("io1 at 3,000 IOPS or less: gp3 includes that at no IOPS charge");
  if (db.MaxAllocatedStorage && db.MaxAllocatedStorage > allocated) notes.push(`autoscaling up to ${db.MaxAllocatedStorage} GiB`);

  const row: Row = {
    Instance: db.DBInstanceIdentifier ?? "",
    Engine: db.Engine ?? "",
    Type: type,
    AllocatedGiB: allocated,
    PeakUsedGiB: "-",
    TargetGiB: "-",
    SavePerMonth: "-",
    Note: "",
  };
  if (minFree === undefined) {
    notes.push("no FreeStorageSpace data");
  } else {
    const peakUsed = Math.max(allocated - minFree / GIB, 0);
    const target = Math.max(Math.ceil((peakUsed * HEADROOM) / 10) * 10, MIN_STORAGE[type] ?? 20);
    row.PeakUsedGiB = Math.round(peakUsed);
    row.TargetGiB = target;
    const price = STORAGE_PRICE[type];
    if (target < allocated && price !== undefined) {
      row.SavePerMonth = `$${((allocated - target) * price * multiAz).toFixed(2)}`;
      if (type === "gp3" && allocated >= 400 && target < 400 && !db.Engine?.startsWith("sqlserver")) {
        notes.push("below 400 GiB gp3 drops to a 3,000 IOPS / 125 MiB/s baseline");
      }
    } else {
      row.SavePerMonth = "$0.00";
    }
  }
  if ((type === "io1" || type === "io2") && db.Iops) {
    notes.push(`IOPS cost $${(db.Iops * (IOPS_PRICE[type] ?? 0) * multiAz).toFixed(2)}/month`);
  }
  row.Note = notes.join("; ");
  return row;
}

async function scanRegion(region: string): Promise<Row[]> {
  const rds = new RDSClient({ region });
  const dbs: DBInstance[] = [];
  for await (const page of paginateDescribeDBInstances({ client: rds }, {})) {
    for (const db of page.DBInstances ?? []) {
      // Aurora storage belongs to the cluster and is billed on what you use, so skip Aurora instances.
      if (db.DBInstanceIdentifier && !db.Engine?.startsWith("aurora")) dbs.push(db);
    }
  }
  const free = await minFreeSpace(new CloudWatchClient({ region }), dbs.map((d) => d.DBInstanceIdentifier ?? ""));
  return dbs.map((db) => assess(db, free.get(db.DBInstanceIdentifier ?? "")));
}

async function main(): Promise<void> {
  for (const region of regions) {
    try {
      const rows = await scanRegion(region);
      if (rows.length === 0) {
        console.log(`${region}: no RDS DB instances (Aurora excluded)`);
        continue;
      }
      rows.sort((a, b) => Number(b.SavePerMonth.replace(/[$-]/g, "") || 0) - Number(a.SavePerMonth.replace(/[$-]/g, "") || 0));
      console.log(`\n${region}: peak used = allocated - lowest FreeStorageSpace over ${days} days`);
      console.table(rows);
      const total = rows.reduce((sum, r) => sum + Number(r.SavePerMonth.replace(/[$-]/g, "") || 0), 0);
      console.log(`Storage you could stop paying for: about $${total.toFixed(2)} a month at us-east-1 list prices.`);
    } catch (err) {
      console.error(`${region}: ${errorText(err)}`);
    }
  }
}

main().catch((err) => {
  console.error(errorText(err));
  process.exit(1);
});

npm install @aws-sdk/client-rds @aws-sdk/client-cloudwatch
npm install --save-dev tsx typescript @types/node

AWS_PROFILE=readonly npx tsx find-overprovisioned-rds-storage.ts --regions us-east-1,eu-west-1 --days 30

us-east-1: peak used = allocated - lowest FreeStorageSpace over 30 days
┌─────────┬─────────────────┬────────────┬───────┬──────────────┬─────────────┬───────────┬──────────────┬───────────────────────────────────────────────────────────────────────────────────────────┐
│ (index) │ Instance        │ Engine     │ Type  │ AllocatedGiB │ PeakUsedGiB │ TargetGiB │ SavePerMonth │ Note                                                                                      │
├─────────┼─────────────────┼────────────┼───────┼──────────────┼─────────────┼───────────┼──────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│ 0       │ 'orders-prod'   │ 'postgres' │ 'gp3' │ 1000         │ 271         │ 410       │ '$135.70'    │ 'autoscaling up to 2000 GiB'                                                              │
│ 1       │ 'billing-mysql' │ 'mysql'    │ 'gp2' │ 500          │ 62          │ 100       │ '$46.00'     │ 'gp2: gp3 has the same per-GB price here and a 3,000 IOPS baseline'                       │
│ 2       │ 'analytics-pg'  │ 'postgres' │ 'io1' │ 400          │ 51          │ 100       │ '$37.50'     │ 'io1 at 3,000 IOPS or less: gp3 includes that at no IOPS charge; IOPS cost $300.00/month' │
│ 3       │ 'wiki-db'       │ 'mariadb'  │ 'gp3' │ 100          │ 72          │ 110       │ '$0.00'      │ ''                                                                                        │
└─────────┴─────────────────┴────────────┴───────┴──────────────┴─────────────┴───────────┴──────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘
Storage you could stop paying for: about $219.20 a month at us-east-1 list prices.