I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack
reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which results in using more disk space that PostgreSQL reports the DB is using.
I use following query to report size of each database:
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
(sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;
schema_name | pg_size_pretty | pct
--------------------+----------------+------------------------------------
production | 605 GB | 62.70818987165323895600
dev | 116 GB | 12.05199834243206743500
pg_toast | 12 GB | 1.26824870382580753200
staging | 12 GB | 1.26031018275065892500
test | 1497 MB | 0.15143744784303601600
pg_catalog | 26 MB | 0.002621403693008641646300
public | 624 kB | 0.000061661486144352849300
information_schema | 96 kB | 0.000009486382483746592200
repack | 0 bytes | 0.00000000000000000000000000000000
this gives an idea that the occupied space should be around 750GB
. However in reality PostgreSQL is using almost twice as much:
$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T /var/lib/postgresql/9.6/main/base/
Part of the problem is pgsql_tmp
, that is occupying 349GB
. Is there a safe way how to remove unused files from pgsql_tmp
?
349G /var/lib/postgresql/9.6/main/base/pgsql_tmp/
I've already tried VACUUM FULL
and pg_repack
on largest tables without any success. The only way how to get rid of wasted disk space seems to be dumping tables to SQL and reimporting into a clean server.
Best Answer
I've updated query for computing database sizes (indexes included):
As for the tmp files, I've deleted all files older than 1 day:
at least in our setup queries typically takes minutes, at most hours. So, temporary files older than one day were probably left by some postgresql process that had crashed. Temp files are suffixed with process's PID, like
pgsql_tmp13774.1
where13774
should be PID of postgresql process.