12

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

  • What does the "size of temporary files" column value represents ? – Ofiris Aug 16 '15 at 12:33
12

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:

To actually compact the size of your database:

To measure size:

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:

10

According to:

http://www.postgresql.org/message-id/BLU0-SMTP179B92C5102247CD961A4B3CF2A0@phx.gbl

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.