Why ON COMMIT DELETE ROWS Temp Tables Can Cripple PostgreSQL Performance

Introduction

Analyzing temporary tables reveals a performance problem when many ON COMMIT DELETE ROWS temporary tables are created and accessed improperly, causing slowdown and lock conflicts.

Why Temporary Tables Are Attractive

Temporary tables reside in a session‑local buffer, avoid regular row‑level locks, and do not generate WAL entries, making writes fast.

Hidden Costs

Each temporary table creates catalog entries in pg_attribute, pg_class, and pg_namespace. For a session, schemas such as pg_temp_n and pg_toast_temp_n are created.

ON COMMIT Options

ON COMMIT PRESERVE ROWS – default, keeps data after transaction.

ON COMMIT DELETE ROWS – deletes data at transaction end.

ON COMMIT DROP – drops the table at transaction end.

Performance Impact of temp_buffers

Larger temp_buffers increase the work required to traverse and clear temporary buffers, reducing throughput. Example benchmark:

tps for 8 MB   = 832.94
tps for 32 MB  = 792.02
tps for 128 MB = 711.78
tps for 256 MB = 595.47
tps for 512 MB = 443.83
tps for 1 GB   = 302.95
tps for 8 GB   = 55.83

Pre‑commit Processing

During pre‑commit PostgreSQL iterates over a list of OnCommitItem structures. The relevant code fragment:

foreach(l, on_commits)
{
  OnCommitItem *oc = (OnCommitItem *) lfirst(l);
  if (oc->deleting_subid != InvalidSubTransactionId)
    continue;
  switch (oc->oncommit)
  {
    case ONCOMMIT_DELETE_ROWS:
      /* Skip truncating if the transaction never accessed a temporary relation */
      if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
        oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
      break;
    case ONCOMMIT_DROP:
      oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
      break;
  }
}

The flag MyXactFlags (or MyXactAccessedTempRel in newer versions) indicates whether any temporary namespace was accessed. If not, truncation of ON COMMIT DELETE ROWS tables can be omitted.

If the transaction hasn't accessed any temporary relations, truncation can be skipped because the tables are still empty.

Evolution of the Optimization

The check was introduced in PostgreSQL 9.3. In PostgreSQL 9.2 and earlier, every temporary table was unconditionally added to the OID list, causing a full scan and truncation for each table.

git show c9d7dbacd387ab3814bc6b38010a9e72a02ea4f5
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10124,7 +10124,13 @@ PreCommit_on_commit_actions(void)
   case ONCOMMIT_DELETE_ROWS:
-    oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+    /* Skip truncating if the transaction hasn't accessed any temporary relations */
+    if (MyXactAccessedTempRel)
+      oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
     break;

Verification Example

Creating a temporary table with ON COMMIT DELETE ROWS and accessing it adds its OID to oids_to_truncate:

# create temp table tt1(id int) on commit delete rows;
# begin;
# select relfilenode from pg_class where relname = 'tt1';
 relfilenode 
------------
 158890
# select 1 from tt1;
 ?column? 
----------
 (0 rows)

Using GDB shows the OID (158890) processed in PreCommit_on_commit_actions().

Greenplum Specifics

In Greenplum the optimization is disabled (wrapped in #if 0), so every ON COMMIT DELETE ROWS temporary table is always truncated, even if never accessed. Because Greenplum stores temporary tables in shared_buffers, the performance penalty is larger.

#if 0
  /* Skip truncating if the transaction hasn't accessed any temporary relations */
  if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
#endif
  oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);

Practical Recommendations

Temporary tables share the same buffer‑traversal cost as regular tables; larger buffers degrade performance, especially with many TRUNCATE or DROP operations.

Avoid accessing empty ON COMMIT DELETE ROWS temporary tables, as the access forces unnecessary truncation work.

In Greenplum the check is disabled, so refrain from abusing ON COMMIT DELETE ROWS temporary tables.

Reference

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c9d7dbacd387ab3814bc6b38010a9e72a02ea4f5