[PATCH] Improving Visibility of Temporary Table Usage

[Mohamed ALi]

Hi hackers,

Currently pg_stat_database provides temp_files and temp_bytes to track
temporary files created during query execution (sorts, hashes), but
offers no visibility into temporary tables created with CREATE TEMP
TABLE. This patch adds five new columns to fill that gap:

  temp_tables        - number of temporary tables created
  local_blks_hit     - local buffer cache hits
  local_blks_read    - local blocks read from disk
  local_blks_dirtied - local blocks dirtied
  local_blks_written - local blocks written to disk

pg_stat_statements already tracks local_blks_* per-query, but there
is no database-level aggregation. This patch provides that, similar
to how pg_stat_database aggregates regular table I/O (blks_read,
blks_hit) .

Motivation
----------

DBAs currently have no way to answer questions like:
- How many temp tables is this database creating?
- Is temp_buffers sized correctly for this workload?
- How much disk I/O are temp tables causing?

Existing workarounds are either intrusive or unreliable:

- Setting log_statement = 'ddl' logs CREATE/DROP commands but
  requires parsing log files and adds logging overhead.

- Querying pg_class for temporary schemas:

    SELECT n.nspname, c.relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname LIKE 'pg_temp_%' AND c.relkind = 'r';

  This only shows currently-existing temp tables (not historical
  counts), and a bloated pg_class from excessive temp table
  creation can itself degrade the performance of such queries.

With this patch, a simple query answers all three questions:

  SELECT datname, temp_tables,
         local_blks_hit, local_blks_read,
         local_blks_dirtied, local_blks_written,
         round(100.0 * local_blks_hit /
               NULLIF(local_blks_hit + local_blks_read, 0), 2)
               AS hit_ratio
  FROM pg_stat_database WHERE datname = current_database();

Example with undersized temp_buffers (800kB):

   temp_tables | local_blks_hit | local_blks_read | local_blks_dirtied
| local_blks_written | hit_ratio
  
-------------+----------------+-----------------+--------------------+--------------------+----------
             1 |           1041 |            3264 |                 67
|               3216 |     24.17

Same workload with adequate temp_buffers (10MB):

   temp_tables | local_blks_hit | local_blks_read | local_blks_dirtied
| local_blks_written | hit_ratio
  
-------------+----------------+-----------------+--------------------+--------------------+----------
             1 |           4305 |               0 |                 67
|               1088 |    100.00

I have also attached a standalone SQL test script
(test-patch-comprehensive.sql) and its output
(test-patch-results.out) that exercises all five new columns
across 12 scenarios including cache hit ratio analysis,
undersized temp_buffers detection, and UPDATE/dirtied block
tracking. Reviewers can run the script against a patched
instance to verify behavior.


-- 
Mohamed Ali
Sr DBE
AWS RDS

v1-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patch
Description: Binary data

test-patch-comprehensive.sql
Description: Binary data

test-patch-results.out
Description: Binary data