PostgreSQL 是世界上最强大的数据库系统之一。我一直对它的强大功能充满热情，尤其是它的现代 SQL 语言功能。
然而，这并不意味着一切都很好。有些地方还是很麻烦。新手用户如果不知道这个问题，可能会遇到麻烦，而且我发现这种情况经常发生。这是给那些使用临时表围绕 PostgreSQL 设计解决方案的人的提示。
问题一：目录臃肿，性能不佳
PostgreSQL 用户经常联系我们，抱怨数据库服务器响应速度太慢。不只是一条 SQL 运行缓慢，数据库中的所有东西都运行缓慢。通常，调查从头开始，从主机开始。我使用pg_gather扫描并快速识别 PostgreSQL 层的所有问题。很多此类调查最终都会导致目录臃肿。
通常，我预计典型 PostgreSQL 实例的所有目录信息大约为 20 MB。大小取决于数据库中的对象数量。具有许多对象（表、索引等）的数据库（例如具有许多分区的大量分区表）预计会具有更大的目录。但如果大小比预期的要大得多，则应该发出警报。
以下是来自实际生产环境的 pg_gather 发现的一些示例：
另一个：
另一个目录有1.2GB：
我见过的最差的是40GB！目录。
问题二：目录表上的自动清理活动过多
在某些情况下，当我们使用 PostgreSQL 日志（使用 log_autovacuum_min_duration）跟踪自动清理活动时，我们可能会看到这些目录表中的自动清理运行过于频繁
2024-12-26 11:48:50.795 UTC [1980] LOG: automatic vacuum of table "postgres.pg_catalog.pg_attribute": index scans: 1 pages: 0 removed, 15015 remain, 14977 scanned (99.75% of total) tuples: 344581 removed, 3664 remain, 569 are dead but not yet removable removable cutoff: 449706, which was 16999 XIDs old when operation ended new relfrozenxid: 449706, which is 64253 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 14960 pages from table (99.63% of total) had 1286231 dead item identifiers removed index "pg_attribute_relid_attnam_index": pages: 8946 in total, 4780 newly deleted, 8342 currently deleted, 8342 reusable index "pg_attribute_relid_attnum_index": pages: 6768 in total, 3531 newly deleted, 6193 currently deleted, 6193 reusable avg read rate: 29.354 MB/s, avg write rate: 25.649 MB/s
仔细观察 PostgreSQL 可以发现受临时表影响的目录表。主要包括：
pg_catalog.pg_attribute
pg_catalog.pg_class
pg_catalog.pg_constraint
pg_catalog.pg_depend
pg_catalog.pg_index
pg_catalog.pg_type
有时， pg_catalog . pg_statistic等表 也需要清理。有时 autovacuum 每小时运行数百次。
如果有人大量使用临时表，这将为自动清理工作者带来足够的工作量。更不用说所有这些造成的 IO 量了。
问题三：高 DDL 活动和应用程序设计
当架构师听说每个应用程序会话都需要发出DDL “CREATE TEMPORARY TABLE …”作为第一步，然后应用程序才能使用临时表时，他们常常会感到惊讶。很难说服人们数百个会话需要通过多个会话并行运行 DDL。这不仅会导致应用程序逻辑的修改/重新设计，还会导致性能显著下降。
问题四：连接池效率低下
PostgreSQL 中的临时表在特定连接/会话中有效。这意味着无法实现良好的连接池。每个会话都必须创建自己的临时表。一旦将连接释放到池中，就无法保证应用程序将获得与创建临时表时相同的连接。外部连接池解决方案（如 pgBouncer）清楚地记录了这一限制。
应用程序逻辑除了使用“会话模式”池之外别无选择。然而，实际上，我看到大多数应用程序都会保留它们创建的连接，直到应用程序关闭。因此，实际上，会话模式池非常接近无连接池。
问题五：PostgreSQL 日志和审计日志泛滥
使用临时表的另一个副作用 是它可能会用 DDL 淹没 PostgreSQL 日志。大多数生产系统都会有参数设置 log_statement=‘ddl’ 或其他 DDL 审计方法。这将导致所有临时表创建都记录到 PostgreSQL 日志中。最终结果是大量难以分析的日志文件。
问题六：无助的 autovacuum
临时表像常规表一样存储 xid，因此会变得臃肿。但自动清理（或手动清理）无法对临时表执行任何操作。
通常，我们喜欢看到与数据库的长连接（而不是频繁的连接和断开连接）。但这种长连接可能会保留一些带有旧 xid 引用的临时表。最糟糕的情况可能是系统接近事务环绕条件，触发警报并在深夜被呼叫。
为什么会发生这种情况？
我经常听到的一个问题是：“我们一直在 XYZ SQL 数据库中使用临时表，没有任何问题。为什么 PostgreSQL 中会出现这种情况？”
区别在于设计选择和实现。PostgreSQL 中的临时表允许每个会话创建一个具有相同名称但可能具有不同结构的临时表。这是一种很大的灵活性，但也有其成本。这意味着每个会话都需要有自己的临时模式来保存临时表。因此，所有临时表都将在映射到每个会话的单独模式中创建。这是额外元数据生成的来源。然后，PostgreSQL 会将临时表信息插入到所有目录表（pg_class、pg_attribute 等）中，就像常规表一样。因此，显然，这些都是 DDL 操作，并且这些目录表上将有更多的 DML 操作。
临时表会在会话结束时或（可选）当前事务结束时自动删除，从而导致目录表中出现死元组。此外，这种设计迫使用户每次都创建临时表，在每个会话中重复创建，从而导致系统中出现大量 DDL 活动。
临时表的其他问题/限制
按照标准，创建 TEMPORARY 表时可以指定 GLOBAL 或 LOCAL。但 PostgreSQL 不会区分它们；它只是忽略它们。
PostgreSQL 社区非常清楚所有问题，并且都有详细的记录。
参考：
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY
它继续说道：
“ SQL 标准还区分了全局临时表和本地临时表，其中本地临时表在每个会话中为每个 SQL 模块都有一组单独的内容，但其定义仍在会话之间共享。 ”
因此，PostgreSQL 允许用户会话拥有名称相同但表结构不同的临时表。实际上，我还没有看到这种灵活性非常有用的情况。因此，实际上，PostgreSQL 已经偏离了标准，并破坏了临时表的真正可用性，转而用于一些不太有用的东西。
概括
PostgreSQL 在 SQL 处理的每个阶段都经常使用目录信息，从表和列的识别、权限、统计信息等开始。精简的目录对于性能至关重要；另一方面，臃肿的目录对性能来说很糟糕。
使用临时表是导致目录膨胀的最常见原因，会导致严重的性能下降。如果某些应用程序设计必须使用临时表，那么除了用重复的 DDL 充斥系统之外，别无选择。
并非所有目录膨胀情况都是由临时表引起的。将许多架构整合到单个数据库中可能是导致目录过大和性能不佳的另一个常见原因。以下是此类整合环境的一个示例。
PostgreSQL 是免费且开放的，因此将所有内容整合到单个数据库中并没有太多好处（与专有软件不同）。整合会带来许多挑战和成本。
据我观察，PostgreSQL 当前的临时表实现对于大量生产使用来说没有太大价值或不可持续。它有很多实际困难。我建议在设计/架构中尽可能避免使用它，并寻找解决方法。
如果目录已经膨胀，性能受到影响，则在目录表上运行 VACUUM FULL 是解决方案。采取暂时锁定并在目录表上运行 VACUUM FULL 是消除膨胀的最实用方法。有些用户使用临时表的自定义实现切换到 UNLOGGED 表。有一个扩展出口 ( pgtt ) 使用 UNLOGGED 表实现类似 Oracle 的全局临时表。PostgreSQL 社区已经做出了多项努力来实现更好的临时表，包括此 commitfest 项目：https 😕/commitfest.postgresql.org/26/2349/
我希望 PostgreSQL 能够很快实现这一目标，因为它将使所有架构师/设计师、开发人员和 DBA 的生活变得更加轻松。
#PG证书#PG考试#PostgreSQL培训#PostgreSQL考试#PostgreSQL认证