I haveTRUNCATEd a huge (~120Gb) table called 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.

  • 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). Jun 26, 2011 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, 2011 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. Jun 27, 2011 at 12:26

1 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.

  • 3
    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. Jun 27, 2011 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. Jun 27, 2011 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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