Search code examples
postgresqlgoogle-cloud-platformgoogle-cloud-sql

Google Cloud SQL Storage Expanding Issue

My PostgreSQL database storage is expanding way too much relative to my real database storage. I am assuming it is making logs for every actions against the database. If so, how do I turn it off?

enter image description here

All tables' storage size in the database:

   table_name    | pg_size_pretty 
-----------------+----------------
 matches         | 3442 MB
 temp_matches    | 3016 MB
 rankings        | 262 MB
 atp_matches     | 41 MB
 players         | 11 MB
 injuries        | 4648 kB
 tournaments     | 1936 kB
 temp_prematches | 112 kB
 locations       | 104 kB
 countries       | 16 kB
(10 rows)

My storage usage should be around 10GB.

Solution

  • Your PostgreSQL instance may have Point-in-time recovery (PITR) enabled.

    To add explanation, PITR uses write ahead logs (WAL). It is necessary to archive the WAL files for instances it is enabled on. This archiving is done automatically on the backend and will consume storage space (even if the instance is idle) and therefore using this feature would result on an increased storage space on your DB instance.

    Here's a similar issue: Google Cloud SQL - Postgresql storage keeps growing

    You can stop the storage increase by disabling Point-in-time recovery: https://cloud.google.com/sql/docs/postgres/backup-recovery/pitr#disablingpitr