patternsqlModerate
PostgreSQL: Disk space not released after TRUNCATE
Viewed 0 times
postgresqlafterspacedisktruncatereleasednot
Problem
I have
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.
TRUNCATEd 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.
Solution
According to comments in the source,
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.
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.
Context
StackExchange Database Administrators Q#3491, answer score: 13
Revisions (0)
No revisions yet.