TRUNCATE and VACUUM FULL doesn't free space on PostgreSQL server

Question

I imported data into 30 tables (every table also has indexes and constraints) in my PostgreSQL database. After some check I truncated all the tables and performed vacuum full analyze on all the tables. Some space indeed was freed but I`m sure that more data need to be freed. Before the import my PostgreSQL directory was about 20G. After the import it grew to be 270G. Currently, the size of the data directory is 215G.

I run this select :

SELECT
 relname as "Table",
  pg_size_pretty(pg_total_relation_size(relid)) As "Size",
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as 
  "External Size"
  FROM pg_catalog.pg_statio_user_tables ORDER BY 
   pg_total_relation_size(relid) 
   DESC;

and the result was that the biggest tables are 660M (and right now there are only 4 tables that their size bigger than 100M).

                   Table             |    Size    | External Size
      -------------------------------+------------+---------------
       my_table_1                    | 660 MB     | 263 MB
       my_Table_2                    | 609 MB     | 277 MB
       my_table_3                    | 370 MB     | 134 MB
       my_table_4                    | 137 MB     | 37 MB
       my_table_5                    | 83 MB      | 31 MB
       my_table_6                    | 5056 kB    | 24 kB
       mariel_test_table             | 4912 kB    | 8192 bytes

  ..........

The data/base directory size is 213G.

I run also this select :

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

output :

             relation              |  size
-----------------------------------+--------
 my_table_1                        | 397 MB
 my_Table_2                        | 332 MB
 my_Table_3                        | 235 MB
 my_table_7                        | 178 MB
 my_table_4                        | 100 MB
 my_table_8                        | 99 MB

The outputs of the selects aren't identical.

tempfile sizes :

   SELECT temp_files AS "Temporary files"
   , temp_bytes AS "Size of temporary files"
   FROM   pg_stat_database db;;
   Temporary files | Size of temporary files
   -----------------+-------------------------
           0 |                       0
           0 |                       0
           0 |                       0
         100 |             47929425920
           0 |                       0

I also tried to restart the PostgreSQL instance and the Linux server. What can I try next?

Are you sure this is from your database? Maybe you imported earlier in another database tables with same name, check DB name as well. — partlov, Aug 01 '17 at 07:42
Have you tried using these wiki.postgresql.org/wiki/Disk_Usage ? And how does it go with pg_stat_all_tables rather than pg_statio_user_tables (even though you only you use the tables' id so it should also work, but it would be more clear). Also, you should add your postgresql version. — Asoub, Aug 01 '17 at 07:43
I run the selects in the wiki page the results are the same -> The biggest database is the database I`m talking about - 213 G size. The biggest relations are tables and look exactly like the output above only the size a little bit different (200M ..) — JeyJ, Aug 01 '17 at 07:45
Have you try dropping and recreating the tables (or even the schema) ? This would surely works but I guess you want something cleaner and understand why vaccum doesn't freee all the space as it should after truncates. On your last query, is that 4.7GB of temp files ? Maybe the size also comes from log files. — Asoub, Aug 01 '17 at 08:49
I dont want to drop them and recreate them. the temp files size is 47G. So is there anything else I can check ? — JeyJ, Aug 01 '17 at 10:37
Same: I am using PG version 9.4 and just ran `VACUUM FULL ANALYZE` on every table in my DB that had n_dead_tuples > 0. The query against `pg_stat_all_tables` for n_dead_tup > 0 reports no more tables (yay), but the "Size of temporary files" reported by pgAdmin4 is now larger (~1TB instead of 900GB) and there is no additional free space on the disk where the tablespace lies as reported by `df`. Just bumping this in-case you figured something out. — mephicide, Dec 07 '17 at 22:20

TRUNCATE and VACUUM FULL doesn't free space on PostgreSQL server

0 Answers0