TRUNCATE - timing of the return of disk space - caused by long-lived client?

Started by Vince Negriover 18 years ago4 messagesgeneral

vnegri@asl-electronics.co.uk

over 18 years ago

Hi All,

Running postgres 8.1.9 on SLES10 (kernel 2.6.16) currently, I noticed something about the behaviour
of TRUNCATE on a large table.

The docs imply that TRUNCATE is the best way to delete everything in a table
(rather than DELETE) since there is no need to VACUUM afterward - the disk space
used is immediately returned to the operating system.

In the setup in question, there is one table in the cluster that acts as a sequential
log. A long-lived process (24/7) connects to the cluster and writes rows to the table
(existing rows are never altered.) The client does not use transactions, only single INSERT commands.

Obviously this table can't be allowed to grow for ever, but it is important not to
disrupt the client connection. One approach is to periodically
DELETE old entries and then do a VACUUM so that they can be re-used. This is quite slow since
the table is large.

So I tried TRUNCATE on the table. It appeared to work - in that the row count dropped to zero
and the connected client was not disrupted, and "du" on the postgres data directory showed a fall.

But the available disk space (reported by "df") did not fall.

So I used "lsof | grep pgsql | grep deleted" to look for files that have been deleted but are held open
and sure enough, there is the file for the table I just truncated. It is referenced by a number of
postmaster processes(threads?) Most of which are associated with connections that have *never queried* the
table in question, which is odd, but one process is associated with the long-lived connection.

What causes the file handles of the truncated table to be released by all postmaster processes?
I am concerned that some of these files will only get fully deleted once all clients have disconnected
or the postgres server shuts down (neither of which is desirable.)

Vince