PostgreSQL: Disk space not released after TRUNCATE

Question

I haveTRUNCATEd a huge (~120Gb) table called files:

TRUNCATE files;
VACUUM FULL files;

The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space?

UPDATE: The disk space was released after ~12 hours, without any action on my side. I use Ubuntu 8.04 server.

• Denis de Bernardy: I was about to suggest "vacuum it!", but considering you just did, I'd advise taking this over to either of the pg-hackers or pg-performance lists (and linking back to the thread or answer once you've got one).
• DrColossos: Does this link (postgresql.1045698.n5.nabble.com/…) give any advice for you? Maybe there is still something accessing the table or similar.
• Mike Sherrill 'Cat Recall': @DrColossos: I read a comment in the source (a comment which I can't find right now) that said PostgreSQL notified all the connections that a truncate was about to take place, and it locked the necessary resources. (There are several, including the table itself, indexes, sequences, and toast tables.) I'm pretty sure I found the comment earlier by tracing through ExecuteTruncate(), but I'm not 100% positive about that.

Answer 1

According to comments in the source, truncate creates a new, empty storage file, and deletes the old storage file at commit time. (Docs suggest "storage file" is just a file as far as the OS is concerned, but I might be misunderstanding the terminology.)

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.

Since it seems to be deleting a file, I can imagine some cases in which the underlying operating system might not immediately free that space. I imagine that in some cases the storage file might end up in the Recycling Bin under Windows, for example. But in my case, truncating a table under PostgreSQL 9.something immediately increased the freespace under Windows.

Truncation is also recorded in the WAL log. I don't know how much effect that might have.

• Peter Eisentraut: It's conceivable that another backend process still has a file descriptor to the file open. If this happens again, I'd try terminating all other backend processes, just to see whether it makes a difference.
• Mike Sherrill 'Cat Recall': I'm pretty sure I read that ExecuteTruncate() is supposed to make sure that can't happen. All part of locking the resources necessary to eventually delete the old storage file. But I don't know where I found that in the source. I'm just browsing it online; it's easy to get lost that way.