[SOLVED] Postgres did not free the space to OS after TRUNCATE of a 145GB table

Postgres did not free the space to OS after TRUNCATE of a 145GB table

I truncated a 145GB table today, but the space did not return to the OS. I already validated inside the database, and the table is now empty.

Despite space was not freed to the OS, I notice that running the du command in the partition already reports 145GB less, but when I run df -h it does not. A discrepancy of 145GB cannot be because of inode size.

I am running a Mirth server with a Postgres 9.3 database in a CentOS 7.

Any clue why space was not freed?

Solution

You have to wait until the transaction is committed for the file to get deleted.

See the following comment in ExecuteTruncateGuts:

/*
 * Need the full transaction-safe pushups.
 *
 * Create a new empty storage file for the relation, and assign it
 * as the relfilenode value. The old storage file is scheduled for
 * deletion at commit.
 */

But since you say that du doesn't report the space any more, one of the following must be the case:

The transaction that ran the TRUNCATE is still open.
Something else has the file open. UNIX does not actually remove the file until the last (hard) link to it is gone and the last process has closed it. The symptom is that df shows that the file is still there, but du doesn't list it.