PostgreSQL 教程: 迁移 Oracle / SQL Server 避坑指南

摘要：在本教程中，我们会介绍将 Oracle / SQL Server 迁移至 PostgreSQL 时的一些核心误区与避坑指南。

目录

核心痛点与根源分析

在数据库迁移项目中，大部分团队犯错的根源可以总结为：追求上线速度、缺乏 PostgreSQL 原生经验、过度依赖自动化工具，并误以为 PostgreSQL 的行为与 Oracle 或 SQL Server 完全一致。

通常，代码转换本身仅占整个迁移项目约 25% 的工作量，而测试、性能调优和验证占到 50% 以上。如果在项目初期直接照搬源库的设计，后续将在性能调优和生产运维阶段付出数倍的重构代价。

开发与迁移初期：显性规范误区

误区 1：标识符大小写与双引号滥用

• 问题现象：Oracle 默认将未加双引号的元数据转换为大写，而 PostgreSQL 默认转换为小写。迁移工具为防止报错，往往会将源库的大写表名/列名加上双引号拉取到 PostgreSQL 中（如 "USERS"、"FIRST_NAME"）。
• 引发后果：后续所有 SQL 必须时刻手动加上双引号与大写。一旦后续开发新增了未加双引号的列（变成小写），会导致字段无法识别，极难维护。
• 解决方案：在迁移初期将表名、列名等所有标识符统一转换为 PostgreSQL 原生的全小写，彻底消除双引号。

误区 2：混淆空字符串（''）与 NULL 的处理逻辑

• 问题现象：Oracle 将空字符串 '' 视同 NULL；而 PostgreSQL（标准 ANSI 规范）明确区分空字符串与 NULL。在 PostgreSQL 中，任何字符串与 NULL 进行连接（||），结果均为 NULL。
• 解决方案：除了使用 COALESCE 函数处理可能为 NULL 的字段外，更推荐直接使用 PostgreSQL 内置函数（如 concat_ws）进行字符串拼接，提升代码可读性与维护性。

误区 3：忽视包含 NULL 值的唯一约束差异

• 问题现象：在 Oracle 的多列唯一索引中，包含 NULL 的记录可能触发唯一性限制；但在 PostgreSQL 中，由于 NULL != NULL，包含 NULL 的行可以重复插入多次，导致数据冗余与业务逻辑异常。
• 解决方案：迁移包含可空列的唯一索引时，显式指定 NULLS NOT DISTINCT 关键字（PostgreSQL 15+ 支持），以保持与源库一致的业务约束。

误头 4：死板照搬业务代码，忽视 PostgreSQL 原生高级特性

• 问题现象：试图用 PL/pgSQL 100% 逐行重写源库中的复杂存储过程或函数。
• 典型案例：某团队曾维护一套 2000 多行处理 IP 地址与子网计算的 Oracle Package。迁移时将其彻底废弃，改用 PostgreSQL 原生的 inet / cidr 数据类型及其原生运算符，直接节省了数周的代码转换与测试工作。
• 解决方案：战术性地利用 PostgreSQL 特性（如原生网络/JSON数据类型、PL/V8、PL/Perl 等），避免重复造轮子。

测试与架构设计阶段：架构与性能误区

误区 5：过度设计“双向同步”回退方案

• 问题现象：管理层常坚持要求搭建 Oracle ↔ PostgreSQL 的双向实时同步，以备上线失败时无缝切回源库。
• 实际情况：跨异构数据库的双向同步极难处理冲突。实战经验表明，绝大多数回退发生在切库当日的前几小时（直接中止切换），几乎没人会在上线数月后成功双向切回（因为新业务代码早已部署至 PostgreSQL，无法在源库运行）。
• 解决方案：采用单向前行（Fail Forward）策略（如 Oracle -> PostgreSQL -> 备用库/源库副本），将精力投入到前期充分的压力测试中，而不是构建脆弱且复杂的双向同步回路。

误区 6：数据类型选择不当（最致命的性能杀手：NUMERIC 滥用）

• 问题现象：Oracle 的 NUMBER 类型精度最高可达 38 位。迁移工具为保证不溢出，通常将所有数字列（包括主键、外键、ID）统一映射为 PostgreSQL 的 NUMERIC 类型。

• 性能影响：

• NUMERIC 是可变长高精度数值类型，计算与索引开销极大。

• 实验数据：对 1000 万行表进行 Join 连接，使用 BIGINT 比使用 NUMERIC 性能提升 39%。

• 后果与建议：若在调优阶段才发现性能低下，所有存储过程、触发器和应用代码均需重新修改类型。主键与外键应优先选用 BIGINT 或 INT，仅对财务/高精度货币数据使用 NUMERIC。

误区 7：盲目照搬 B-Tree 索引，忽略 Hash / GIN 等索引特性

• 问题现象：虽然 90% 的 B-Tree 索引可以直接移植，但剩余 10% 采用 PostgreSQL 特性索引可带来质的飞跃。
• 优化举例：
• 通配符模糊查询：对于 LIKE '%xxx%' 查询，可利用 GIN 结合 Trigram 索引实现快速检索。
• UUID 精确查找：使用 B-Tree 索引需遍历 5 个 Buffer（约 106 μs）；改用 Hash 索引仅需遍历 2 个 Buffer（约 76 μs）；若配合 PostgreSQL 原生的 uuid 数据类型，耗时可进一步降至 67 μs。

生产运行阶段：上线后暴露的隐蔽灾难

误区 8：在 PL/pgSQL 中滥用 EXCEPTION 异常捕获块

• 问题现象：习惯性在每一个存储过程/函数中添加 EXCEPTION 块（如 WHEN NO_DATA_FOUND THEN RETURN NULL）。

• 底层机制：PostgreSQL 在处理 PL/pgSQL 的 EXCEPTION 块时，会在底层隐式创建 Savepoint（保存点）并开启子事务。

• 后果与引发灾难：

• 事务 ID（XID）消耗剧增：带 Exception 块的函数内部循环调用会快速消耗大量 XID。

• 触发 Autovacuum 暴风：频繁消耗 XID 会导致系统过快触及 20 亿次 XID 环绕保护限制，迫使数据库强行触发 Vacuum Freeze 占用极高 IO/CPU，甚至可能导致数据库为防止数据损坏而自动关停。

• 解决方案：不要盲目套用 Exception；对于查无结果等场景，依靠返回 NULL 处理即可；对于 Upsert 逻辑，严禁依赖捕获主键冲突异常做 Update，必须使用原生的 INSERT ON CONFLICT。

误区 9：频繁创建临时表（Temp Tables）导致系统目录膨胀

• 问题现象：SQL Server 背景的开发者习惯在存储过程和高频业务逻辑中大量创建与销毁临时表。
• 底层机制：PostgreSQL 的临时表会在系统目录（Catalog，如 pg_class、pg_attribute）中写入真实元数据记录。
• 后果与引发灾难：高频创建临时表会在系统目录中产生海量死元组（Dead Tuples），导致 pg_attribute 等系统表体量急速膨胀（甚至达到数十 GB / TB 级别），直接拖慢整个数据库的所有 SQL 执行速度。
• 解决方案：高频业务逻辑中禁止使用临时表；改用 CTE（通用表表达式/WITH子句） 或内联子查询；仅在低频报表等离线场景下适度使用临时表。

总结与最佳实践

1. 黄金时间分配原则：宁可在迁移初期多花 2 天时间建立规范的数据类型映射与索引策略，也不要在测试与生产阶段花费数周重构代码。
2. 摆脱惯性思维：深刻理解 PostgreSQL 的底层工作机制（如 MVCC、XID 环绕保护、Catalog 膨胀机制），用“PostgreSQL 的方式”编写 SQL 和存储过程。
3. AI / LLM 辅助迁移建议：在使用大语言模型辅助生成或转换 SQL/PLpgSQL 代码时，应在 Prompt 中显式添加禁忌清单（例如：“禁用 NUMERIC 作为主键”、“禁用无必要的 EXCEPTION 块”、“高频逻辑禁用临时表”、“使用 INSERT ON CONFLICT 代替异常捕获”），引导 AI 输出符合最佳实践的代码。

参考

Top mistakes when migrating from Oracle and SQL Server to PostgreSQL

了解更多

PostgreSQL 管理