PostgreSQL and Temporary Tables. Part 2: Why 1024 Counters Are Not Enough

The second part of the series on PostgreSQL temporary tables focuses on performance issues arising from their intensive use. Despite moving temporary tables to a RAM disk, the authors encountered system limitations related to DDL operations and catalog locks. Specifically, they discovered that an array of 1024 lock counters, unchanged since 2011, becomes a bottleneck under heavy load. This leads to contention, delays in logical replication, and the accumulation of WAL files. The article examines the mechanics of LWLock:LockManager and explains how temporary tables in one session can negatively impact the performance of the entire database. The authors highlight architectural limitations in PostgreSQL that manifest under high load and suggest diagnostic methods for identifying these issues in high-performance systems.