是时候从 MySQL 转到 PostgreSQL 18 了

是时候从 MySQL 转到 PostgreSQL 18 了

数据库技术革新的浪潮中，PostgreSQL 18 的发布标志着关系型数据库进入了新的时代，它不仅在性能上实现质的飞跃，更为开发者的工作效率带来了巨大提升。

近年来，随着应用复杂度的增加和数据规模的膨胀，许多基于 MySQL 的系统开始触及性能和维护的天花板。而 PostgreSQL 18 的发布，为这些挑战提供了全新的解决方案。

根据 db-engines.com 的趋势数据，PostgreSQL 是四大主流关系型数据库中唯一保持持续上升势头的系统，其受欢迎程度逐年攀升。这一趋势并非偶然，而是源于 PostgreSQL 在开发者友好性、性能、可扩展性以及 AI 趋势适应能力上的全面优势。

PostgreSQL 18 的异步 I/O 革命

PostgreSQL 18 引入了全新的异步 I/O（AIO）子系统，这是数据库性能领域的重大突破。与之前依赖操作系统预读机制的同步 I/O 不同，AIO 允许 PostgreSQL 并发地发出多个 I/O 请求，而不是等待每个请求顺序完成，这在云存储或低成本存储设备场景下的优势尤为明显。

实际测试表明，在顺序扫描、位图堆扫描等读取密集型场景中，性能可提升 2~3 倍。这对于实时读、数据仓库、报表分析等需要处理大量数据的场景尤其重要。

跳跃式扫描终结全表扫描噩梦

多列B树索引的 “最左前缀匹配” 限制，终于在 PostgreSQL 18 被打破了。新引入的跳跃式扫描(Skip Scan)技术，让查询能直接命中非首列的索引条件，彻底改变了此前必须全表扫描的尴尬处境。

举个实际例子，假设有 (a,b,c) 的联合索引，在以前版本中，执行 WHERE b >= 42 这样的查询会直接走全表扫描。而新版本会自动生成动态等式约束，逐个匹配 a 列的可能值并扫描对应 b 列范围，索引读取量最高可减少 90%。

更惊喜的是，非B树唯一索引现在能作为分区键使用，GIN索引也支持并行创建了。

RETURNING 支持获取新旧值

以往更新数据后要对比新旧值，要么写触发器要么查两次表。现在只需在 RETURNING 子句里加上 old.* 和 new.*，譬如：

UPDATE user
SET photo_path = '...'
WHERE user_id = 404
RETURNING old.photo_path, new.photo_path;

这在做数据审计或变更追踪时，这种代码能让你处理性能翻倍，并大幅简化业务层代码逻辑。

---

开发友好性

在涉及数据删除或更新的业务逻辑中，开发人员常常需要获取数据变动前的原始值，以执行相应的后续操作。一个典型的场景是：删除用户记录时，需要同时清理其关联的头像文件。这就要求在删除数据库记录前，先获取到该头像文件的存储路径。

传统的实现方式通常需要两次独立的数据库操作：先查询获取路径，再执行删除。这不仅增加了代码复杂性，还可能在并发场景下引发数据不一致的问题。

为了解决这一痛点，我们对 Zongsoft 数据引擎进行了改进，通过原生的 RETURNING 子句支持，将 查询原值 与 执行删除 合并为一个原子操作。这显著提升了代码的健壮性与简洁性，具体应用如下：

// 构建删除选项，明确指定需要返回的字段（例如用户的头像路径）
var options = DataDeleteOptions.Return(nameof(User.PhotoPath));

// 执行删除操作，此操作会自动生成包含 RETURNING 子句的SQL脚本
// 在删除数据的同时，会将被删除记录的指定字段原始值返回
var count = this.DataAccess.Delete<User>(
    Condition.Equal(p => p.UserId, 100), // 删除条件：UserId 主键为 100 的用户
    options //传递删除选项，该选项指定了要返回的字段
);

if (count > 0) // 如果成功删除了记录
{
    // 遍历所有被删除记录（此处为一条）的返回结果集
    foreach (var entry in options.Returning)
    {
        // 从返回的原始数据中安全地尝试获取 PhotoPath 字段的值
        if (entry.TryGetValue<string>(nameof(User.PhotoPath), out var path) && !string.IsNullOrEmpty(path))
        {
            // 如果成功获取到非空路径，则执行文件清理等后续业务操作
            FileSystem.File.TryDelete(path);
        }
    }
}

说明

通过一个原子操作替代了传统的 “先查询，后删除” 两步操作，其开发友好性体现在：

1. 原子性与一致性： 有效避免了在两次独立数据库操作之间，数据状态可能被其他并发操作修改而导致的业务逻辑错误，保证了操作的原子性和数据的一致性。

2. 代码简洁性与性能： 显著减少了数据库的往返次数，简化了代码逻辑，一次操作即可完成所有任务，提升了执行效率。

3. 意图清晰与可维护性： 通过声明式的 Return 方法，代码的意图（需要获取被删除数据的某个原始值）一目了然，极大地增强了代码的可读性和可维护性。

丰富的数据类型

PostgreSQL 提供比 MySQL 更加丰富的原生数据类型，包括数组、hstore（键值对）、范围类型（数值范围、日期范围等）以及几何数据类型。

特别是对于地理空间数据，PostgreSQL 的 PostGIS 扩展是开源领域最强大的空间数据库扩展，支持各种几何对象、空间函数和空间索引，远超 MySQL 的空间功能。

完善的 JSON 和 CTE 支持

PostgreSQL 的 JSONB 数据类型提供了比 MySQL 的 JSON 更高效的查询性能和更丰富的查询操作符。JSONB 以二进制格式存储，支持索引，可以大幅提升查询速度。

同时，PostgreSQL 对 Common Table Expressions (CTE) 的支持更加完善，包括递归 CTE，能够处理复杂的层次化数据查询。重要的是查询、删除、新增、修改语句均支持 CTE，这对编写复杂操作语句提供了提供了简洁高效的方式。

丰富的索引种类

PostgreSQL 提供比 MySQL 更多样化的索引类型，满足不同场景的查询优化需求：

• B-tree：标准索引，适用于等值查询和范围查询
• Hash：更快的等值查询，但不支持范围查询
• GIN（通用倒排索引）：适用于多值类型（数组、全文搜索、JSONB）
• GiST（通用搜索树）：适用于几何数据和全文搜索
• SP-GiST（空间分区通用搜索树）：适用于非平衡数据结构
• BRIN（块范围索引）：适用于大型表的范围查询

这些索引类型让开发者可以根据具体业务场景选择最优的索引策略，大幅提升查询性能。

强大的扩展生态系统

PostgreSQL 的扩展生态系统是其最大优势之一。通过安装扩展，可以轻松为数据库添加新功能。

在 AI 时代，pgVector 扩展特别值得关注，它为 PostgreSQL 提供了完整的向量数据库能力，支持各种向量相似度搜索算法（L2 距离、余弦相似度、内积等），可以直接支持 RAG（检索增强生成）应用，无需部署专门的向量数据库。

分库分表的高级支持

对于超大规模数据，PostgreSQL 提供了成熟的分区表功能，支持范围分区、列表分区和哈希分区。与 MySQL 的分区实现相比，PostgreSQL 的分区表功能更加完善和稳定。

此外，Citus 扩展为 PostgreSQL 提供了分布式数据库能力，可以透明地将数据分片分布到多个节点上，实现水平扩展，满足互联网级别的高并发访问需求。

结语：PostgreSQL 18 正当时

随着 PostgreSQL 18 的发布，其在性能、开发者能力和分布式特性上又有显著提升。在 AI 时代，PostgreSQL 凭借 pgVector 等扩展，已成为事实上的标准数据库选择。简而言之，它相对 MySQL 而言具备明显优势：

• 性能优势：面对海量数据具备更好的性能和更多的优化手段。
• 可扩展性：丰富的索引类型、强大的分区表和扩展生态系统，满足各种业务场景。
• 开发效率：更丰富的数据类型、完善的 JSONB 支持、增强的 RETURNING 子句、全面的 CTE 支持，大幅提升开发效率。
• AI 趋势：pgVector 等扩展为 AI 应用提供原生支持，避免多技术栈复杂性。

无论是新项目还是现有系统迁移，现在都是转向 PostgreSQL 的最佳时机。这一转变将为你带来更强大的数据处理能力、更高效的开发体验和更好的长期可扩展性。