PostgreSQL "size of temporary files"

Question

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is vacuumed.

Now pgAdmin III tells me that the "Size of temporary files" is 50G~+.

1. What are these temporary files? are these like SQL Server transaction log?
2. How can I get rid of them, it seems the database is much bigger than it should (the total size of the database is 91 GB)

Using Posgres 9.4.1 on a Windows 2012 server.

A screenshot of the database statistics tab:

A screenshot of the database statistics tab

• Ofiris: What does the "size of temporary files" column value represents ?

Answer 1

I found nothing in the pgAdmin documentation, but the source code reveals the query behind these entries (added for Postgres 9.2+):

It boils down to:

SELECT temp_files AS "Temporary files"
 , temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;

And the Postgres manual has details for pg_stat_database:

tmp_files bigint Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting.

temp_bytes bigint Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.

Note that these values do not contribute to the size of your database. But they indicate that your setting for work_mem may be too low, so that many sort operations spill to disk (which is very slow as compared to just RAM).

Related:

• Optimize simple query using ORDER BY date and text

To actually compact the size of your database:

• VACUUM returning disk space to operating system

To measure size:

• Measure the size of a PostgreSQL table row

Aside: WAL (Write Ahead Log) would be equivalent in Postgres for the transaction log in SQL Server. Nice explanation in this related answer on SO:

• Why do SQL databases use a write-ahead log over a command log?

• user228505: to reset statistics call select pg_stat_reset();

Answer 2

According to:

http://www.postgresql.org/message-id/[email protected]

The temp counter (files and space used) shows a total of all temp files used since probably cluster creation. It does not reflect the current space used by temp files.

My system for example shows almost 700GB of temp files used, but the actual space taken up by temp files in /var/lib/pgsql/9.3/data/base/pgsql_tmp is only 53MB currently.

• Nux: It's actually since stats reset which you can check with something like: SELECT min(stats_reset) AS min_last_reset, max(stats_reset) AS max_last_reset, sum(temp_files) AS temp_files_count, pg_size_pretty(sum(temp_bytes)) AS temp_bytes_size FROM pg_stat_database