PostgreSQL中基于ID的Keyset分页：优先返回非空private_key记录

你的核心问题是原来的Keyset分页仅基于id过滤，添加ORDER BY后仅对当前页的50条数据生效，无法实现全局范围内优先返回private_key非空的记录。要解决这个问题，需要调整Keyset分页的依据，结合private_key的非空优先级构建排序和过滤条件。

核心思路 #

不再单独用id作为分页唯一依据，而是把**private_key是否非空作为第一排序维度**（非空记录排前面），id作为第二维度（保证顺序稳定）。然后基于这个排序组合构建Keyset过滤条件，确保分页是全局排序后的连续数据。

具体实现 #

1. 第一次查询（无分页偏移时）

直接按优先级排序，取前50条：

SELECT id, name, private_key
FROM users
ORDER BY private_key IS NOT NULL DESC, id ASC
LIMIT 50;

执行后，记录结果集中最后一条数据的两个值：

• private_key IS NOT NULL的布尔结果（记为last_is_non_null，值为true或false）
• 对应的id（记为last_id）

2. 后续分页查询

使用PostgreSQL支持的元组比较语法，基于上一页的最后一条记录构建过滤条件，确保拿到全局排序后的下一批50条数据：

SELECT id, name, private_key
FROM users
WHERE (private_key IS NOT NULL, id) > ('{last_is_non_null}', {last_id})
ORDER BY private_key IS NOT NULL DESC, id ASC
LIMIT 50;

• 元组比较逻辑：PostgreSQL会先比较第一个元素（private_key IS NOT NULL），非空的true优先级高于空的false。当非空记录全部取完后，才会开始取private_key为空的记录，完全符合需求。
• 替换占位符：把{last_is_non_null}换成上一页最后一条的布尔值（比如true），{last_id}换成对应的id数值（比如100）。

性能优化：创建复合表达式索引 #

为了让分页查询高效执行，需要创建匹配排序维度的索引，避免全表扫描：

CREATE INDEX idx_users_priority_id ON users ((private_key IS NOT NULL) DESC, id ASC);

这个索引直接包含了排序所需的表达式结果和id，PostgreSQL可以直接用它快速定位分页位置，大幅提升查询速度。

为什么原来的方式不行？ #

你之前的查询是先通过id > [last_id]过滤出50条数据，再执行ORDER BY——这相当于只对当前页的50条数据排序，而非全局先排序再分页。调整后的方式是先按全局优先级排序，再基于排序后的位置做Keyset分页，确保每一页都是全局排序后的连续数据。

内容的提问来源于stack exchange，提问作者Sam Leurs