支持任意页选择的高性能Postgres分页方案求助

问题背景 #

我的PostgreSQL数据库数据量越来越大，一开始用LIMIT/OFFSET做分页，但偏移量较大时，页面加载速度明显变慢。

快慢查询对比 #

• 加载快的查询：

SELECT * FROM users ORDER BY id DESC LIMIT 100 OFFSET 0

• 加载慢的查询：

SELECT * FROM users ORDER BY id DESC LIMIT 100 OFFSET 10000

当前查询生成逻辑 #

SELECT * FROM users ORDER BY id DESC LIMIT ${perPage} OFFSET ${(page - 1) * perPage}

注意：id字段已建立索引。

我清楚慢查询的原因：OFFSET会扫描所有偏移量之前的记录才能定位到目标位置，数据量越大，开销越高。

需求约束 #

我试过键集分页（keyset pagination）这类方案，但它需要依赖上一次查询的位置，无法直接跳转到指定页面——支持直接加载特定页面是硬性要求。

我尝试过游标方案，但修改查询时总是出现语法错误，也不确定游标是否需要先执行一次查询再基于它操作。

核心诉求：找一种简单高效的方法，能根据page和perPage参数从大数据量表中获取数据子集，且具备良好的扩展性。

可行优化方案 #

1. 预计算分页边界（适合低更新频率场景） #

如果表的数据更新不频繁，可以提前计算每个页面对应的id范围，存到专门的分页边界表中：

-- 创建分页边界表
CREATE TABLE user_page_boundaries (
    page_num INT PRIMARY KEY,
    min_id BIGINT,
    max_id BIGINT,
    total_rows INT
);

-- 定期刷新边界数据（例如每天凌晨执行）
TRUNCATE user_page_boundaries;
INSERT INTO user_page_boundaries
SELECT 
    CEIL((ROW_NUMBER() OVER (ORDER BY id DESC))::FLOAT / 100) AS page_num,
    MIN(id) AS min_id,
    MAX(id) AS max_id,
    COUNT(*) AS total_rows
FROM users
GROUP BY page_num;

之后查询指定页面时，先从边界表拿到对应id范围，再用范围查询：

SELECT * FROM users 
WHERE id BETWEEN (SELECT min_id FROM user_page_boundaries WHERE page_num = ${page}) 
              AND (SELECT max_id FROM user_page_boundaries WHERE page_num = ${page})
ORDER BY id DESC LIMIT ${perPage};

这种方式直接利用id索引定位，速度极快，但需要注意数据更新后要同步刷新边界表。

2. 利用索引计算偏移（通用高效方案） #

通过子查询先定位到目标页的起始id，再用范围条件过滤，避免全表扫描：

SELECT * FROM users
WHERE id <= (
    SELECT id FROM users ORDER BY id DESC OFFSET ${(page - 1) * perPage} LIMIT 1
)
ORDER BY id DESC LIMIT ${perPage};

子查询会通过id索引直接跳到目标偏移对应的id值，主查询用id <= X的范围条件走索引扫描，比直接用大OFFSET的查询效率提升明显。

3. 游标方案的正确用法 #

如果要用游标，需要遵循会话级的操作流程：

-- 声明游标（必须指定与查询一致的ORDER BY）
DECLARE user_cursor CURSOR FOR SELECT * FROM users ORDER BY id DESC;

-- 移动游标到目标位置（跳过前面的(page-1)*perPage条记录）
MOVE FORWARD ${(page - 1) * perPage} IN user_cursor;

-- 获取当前页的perPage条数据
FETCH NEXT ${perPage} FROM user_cursor;

-- 关闭游标
CLOSE user_cursor;

但游标是会话绑定的，适合长连接后端服务场景，Web应用这类短连接场景不推荐使用。

方案选择建议 #

• 数据更新频率低：优先选预计算分页边界，性能最优，代码实现简单。
• 数据更新频繁：选利用索引计算偏移的写法，无需额外维护表，扩展性好。
• 长连接服务场景：可以考虑游标方案，但短连接场景不适用。

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