Re: Idle sessions keep deleted files open

Hi,

On 2020-03-30 00:49:13 +0200, Peter J. Holzer wrote:
> I noticed this with PostgreSQL 11.7 on Linux:
>
> I did a «vacuum full» on a table which had become quite bloated (size
> before the vacuum: 392 GB, after: 291 GB). I expected the disk usage to
> increase as the new table was written and then drop sharply after the
> vacuum was finished.
>
> However, that didn't happen. While usage increased by about 300 GB
> during the vacuum, it stayed the same afterwards (as reported by «df»).
>
> However, «du» reported about 400 GB less than «df». So apparently, files
> had been unlinked but were still in use by some process.
>
> «lsof» confirmed this: There were some idle sessions which were keeping
> the files open. As soon as I got those sessions to execute some request,
> they closed the files, causing the disk space to be freed.
>
> I think I noticed that before when dropping tables, but didn't draw the
> right conclusion at the time.
>
> So, if you are reorganizing large tables, keep an eye on idle sessions -
> they may keep deleted files around for quite some time.

I think this is partially related to
https://postgr.es/m/20181003223725.elcu3t44fpd4lm56%40alap3.anarazel.de

Greetings,

Andres Freund