【postgresql】频繁 DROP/CREATE TABLE 在 PostgreSQL 中的问题及解决思路

如果某个应用程序设计成了频繁drop table /create table ,而且连的是postgresql数据库，这样有什么问题？在应用程序不改的情况下如何解决这个问题？

一、核心问题分析

1. 系统目录膨胀（最关键的问题）

PostgreSQL 的所有元数据都存储在系统目录表中（pg_class、pg_attribute、pg_depend、pg_description 等）。每次 DROP/CREATE TABLE：

CREATE TABLE t1 (...);  
  → pg_class 新增 1 条记录（表本身）  
  → pg_attribute 新增 N 条记录（每列一条）  
  → pg_depend 新增多条（依赖关系）  
  → pg_type 新增记录（行类型）  

DROP TABLE t1;  
  → 以上所有记录变为 dead tuple（死元组）  
  → 不会立即物理删除，等待 VACUUM 清理

当频率很高时，dead tuple 的产生速度远超 autovacuum 的清理速度，导致：

系统目录表持续膨胀，可能从几 MB 膨胀到几 GB
目录扫描变慢，所有涉及元数据的操作都受影响
查询 information_schema、\d 等元数据查询极慢

2. 目录锁竞争

DDL 操作持有 AccessExclusiveLock（最严格的锁），并且需要获取 pg_class 上的 RowExclusiveLock。高频 DDL 会造成：

多个连接竞争目录行锁
其他会话的查询规划阶段被阻塞（需要读取目录元数据）

3. relcache / catcache 反复失效

-- 每次 DDL 后，PostgreSQL 会发送 catalog invalidation 消息
-- 所有其他会话必须重新加载相关缓存

高频 DDL 导致所有数据库会话不断重建缓存，消耗 CPU 和内存。

4. WAL 日志量巨大

每次 DDL 都产生大量 WAL 记录（目录修改需要写 WAL），增加：

磁盘 I/O 压力
如果有流复制，还会显著增加主从复制延迟

5. 文件系统层面的开销

每次 CREATE TABLE → 在 $PGDATA/base/oid/ 下创建文件（每个 8KB block 一个文件）
每次 DROP TABLE   → 标记文件删除，操作系统需要处理 unlink

高频操作会导致大量小文件的创建和删除，文件系统缓存和 inode 相关操作变慢。

6. 事务 ID 消耗与回卷风险

每个 DDL 消耗一个事务 ID。长期高频运行会加速事务 ID 回卷（wraparound）的进程。

二、不改应用的解决方案

方案 1：激进调优 autovacuum 对系统目录的清理（首选）

系统目录表本质上也是普通表，可以单独配置 autovacuum 参数：

-- 对关键目录表设置非常激进的 vacuum 参数
ALTER TABLE pg_catalog.pg_class SET (
    autovacuum_vacuum_scale_factor = 0.001,   -- 默认 0.1，改为 0.1% 即触发
    autovacuum_analyze_scale_factor = 0.001,
    autovacuum_vacuum_cost_delay = 0          -- 取消 cost-based throttling
);

ALTER TABLE pg_catalog.pg_attribute SET (
    autovacuum_vacuum_scale_factor = 0.001,
    autivacuum_analyze_scale_factor = 0.001,
    autovacuum_vacuum_cost_delay = 0
);

ALTER TABLE pg_catalog.pg_depend SET (
    autovacuum_vacuum_scale_factor = 0.001,
    autovacuum_analyze_scale_factor = 0.001,
    autovacuum_vacuum_cost_delay = 0
);

ALTER TABLE pg_catalog.pg_description SET (
    autovacuum_vacuum_scale_factor = 0.001,
    autovacuum_analyze_scale_factor = 0.001,
    autovacuum_vacuum_cost_delay = 0
);

同时全局调快 autovacuum：

autovacuum_naptime = 10s                    # 默认 1min，缩短到 10s
autovacuum_vacuum_cost_delay = 0            # 取消节流，全力清理
maintenance_work_mem = 1GB                  # 加大 vacuum 工作内存
autovacuum_work_mem = 1GB                   # 加大 autovacuum 工作内存

方案 2：定时手动 VACUUM 系统目录

通过 pg_cron 或操作系统 cron 定期执行：

-- 用 pg_cron，每分钟 vacuum 一次关键目录表
SELECT cron.schedule('vacuum_catalog', '* * * * *', $$
    VACUUM pg_catalog.pg_class;
    VACUUM pg_catalog.pg_attribute;
    VACUUM pg_catalog.pg_depend;
    VACUUM pg_catalog.pg_type;
    VACUUM pg_catalog.pg_description;
$$);

或者在操作系统层面用 cron + psql：

# /etc/cron.d/pg_catalog_vacuum
* * * * * postgres psql -d mydb -c "VACUUM pg_catalog.pg_class, pg_catalog.pg_attribute, pg_catalog.pg_depend, pg_catalog.pg_type;"

方案 3：使用 `pg_catalog` 的 fillfactor 调优

PostgreSQL 支持为系统目录设置 fillfactor，留更多空间给 HOT update：

ALTER TABLE pg_catalog.pg_class SET (fillfactor = 50);
ALTER TABLE pg_catalog.pg_attribute SET (fillfactor = 50);
ALTER TABLE pg_catalog.pg_depend SET (fillfactor = 50);

fillfactor 降低意味着每页留更多空闲空间，dead tuple 更新时更容易做 HOT update，减少索引膨胀。不过对于大量 DELETE（DROP TABLE 导致的删除），效果有限。

方案 4：评估应用模式，用触发器/规则拦截改造

虽然不改应用代码，但可以在数据库层面拦截 DDL：

思路 A：拦截并改造（用 event trigger）

-- 创建事件触发器，在 DROP TABLE 时不真正删除，而是重命名/移到归档 schema
CREATE OR REPLACE FUNCTION preserve_dropped_table() RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type = 'table' AND obj.schema_name = 'public' THEN
            -- 这里无法阻止 drop，因为是 AFTER event
            -- 但可以记录日志、清理关联资源等
            RAISE NOTICE 'Table dropped: %.%', obj.schema_name, obj.object_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT_TRIGGER log_dropped_tables
    ON sql_drop
    EXECUTE FUNCTION preserve_dropped_table();

注意：event trigger 是 AFTER 触发器，无法阻止 DROP。但可以配合其他机制使用。

思路 B：拦截 CREATE 并重定向（更高级）

-- 在 session 级别设置 search_path，加上一个包含同名视图的 schema
-- 应用以为自己在操作真实表，实际上可能被重定向

这需要根据具体应用模式来设计，不够通用。

方案 5：连接池 + 并发控制

使用 PgBouncer 等连接池减少同时连接数，降低锁竞争：

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

方案 6：WAL 和 checkpoint 调优

减少 DDL 带来的 WAL 压力：

wal_level = minimal                       # 如不需要复制/备份，降低 WAL 级别
max_wal_size = 16GB                       # 加大 WAL 上限
checkpoint_timeout = 30min                # 拉长 checkpoint 间隔
full_page_writes = off                    # 非生产环境可关闭（生产慎用）

方案 7：如果数据不重要——使用 UNLOGGED 表

如果应用允许（数据丢失可接受），可以在数据库层面使用模板表或者拦截建表过程。这比较 hack：

-- 修改 template0/template1 不现实，但可以用默认表空间策略
-- 或者通过 hook（需要扩展，如 pgext）来将表设为 UNLOGGED

实际操作中较难不改应用就实现。

方案 8：定期 REINDEX 系统目录

-- 阻塞性重建（需要排他锁，建议在低峰期执行）
REINDEX TABLE pg_catalog.pg_class;
REINDEX TABLE pg_catalog.pg_attribute;
REINDEX TABLE pg_catalog.pg_depend;
REINDEX TABLE pg_catalog.pg_type;

-- 或者使用 CONCURRENTLY（PostgreSQL 12+，对目录表的支持有限）

三、推荐的综合方案（不改应用）

┌──────────────────────────────────────────────────────┐
│                  postgresql.conf 配置                  │
├──────────────────────────────────────────────────────┤
│  autovacuum_naptime = 10s                            │
│  autovacuum_vacuum_cost_delay = 0                    │
│  maintenance_work_mem = 2GB                          │
│  autovacuum_work_mem = 2GB                           │
│  checkpoint_timeout = 30min                          │
│  max_wal_size = 16GB                                 │
└──────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────┐
│              系统目录表单独参数（init.sql）              │
├──────────────────────────────────────────────────────┤
│  对 pg_class / pg_attribute / pg_depend /            │
│  pg_type / pg_description / pg_description           │
│  设置 autovacuum_vacuum_scale_factor = 0.001         │
│  设置 autovacuum_vacuum_cost_delay = 0               │
└──────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────┐
│              定时任务（pg_cron 或 OS cron）             │
├──────────────────────────────────────────────────────┤
│  每 1-5 分钟 VACUUM 关键系统目录表                     │
│  每天低峰期 REINDEX 关键系统目录表                     │
│  监控目录表大小，告警阈值                              │
└──────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────┐
│                   监控指标                             │
├──────────────────────────────────────────────────────┤
│  pg_class 行数和 dead tuple 比例                      │
│  pg_attribute 行数和 dead tuple 比例                  │
│  目录表的总大小（pg_total_relation_size）              │
│  catalog cache miss 率（pg_stat_database）            │
└──────────────────────────────────────────────────────┘

监控查询示例：

-- 查看目录表的膨胀情况
SELECT 
    schemaname, 
    relname, 
    n_live_tup, 
    n_dead_tup,
    CASE WHEN n_live_tup > 0 
         THEN round(n_dead_tup * 100.0 / n_live_tup, 2) 
         ELSE 0 
    END AS dead_pct,
    pg_size_pretty(pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(relname))) AS total_size
FROM pg_stat_user_tables 
WHERE schemaname = 'pg_catalog' 
  AND relname IN ('pg_class','pg_attribute','pg_depend','pg_type')
ORDER BY n_dead_tup DESC;

四、根治思路

如果上述优化仍不够，本质上是应用架构问题。最佳的改法（即使不改应用本身，也改基础设施）：

在应用和 PostgreSQL 之间加一个代理层（如自研或使用 pgcat 等），拦截 DDL，将 DROP/CREATE 改为 TRUNCATE + 重用表
换用内存表方案：如果数据是临时的，考虑用 Redis / Memcached 代替频繁建表
使用 schema 池化：预先创建多个 schema，每个 schema 预建好所有需要的表，应用轮换使用

但核心结论是：不改应用的情况下，通过激进调优 autovacuum + 定时手动 VACUUM 系统目录 + 监控，是最实际的解法。

声明：以上为xiaomi mimo v2.5 pro的输出，不代表本人观点。

结语：实际项目中，vacuum很可能来不及处理这种高频的drop/create，而且因为每次删除创建的都是不同的表名，所以truncate也没意义。虽然频繁drop table/create table在其他数据库中也有问题（比如ORACLE的object_id会超），但是在postgresql中做这种操作对数据库的影响就极为严重。就算如已将ustore作为默认存储引擎的GaussDB，其元数据表依旧是astore，也是会遇到这个问题的。但正如AI回复，这个问题本质上是 "应用架构问题" ，或者说PG类内核的数据库就不是为了频繁drop table/create table做的底层设计。或许有其他PG的发行版有重构元数据的组织方式以及存储结构能解决此问题，但这种颠覆性修改注定难以和原版PG保持跟进。

频繁 DROP/CREATE TABLE 在 PostgreSQL 中的问题及解决思路