DEV Community

loading...

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

uiur profile image zato ・2 min read

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.
Screen Shot 2021-01-18 at 19.10.52

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
...

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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:
Screen Shot 2021-01-18 at 18.50.18

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.

Screen Shot 2021-01-18 at 18.46.21

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.

Discussion (0)

pic
Editor guide