ApsaraDB RDS:What do I do if the disk space is not released after I execute the TRUNCATE or DROP TABLE statement in a PostgreSQL database?

Causes

When you execute the TRUNCATE and DROP TABLE statements in a PostgreSQL database, the system initiates an unlink call for each file that you want to delete upon the commitment of the transaction. An unlink call removes the link between a file and its inode. However, if a file is unlinked while it is being opened by a process, the file data is retained on the disk and the associated disk space is not immediately released. The system reclaims the disk space only when one of the following conditions is met:

• All processes that open the file to be deleted are terminated.

• In the processes that open the file to be deleted, a close call is made to the file descriptor (fd) to close the file.

If a connection accesses the table file before the TRUNCATE and DROP TABLE statements are executed and the connection remains idle after the statements are executed, the connection may still have a reference to the file. In this case, the system will not immediately reclaim the associated disk space.

Solutions

If the disk space is not released after you execute the TRUNCATE or DROP TABLE statement, you can use the following methods to resolve the issue:

• Close all idle connections to the database for which you want to release the disk space.

  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE state='idle' AND backend_type='client backend';

• If you want to release the disk space on a self-managed database, you must identify the processes that have opened the unlinked files, and then terminate these processes.

  1. Run the lsof command on the server of the database to find the files that are deleted but are still open by processes.

     lsof +L1

  2. Execute the following SQL statement to terminate the processes:

     SELECT pg_terminate_backend(pid);

     In the statement, pid specifies the process ID of the session to be terminated.