[PATCH] Improving Visibility of Temporary Table Usage

Started by Mohamed ALi7 months ago3 messageshackers
Jump to latest
#1Mohamed ALi
moali.pg@gmail.com

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

Attachments:

t139591_1
v1-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patchapplication/octet-stream; name=v1-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patchDownload+213-8
test-patch-comprehensive.sqlapplication/octet-stream; name=test-patch-comprehensive.sqlDownload
test-patch-results.outapplication/octet-stream; name=test-patch-results.outDownload
#2Mohamed ALi
moali.pg@gmail.com
In reply to: Mohamed ALi (#1)
Re: [PATCH] Improving Visibility of Temporary Table Usage

Hi hackers,

Here is v2 of this patch, rebased on current HEAD (cae90d74796, as of
2026-06-25).

This rebase was triggered by the CFBot "Needs rebase" notification on CommitFest
entry #6835 [1]https://commitfest.postgresql.org/patch/6835/. The patch no longer applied cleanly due to upstream's async I/O
refactor

[1]: https://commitfest.postgresql.org/patch/6835/

Changes from v1:
- Rebased onto PostgreSQL 19beta1 HEAD (1047 commits upstream since v1)
- Adapted bufmgr.c changes to work with the async I/O refactor introduced
upstream. pgstat_count_local_blk_hit() is now placed inside TrackBufferHit(),
the unified hit-tracking function added by the async I/O work.
pgstat_count_local_blk_read() is placed in StartReadBuffersImpl() next to
the existing local_blks_read update.
- Added missing !OidIsValid(dboid) || !IsUnderPostmaster guards to
pgstat_count_temp_table() for consistency with the other 4 counting
functions added in the same patch.
- Added !IsBootstrapProcessingMode() check in heap.c (defensive; bootstrap
cannot create temp tables, but makes intent explicit).

Patch attached.

--
Mohamed Ali

Attachments:

t139591_2
v2-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patchapplication/octet-stream; name=v2-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patchDownload+217-8
#3Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Mohamed ALi (#2)
Re: [PATCH] Improving Visibility of Temporary Table Usage

Hello

+      <para>
+       Number of temporary table blocks read from disk in this database.
+      </para></entry>

The counters also seem to include index blocks, is that intended? If yes, then the wording should indicate that. E.g.

CREATE TEMP TABLE t(a int);
INSERT INTO t SELECT generate_series(1,100000);
CREATE INDEX t_a_idx ON t(a);
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT count(a) FROM t WHERE a BETWEEN 1 AND 50000;

+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>temp_tables</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of temporary tables created in this database.
+      </para></entry>
+     </row>

Similar question: it currently also includes vacuum/cluster/create index/alter table alter column. Is that intended?

CREATE TEMP TABLE t(a int);
INSERT INTO t SELECT g FROM generate_series(1,100) g;
VACUUM FULL t;
CREATE INDEX idx ON t(a);
CLUSTER t USING idx;
ALTER TABLE t ALTER COLUMN a TYPE bigint;
SELECT pg_stat_force_next_flush();
SELECT temp_tables FROM pg_stat_database WHERE datname=current_database();