Environment

• YugabyteDB Version: - All Versions

Issue

User may observe a large number of temporary schemas (pg_temp_* and pg_toast_temp_*) in the database catalog, even though there are no large numbers of concurrent sessions and active connections are limited. 

Cause

This can be frequently observed in environments using Materialized Views (specifically REFRESH MATERIALIZED VIEW CONCURRENTLY) or applications that make heavy use of temporary tables.

Resolution

The presence of multiple pg_temp_* and pg_toast_temp_* schemas is often expected PostgreSQL behavior.

You can confirm the existence of these schemas by querying the pg_namespace catalog.

SELECT n.nspname AS schema_name,
       r.rolname AS owner
FROM pg_namespace n
JOIN pg_roles r ON n.nspowner = r.oid
WHERE n.nspname LIKE 'pg_t%';

To improve performance, PostgreSQL often does not delete these schema namespaces immediately when the session ends. Instead, it keeps them as empty "slots" to be reused by future backend processes (sessions).

These schemas are generally empty and do not consume significant disk space or resources. Manual cleanup is typically not required as the system manages them automatically for reuse.

Reference: SUPPORT-835