PostgreSQL v9 pg_dump导出大对象时运行极慢问题咨询

PostgreSQL 9.x版本下500万级小尺寸大对象导致pg_dump运行超5小时无法完成的现象不属于正常预期，是版本原生设计缺陷导致的性能问题。

• 你之前做的4项优化仅对普通表数据导出生效：PostgreSQL 9.x的pg_dump并行逻辑（-j参数）不覆盖大对象导出环节，无论开多少并行线程，所有大对象的元数据拉取、内容读取都由单线程串行执行。
• 9.x版本大对象导出采用逐对象查询的逻辑：每导出1个大对象就要单独发起一次系统表查询、一次数据读取请求，500万个大对象对应500万次独立IO交互，单blob体积仅100KB时，元数据查询、IO调度的开销占比远高于实际数据读取开销，直接导致导出耗时指数级上升。
• 逐表导出时不带-b参数速度快，本质是默认跳过了大对象导出逻辑，恢复后应用报资源找不到，就是因为存储在pg_largeobject系统表中的业务二进制资源未被同步。加上-b参数后触发上述串行逐对象导出逻辑，速度会再次骤降。

• 短期应急导出方案：
  • 第一步：不带-b参数，用你之前调好的并行目录格式参数导出全量表结构、表数据，该步骤数分钟即可完成，恢复到目标库后先执行表结构和数据恢复。
  • 第二步：用超级用户登录源库，通过COPY (SELECT * FROM pg_largeobject) TO '/path/lo_data' WITH BINARY;、COPY (SELECT * FROM pg_largeobject_metadata) TO '/path/lo_meta' WITH BINARY; 命令批量导出大对象的两张系统表全量数据。
  • 第三步：清空目标库的pg_largeobject、pg_largeobject_metadata表，用COPY命令把第二步导出的两个文件导入目标库，校验大对象权限匹配即可。该方案绕过了pg_dump原生的逐对象导出逻辑，500万小blob的全量导出+导入耗时通常可压缩到30分钟以内。
• 长期优化方案：
  • 升级到PostgreSQL 12及以上版本：高版本对大对象导出做了批量读取优化，同时支持大对象导出的并行调度，同数据量下大对象导出耗时仅为9.x版本的1/10甚至更低。
  • 业务侧存储改造：如果单blob尺寸普遍在1MB以内，可将大对象存储替换为bytea字段类型，bytea数据随普通表数据走并行导出逻辑，不会触发单线程串行卡顿问题。

内容的提问来源于stack exchange，提问作者Nader Kahwaji