How I saved $2,000 / month in Cloud SQL for Postgres; point-in-recovery might cost too much

I have a large postgres database for our web application on Google Cloud (Cloud SQL for PostgreSQL).
A few months ago, I found that the storage usage was over 2TB and it costed us around $3,000 / month. I thought this was too much.

Here's the note when I solved this issue.

tl;dr

Disable point-in-time recovery if you don't need it.

This feature uses a lot of storage because it saves write-head log for some period.
It may cause a problem especially when your database has a large table with high frequency of updates.

The option is in Backups section.

Analyze the size of tables

First, I looked at how much storage each table used.

    table_schema    |                table_name                 | row_estimate  |   total    |   index    |   toast    |   table
 --------------------+-------------------------------------------+---------------+------------+------------+------------+------------
  public             | table1                      | 6.7337024e+08 | 205 GB     | 130 GB     |            | 76 GB
  public             | table2                        | 7.2914736e+07 | 11 GB      | 3871 MB    | 8192 bytes | 7497 MB
  public             | table3                                    |  2.826758e+07 | 10 GB      | 5137 MB    | 8192 bytes | 5514 MB
...

You can see that quickly by running a SQL in this link.

The size of the biggest table is ~200GB while the total disk usage is 2TB. This is strange.

Vacuum

I run vacuum full just in case. This command removes deleted rows from disk and frees database space.

But it made no difference in this case.

vacuum full table1;

Disable point-in-recovery

I found point-in-recovery was enabled in Cloud Console.

It says:

Allows you to recover data from a specific point in time, down to a fraction of a second, via write-ahead log archiving.

The large table was a write-heavy table, so it produces a lot of write-ahead log.

I disabled the option and restarted the database:

The usage went from ~2TB to ~200GB. As time goes, it increases a little but it's still lower than 300GB .

Also, the cost dropped dramatically. And the monthly cost went from ~$2,500 to ~$200.

I'm totally happy about this result.

Closing

Point-in-recovery is a kind of backup recovery feature, which enables you to restore your database to any point of time.
It's useful in many cases. But in some case, it may cost you more than it benefits.