In 2023, I wrote about temporary tables in PostgreSQL and how to move them to a RAM disk. It helped: in our measurements, the share of ext4 in the CPU profile dropped from 13.5% to 1.6%, and disk overhead stopped being a concern. But creating, truncating, and dropping temporary tables remained ordinary DDL, with system catalog changes and strong locks held until the end of the transaction. That turned out to be a separate cost, showing up in some unexpected places.
On one server, active backends periodically got stuck waiting on LWLock:LockManager. On another, logical replication fell behind and WAL accumulated. The symptoms looked completely different, but both investigations led to the same question: what happens inside PostgreSQL when temporary tables are created and cleared by the thousands? We will start with locking. That investigation uncovered an array of 1,024 counters that takes up four kilobytes, is not configurable, and has remained unchanged since 2011. It also revealed that temporary tables in one session can deny ordinary queries in other sessions the fast path for acquiring locks.
How it started
The installation that prompted the investigation runs PostgreSQL 18 with max_connections set to 2,500. About 1,800 sessions are connected, with roughly 150 active at any given moment. Every so often, the server would stop responding, and most active backends in pg_stat_activity would be waiting on LWLock:LockManager. We captured pg_locks during one of these incidents: 47,592 rows, only 108 of them with fastpath = true. One backend held 843 AccessExclusiveLock locks, all on temporary relations.
To understand what that means, we need to look at table locking in PostgreSQL and how the fast path works. The source links below point to a specific commit on the REL_18_STABLE branch so that line numbers stay stable.
How the fast path works
Every statement acquires a lock on each relation it touches. There are eight table lock modes. Their compatibility is defined by the conflict matrix, and they are numbered in lockdefs.h:
#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6
#define ExclusiveLock 7
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE */
The first three modes are weak. They never conflict with one another, and they are the modes used for ordinary data access and modification. Modes five through eight are strong: each conflicts with at least one of the first three, and they are mainly used by DDL. The fourth mode is a special case; we will return to it later.
Before the fast path was introduced, new locks were recorded in the lock manager's shared hash tables. These are divided into 16 partitions (lwlock.h), each protected by an LWLock. Acquiring a lock that a backend does not already hold therefore means accessing a hash table while holding the partition's LWLock. When hundreds of backends keep acquiring and releasing locks on the same lookup tables, those 16 partitions become a bottleneck. That is the LWLock:LockManager wait we observed.
Robert Haas added the fast path in 2011 to address this. Since PostgreSQL 9.2, a backend can, under certain conditions, record weak locks in its own small array without adding them to the shared hash table. As long as there is no conflicting strong lock, that is sufficient.
When a backend requests a strong lock, PostgreSQL scans the other backends and transfers matching fast-path locks on that relation into the shared table (FastPathTransferRelationLocks). Otherwise, an ALTER TABLE could proceed while a SELECT was still running. But there is also the reverse question: how can a backend about to acquire a weak lock quickly tell whether the fast path is still available, or whether someone has already requested a strong lock on the relation? The developers provided a shared array of counters for this. A comment in lock.c describes it, and one phrase is worth remembering:
We partition the locktag space into FAST_PATH_STRONG_LOCK_HASH_PARTITIONS, and maintain an integer count of the number of "strong" lockers in each partition. When any "strong" lockers are present (which is hopefully not very often), the fast-path mechanism can't be used, and we must fall back to the slower method of pushing matching locks directly into the main lock tables.
The structure itself appears ten lines below:
#define FAST_PATH_STRONG_LOCK_HASH_BITS 10
#define FAST_PATH_STRONG_LOCK_HASH_PARTITIONS (1 << FAST_PATH_STRONG_LOCK_HASH_BITS)
typedef struct
{
slock_t mutex;
uint32 count[FAST_PATH_STRONG_LOCK_HASH_PARTITIONS];
} FastPathStrongRelationLockData;
This is an array of 1,024 four-byte counters. The array itself takes up 4,096 bytes; the only other field in the structure is a spinlock. Each relation maps to one of the 1,024 buckets through the hash of its locktag. A backend requesting a strong lock increments the bucket's counter before the lock is actually granted (BeginStrongLockAcquire). A backend that wants to acquire a weak lock through the fast path first checks for a free slot in the appropriate group (line 987), then checks that the counter for the relation's bucket is zero (line 999). If it is not zero, the backend takes the ordinary path through the shared table. When the strong lock is released, the counter is decremented (lines 1496–1497).
The counter knows nothing about individual relations. It only knows that there is a strong lock associated with its bucket, either already granted or still waiting. New weak locks on any other relation that hashes to the same bucket must also go through the shared table. The reasoning is understandable: when there are few strong locks, these false matches are rare and go unnoticed. That is exactly what the comment says: hopefully not very often.
Where temporary tables come in
CREATE TEMPORARY TABLE takes an AccessExclusiveLock on the relation being created. So do TRUNCATE and DROP. These locks are held until the end of the transaction, rather than the end of the statement. Rolling back to a savepoint can release them earlier, but that is uncommon in normal operation.
Consider a transaction that creates twenty temporary tables, populates them, and clears them at the end. Even if we count only the tables themselves, excluding indexes and TOAST relations, it holds strong locks in roughly twenty of the 1,024 buckets until it finishes. With 150 such transactions running concurrently, there are already several thousand strong locks on different relations. Assuming uniform and independent hashes for these locktags, the expected fraction of occupied buckets follows the usual balls-into-bins formula: 1 − (1 − 1/1024)^N ≈ 1 − e^(−N/1024), where N is the number of distinct relations with strong locks:
| Distinct relations with strong locks | Buckets with a nonzero counter |
|---|---|
| 843 | 56% |
| 2,000 | 86% |
| 5,000 | 99% |
Notice that ordinary tables are now affected too. A lookup table read by 1,600 sessions, with no strong locks ever taken on it, always maps to the same bucket. In the model with five thousand strongly locked relations, the probability that someone's temporary table shares that bucket exceeds 99%. While the bucket is occupied, every new weak lock acquisition on the lookup table goes through the shared hash table under the LWLock of one of the 16 partitions. That brings us back to LWLock:LockManager.
At this point, the system can behave like a traffic jam. As strong locks accumulate, more queries lose access to the fast path and compete for the lock manager's internal locks. The waits make transactions take longer, and those transactions continue holding the locks they have already acquired. If new transactions keep arriving, the number of locks held simultaneously grows further, contention increases, and transactions slow down even more. A brief load spike can turn into a persistent traffic jam that sustains itself.
To be clear, the calculation above is a model, not a reconstruction of the incident. The pg_locks snapshot is consistent with it: almost no locks were on the fast path, and one backend had accumulated 843 AccessExclusiveLock locks on temporary relations. But pg_locks does not expose these counter values or explain why the fast path was bypassed. PostgreSQL 18 has no built-in metric for the occupancy of these 1,024 buckets, so the percentages above are calculated estimates rather than direct measurements taken during the incident.
It is worth pausing at the number 1,024. It is 1 << 10, where 10 is a constant in the source code. It is not a postgresql.conf setting or a value derived from max_connections or shared_buffers. The array takes up four kilobytes and has done so since 2011. With FAST_PATH_STRONG_LOCK_HASH_BITS = 20, it would take up four megabytes, barely noticeable on a server with hundreds of gigabytes of RAM. In the same model, five thousand relations would occupy about 0.5% of the buckets instead of 99%. The probability of accidentally disabling the fast path for a particular lookup table would drop by roughly a factor of two hundred, and widespread fast-path bypass caused by collisions—the specific mechanism described here—would virtually disappear. That would not eliminate every LockManager wait: strong locks, lock transfers, and work on the ordinary path would remain. But it would remove the background load from thousands of unrelated SELECT queries whose fast path had been disabled by those collisions. Interestingly, the other fast-path limit, the number of slots per backend, stayed at 16 for years but is finally derived from max_locks_per_transaction in PostgreSQL 18. The 1,024 buckets are still 1,024 buckets.
Not every operation on temporary tables causes this effect. The fourth lock mode, ShareUpdateExclusiveLock, used by ANALYZE and ordinary VACUUM, behaves differently. It cannot be acquired through the fast path, but it does not prevent other locks from using the fast path either, because it is not considered "strong" by this mechanism. So running ANALYZE on a temporary table does not, by itself, disable the fast path for other relations in the same bucket. And TRUNCATE in autocommit runs in its own short transaction, so its strong lock has a much shorter lifetime. What matters is how many strong locks are held simultaneously and how long they remain held.
Could temporary tables simply be excluded?
The first thought is obvious: a temporary table is visible only to its own backend, so why should it participate in the shared locking scheme at all? In the current implementation, however, temporary relations use the same relation-locking protocol as other relations. The check on line 999 only looks at the bucket counter. The locktag passed to this code does not even indicate whether the relation is temporary. Excluding temporary relations would require a separate correctness argument, and their definitions still live in the shared catalog, which all backends can see.
One proposed approach is global temporary tables: the definition is created in advance and reused, while the data remains private to each session. A patch was proposed in 2019, moved between commitfests for several years, and was marked "Returned with feedback" in July 2022. It is not part of PostgreSQL 18.
What happens to the catalog
We discussed catalog growth in the first article. Let us briefly revisit what causes it, then look at how constant catalog changes can slow down logical replication.
Every CREATE of a temporary table adds rows to pg_class, pg_attribute, pg_type, pg_depend, and their indexes. If the table has a primary key, there are already two relations; with TOAST, there can be up to four. Every DROP removes all of this. PostgreSQL's catalogs are MVCC tables like any others: deleted rows become dead tuples and wait for autovacuum. On a busy server, that means a continuous stream of dead tuples in some of the database's most frequently accessed tables. On the installation where this investigation started, pg_class contained 445,000 visible rows, with dead versions on top of that. Statistics for temporary tables also live in the shared catalog: the first ANALYZE adds rows to pg_statistic, while subsequent runs and the eventual DROP leave dead versions behind.
Another consequence appeared on a different installation, where we tried using logical replication to feed a data warehouse. Replication fell behind and WAL accumulated. At the top of the perf profile were pg_qsort at 38.22% and xidComparator at 35.90%: sorting and comparing transaction IDs. A likely source of this load is building historic catalog snapshots. To interpret changes in WAL correctly, logical decoding needs to know what the catalog looked like at the time. It therefore keeps an array of committed transactions that changed the catalog and sorts the entire array whenever it builds a new snapshot.
Temporary data itself is not replicated, but creating, dropping, and ordinarily truncating temporary tables changes the shared catalog. Those transactions add entries to the array and trigger new snapshots. With a heavy stream of such changes, repeated sorting can become a bottleneck even without long-running queries. On that installation, pg_class and pg_statistic had grown substantially, and autovacuum could not keep up. The profile is consistent with this mechanism, but a call stack is needed to confirm the exact sorting call involved. Even when temporary data never enters the replication stream, frequent changes to its catalog definitions can significantly slow replication down.
Ordinary VACUUM has another trap. At the end of its work, it calls vac_update_datfrozenxid, which, as the comment puts it, "must seqscan pg_class to find the minimum Xid, because there is no index that can help us here". If every temporary table is vacuumed with a separate command, each command ends with a scan of pg_class. At 445,000 rows, that cost becomes noticeable. Since PostgreSQL 16, VACUUM (SKIP_DATABASE_STATS) can skip this step. Database-wide frozen-XID information then needs to be updated separately and periodically, for example with VACUUM (ONLY_DATABASE_STATS).
Moving files to tmpfs therefore leaves a second source of overhead: constant CREATE, DROP, and transactional TRUNCATE generate strong locks and catalog tuple churn. Even small tables can be expensive when there are many of them, DDL runs continuously, and transactions take a long time to finish.
What can be done
We are often told that we use temporary tables too heavily and could do without them. We addressed that in the first article and will not repeat the discussion here. Disk overhead was covered there too: tmpfs for temporary tables removes filesystem work from the profile, but does nothing about locking or the catalog. If eliminating temporary tables is not an option, there are several approaches, from the simplest to the more involved.
First, fewer backends. Contention on the lock manager's partitions increases with the number of backends accessing them. Limiting the number of concurrently working backends through a connection pool compatible with the application does not fix the underlying cause, but it can significantly reduce the symptoms.
Second, use DELETE instead of TRUNCATE inside transactions. DELETE itself takes a RowExclusiveLock and does not increment the bucket counter. However, if the table was created in the same transaction, the AccessExclusiveLock from CREATE is already held and will remain so. The trade-offs are dead rows that cannot be vacuumed away inside the transaction, because VACUUM cannot run there, and the loss of TRUNCATE's storage-reset semantics. For small tables, the extra cost of DELETE may be lower than the cost of holding a strong lock until the transaction ends.
Third, create tables in advance, in separate short transactions, and reuse them. The strong lock from CREATE is then released at the end of the short preparation transaction, and there is no need to create and drop a table definition on every use. The application does, however, have to track available tables and manage their reuse.
Finally, we can wait for global temporary tables or help get them implemented. Alternatively, a first experiment would be to run a build with FAST_PATH_STRONG_LOCK_HASH_BITS = 20 in a test environment: the memory difference is small, and the estimated probability of accidental collisions drops by roughly a factor of two hundred. An experiment would show how this affects CPU caches and the shared spinlock. Server memory can grow, but this array does not grow with it. With several thousand relations holding strong locks simultaneously, almost every bucket can be occupied.
Trying the larger bucket count in production by rebuilding PostgreSQL with a different constant is, frankly, a little unnerving. Even if the change helps, rolling it out across all our installations would be awkward: every installation would need a custom build instead of vanilla PostgreSQL, and the change would have to be carried forward, built, and tested with each new version. In effect, that means maintaining a separate PostgreSQL variant for the application. Taking on that responsibility just to change one constant is not an appealing prospect.
At lsFusion, we chose the third approach: the application server maintains a pool of UNLOGGED tables, clears them with DELETE inside working transactions and TRUNCATE between transactions, and retires and replaces heavily reused tables. Unlike TEMP tables, these tables are shared across sessions; their data is lost after a crash and is not replicated. In our test environment, four save operations required zero CREATE statements instead of forty. We have not yet measured the effect on LockManager or logical decoding in production. We expect a reduction in overhead and will report back once we have measured it.
The mechanisms described here appear to share an assumption: strong locks and catalog changes are relatively rare events. That makes sense for a database with a largely static schema. But temporary tables exist for intermediate work: applications create, use, and clear them as tasks are performed. When there are many such tasks, "rare" DDL becomes a routine part of the workload, and the limitations of these mechanisms start affecting unrelated queries and logical replication.
These problems with the fast path and logical decoding give the impression that heavy use of temporary tables is simply not treated as a workload worth planning for. It is not clear why. Temporary tables are a standard PostgreSQL feature, and wanting to use them frequently does not, in itself, seem like an application design error. Yet applications have to build their own table pools, replace TRUNCATE with DELETE, or consider maintaining a custom database build. Making the bucket count configurable would be a useful first step: it could then be adjusted to the workload without rebuilding PostgreSQL. It would be good to see this workload accommodated within PostgreSQL itself, so that "hopefully not very often" no longer has to be a condition for normal operation under load.
Top comments (1)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.