Storage spikes on ApsaraDB RDS for PostgreSQL typically come from one of two sources: write-ahead log (WAL) accumulation from heavy write workloads, or temporary files generated by expensive queries. Use the storage analysis feature to confirm which source is consuming space before taking action.
WAL log accumulation
Heavy UPDATE workloads generate WAL entries faster than they can be archived or cleaned up.
Fix: Expand the storage capacity of the instance, or reduce the frequency of write operations to give WAL archiving time to catch up.
Temporary file growth from queries
Sorting and join operations that spill to disk as temporary files can consume storage rapidly, especially when a burst of concurrent or poorly optimized queries runs at the same time.
Fix: Set a per-query temporary file limit to detect runaway queries before they exhaust storage. Run the following statement as the instance's privileged account:
ALTER ROLE ALL SET temp_file_limit = [$Table_Space_Limit];Replace [$Table_Space_Limit] with the maximum temporary file storage, in KB, that any single query is allowed to use. When a query exceeds this limit, PostgreSQL returns an error, which lets you identify the problematic SQL statement before it exhausts storage.