The disk space is not released after the execution of the TRUNCATE or DROP TABLE statement-ApsaraDB RDS(RDS)-阿里云帮助中心

After you run TRUNCATE or DROP TABLE, disk space may not be released immediately. This topic explains why this happens and how to fix it.

Cause

When you run TRUNCATE or DROP TABLE, PostgreSQL issues an unlink call for each file to be deleted at transaction commit. An unlink call removes the link between a file and its inode. However, if another process has the file open at the time of the unlink, the file data remains on disk until one of the following conditions is met:

All processes that have the file open are stopped.
Each process that has the file open closes the file descriptor (fd).

A common trigger is an idle connection that accessed the table before the statement ran. Because the connection still holds a reference to the file, the OS cannot reclaim the disk space.

Solution

Close idle connections (RDS and self-managed databases)

Run the following query to stop all idle client connections to the database:

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

Identify and stop processes holding unlinked files (self-managed databases only)

If closing idle connections does not release the space, a non-idle process may still be holding the unlinked file open. To locate and stop it:

Run lsof +L1 on the database server to list files that have been deleted but are still held open:
```
lsof +L1
```
From the output, identify the process ID (PID) of the process holding the file open. Then stop the corresponding database session:
```
SELECT pg_terminate_backend(<pid>);
```
Replace <pid> with the PID from the lsof output.