PostgreSQL - consider using backup retention in non-production projects

This FinOps policy recommends setting a deliberately low automated backup retention count for Cloud SQL for PostgreSQL instances in non-production projects, rather than leaving the default in place or copying a production-sized value. Backup storage is billed per GB every month a backup is retained, so a nonproduction instance holding weeks of backup history accumulates ongoing storage charges long after most of that history has any practical recovery value. This policy applies to nonproduction Cloud SQL for PostgreSQL instances such as staging, development, and QA, where a short recovery window is normally sufficient.

Why This Policy Matters

How It Helps Reduce Cloud Costs

Cloud SQL bills backup storage separately from the instance's provisioned disk, at a per-GB monthly rate. The backup_retention_settings block on google_sql_database_instance controls how many automated backups are kept, using retained_backups as a count when retention_unit is set to COUNT. Google's default is 7 retained backups for Cloud SQL Enterprise edition instances, and 15 for Enterprise Plus edition instances.

Non-production instances often end up with the same retention count as production, either because a module was copied wholesale or because nobody revisited the setting after the environment was created. This policy ensures retention on non-production instances reflects how that environment is actually used, not how production is configured.

An environment tag alone does not settle the question. A project labeled non-production but quietly relied on for disaster-recovery testing may genuinely need a longer retention count than this policy would otherwise suggest.

Potential Savings

Backup storage for Cloud SQL is typically priced in the range of $0.08 to $0.11 per GB per month, though the exact rate varies by region. Cloud SQL backups are incremental, so the exact relationship between retention count and storage size depends on how much data changes between backups, not a fixed size per backup. Even accounting for that, raising the count from 7 to something closer to 30 typically multiplies the backup storage footprint for an instance several times over, since each additional retained backup adds more billed storage on top of what came before.

Teams that intentionally cap non-production retention at a count matching realistic recovery needs, rather than a number inherited from production, consistently carry a smaller backup storage footprint across their staging and development fleets.

Implementation Guide

Infrastructure-as-Code Example (Terraform)

The following examples show a staging instance configured with a production-sized retention count, and the corrected configuration that scales it down for non-production use.

Non-compliant configuration: staging instance retaining a production-sized backup count

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 30
        retention_unit    = "COUNT"
      }
    }
  }
}

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 30
        retention_unit    = "COUNT"
      }
    }
  }
}

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 30
        retention_unit    = "COUNT"
      }
    }
  }
}

This configuration retains 30 backups on a staging instance, the same count a production team might justify for a longer recovery window. This is a common source of wasted spend on environments that were originally cloned from a production module.

Compliant configuration: retention count scaled to non-production needs

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 7
        retention_unit    = "COUNT"
      }
    }
  }
}

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 7
        retention_unit    = "COUNT"
      }
    }
  }
}

resource "google_sql_database_instance" "staging" {
  name             = "app-staging"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  project          = var.project_id

  settings {
    tier = "db-custom-2-7680"

    backup_configuration {
      enabled = true

      backup_retention_settings {
        retained_backups = 7
        retention_unit    = "COUNT"
      }
    }
  }
}

Reducing retained_backups from 30 to 7 keeps a full week of recovery history, typically enough for a staging environment, while meaningfully shrinking the backup storage footprint for this instance. Because backups are incremental, the exact reduction depends on the instance's rate of change, but cutting the retained count to a quarter of its previous value is a directionally reliable way to cut the associated storage cost. This change is non-destructive; it does not delete the database or any existing backup immediately, it only changes how many future backups are kept going forward.

Step-by-Step Fix Instructions

1. List every google_sql_database_instance resource in Terraform state or configuration that belongs to a non-production project, such as staging, development, or QA.

2. Check each instance's backup_configuration.backup_retention_settings.retained_backups value.

3. Confirm with the team whether any of these instances have a specific reason for a longer retention count, such as testing backup or disaster-recovery procedures.

4. Lower retained_backups to a count matching the organization's non-production policy, commonly 7 or fewer.

5. Run terraform plan to confirm the change is non-destructive.

6. Apply the change and confirm in the Cloud Console, under the instance's Backups page, that the retention count reflects the update.

7. Add Infracost to CI/CD so any future change to backup_retention_settings on a non-production instance gets flagged for review before merge.

Best Practices

• Set backup retention counts for non-production Cloud SQL modules explicitly in shared Terraform modules, rather than letting teams copy production settings by default.

• Keep production and non-production retention counts as separate module variables so a single change cannot accidentally affect both environments.

• Revisit retention counts periodically. A count that was reasonable when an environment was created can become outdated as usage patterns shift.

• Treat this policy as something to apply by exception, not a blanket rule. If a specific non-production instance genuinely needs a longer retention count, document why rather than leaving a high count in place by default.

Tools and Scripts

Infracost supports this policy check in its free trial and paid plans. When Infracost runs in CI/CD, it evaluates google_sql_database_instance resources introduced or modified in a pull request and surfaces a finding whenever a non-production instance's retained_backups count looks disproportionate, giving the team a chance to confirm it before the change merges.

This gives platform teams a pre-merge signal instead of discovering an oversized backup footprint on next month's bill. Infracost enables teams to track how many non-production instances are out of line with policy over time, which supports burning down this category of waste incrementally and reporting progress to engineering leadership.

To check current backup retention settings using the gcloud CLI:

gcloud sql instances describe INSTANCE_NAME --project=PROJECT_ID --format="value(settings.backupConfiguration)"

gcloud sql instances describe INSTANCE_NAME --project=PROJECT_ID --format="value(settings.backupConfiguration)"

gcloud sql instances describe INSTANCE_NAME --project=PROJECT_ID --format="value(settings.backupConfiguration)"

Examples of Impact

Illustrative example: a cloned production module. A team stands up a staging replica of a production database by copying the production Terraform module, including its retained_backups value of 30. The staging instance never needs a month of recovery history, since any real incident on staging is usually resolved by simply re-seeding it from a script. Scaling retained_backups down to 7 removes weeks of unnecessary backup storage without changing how the team actually recovers staging data.

Illustrative example: forgotten QA environment. A QA project inherits a high retention count set years earlier for a different testing purpose. Nobody revisits it as the project's role changes. A routine policy review catches the mismatch and reduces the count to match how the environment is used today, cutting that instance's backup storage line item without any change to the QA workflow.

(These are illustrative, composite scenarios, not specific customer accounts.)

Considerations and Caveats

• Default retention varies by edition: Cloud SQL defaults to 7 retained backups for Enterprise edition instances and 15 for Enterprise Plus edition instances, so confirm which edition an instance uses before assuming the default applies.

• Retention count affects recovery window, not just cost: lowering retained_backups also reduces how far back a team can restore from a full backup snapshot. Pick a count that still covers realistic non-production recovery needs, not the lowest number possible.

• Point-in-time recovery is a separate setting: if point_in_time_recovery_enabled is set, Cloud SQL retains transaction logs separately from backup snapshots, and this policy's retained_backups change does not affect that log retention window.

• Provider-specific: this policy targets Cloud SQL for PostgreSQL and the google Terraform provider. AWS RDS for PostgreSQL uses a different, time-based mechanism, backup_retention_period on aws_db_instance, measured in days rather than a backup count.

• This policy does not apply to non-production instances used to validate backup and disaster-recovery procedures, where deliberately matching production retention is the environment's purpose.

Related Policies and Concepts

• PostgreSQL - consider enabling autogrow on non-production projects: another Compute-group FinOps policy addressing right-sized configuration for non-production Cloud SQL for PostgreSQL instances, focused on storage growth settings rather than backup retention.

• RDS and EMR gp2-to-gp3 storage upgrades: other Compute-group FinOps policies that reduce cost through a configuration change rather than a usage change, the same underlying discipline this policy applies to backup retention counts.

• BigQuery - consider using on-demand pricing before purchasing reserved slots: a related Compute-group policy focused on not paying for more capacity or retention than a workload actually needs.

• "How to Enforce Cloud Cost Policies in Your CI/CD Pipeline": a planned Infracost resource article covering the broader workflow of catching cost policy violations like this one at the pull request stage, before they reach production billing.

Frequently Asked Questions (FAQs)

Is this policy supported in Infracost?

Yes. Infracost evaluates google_sql_database_instance resources in pull requests and surfaces a finding when a non-production instance's backup retention count looks disproportionate, so the team can confirm it's justified before merge. This check is available in the free trial and all paid plans.

Can this policy be customized?

Yes. Teams can adjust the policy's scope in Infracost, for example setting a different retention count threshold for specific projects or environments, rather than relying on a single default.

Does Infracost automatically fix violations?

No. Infracost identifies and reports a google_sql_database_instance resource whose backup retention count looks disproportionate for a non-production project. Lowering retained_backups is a decision the team makes and applies manually.

Is this policy cloud-agnostic?

No. This specific policy targets Cloud SQL for PostgreSQL and its Terraform provider, google. AWS RDS for PostgreSQL uses a different, time-based backup retention mechanism, so the Terraform resource and attribute names differ.

How often should I review backup retention settings?

Teams running Infracost in CI/CD get a check on every pull request that touches a google_sql_database_instance resource's backup configuration. For instances already in place, review retention counts on a recurring basis, quarterly is a reasonable cadence for most non-production fleets.

What happens if I set the backup retention count too low for a non-production instance?

A very low count shortens how far back the team can restore from a full backup snapshot. If a non-production instance is used for anything beyond routine testing, such as validating backup and disaster-recovery procedures, the retention count should stay high enough to support that use.

Does reducing backup retention affect point-in-time recovery?

Not directly. Point-in-time recovery relies on transaction log retention, a separate setting from retained_backups. Reducing the backup count changes how many full backup snapshots are kept, not how many days of transaction logs are retained for point-in-time recovery.