Recently, we encountered an issue where catalog queries executed by the drivers during session creation were taking significantly longer than expected. These queries, managed by PHP drivers, were impacted by a performance bottleneck. Due to this user session creations are taking more time.

Upon investigation, I discovered that the pg_attribute and pg_class system catalog tables were highly bloated. Further analysis revealed the root cause: the excessive use of temporary tables, which are dropped upon commit. Additionally, autovacuum was disabled, and manual vacuuming was performed only during nighttime. This approach, combined with a high workload, caused substantial bloat in the pg_attribute and pg_class tables due to frequent temporary table entries.

In PostgreSQL, temporary tables are often used for intermediate computations, such as during report generation. While they offer convenience, their excessive use can lead to performance degradation. A critical issue arises when the system catalog tables—pg_attribute and pg_class—become bloated, slowing down catalog queries and impacting overall database performance.

This blog post explores the root causes of this issue and proposes a practical solution: replacing temporary tables with unlogged tables. By understanding and addressing the underlying challenges, you can significantly improve query performance and maintain a healthy database environment.

The Problem with Excessive Temporary Table Usage

Temporary tables are ephemeral by design; they exist only within the scope of a database session. However, creating and dropping temporary tables at a high frequency can have unintended consequences:

1. Catalog Bloat: Each temporary table creation inserts metadata into system catalogs like pg_class and pg_attribute. Frequent creation and deletion of temporary tables can cause these catalogs to bloat, leading to inefficient queries on these tables.
2. System Slowdown: Bloated catalogs require more I/O and CPU resources for operations involving metadata. As a result, queries that depend on these catalogs (e.g., pg_stat_activity or pg_locks) become slower, degrading the overall database performance.
3. Increased Maintenance Overhead: To mitigate bloat, administrators may need to vacuum or reindex the catalogs more frequently, adding to the operational burden.

Reproducing the Issue:

• Create a file with below content – /tmp/temp_tables.sql
  
  
  PgSQL

  BEGIN; CREATE TEMPORARY TABLE simple_table ( col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT, col8 INT, col9 INT, col10 INT ) ON COMMIT DROP; -- Insert 10 rows with random data INSERT INTO simple_table SELECT trunc(random() * 1000)::INT AS col1, trunc(random() * 1000)::INT AS col2, trunc(random() * 1000)::INT AS col3, trunc(random() * 1000)::INT AS col4, trunc(random() * 1000)::INT AS col5, trunc(random() * 1000)::INT AS col6, trunc(random() * 1000)::INT AS col7, trunc(random() * 1000)::INT AS col8, trunc(random() * 1000)::INT AS col9, trunc(random() * 1000)::INT AS col10 FROM generate_series(1, 10); COMMIT;

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

  BEGIN; CREATE TEMPORARY TABLE simple_table (     col1 INT,     col2 INT,     col3 INT,     col4 INT,     col5 INT,     col6 INT,     col7 INT,     col8 INT,     col9 INT,     col10 INT ) ON COMMIT DROP;   -- Insert 10 rows with random data INSERT INTO simple_table SELECT     trunc(random() * 1000)::INT AS col1,     trunc(random() * 1000)::INT AS col2,     trunc(random() * 1000)::INT AS col3,     trunc(random() * 1000)::INT AS col4,     trunc(random() * 1000)::INT AS col5,     trunc(random() * 1000)::INT AS col6,     trunc(random() * 1000)::INT AS col7,     trunc(random() * 1000)::INT AS col8,     trunc(random() * 1000)::INT AS col9,     trunc(random() * 1000)::INT AS col10 FROM generate_series(1, 10); COMMIT;

  Size of tables before performing any test
  
  
  PgSQL

  postgres=# select pg_size_pretty(pg_total_relation_size('pg_catalog.pg_description')) as pg_description, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_namespace')) as pg_namespace, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_index')) as pg_index, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_attribute')) as pg_attribute, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_class')) as pg_class; pg_description | pg_namespace | pg_index | pg_attribute | pg_class ----------------+--------------+----------+--------------+---------- 608 kB | 320 kB | 64 kB | 736 kB | 184 kB (1 row) postgres=# show autovacuum; autovacuum ------------ off (1 row)

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  postgres=# select pg_size_pretty(pg_total_relation_size('pg_catalog.pg_description')) as pg_description, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_namespace')) as pg_namespace, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_index')) as pg_index, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_attribute')) as pg_attribute, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_class')) as pg_class; pg_description | pg_namespace | pg_index | pg_attribute | pg_class ----------------+--------------+----------+--------------+---------- 608 kB         | 320 kB       | 64 kB    | 736 kB       | 184 kB (1 row)   postgres=# show autovacuum; autovacuum ------------ off (1 row)

  After performing pgbench with the SQL file created in step-1 it bloated the catalog tables.
  
  
  PgSQL

  pgbench --file=/tmp/temp_tables.sql --client=900 --time=7200 --no-vacuum postgres postgres=# select pg_size_pretty(pg_total_relation_size('pg_catalog.pg_description')) as pg_description, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_namespace')) as pg_namespace, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_index')) as pg_index, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_attribute')) as pg_attribute, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_class')) as pg_class; pg_description | pg_namespace | pg_index | pg_attribute | pg_class ----------------+--------------+----------+--------------+---------- 608 kB | 912 kB | 96 kB | 27 GB | 2029 MB (1 row)

  1 2 3 4 5 6 7 8 9 10 11

  pgbench --file=/tmp/temp_tables.sql --client=900 --time=7200 --no-vacuum postgres   postgres=# select pg_size_pretty(pg_total_relation_size('pg_catalog.pg_description')) as pg_description, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_namespace')) as pg_namespace, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_index')) as pg_index, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_attribute')) as pg_attribute, pg_size_pretty(pg_total_relation_size('pg_catalog.pg_class')) as pg_class; pg_description | pg_namespace | pg_index | pg_attribute | pg_class ----------------+--------------+----------+--------------+---------- 608 kB         | 912 kB       | 96 kB    | 27 GB        | 2029 MB (1 row)

  The Solution: Unlogged Tables

  A viable alternative to temporary tables is the use of unlogged tables. Unlogged tables offer several advantages in scenarios where data persistence is not required beyond the current session or operation:

  1. Reduced Catalog Overhead: Unlike temporary tables, unlogged tables persist across sessions. As a result, the metadata for these tables does not need to be repeatedly inserted and removed from system catalogs.
  2. Improved Write Performance: Unlogged tables do not write data to the Write-Ahead Log (WAL), making inserts, updates, and deletes faster.
  3. Easy Cleanup: Truncating unlogged tables is straightforward and efficient, allowing for reusability without incurring catalog bloat.
  4. Crash Safety: While unlogged tables are not crash-safe (data is lost in the event of a crash), this trade-off is often acceptable for temporary or intermediate data.

  I recommended that clients incorporate unlogged tables into their report generation procedures and truncate them after each transaction. This approach effectively resolved the performance issue.