PostgreSQL使用大OFFSET查询返回随机行的问题咨询

核心原因 #

任何不带ORDER BY的SQL查询，返回结果的顺序都是未定义的，这是SQL标准的明确规定，不属于PostgreSQL的异常行为。你观察到的现象完全符合PostgreSQL的底层实现逻辑，具体解释如下：

具体技术细节 #

• PostgreSQL的普通表是堆组织表，没有默认的排序规则，你不加ORDER BY时，查询返回的行顺序就是数据库扫描到行的先后顺序，完全取决于底层的扫描策略。
• 大OFFSET结果随机的原因是触发了**同步扫描（Synchronized Scan）**特性：当表的大小超过阈值（默认是一页以上，也就是至少8KB）时，PostgreSQL为了降低IO开销、提升并发扫描性能，会让多个同时进行的全表扫描共享缓存页。后续启动的扫描不会从表的物理起始位置开始扫，而是直接跟上正在进行的扫描进度，扫完末尾再回头扫开头的部分。你多次执行大OFFSET的全表扫描时，每次扫描的起始点不一样，自然OFFSET拿到的行就不同。
• 小OFFSET结果稳定的原因是：小OFFSET只需要扫描表开头的少量数据，不会触发同步扫描机制，每次都是从表的物理起始位置开始扫描，所以返回结果固定。

解决方案 #

只要在查询时加上明确的ORDER BY子句，就能得到稳定的排序结果，示例如下：

select * from test_table order by id offset 100000 limit 1;

如果需要优化大OFFSET的查询性能，推荐使用keyset分页（也叫游标分页），基于上一页的最后一个主键值过滤，避免全表扫描跳过大量行的开销。

内容的提问来源于stack exchange，提问作者Slava Nikulin