All Products
Search
Document Center

ApsaraDB RDS:What do I do if the storage usage on an ApsaraDB RDS for PostgreSQL instance significantly increases?

Last Updated:Aug 24, 2023

Problem description

The storage usage of the RDS instance significantly increases.

Causes

  • Cause 1: A large number of logs are generated due to updates. The logs cannot be archived or deleted at the earliest opportunity.
  • Cause 2: A large number of operations such as sorting and join operations are performed during queries. As a result, temporary tables are generated in a short period of time and occupy a large amount of storage.

Solutions

  • Solution that corresponds to Cause 1: Expand the storage capacity of the RDS instance or decrease the update frequency.
  • Solution that corresponds to Cause 2: Execute the following statement to specify the upper limit of storage that can be occupied by the temporary table for each query. Unit: KB. If the storage that is occupied by the temporary table of a single query reaches the upper limit, an error is reported. This way, you can identify abnormal SQL statements at the earliest opportunity and prevent storage from being exhausted. You must use the privileged account of your RDS instance to execute the following statement:
    alter role all set temp_file_limit = [$Table_Space_Limit];
    Note [$Table_Space_Limit] specifies the upper limit of storage that can be occupied by the temporary table of a query.