Temp File In PostgreSql

user_10477038 0
Viewed 7954

My db size is growing up to 41GB but when I create a backup it becomes 2.4 / 2.5GB, Why this much difference?

I have performed some vaccum, Reindexing etc. but still the size is not shrinking and it is showing 19 GB temp file but when I check in C:\Program Files\PostgreSQL\10\data\base\pgsql_tmp the folder is empty..

How can I delete those temp files? Is there a way to reduce my DB size?

EDIT

db stat result

Query to get temp files

SELECT temp_files AS "Temporary files"
     , temp_bytes AS "Size of temporary files"
    FROM   pg_stat_database db;
postgresql optimization
1 Answers

The values in pg_stat_database are cummulative since the server was started, so it's not the number of bytes currently used. It's the number of bytes used since the statistics were reset.

As this is a cumulative number, it will increase constantly and will never shrink unless you reset the statistics.

You can reset those statistics (as the superuser) using 

select pg_stat_reset();

It seems that your queries are indeed using many temp files and temp space. That could indicate that you have not given Postgres enough memory (e.g. work_mem or temp_buffers) But that is a different question.

Those temp file are also not part of a database dump as they are only used during query execution.

user_3303150
Related