How to shrink Google Cloud SQL disks

When you use Google Cloud SQL instances, you can configure the disk to automatically scale with "Auto storage increase".
You can also put limits on this functionality to prevent a situation where the storage increases dramatically and uncontrollably.

In some cases, such as accidentally overloading the database with a large SELECT query, the cache can balloon unexpectedly, causing a dramatic increase in storage. This can result in you paying for significantly more storage than necessary.

For example, the graph below illustrates how a database of roughly 1.5TB spiked to nearly 20TB after repeated sorting of a large table, causing an enormous temporary cache build-up. Although the actual data usage returned to normal, the storage capacity did not automatically scale back down, leaving the user with a steep bill. Despite usage returning to pre-incident levels, the cost remains high due to the increased storage capacity, which persists unless manually adjusted.

How to resolve the situation

Unfortunately, there’s no straightforward UI or CLI command to shrink the allocated storage in Google Cloud SQL. Moreover, the backup and restore function in Google Cloud doesn’t allow you to restore to an instance with less storage capacity, so that option is not feasible.

Here are the three potential ways to address this problem:

1. Backup and restore your database manually to a new database

You can use mysqldump, or any database backup and restore tool that you prefer, in order to restore the database to a new instance with a smaller database.
However, for large databases, this approach may be impractical due to the significant downtime required to ensure data consistency and avoid corruption during the backup process.

2. Ask Google Cloud to resize the database for you

If you are paying for technical support, you can ask Google Cloud to perform the action for you.

As communicated to me, by them, they need a 48 hour notice before they start, and they do not provide this service on the weekends or major holidays.
They also want a wide time-range when they can perform the action, in order to ensure they can facilitate the request.

In order to perform this action they will need to take your database offline for the duration of the resizing.

3. Ask Google Cloud for access to the self-service shrink CLI

If you are paying for technical support, you can ask Google Cloud to allow you to access the self-service shrink CLI commands for specific users on specific projects.

This allows you to access the specific alpha command in order to shrink the database back down yourself.

After doing so you can then shrink the SQL instance at your will.
The first step is to query the database to learn how long it would take to shrink the database, and what the recomended minimum size is.

gcloud alpha sql instances get-storage-shrink-config "${DATABASE_ID}" --project="${PROJECT_ID}"

Output:

kind: sql#getDiskShrinkConfig
message: |-
  The size of your instance's storage impacts IOPS, and therefore shrinking your storage may have a negative impact on IOPS. Before shrinking your storage, please refer to our documentation on Cloud SQL limits to understand what impact shrinking your storage might have: https://cloud.google.com/sql/docs/quotas#fixed-limits.
  Additionally, we recommend leaving an appropriate buffer for operational purposes, like reindexing, etc. We recommend a buffer of at least 100 GB or 20% more than current usage, whichever is larger. For more details, please refer to: https://cloud.google.com/sql/docs/shared-responsibility
  The estimated operation time is 308 minutes. The actual operation time can be longer than the estimated time.
minimalTargetSizeGb: '1973'

After doing that you can then shrink the database to meet your requirements:

gcloud alpha sql instances perform-storage-shrink "${DATABASE_ID}" --storage-size="2048GB" --project="${PROJECT_ID}"

Output:

Confirm that you have already run `gcloud alpha sql instances get-storage-shrink-config $instance_name` and understand that this operation will restart your database and 
might take a long time to complete (y/n)

Do you want to continue (Y/n)?  

Performing a storage size decrease on a Cloud SQL instance....⠛

You can see that for this example it warns me that it will take 5 hours, so be-prepared for a long downtime during your off-hours.