CODEWITHSUNDEEP
postgresqlcentos7truncatemirth
Winter
Winter

Reputation: 1916

Postgres did not free the space to OS after TRUNCATE of a 145GB table

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?

Upvotes: 3

Views: 3863

Answers (3)

Siwei
Siwei

Reputation: 21569

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;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246523

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.

Upvotes: 3

s sato
s sato

Reputation: 181

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

Upvotes: -1

Related Questions