Release disk space not freed after TRUNCATE or DROP TABLE on PostgreSQL - ApsaraDB RDS

After you run TRUNCATE or DROP TABLE in a PostgreSQL database, the disk space may not decrease immediately, even though the data is logically removed. This article explains why and how to reclaim the space.

Note

TRUNCATE and DROP TABLE remove data at the database level immediately. The delay described here is an OS-level storage reclamation issue — your data is deleted, but the OS has not yet freed the underlying disk blocks.

Why this happens

When a transaction that contains TRUNCATE or DROP TABLE commits, PostgreSQL issues an unlink system call for each affected table file. An unlink call removes the file's directory entry and severs the link between the file and its inode.

If any process still holds an open file descriptor (fd) to that file at the time of the unlink, the OS retains the file data on disk until one of the following conditions is met:

All processes that have the file open are stopped.
Every open file descriptor to the file is closed by a close system call.

The most common cause: a client connection accessed the table before TRUNCATE or DROP TABLE ran, then went idle without closing. That idle connection still holds a reference to the file, which prevents OS-level disk reclamation.

Reclaim the disk space

Option 1: Stop idle connections

Run the following SQL to close all idle client connections to the database:

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

Important

This stops all client sessions that are currently idle. Before running this in production, confirm that the idle connections are not part of a connection pool that your application relies on. Stopping pooled connections abruptly may cause brief errors until the pool reconnects.

Once all idle connections are closed, the OS reclaims the disk space automatically.

Option 2: Identify and stop specific processes (self-managed databases)

On self-managed PostgreSQL servers, you can identify exactly which processes are holding references to deleted files before stopping them.

On the database server, run lsof to list files that have been deleted but are still held open:
```
lsof +L1
```
For each relevant PID from the lsof output, run the following SQL to stop that session:
```
SELECT pg_terminate_backend(<pid>);
```
Replace <pid> with the process ID from the lsof output.

After all holding processes are stopped, the OS releases the disk space automatically.