CODEWITHSUNDEEP
postgresqlgoogle-cloud-sql
edwnmnashe
edwnmnashe

Reputation: 43

Increase in data storage on Google Cloud SQL after deleting data from tables

After deleting tons of table data approximately about 35 million records from different tables storage has jumped from 41GB to 89GB, this is the exact opposite of what were expecting. Any idea of what could be going on?

The database is PostgreSQL.

Upvotes: 2

Views: 642

Answers (1)

EX0MAK3R
EX0MAK3R

Reputation: 185

From the docs:

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

Also, Write-Ahead Logging is the reason for the increase in size because of the additional logs PostgreSQL has created when you ran the delete operations.

PostgreSQL automatically performs routine autovacuum which should be able to free up space. If you want to manually reclaim space, you do that either with VACUUM or VACUUM FULL, you can read more about here and decide which one is better for you.

Upvotes: 2

Related Questions