【数据库】PostgreSQL 分页查询中的隐式陷阱：不稳定的排序导致数据丢失与重复

几乎每一个 Web 应用都需要分页——列表页、管理后台、API 接口，无不依赖 LIMIT/OFFSET。然而 PostgreSQL 官方文档对此有一条容易被忽视的警告：

If LIMIT is used without ORDER BY, the rows to be returned are unpredictable. If ORDER BY is present, it determines the order in which rows are returned — but not which rows. If two rows have identical values for all ORDER BY columns, their relative order is implementation-defined.

翻译：即使你写了 ORDER BY，只要排序列上存在重复值，相同查询返回的行顺序就不确定。而 LIMIT/OFFSET 依赖的正是"确定的顺序"——一旦顺序摇摆，翻页就会出现数据丢失与重复。

这不是理论推演。Google SRE 手册和 Stripe 工程博客都将"分页排序不稳定"列为分布式系统中常见的、且极难排查的 Bug 类型。本文将系统梳理问题的根因、复现方式、影响范围以及工程上的解决方案。

问题复现

场景构造

假设有一张订单表：

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    amount      NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_created ON orders (created_at);

一个典型的分页查询：

SELECT id, user_id, status, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

问题何时触发

在单次查询中结果看起来完全正常。问题出在以下几种情况：

1. 并发写入：第 1 页和第 2 页之间有新订单插入，导致后续页面的行发生位移
2. 表膨胀与 vacuum：PostgreSQL 的 MVCC 机制下，dead tuple 的清理可能改变物理扫描顺序
3. 并行查询：PostgreSQL 的并行执行计划会将数据分配给多个 worker，合并时的顺序不稳定
4. 索引重建：REINDEX 操作会改变相同 key 值在索引中的物理排列

具体表现

第 1 页: [A, B, C, D, E, F, G, H, I, J]
第 2 页: [D, E, F, G, H, I, J, K, L, M]  -- D-J 重复出现

或更隐蔽的形式：

第 1 页: [A, B, C, D, E, F, G, H, I, J]
第 2 页: [K, L, M, N, O, P, Q, R, S, T]  -- 中间有数据被跳过

根因分析

PostgreSQL 不保证排序稳定性

数据库的排序稳定性（sort stability）指的是：当两行的排序键值相同时，它们之间的相对顺序是否与输入（或上一次查询）保持一致。

PostgreSQL 使用不稳定的排序算法（通常基于 quicksort 的变体）。原因很直接：稳定排序需要额外内存来保留原始顺序信息，对于可能涉及数百万行的数据库操作，这个开销不值得。

当 created_at 相同时（常见于批量写入场景），PostgreSQL 对这些行的排列顺序由以下因素决定，且这些因素在查询之间可能变化：

• 磁盘上 tuple 的物理位置（CTID）
• 是否启用并行查询以及 worker 的分配方式
• 执行计划中是否走了索引扫描 vs 顺序扫描
• Buffer pool 的缓存状态

MVCC 加剧了不确定性

PostgreSQL 的 MVCC 机制意味着同一时刻可能存在多个版本的同一行数据。当事务隔离级别为 READ COMMITTED（默认值）时，每次语句都能看到已提交的最新数据。这导致：

• 第 1 页查询时某些行还没提交，第 2 页查询时已经提交并被纳入结果
• vacuum 回收空间后，新插入的行可能占据之前被回收的物理位置，改变扫描顺序

分布式场景下的放大效应

在微服务架构中，分页查询的结果通常会被缓存或传递给下游服务。如果排序不稳定：

• 前端展示异常：用户翻页时看到重复数据或遗漏数据，严重损害信任度
• 数据同步不一致：下游消费者基于分页做全量同步，会漏数据或产生重复
• API 分页合约失效：REST API 承诺的分页语义在客户端侧表现为不可靠

这也是为什么 Google SRE 手册将其归类为系统性可靠性风险，而非简单的"排序问题"。

影响范围评估

影响维度	严重程度	说明
数据一致性	高	导出/同步场景下会产生重复或丢失
用户体验	中	翻页看到重复或跳跃，影响信任
排查难度	高	问题间歇性出现，难以在测试环境复现
并发环境	极高	写入越频繁，问题越明显

解决方案

方案一：ORDER BY 追加唯一列（推荐）

最直接也最可靠的修复方式：在 ORDER BY 中追加一个唯一列，通常是主键。

-- 修复前
SELECT id, user_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- 修复后
SELECT id, user_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 0;

原理：id 是主键，全局唯一，因此 (created_at, id) 的组合不可能重复，排序结果完全确定。

性能考量：如果已有 (created_at DESC) 的索引，追加 id 排序 PostgreSQL 仍然可以走该索引，因为主键本身就是行的物理标识，追加排序的开销极小。如果想充分利用索引，可以创建复合索引：

CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

方案二：游标分页（Keyset Pagination）

对于数据量大、深分页频繁的场景，OFFSET 本身就有性能问题（需要扫描并跳过前面的所有行）。游标分页同时解决了性能和稳定性问题：

-- 第 1 页
SELECT id, user_id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- 第 2 页（使用第 1 页最后一条的 created_at 和 id）
SELECT id, user_id, status, created_at
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < ('2025-06-15 10:30:00+08', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 10;

优势：

• O(1) 跳页成本：不需要扫描 offset 数量的行，直接从游标位置开始
• 天然抗并发写入：新插入的数据不会影响已返回的页面
• 排序完全确定：复合条件 (created_at, id) 唯一标识了位置

劣势：

• 不支持随机跳页（只能上一页/下一页）
• 需要后端维护游标状态

方案三：基于 ID 的分页

如果业务允许按主键排序，这是最简单可靠的方式：

-- 第 1 页
SELECT * FROM orders
WHERE status = 'paid'
ORDER BY id DESC
LIMIT 10;

-- 第 2 页
SELECT * FROM orders
WHERE status = 'paid' AND id < [上一页最小id]
ORDER BY id DESC
LIMIT 10;

主键天然唯一，排序永远稳定。但缺点是排序维度受限，无法按业务时间排序。

方案对比

graph TD
    A[分页稳定性方案] --> B[追加唯一列到 ORDER BY]
    A --> C[游标分页 Keyset]
    A --> D[基于 ID 分页]

    B --> B1[改动最小]
    B --> B2[深分页性能差]
    B --> B3[支持随机跳页]

    C --> C1[深分页性能好]
    C --> C2[抗并发写入]
    C --> C3[不支持随机跳页]

    D --> D1[实现最简单]
    D --> D2[排序维度受限]

方案	稳定性	深分页性能	随机跳页	改动成本	抗并发写入
ORDER BY 追加唯一列	确定	差（OFFSET 扫描）	支持	极低	否
游标分页	确定	优	不支持	中	是
基于 ID 分页	确定	优	不支持	低	是

排查与防御清单

排查现有系统中的隐患

-- 查找项目中使用 LIMIT/OFFSET 但 ORDER BY 未包含唯一列的查询
-- 在应用代码中搜索以下模式：
-- 1. LIMIT + ORDER BY 但 ORDER BY 列不包含主键/唯一索引
-- 2. LIMIT + OFFSET 但无 ORDER BY
-- 3. API 接口接受 page/offset 参数但不包含排序稳定化处理

代码审查检查项

• 所有包含 LIMIT 的查询是否都有 ORDER BY？
• ORDER BY 的列组合是否保证唯一性（含主键/唯一列）？
• 分页 API 的排序条件是否在前后端保持一致？
• 是否有基于分页做数据导出/同步的逻辑？

工程规范建议

1. ORM 层拦截：在查询构建层添加 lint 规则，检测不稳定的分页查询
2. Code Review 清单：将"分页排序稳定性"加入 CR checklist
3. 集成测试覆盖：在并发写入场景下验证分页结果的幂等性
4. 监控告警：对导出类任务的结果行数与预期偏差进行监控

总结

LIMIT/OFFSET 看似简单，但在 PostgreSQL 的 MVCC 架构和不稳定排序算法下，缺少唯一排序列的分页查询是一个系统性的可靠性隐患。问题在低并发时几乎不可见，但在高并发写入、大数据量场景下会以数据丢失和重复的形式暴露。

修复成本极低——在 ORDER BY 中追加一个唯一列即可。对于深分页场景，游标分页是更优的选择。关键不在于技术难度，而在于意识到这个问题的存在。