Idle sessions keep deleted files open

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.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"