3

I truncated a 145GB table today, but the space did not return to the OS. I already validated inside the database, and the table is now empty.

Despite space was not freed to the OS, I notice that running the du command in the partition already reports 145GB less, but when I run df -h it does not. A discrepancy of 145GB cannot be because of inode size.

I am running a Mirth server with a Postgres 9.3 database in a CentOS 7.

Any clue why space was not freed?

Winter
  • 1,896
  • 4
  • 32
  • 41

3 Answers3

3

You have to wait until the transaction is committed for the file to get deleted.

See the following comment in ExecuteTruncateGuts:

/*
 * Need the full transaction-safe pushups.
 *
 * 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.
 */

But since you say that du doesn't report the space any more, one of the following must be the case:

  • The transaction that ran the TRUNCATE is still open.

  • Something else has the file open. UNIX does not actually remove the file until the last (hard) link to it is gone and the last process has closed it. The symptom is that df shows that the file is still there, but du doesn't list it.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you Laurenz. I checked now and the space was freed 7h later, probably because of the retained for crash recovery as you said. Next time this happens i will try checkpoint statement. Do you have any idea if this statement is heavily and long ? – Winter Apr 25 '19 at 08:34
  • Btw I did not recover to any point the only operation that i executed was a truncate with cascade. – Winter Apr 25 '19 at 08:41
  • Ext4 as file system. Probably was postgres since is the only one who manipulates directly the files. Maybe Mirth held the old table for some strange reason... – Winter Apr 25 '19 at 13:18
  • I realized that my answer was wrong - the files gets deleted (unlinked) at `COMMIT` time after all. My reasoning was bogus. So something must have been holding the file open. Not sure if that can have been PostgreSQL. – Laurenz Albe Apr 25 '19 at 14:05
0

maybe there are not only big tables, but also views.

you should run this SQL to check which table/view is consuming your disk page.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 100;
Siwei
  • 19,858
  • 7
  • 75
  • 95
-1

When you delete or truncate table, PostgreSQL doesn't return its space to OS
It just marks each row as "deleted".
When you insert a new row, PostgreSQL re-use "deleted" space to store a new data.

To free such "deleted" space, you should use VACUUM FULL
https://www.postgresql.org/docs/devel/sql-vacuum.html

s sato
  • 181
  • 8
  • 3
    This is not true. `TRUNCATE` eventually deletes the file and returns the space to the operating system. – Laurenz Albe Apr 25 '19 at 05:51
  • Hi a sato, please see https://www.postgresql.org/docs/9.3/sql-truncate.html as far as i know truncate is quite good to remove big tables. VACUUM FULL locks the table and in my case due the table size could take a long time. – Winter Apr 25 '19 at 08:36