Re: After upgrade to 9.5 space not being released

On Fri, Jun 3, 2016 at 1:17 PM, Ganesh Kannan
<ganesh(dot)kannan(at)weatheranalytics(dot)com> wrote:
>
> I have to ask this out of curiosity... not removing the 9.4 data directories explains why the space was not released. However, why did I not see the same issue with Index tablespace directories. I have about 4 TB of indexes and the vols were pretty much 90% utilized before the "alter table set logged", and may be 95% or so after running the statement. Does the switch from "unlogged" to "logged" of tables has a different effect on indexes - may be indexes are not "really" rebuilt as were the tables.

When a relation is removed, any datafiles after the first one are
immediately unlinked. But that leaves the old directory with links to
full-sized files.

But the first datafile of each is treated differently, it is first
truncated to zero size, and then not unlinked until the next
checkpoint completes. Truncating via one hard link of course reduces
the size as "seen" by the other hard link, too, so that space gets
freed.

So if most of your indexes are less than 1 gig, the space used by
those indexes will get freed via truncation, while the larger tables
will only have the first gig freed.

That's my theory, anyway.

Cheers,

Jeff