PostgreSQL 表太多时，内存和元数据查询会先撑不住

2026-06-30 28 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：9 分钟

“表很多”听起来像是建模风格问题，但在 PostgreSQL 里，它也可能变成实打实的内存和 CPU 问题。一次故障排查中，Linux OOM killer 会偶发杀掉 PostgreSQL，另一些长查询则持续占用 CPU、拖慢应用；最后问题都指向同一个根因：单个数据库里有成千上万张表以及随之膨胀的元数据对象。

问题不一定出在 work_mem

排查 PostgreSQL 内存问题时，很多人会先看几个熟悉的方向：

  • shared_buffers：PostgreSQL 启动时分配的共享缓冲区。
  • work_mem：每个后端进程执行排序、哈希等操作时使用的私有内存上限。
  • 动态共享内存：并行查询 worker 和后端进程之间交换数据时使用。
  • 大字段处理：大型 JSON、二进制数据、PostGIS 几何对象被 de-TOAST 后带来的内存压力。

这些都常见，也都值得检查。但这次真正膨胀的是 CacheMemoryContext。

CacheMemoryContext 用来存放 relcache、catcache 等缓存。catcache 缓存系统目录表里的元数据项；relcache 缓存 relation 的描述信息，也就是表、视图、索引以及其他记录在 pg_class 里的对象。关键点在于：这些缓存没有固定大小上限，它们会记住当前数据库连接曾经用过的元数据。

这个设计在普通数据库里很合理，因为元数据规模通常有限。但如果一个数据库里有几万张表，长生命周期连接又陆续访问了大量表，那么单个连接的 CacheMemoryContext 就可能涨到数百 MB。一个连接吃掉半 GB 内存也许还不致命，但如果连接池里有很多这样的长连接，总内存很快会被吃光。

表多，实际对象会更多

“20000 张表”听起来已经很多，但 PostgreSQL 实际创建和维护的对象远不止表本身。

一张带主键、自增列、复合类型的表，可能额外带来：

  • 主键索引；
  • TOAST 表；
  • TOAST 索引；
  • identity 列使用的 sequence；
  • 每张表对应的 composite type；
  • composite type 对应的 array type；
  • 每列在 pg_attribute 里的记录；
  • 约束在 pg_constraint 里的记录。

因此，表数量膨胀会直接放大系统目录表，例如 pg_class、pg_attribute、pg_type、pg_constraint。这不仅影响内存缓存，也会影响元数据查询。原文案例中，长时间运行的是 PostGIS 的 raster_overviews 元数据视图查询，它依赖 pg_class、pg_attribute、pg_type、pg_namespace、pg_constraint 等系统目录表。元数据表一旦大到离谱，原本应该很轻的查询也会变重。

可以这样复现实验：观察 CacheMemoryContext 增长

下面的实验会创建大量表，建议只在临时数据库里运行。完成后直接 DROP DATABASE 清理，避免污染业务环境。

需要 PostgreSQL 支持 pg_get_backend_memory_contexts()。执行前请把数据库名 many_tables_lab 改成你自己的测试库名。

createdb many_tables_lab
psql many_tables_lab

在 psql 中创建 20000 张表，每张表 20 列：

DO $$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..20000 LOOP
    EXECUTE format(
      E'CREATE TABLE %I (\n'
      ' id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n'
      ' col2 text,\n'
      ' col3 integer,\n'
      ' col4 timestamp,\n'
      ' col5 boolean,\n'
      ' col6 double precision,\n'
      ' col7 boolean,\n'
      ' col8 varchar(42),\n'
      ' col9 numeric,\n'
      ' col10 xml,\n'
      ' col11 bytea,\n'
      ' col12 bit varying(8),\n'
      ' col13 inet,\n'
      ' col14 date,\n'
      ' col15 bigint,\n'
      ' col16 smallint,\n'
      ' col17 uuid,\n'
      ' col18 timestamp with time zone,\n'
      ' col19 real,\n'
      ' col20 jsonb\n'
      ')',
      'tab_' || i
    );

    IF i % 100 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
END;
$$;

此时查看当前连接的 CacheMemoryContext：

SELECT pg_size_pretty(total_bytes) AS cache_memory
FROM pg_get_backend_memory_contexts()
WHERE name = 'CacheMemoryContext';

刚创建完表后，缓存可能还不夸张。接下来让当前连接逐个访问这些表：

DO $$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..20000 LOOP
    EXECUTE format('TABLE %I', 'tab_' || i);

    IF i % 100 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
END;
$$;

再次查看缓存：

SELECT pg_size_pretty(total_bytes) AS cache_memory
FROM pg_get_backend_memory_contexts()
WHERE name = 'CacheMemoryContext';

你会看到 CacheMemoryContext 明显增长。这个实验说明的不是“访问表本身消耗了数据内存”，而是“长连接访问大量 relation 后，元数据缓存不断积累”。

还可以顺手看一下系统目录表规模：

SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname IN (
  'pg_class',
  'pg_attribute',
  'pg_type',
  'pg_namespace',
  'pg_constraint'
)
ORDER BY relname;

清理测试库：

psql -d postgres -c "DROP DATABASE many_tables_lab;"

缓解手段：限制连接、缩短空闲连接寿命、减少对象数

最好的方案仍然是不要在单个数据库里制造过多表和对象。如果表数量来自租户隔离、时间分片、动态建表或地理栅格数据拆分，应该认真评估是否能改成分区表、租户字段、归档库、对象存储或更少的逻辑表。

如果短期内无法重构，可以先降低爆炸半径：

# postgresql.conf 示例：按业务连接行为调整，不要直接照抄到生产
max_connections = 100
idle_session_timeout = '30min'

如果使用 PgBouncer，可以这样控制数据库连接数量。下面只是一个可改造的最小示例，实际密码、认证方式、池大小需要按环境调整：

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 30
reserve_pool_size = 5
server_idle_timeout = 600

这些配置不能让 PostgreSQL 的元数据查询突然变快，但可以限制同时膨胀的后端进程数量。idle_session_timeout 适用于 PostgreSQL 14 及以上版本，它会终止空闲太久的 session，从而避免长连接无限期积累缓存。前提是应用或连接池必须能正确处理断开的连接。

采用建议：把元数据规模当成容量指标

表太多不是抽象的“设计不优雅”，它会落到两个具体问题上：长连接的元数据缓存越来越大，以及系统目录表查询越来越重。PostgreSQL 未来也许会有更聪明的元数据缓存策略，但今天更稳妥的做法是控制对象数量。

落地时可以用这份检查清单：

  • 定期统计单库表、索引、sequence、TOAST 对象数量。
  • 监控 pg_class、pg_attribute、pg_type 等系统目录表的估算行数。
  • 对长生命周期连接抽样查看 pg_get_backend_memory_contexts()。
  • 给连接池设置合理上限，不让大量后端进程同时积累巨型 relcache/catcache。
  • 对空闲 session 使用 idle_session_timeout，但先确认应用重连逻辑可靠。
  • 对“每客户一张表”“每天一张表”“每文件一张表”这类设计保持警惕，优先评估分区、归档、租户字段或单独数据库拆分。

同样的思路也适用于 schema、database、role：单个集群或单个数据库里的元数据规模应该保持温和。PostgreSQL 擅长管理数据，但不代表你可以无限制造元数据对象而不付账单。

# AI # Go # LLM # PostgreSQL # 可观测性 # 后端 # 安全 # 数据库

相关推荐