12

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.

|improve this question
  • 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). – Denis de Bernardy Jun 26 '11 at 21:32
  • Does this link (postgresql.1045698.n5.nabble.com/…) give any advice for you? Maybe there is still something accessing the table or similar. – DrColossos Jun 27 '11 at 9:24
  • @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. – Mike Sherrill 'Cat Recall' Jun 27 '11 at 12:26
12

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.

|improve this answer
  • 2
    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. – Peter Eisentraut Jun 27 '11 at 17:28
  • 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. – Mike Sherrill 'Cat Recall' Jun 27 '11 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.