Postgres 9.2: monitoring temp files generation in real time

With other great new features, PostgreSQL 9.2 makes DBAs life easy by providing more metrics  in statistics collector views. Out of those , pg_stat_database caught my eyes because it provides a lot more details compare to Postgres 9.1 and other previous versions.

Postgres 9.1

postgres=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
stats_reset | timestamp with time zone |


Postgres 9.2

postgres=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |


As you have noticed above, pg_stat_database has number of  new columsn in  Postgres 9.2 compare to previous versions.  I’m here to discuss temp_files and temp_bytes columns.

Documentation is self explanatory :
                  temp_files : 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: 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.

postgres=# select datname,temp_files,temp_bytes from pg_stat_database where datname='XXX;
datname | temp_files | temp_bytes
-----------+------------+---------------
XXX | 107309 | 4650188504602
(1 rows)

As you are aware that temp_files generation could cause performance issues . Historically, it was possible to track temp files generation by enabling log_temp_files setting in postgresql.conf and analyze logs using pg_badger. Now, Postgres 9.2 allows you to monitor temp files generation in real time! It is trivial to add above query to start monitoring, trending and alerting using Circonus (See below graph) .

Left Y:  counter of number of files generated
Right Y: counter of temp files size (in MB) generated