当前位置： 首页 > ai >正文

PostgreSQL因为A/B switch以及group表过多导致WAL full的情况讨论

ai 2025/8/11 14:41:41

背景

我们用的是pgsql数据库，我们系统设计是一个customer group一个逻辑分组，然后一个分组有15个表左右，也就是detail_tableA_groupA,detail_tableB_groupA......然后customer group有很多，有500个，那么一个customer15个表，那就有7500个表，导入的时候我们已经使用A/B table/view switch，这样就会导致有15000个表，每次都是执行导入a表或者b表，每次导入都是整表全量导入，经常导致WAL文件满然后数据库down掉。DBA建议我们分不同schema，这样就可以把500个group，15000个表分为约10个schema，每个schema约1500个表，请问这样合理吗，是否会减少WAL full的情况。

解读

Powered by Moshow郑锴@https://zhengkai.blog.csdn.net/

结论先说

  • 把 500 个 group（≈15,000 张表含 A/B）拆到 10 个 schema，主要是“管理上的整理”，几乎不会减少 WAL 产出，也不能从根本上避免 WAL 填满。WAL 的体量取决于你的“写入量、索引维护、检查点/归档/复制配置、并发度”，与表分在哪个 schema 关系不大。

  • 你们的“按组整表全量 + A/B 切换”的模式本身会在短时间内制造巨量 WAL。如果并发导入多个组、或归档/复制滞后，就很容易把 pg_wal 盘打满。

为什么会 WAL 满

  • 导入量大且集中：整表全量 COPY/INSERT + 同步维护索引，会产生接近“数据大小 ×（1~多）倍”的 WAL（索引构建也要写 WAL）。

  • 并发导入：同一时间跑多个组，WAL 生成速率叠加，超过归档/回收能力。

  • 检查点与全页写：频繁 checkpoint 会触发更多 full-page images；设置不当会放大 WAL。

  • 归档/复制滞后：archive_command 失败、或有复制槽/下游滞后，会导致 WAL 无法回收，pg_wal 迅速被撑满。

  • A/B 策略的额外成本：新表建索引、写入统计与元数据变更，都会追加 WAL；表越多，目录开销也更大。

该不该分 schema？

  • 可以分：提升可读性、权限隔离、运维分组、减少一个 schema 下目录项过多的管理麻烦。

  • 不要指望它减 WAL：WAL 是“集群级”的日志，写入多少由变更量与参数决定，与 schema 分布无关。

  • 可能的边际好处：轻微缓解系统目录热度/锁竞争，但对 WAL 体量影响可以忽略。

优先级最高的改造与参数（按效果排序）

  1. 架构/流程层面降峰值

  • 限制并发导入的组数（例如同一时刻 ≤N 个组），把峰值 WAL 速率压下来。

  • 分批提交：一口气整表导入很猛，改为“分块 COPY、按块提交”（找到你们系统在“提交次数”和“单次事务大小”之间的平衡）。

  • 先 TRUNCATE 后 COPY：如果目标是全量覆盖，优先使用 TRUNCATE（WAL 极少），而不是 DELETE；随后 COPY 裸表再建索引。

  • 导入期间 synchronous_commit=off（会话级）：不减 WAL 体积，但显著减 fsync 压力，提升吞吐，缓解堆积。

  • 先导数据、后建索引：对新表或被 TRUNCATE 的表，先 COPY，再建索引；比边写边维护索引的 WAL 更低。

  1. 用“分区交换”替代“表 A/B 切换”

  • 把每类明细表收敛为“15 个父表”，按 group_id 做 LIST/RANGE 分区（500 个分区/父表）。

  • 对需要全量刷新的 group：

    • 新建“独立同构表”，COPY 数据、建索引、ANALYZE；

    • 在同一事务内执行 ALTER TABLE … ATTACH PARTITION 把它“挂为该组的新分区”，再 DETACH+DROP 旧分区。

  • ATTACH/DETACH 属于元数据级别操作，数据不搬迁，WAL 极小；命名稳定、统计信息友好、计划器可用。

  • 注意：要满足分区约束检查；目标表需为 LOGGED；大表验证会扫描但不会产生成比例 WAL。

  1. 降低 WAL 产出与冲击的技巧

  • wal_compression=on：显著减少 full-page images 的体积。

  • 提高 checkpoint_timeout、max_wal_size，checkpoint_completion_target≈0.9：减少频繁 checkpoint 带来的 FPI 洪峰。

  • （可选、谨慎）导入窗口内关闭 full_page_writes：能大幅减 WAL，但崩溃后需依赖干净检查点；如果没有严格高可用/恢复要求且在维护窗内，才考虑。

  • （若无任何复制/归档需求）wal_level=minimal + COPY FREEZE 在维护窗口进行：对“新建且空表”的 COPY 可最小化 WAL，显著降压；需要重启改集群级参数，慎用。

  • UNLOGGED 作为中间落地表：加载几乎不记 WAL；然后批量 INSERT/CTAS 进正式表（仍会记 WAL，但可错峰/分组推进）。注意 UNLOGGED 崩溃后会被清空。

  1. 让 WAL 有地儿去

  • 增大 pg_wal 所在盘容量或迁移到更大/更快卷。

  • 确保归档/复制健康：不要让 archive_command 失败；检查是否有落后的 replication slot 长期占坑；监控 slot 延迟并按需丢弃无主的槽。

  • 合理的 autovacuum/analyze：避免导入后长时间不做 ANALYZE 造成计划不稳定、重复大动作。

常用方案对比🌟

方案/设置对 WAL 体积的影响风险/注意事项适用场景
限制导入并发 + 分批提交直接降峰值速率需要压测找到最佳批量大小任何场景
TRUNCATE + COPY + 后建索引明显降低需要停写窗口全量覆盖
分区交换（ATTACH/DETACH）数据不搬迁，WAL 很小需要分区化改造需要 A/B 效果且稳定名
wal_compression=on降低 FPI 体积CPU 略增通用、推荐
提高 max_wal_size/检查点参数降低 FPI 次数/峰值占用更多磁盘磁盘富余
synchronous_commit=off（会话）不减体积，提吞吐崩溃可能丢最近事务可接受轻微数据回滚
wal_level=minimal + COPY FREEZE可大幅降低要无复制；需重启；严格条件维护窗口批量导入
UNLOGGED 中转中转不记 WAL崩溃丢中转表；最终写仍记 WAL想把压力错峰

一个可执行的导入流程范式（推荐）

  • 将 15 张“父表”按 group_id 做分区（每父表 500 分区）。

  • 对某 group 全量刷新：

    1. 在同构 schema 下创建“新数据表”（LOGGED），不建索引；

    2. 会话设置 synchronous_commit=off；COPY 数据（满足条件时可用 COPY FREEZE）；

    3. 建索引（提高 maintenance_work_mem）、ANALYZE；

    4. BEGIN；ALTER TABLE 父表 ATTACH PARTITION 新表（带约束验证）；DETACH 旧分区并 DROP；COMMIT。

  • 全流程 WAL 主要集中在“COPY + 建索引”，交换本身 WAL 极少；通过限制并发 + 分批 COPY，整体峰值可控。

关于你们当前 A/B 表

  • 如果暂不重构为分区：也尽量改为“TRUNCATE 目标表 + COPY + 后建索引”的一次性替换，而不是维护两套 A/B 并频繁重建索引。

  • 若坚持 A/B：考虑把“新表加载”阶段用 UNLOGGED，完成后在低峰期 ALTER SET LOGGED（注意这一步会重写表并产生日志，需评估窗口）。

我建议你们先落地的 5 条

  1. 立刻开启 wal_compression，并把 max_wal_size、checkpoint_timeout、checkpoint_completion_target 调到更保守的值。

  2. 检查 replication slots 与归档是否有滞后/失败，确保 WAL 能被及时回收。

  3. 把导入并发控制在一个稳态上限（例如 2–4 个组同时导），并把单次事务控制在“几百万行”级别。

  4. 改导入流程为“TRUNCATE + COPY + 后建索引 + ANALYZE”。

  5. 中期推进“分区 + 分区交换”替代“表级 A/B 切换”。

需要一点关键信息（好给你更具体的参数）

  • PostgreSQL 版本号？是否有物理/逻辑复制、归档？

  • 单表数据量与索引规模（行数/GB）大致范围？

  • 现在导入的并发度、多长时间跑完一次全量？

  • pg_wal 目录磁盘大小、当前 max_wal_size 与 checkpoint 参数？

附录

 Powered by Moshow郑锴@https://zhengkai.blog.csdn.net/

synchronous_commit=off（会话）

在 PostgreSQL 中设置 synchronous_commit=off（会话级）是一个非常实用的性能优化手段，尤其适用于你们这种“批量导入数据”的场景。下面我来详细解释它的作用和影响：

🚀 它的作用是什么？

当你设置 synchronous_commit=off，事务提交时不会等待 WAL 写入磁盘（fsync）完成，而是直接返回成功给客户端。这意味着：

  • 事务提交更快：因为省去了等待磁盘刷写的时间。

  • 吞吐量显著提升：尤其在大量 INSERT/COPY 的场景下，可以提高导入速度 2～10 倍，甚至更多。

  • WAL 仍然会生成：只是写入是异步的，由后台进程负责 flush。

📉 有什么副作用？

  • 可能丢数据：如果数据库崩溃（如断电、kill -9），最近提交但尚未写入磁盘的事务可能会丢失。

  • 不会影响事务一致性：不会出现“部分提交”；要么整个事务丢失，要么完整保留。

  • 不建议用于关键业务写入：适合用于“可重导入”的数据，比如你们的全量导入流程。

✅ 适用场景

场景是否推荐使用 synchronous_commit=off
批量导入（COPY/INSERT）✅ 非常推荐
ETL 中间表写入✅ 推荐
日志类、缓存类表✅ 推荐
订单、交易类表❌ 不建议
需要强一致性场景❌ 不建议

🛠 如何设置（会话级）

你可以在导入脚本或连接后执行：

sql

SET synchronous_commit TO OFF;

或者在连接字符串中设置：

postgresql://user:pass@host/dbname?options=-c%20synchronous_commit=off

设置后只影响当前连接，不会影响其他会话。

🧠 总结一句话

synchronous_commit=off 是“牺牲一点点崩溃安全性，换取巨大导入性能”的利器。只要你导入的数据可以重做，它就是你最值得用的参数之一。

如果你想进一步压榨导入性能，我还可以帮你搭配其他参数，比如 wal_writer_delay、commit_delay、maintenance_work_mem 等，打造一套“极速导入配置”。要不要我帮你配一套？

wal_compression=on

wal_compression=on 是 PostgreSQL 中一个非常实用的参数，尤其在你进行大量写入或批量导入时，它可以显著减少 WAL（Write-Ahead Logging）日志的体积，从而提升整体性能。下面我来详细拆解它的作用和适用场景：

🧩 它到底做了什么？

当你设置 wal_compression=on，PostgreSQL 会尝试压缩那些写入 WAL 的数据块，尤其是那些因为 UPDATE 或 COPY 操作而触发的 full-page writes（完整页写入）。

  • 默认情况下，每次修改数据页时，PostgreSQL 会把整个数据页写入 WAL，以确保崩溃恢复时能还原。

  • 如果这些页之间变化不大，压缩就能显著减少 WAL 的体积。

  • 使用的是 pglz 压缩算法（轻量级、低 CPU 消耗）。

📉 有什么好处？

  • 减少 WAL 文件大小：尤其在高频写入场景下，WAL 体积可以减少 30%～70%。

  • 降低磁盘 I/O 压力：WAL 写入更少，磁盘负担更轻。

  • 提升性能：在某些场景下，尤其是 SSD 或网络存储，性能提升非常明显。

  • 更快的备份与复制：WAL 更小，流复制和归档也更高效。

⚠️ 有什么代价？

  • 略微增加 CPU 使用率：因为需要进行压缩运算，但通常非常轻微。

  • 对 INSERT 大量新数据影响较小：因为 INSERT 通常不触发 full-page writes，压缩效果有限。

  • 对 UPDATE/COPY 更有效：尤其是修改已有数据页时。

✅ 适用场景

场景是否推荐开启 wal_compression=on
大量 UPDATE 操作✅ 非常推荐
批量 COPY 导入（更新旧数据）✅ 推荐
高并发写入系统✅ 推荐
INSERT 新数据为主⚠️ 效果有限
CPU 紧张场景❌ 需评估是否值得开启

🛠 如何设置

你可以在 postgresql.conf 中设置：

conf

wal_compression = on

或者在运行时通过 SQL 设置（需超级用户）：

sql

ALTER SYSTEM SET wal_compression = 'on';
SELECT pg_reload_conf();

🧠 总结一句话

wal_compression=on 是一个“用少量 CPU 换大量磁盘空间”的优化选项，在写入密集型场景中非常值得开启，尤其是你在做批量导入或频繁更新时。

如果你正在调优导入性能，我可以帮你搭配一整套参数组合，比如 synchronous_commit=off、wal_writer_delay、commit_delay、checkpoint_timeout 等，打造一个“极速导入模式”。要不要我帮你配一套？

PostgreSQL 导入优化参数配置

参数推荐值作用说明
synchronous_commitoff提交事务不等待 WAL 写入磁盘，显著加快写入速度
wal_compressionon压缩 WAL 中的 full-page writes，减少磁盘 I/O
wal_writer_delay200msWAL 写入延迟，减少频繁刷盘（默认是 200ms，可视情况调高）
commit_delay10000（单位微秒）提交延迟，允许多个事务聚合写入 WAL，提高效率
checkpoint_timeout30min延长检查点间隔，减少导入期间的磁盘压力（默认 5min）
checkpoint_completion_target0.9检查点更平滑地进行，避免 I/O 峰值
max_wal_size2GB 或更高增加 WAL 空间，避免频繁触发 checkpoint
maintenance_work_mem512MB 或更高COPY 导入时用于索引构建、排序等操作，提升效率

✅ 会话级设置（导入脚本中执行）

SET synchronous_commit TO OFF;

✅ 系统级设置（postgresql.conf）

wal_compression = on
wal_writer_delay = 200ms
commit_delay = 10000
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
max_wal_size = 2GB
maintenance_work_mem = 512MB

⚠️ 修改这些参数后需重启 PostgreSQL 服务才能生效。

🚀 COPY 导入技巧加持

  • 使用 COPY ... FROM STDIN WITH (FORMAT csv) 而非 INSERT。

  • 禁用索引和约束，导入后再启用。

  • 使用事务包裹多个 COPY 操作，减少 commit 次数。

  • 导入完成后手动执行 CHECKPOINT，确保数据落盘。

http://www.xdnf.cn/news/17449.html

相关文章：

  • 冒泡排序实现以及优化
  • django基于Python的设计师作品平台的数据可视化系统设计与实现
  • 数字图像处理2——图像增强
  • Java设计模式之开闭原则介绍与说明
  • TypeScript中的type和interface的区别是什么？
  • 红楼梦文本数据分析
  • LeetCode 869.重新排序得到 2 的幂：哈希表+排序（一次初始化）
  • 前端开发的奇技淫巧 --- 持续更新中
  • 使用线性降维方法进行数据降维
  • 使用tcp ntrip 协议 接收数据报错 java.net.SocketException: Connection reset
  • MariaDB 数据库管理与web服务器
  • 变量详解：创建初始化与内存管理
  • 【数据结构入门】栈和队列的OJ题
  • Virtio 驱动关键结构体与函数详解
  • RabbitMQ面试精讲 Day 18：内存与磁盘优化配置
  • 01.【面试题】在SpringBoot中如何实现多数据源配置
  • UNet改进（31）：基于Adaptive Attention的UNet设计与实践
  • 智慧社区（十一）——Spring Boot 实现 Excel 导出、上传与数据导入全流程详解
  • 【永磁同步电机数学模型全程推导】【7 转矩方程】
  • IntelliJ IDEA 2025.2 重磅发布
  • 移动端音频处理实践：59MB变声应用的技术实现分析
  • 【GPT入门】第43课 使用LlamaFactory微调Llama3
  • GitLab 零基础入门指南：从安装到项目管理全流程
  • 复杂项目即时通讯从android 5升级android x后遗症之解决 ANR: Input dispatching timed out 问题 -优雅草卓伊凡
  • Android Intent 解析
  • 绕过文件上传漏洞并利用文件包含漏洞获取系统信息的技术分析
  • GPT OSS深度解析：OpenAI时隔6年的开源模型，AI民主化的新里程碑?
  • ubuntu 安装内核模块驱动 DKMS 介绍
  • RL代码实践 02——策略迭代
  • IDEA 如何导入系统设置