事务性 CRUD 应用：PostgreSQL 与 MariaDB 对比

当 CRUD 应用超出原型阶段

原型 CRUD 应用通常感觉很快，因为数据少、团队小、流量可预测。你可以用简单查询、几个索引和手工的模式调整就应付过去。然后应用有了真实用户、真实工作流和真实的最后期限。

增长会改变负载。列表和仪表板整天被打开。更多人编辑相同记录。后台任务开始批量写入。这时“昨天还能用”会变成页面变慢、偶发超时和高峰时段的锁等待。

如果你看到像翻到第 20 页后列表变慢、发布里包含数据回填（不仅仅是新列）、越来越多的“灵活字段”用于元数据和集成载荷，或者支持工单说“繁忙时保存要很久”，那说明你可能已经越过了临界点。

这时比较 PostgreSQL 和 MariaDB 不再只是品牌偏好，而是实际问题。对于事务性 CRUD 工作负载，通常决定胜负的细节是：当查询变复杂时的索引选项、大表上的迁移安全性、JSON 存储与查询，以及能减少应用端工作的查询特性。

本文聚焦于这些数据库行为。不涉及服务器尺寸、云定价或厂商合同的深度讨论——这些很重要，但通常比你产品依赖的模式与查询风格更容易以后修改。

从应用需求开始，而不是数据库品牌

更好的起点不是“PostgreSQL vs MariaDB”，而是你应用的日常行为：创建记录、更新少数字段、列出带筛选的结果，并在多人并发点击时保持正确性。

把最繁忙的界面写下来。每次写入对应多少次读取？高峰什么时候出现（早晨登录、月末报告、大量导入）？记录你依赖的精确筛选和排序，因为这些会驱动后续的索引设计和查询模式。

然后定义你的硬性要求。对于很多团队来说，这意味着对资金或库存的严格一致性、记录“谁修改了什么”的审计轨迹，以及随着模式演进仍能稳定运行的报告查询。

运维现实与功能同等重要。决定你是使用托管数据库还是自托管、从备份中恢复需要多快，以及你能接受多长的维护窗口。

最后，用几个明确的目标定义“足够快”。例如：正常负载下 p95 API 延迟（200 到 400 ms），高并发下 p95（可能是正常的 2 倍），更新时最大可接受锁等待（低于 100 ms），以及备份和恢复的时间上限。

决定 CRUD 速度的索引基础

大多数 CRUD 应用在表达到数百万行、每个界面都变成“带筛选和排序的列表”之前都感觉很快。那时索引就是 50 ms 查询与 5 秒超时之间的差别。

B-tree 索引是 PostgreSQL 和 MariaDB 的默认主力。当你按列筛选、按键连接、并且 ORDER BY 与索引顺序匹配时，它们能帮大忙。真正的性能差异通常由选择性（匹配的行数）和索引是否能在不额外扫描的情况下同时满足筛选与排序决定。

随着应用成熟，复合索引比单列索引更重要。一种常见模式是多租户筛选加状态再加时间排序，例如 (tenant_id, status, created_at)。把最稳定的筛选放在最前（通常是 tenant_id），然后是下一个筛选，再放排序列。这通常优于优化器无法高效合并的多个单列索引。

区别会在“更智能”的索引上显现。PostgreSQL 支持部分索引和表达式索引，对聚焦的界面很有用（例如只索引“打开”的工单）。它们很强大，但如果查询没有精确匹配谓词，也会让团队吃惊。

索引不是免费的。每次插入和更新都必须维护每个索引，所以很容易优化一个界面却悄悄降低所有写入的速度。

保持纪律的简单办法：

• 只为真实的查询路径（某个可命名的界面或 API 调用）新增索引。
• 优先使用一个好的复合索引，而不是许多重叠的索引。
• 在功能变更后重新检查索引并移除无用项。
• 计划维护：PostgreSQL 需要定期 vacuum/analyze 以避免膨胀；MariaDB 也依赖良好的统计信息和偶尔的清理。
• 在增删索引前后进行测量，而不是凭直觉决策。

针对真实界面的索引：列表、搜索与分页

大多数 CRUD 应用的主要时间花在几个界面上：带筛选的列表、搜索框和详情页。数据库选择本身不如你的索引是否与这些界面匹配重要，但当表变大时，两种引擎确实会提供不同的工具。

对于列表页面，按这个顺序考虑：先筛选，再排序，最后分页。常见模式是“某账户的所有工单，状态在 (open, pending) 中，按最新排序”。以筛选列开头、以排序列结尾的复合索引通常是最优的。

分页需要特别注意。使用 OFFSET 的分页（例如第 20 页使用 OFFSET 380）随着翻页会变慢，因为数据库仍需跳过前面的行。键集分页更稳定：传入最后看到的值（如 created_at 和 id）并请求“接下来的 20 条早于该值的记录”。这也能在新行插入时减少重复和缺页问题。

PostgreSQL 在列表屏有一个有用选项：使用 INCLUDE 的“覆盖”索引，可以在可见性映射允许时启用 index-only 扫描。MariaDB 也能做覆盖读取，但通常通过把所需列直接放入索引定义中实现，这会让索引更宽且维护成本更高。

如果一个列表端点随着表增长而变慢，但只返回 20 到 50 行，或者排序在不移除 ORDER BY 时变慢，或简单筛选时 I/O 飙升，你可能需要更好的索引。更长的查询也倾向于在繁忙时增加锁等待。

示例：一个按 customer_id 和 status 筛选并按 created_at 排序的订单界面，通常受益于以 (customer_id, status, created_at) 开头的索引。如果后来增加了“按订单号搜索”，那通常是单独的索引，而不是把它强行加入列表索引。

迁移：在数据增长后保持发布安全

迁移很快就不再是“改表”那么简单。一旦有真实用户和历史，你还需要处理数据回填、收紧约束以及在不破坏应用的情况下清理旧数据结构。

一个安全默认做法是 expand、backfill、contract。以不会中断现有代码的方式添加所需项，分小步复制或计算数据，然后在确信无误后移除旧路径。

实践中通常意味着添加一个新的可空列或表，分批回填同时保持写入一致，后来用 NOT NULL、外键和唯一规则等约束进行验证，只有在切换完读写后才删除旧列、索引和代码路径。

并非所有模式变更风险相同。添加列通常风险低。为大表创建索引仍然可能代价高昂，因此要安排在低峰并提前测量。更改列类型通常风险最大，因为可能会重写数据或阻塞写入。常见的更稳妥模式是：创建一个新列（新类型），回填，然后切换读写。

回滚在大规模时也改变含义。回滚模式有时容易；回滚数据通常不易。在迁移包含破坏性删除或有损转换时，要明确哪些可以撤销。

JSON 支持：灵活字段而不致后患无穷

JSON 字段很诱人，因为它们让你更快交付：额外表单字段、集成载荷、用户偏好，以及来自外部系统的备注都可以在不改模式的情况下存放。关键是决定哪些应该放在 JSON，哪些值得做成真实列。

在 PostgreSQL 和 MariaDB 中，当 JSON 很少被筛选而主要用于展示、调试存储、作为每用户或租户的配置块，或用于不会驱动报告的小可选属性时，JSON 往往表现最佳。

索引 JSON 是团队常被惊讶的地方。查询 JSON 键一次很容易，但在大表上对它做筛选和排序时性能可能崩溃。PostgreSQL 在为 JSON 路径建索引方面选项丰富，但仍需自律：只为真正用于筛选的少数键建索引，其余保持未索引的载荷。MariaDB 也能查询 JSON，但复杂的“在 JSON 中搜索”模式往往变得脆弱且难以保持高效。

JSON 也削弱了约束。在非结构化的 blob 中强制“必须是这些值之一”或“必须存在”更困难，且报告工具通常偏好类型化列。

一条可扩展的规则：对未知字段先用 JSON，但当你（1）在其上做筛选或排序、（2）需要约束、或（3）每周在仪表盘中频繁看到它时，就把它规范化为列或子表。把订单的完整配送 API 响应存为 JSON 通常没问题，但像 delivery_status 和 carrier 这样的字段一旦支持与报告依赖它们，通常应该成为真实列。

在成熟应用中会出现的查询特性

早期，大多数 CRUD 应用只用简单的 SELECT、INSERT、UPDATE 和 DELETE。后来，你会加入活动流、审计视图、管理报告和需要即时感的搜索。这时选择开始像功能取舍了。

CTE 和子查询有助于让复杂查询更可读。它们在分步构建结果（筛选订单、连接支付、计算总额）时很有用。但可读性可能掩盖成本。当查询变慢时，你可能需要把 CTE 改写成子查询或连接，并重新检查执行计划。

窗口函数在有人要求“按支出对客户排名”、“显示运行总计”或“每个工单的最新状态”时就显得重要。它们常用来替代繁琐的应用循环并减少查询次数。

幂等写入是成熟阶段的另一项需求。在发生重试（移动网络、后台任务）时，upsert 能让你安全写入而不重复创建记录：

• PostgreSQL: INSERT ... ON CONFLICT
• MariaDB: INSERT ... ON DUPLICATE KEY UPDATE

搜索是悄然袭来的功能。内置全文检索可以覆盖产品目录、知识库和支持记录。类似三元组（trigram）的搜索对联想输入和容错拼写有用。如果搜索成为核心（复杂排序、许多筛选、高流量），使用独立搜索引擎往往值得，即便要增加运维复杂度。

示例：一个订单门户最初只有“列出订单”。一年后它需要“显示每个客户的最新订单、按月支出排名并能通过拼写错误的姓名搜索”。这些是数据库能力的问题，不只是 UI 工作。

在负载下的事务、锁和并发

流量低时，大多数数据库都感觉良好。负载下的差别往往在于你如何处理对相同数据的并发修改，而不是原始速度。PostgreSQL 和 MariaDB 都能运行事务性 CRUD 工作负载，但你仍需为争用设计。

用通俗语言说隔离

事务是一组应当一起成功的步骤。隔离控制在这些步骤运行时其他会话能看到什么。更高的隔离可以避免惊讶的读取，但会增加等待。许多应用从默认隔离开始，仅对真正需要的流程（比如扣款并更新订单）收紧隔离。

导致锁痛的真正原因

CRUD 应用里的锁问题通常来自几个常见原因：被大量更新的热点行、每次操作都变化的计数器、许多工作者争抢同一“下一条工作”的队列，以及在事务内长时间持有锁的长事务。

为减少争用，保持事务短小、仅更新需要的列，并避免在事务内进行网络调用。

一个有帮助的习惯是对冲突进行重试。如果两个支持人员同时保存对同一工单的编辑，不要悄然失败。检测冲突、重新载入最新行，并提示用户重新应用修改。

为尽早发现问题，关注死锁、长事务，以及花在等待而非执行的查询。在发布新增界面或后台任务后，把慢查询日志作为常规工作的一部分。

发布后变得重要的运维工作

上线后，你不再只优化查询速度，而是优化恢复、安全变更和可预测的性能。

一个常见的下一步是增加只读副本。主库负责写，副本可以为仪表盘或报表类的只读页面提供服务。这会改变你对数据新鲜度的考虑：有些读取可以容忍秒级延迟，所以你的应用需要知道哪些界面必须从主库读取（例如“刚下的订单”），哪些可以容忍略旧的数据（例如每周汇总）。

备份只是工作的一半。关键是你是否能快速且正确地恢复。定期在独立环境中进行恢复演练，然后验证基本项：应用能连接、关键表存在、关键查询返回预期结果。团队常常太晚发现备份对象有误，或恢复时间远超可接受的停机预算。

升级也不再是“点一下就完事”。安排维护窗口、阅读兼容性说明，并用生产数据副本测试升级路径。即便是次要版本更新也可能改变查询计划或索引与 JSON 函数的行为。

简单的可观测性投入早期就能带来回报。先从慢查询日志和按总耗时排序的热门查询、连接饱和度、复制延迟（若使用副本）、缓存命中率与 I/O 压力，以及锁等待和死锁事件入手。

如何选择：实用评估流程

如果你陷入纠结，别再读功能列表，用你自己的工作负载做一个小试验。目标不是完美基准，而是避免在表达到数百万行、发布节奏加快时出现意外。

1) 构建一个像生产的小型测试

挑出能代表真实痛点的一小部分：一到两个关键表、几个界面，以及背后的写入路径。收集你的热门查询（列表页、详情页和后台任务背后的那些）。生成现实行数（至少是原型数据的 100 倍，且形状相似）。添加你认为需要的索引，然后用相同的筛选和排序运行相同查询并记录耗时。在有写入发生的同时重复（一个简单的插入和更新脚本就足够）。

一个常见快速示例是“客户”列表，按状态筛选、按姓名搜索、按最后活动排序并分页。单个界面通常能显露出你的索引和优化器行为能否经得起时间考验。

2) 像真实发布那样演练迁移

创建数据集的暂存副本，并练习你知道会发生的变更：添加列、变更类型、回填数据、添加索引。测量所需时间、是否会阻塞写入，以及当数据已经变化时真正的回滚意味着什么。

3) 使用简单评分卡

测试后，对每个选项在以下方面打分：针对你的真实查询的性能、正确性与安全性（约束、事务、边缘情况）、迁移风险（锁、停机、恢复选项）、运维工作量（备份/恢复、复制、监控）以及团队熟练度。

选择能降低未来 12 个月风险的数据库，而不是在一次微测中获胜的那个。

常见错误和陷阱

最昂贵的数据库问题通常起源于“快速解决”。两种数据库都能运行事务性 CRUD 应用，但错误的习惯会在流量和数据增长后伤害任一方。

常见陷阱是把 JSON 当作一切的捷径。把“extras”字段留作灵活字段没有问题，但核心字段如状态、时间戳和外键应保持为真实列。否则你会遇到慢筛选、尴尬的验证和当报告成为优先级时痛苦的重构。

索引也有陷阱：为界面上看到的每个筛选添加索引。索引加速读但会减慢写并增加迁移负担。只为用户真正使用的做索引，并在负载下验证效果。

迁移会在锁表时咬你。像重写大列、添加带默认值的 NOT NULL，或创建大索引等一次性大改可能会阻塞写入数分钟。把有风险的更改拆分成多步，安排在应用安静时执行。

另外，不要永远依赖 ORM 的默认配置。当列表视图从 1,000 行变成 1,000 万行时，你需要读执行计划、发现缺失索引并修复慢连接。

快速预警信号：把主要筛选和排序放在 JSON 字段上、索引数量在没有测量的情况下不断增加、在一次部署中重写大表的迁移，以及没有稳定排序的分页（导致缺失或重复行）。

在做决定前的快速清单

在选边前，基于你最繁忙的界面和发布流程做个快速现实检查：

• 你最重要的界面在峰值负载下还能保持快速吗？用真实筛选、排序和分页测试最慢的列表页，确认索引与这些精确查询匹配。
• 你能安全地发布模式变更吗？为下一个重大变更写下 expand-backfill-contract 的计划。
• 你是否有清晰的 JSON 与列使用规则？决定哪些 JSON 键必须可搜索或可排序，哪些是真正的灵活项。
• 你是否依赖特定查询特性？检查 upsert 行为、窗口函数、CTE 行为，以及是否需要函数或部分索引。
• 上线后你能运维它吗？证明你能从备份恢复、测量慢查询并基线延迟与锁等待。

示例：从简单订单跟踪到繁忙的客户门户

想象一个客户门户最初很简单：客户登录、查看订单、下载发票和提交支持工单。第一周，几乎任何事务性数据库都足够。页面加载快，模式很小。

几个月后，增长时刻出现了。客户要求筛选“近 30 天发货、用卡支付、有部分退款的订单”。支持需要快速导出 CSV 进行每周审核。财务需要审计轨迹：谁在什么时候把发票状态从什么改到什么。查询模式比最初更宽、更复杂。

这时决策转向具体特性以及它们在真实负载下的表现。

如果你添加了灵活字段（配送说明、自定义属性、工单元数据），JSON 支持很重要，因为你最终会想在这些字段内查询。诚实评估你的团队是否会为 JSON 路径建索引、验证结构并在 JSON 增长时保持性能可预测。

报告是另一个压力点。一旦你把订单、发票、支付和工单大量连接并加上许多筛选，你会关心复合索引、查询规划，以及在不停机的情况下进化索引的难易。迁移也不再是“周五跑个脚本”，而成为每次发布的一部分，因为一次小的模式变更可能触及数百万行。

一个务实的前进方式是：写下五个你预期在六个月内需要的真实界面和导出，及早加入审计历史表，用你最慢的查询在现实数据规模上做基准测试（而不是一个 Hello World CRUD），并记录团队在 JSON 使用、索引和迁移上的规则。

如果你想在不手工构建每一层的情况下快速前进，AppMaster (appmaster.io) 可以从可视化模型生成可投入生产的后端、Web 应用和原生移动应用。它也会提醒你把屏幕、筛选和业务流程视为真实的查询工作负载，帮助在进入生产前发现索引和迁移风险。