Find Idle Amazon Redshift Clusters

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadRedshiftClusters",
      "Effect": "Allow",
      "Action": "redshift:DescribeClusters",
      "Resource": "*"
    },
    {
      "Sid": "ReadRedshiftMetrics",
      "Effect": "Allow",
      "Action": "cloudwatch:GetMetricData",
      "Resource": "*"
    }
  ]
}

// find-idle-redshift-clusters.ts
// Lists every provisioned Amazon Redshift cluster with its node type and count, state (available or paused),
// the most connections and the CPU it used over the last N days, and an on-demand monthly cost estimate.
// Read-only: it never pauses, snapshots or deletes a cluster.
// Usage: npx tsx find-idle-redshift-clusters.ts [--regions us-east-1,eu-west-1] [--days 14] [--csv idle-redshift.csv]
import { writeFileSync } from "node:fs";
import { RedshiftClient, paginateDescribeClusters, type Cluster } from "@aws-sdk/client-redshift";
import { CloudWatchClient, GetMetricDataCommand, 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 = Number(flag("--days") ?? 14);
const csvPath = flag("--csv");
const LOW_CPU = 5; // percent: average CPU below this with almost no connections is "barely used"

// On-demand price per node-hour in us-east-1, from the AWS Price List (checked September 2026).
// Other Regions differ; reserved nodes are billed for their term whatever the cluster does.
const NODE_HOUR: Record<string, number> = {
  "dc2.large": 0.25,
  "dc2.8xlarge": 4.8,
  "ra3.large": 0.543,
  "ra3.xlplus": 1.086,
  "ra3.4xlarge": 3.26,
  "ra3.16xlarge": 13.04,
};
const HOURS_PER_MONTH = 730;

interface Row {
  Region: string;
  Cluster: string;
  NodeType: string;
  Nodes: number;
  Status: string;
  MaxConnections: number | string;
  AvgCpu: number | string;
  MaxCpu: number | string;
  ComputePerMonth: string;
  Verdict: string;
}

async function listClusters(region: string): Promise<Cluster[]> {
  const client = new RedshiftClient({ region });
  const clusters: Cluster[] = [];
  for await (const page of paginateDescribeClusters({ client }, { MaxRecords: 100 })) {
    clusters.push(...(page.Clusters ?? []));
  }
  return clusters;
}

interface Usage {
  maxConnections: number;
  avgCpu: number;
  maxCpu: number;
  datapoints: number;
}

async function measure(region: string, ids: string[]): Promise<Map<string, Usage>> {
  const cw = new CloudWatchClient({ region });
  const end = new Date();
  const start = new Date(end.getTime() - days * 86_400_000);
  const q = (id: string, cluster: string, MetricName: string, Stat: string): MetricDataQuery => ({
    Id: id,
    MetricStat: {
      Metric: { Namespace: "AWS/Redshift", MetricName, Dimensions: [{ Name: "ClusterIdentifier", Value: cluster }] },
      Period: 86_400, // one datapoint per day
      Stat,
    },
    ReturnData: true,
  });
  const queries = ids.flatMap((cluster, i) => [
    q(`conn${i}`, cluster, "DatabaseConnections", "Maximum"),
    q(`cpuavg${i}`, cluster, "CPUUtilization", "Average"),
    q(`cpumax${i}`, cluster, "CPUUtilization", "Maximum"),
  ]);

  const usage = new Map<string, Usage>();
  ids.forEach((id) => usage.set(id, { maxConnections: 0, avgCpu: 0, maxCpu: 0, datapoints: 0 }));
  for (let n = 0; n < queries.length; n += 500) { // GetMetricData accepts up to 500 queries per call
    let NextToken: string | undefined;
    do {
      const res = await cw.send(new GetMetricDataCommand({
        MetricDataQueries: queries.slice(n, n + 500), StartTime: start, EndTime: end, NextToken,
      }));
      for (const r of res.MetricDataResults ?? []) {
        const match = /^(conn|cpuavg|cpumax)(\d+)$/.exec(r.Id ?? "");
        if (!match) continue;
        const u = usage.get(ids[Number(match[2])]);
        const values = r.Values ?? [];
        if (!u || values.length === 0) continue;
        if (match[1] === "conn") {
          u.maxConnections = Math.max(u.maxConnections, ...values);
          u.datapoints += values.length;
        }
        if (match[1] === "cpuavg") u.avgCpu = values.reduce((a, b) => a + b, 0) / values.length;
        if (match[1] === "cpumax") u.maxCpu = Math.max(u.maxCpu, ...values);
      }
      NextToken = res.NextToken;
    } while (NextToken);
  }
  return usage;
}

function verdict(status: string, u: Usage | undefined): string {
  if (status === "paused") return "PAUSED: storage billed only";
  if (status !== "available") return `skipped: ${status}`;
  if (!u || u.datapoints === 0) return "no metrics: check the cluster age";
  if (u.maxConnections === 0) return "IDLE: no connections";
  if (u.maxConnections <= 2 && u.avgCpu < LOW_CPU) return "BARELY USED: check who connects";
  return "in use";
}

async function scanRegion(region: string): Promise<Row[]> {
  const clusters = await listClusters(region);
  if (clusters.length === 0) return [];
  const ids = clusters.map((c) => c.ClusterIdentifier ?? "");
  const usage = await measure(region, ids);
  return clusters.map((c) => {
    const id = c.ClusterIdentifier ?? "";
    const status = c.ClusterStatus ?? "unknown";
    const nodeType = c.NodeType ?? "";
    const nodes = c.NumberOfNodes ?? 1;
    const u = usage.get(id);
    const price = NODE_HOUR[nodeType];
    const running = status === "available";
    return {
      Region: region,
      Cluster: id,
      NodeType: nodeType,
      Nodes: nodes,
      Status: status,
      MaxConnections: running && u ? u.maxConnections : "-",
      AvgCpu: running && u ? Math.round(u.avgCpu * 10) / 10 : "-",
      MaxCpu: running && u ? Math.round(u.maxCpu * 10) / 10 : "-",
      ComputePerMonth: !running ? "$0 (not running)" : price === undefined ? "unknown node type" : `$${(price * nodes * HOURS_PER_MONTH).toFixed(0)}`,
      Verdict: verdict(status, u),
    };
  });
}

function toCsv(rows: Row[]): string {
  const cols = Object.keys(rows[0] ?? {}) as (keyof Row)[];
  const cell = (v: string | number) => `"${String(v).replace(/"/g, '""')}"`;
  return [cols.join(","), ...rows.map((r) => cols.map((c) => cell(r[c])).join(","))].join("\n") + "\n";
}

async function main(): Promise<void> {
  const rows: Row[] = [];
  for (const region of regions) {
    try {
      rows.push(...(await scanRegion(region)));
    } catch (err) {
      console.error(`${region}: ${err instanceof Error ? `${err.name}: ${err.message}` : String(err)}`);
    }
  }
  if (rows.length === 0) {
    console.log(`No provisioned Redshift clusters in ${regions.join(", ")}.`);
    return;
  }
  console.table(rows);
  const idle = rows.filter((r) => r.Verdict.startsWith("IDLE") || r.Verdict.startsWith("BARELY"));
  const monthly = idle.reduce((sum, r) => sum + (NODE_HOUR[r.NodeType] ?? 0) * r.Nodes * HOURS_PER_MONTH, 0);
  console.log(`${idle.length} of ${rows.length} clusters idle or barely used over ${days} days: about $${monthly.toFixed(0)} a month in on-demand compute (us-east-1 prices).`);
  if (csvPath) {
    writeFileSync(csvPath, toCsv(rows));
    console.log(`Wrote ${rows.length} rows to ${csvPath}`);
  }
}

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

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

# Report on two Regions and keep a CSV for the owners
AWS_PROFILE=readonly npx tsx find-idle-redshift-clusters.ts --regions us-east-1,eu-west-1 --csv idle-redshift.csv

# A longer window for monthly reporting clusters
AWS_PROFILE=readonly npx tsx find-idle-redshift-clusters.ts --regions us-east-1 --days 45

┌─────────┬─────────────┬─────────────────┬───────────────┬───────┬─────────────┬────────────────┬────────┬────────┬────────────────────┬───────────────────────────────────┐
│ (index) │ Region      │ Cluster         │ NodeType      │ Nodes │ Status      │ MaxConnections │ AvgCpu │ MaxCpu │ ComputePerMonth    │ Verdict                           │
├─────────┼─────────────┼─────────────────┼───────────────┼───────┼─────────────┼────────────────┼────────┼────────┼────────────────────┼───────────────────────────────────┤
│ 0       │ 'us-east-1' │ 'bi-prod'       │ 'ra3.4xlarge' │ 4     │ 'available' │ 38             │ 41.2   │ 97.5   │ '$9519'            │ 'in use'                          │
│ 1       │ 'us-east-1' │ 'analytics-poc' │ 'ra3.xlplus'  │ 2     │ 'available' │ 0              │ 1.3    │ 4.8    │ '$1586'            │ 'IDLE: no connections'            │
│ 2       │ 'us-east-1' │ 'finance-q-end' │ 'ra3.xlplus'  │ 2     │ 'paused'    │ '-'            │ '-'    │ '-'    │ '$0 (not running)' │ 'PAUSED: storage billed only'     │
│ 3       │ 'eu-west-1' │ 'staging-dw'    │ 'dc2.large'   │ 1     │ 'available' │ 2              │ 2.1    │ 9.4    │ '$183'             │ 'BARELY USED: check who connects' │
└─────────┴─────────────┴─────────────────┴───────────────┴───────┴─────────────┴────────────────┴────────┴────────┴────────────────────┴───────────────────────────────────┘
2 of 4 clusters idle or barely used over 14 days: about $1768 a month in on-demand compute (us-east-1 prices).
Wrote 4 rows to idle-redshift.csv