分页查询几乎是所有业务系统都绕不开的功能，但在PostgreSQL中，很多人一上来就写LIMIT 20 OFFSET 1000000，结果页面越翻越慢，最后直接超时。问题的根源在于OFFSET的工作方式：数据库必须真实地取出并丢弃前一百万行，才能返回你要的那20行。游标CURSOR正是针对这个问题的解决方案之一，它让数据库记住结果集的读取位置，每次只取需要的行数，从头到尾每个数据块只读一次。
一、游标CURSOR的基本概念与声明语法
游标本质上是在服务端维护的一个结果集指针。执行DECLARE ... CURSOR语句时，PostgreSQL并不会立即执行查询，而是在第一次FETCH时才开始真正执行，之后每次FETCH都会从上一次停止的位置继续读取，直到结果集耗尽。这和客户端把全部结果一次性拉到本地再切片的处理方式完全不同。
基本用法如下：
BEGIN; -- 声明一个游标，查询语句本身可以任意复杂 DECLARE my_cursor CURSOR FOR SELECT id, name, created_at FROM orders WHERE status = 'paid' ORDER BY id; -- 每次取20条，相当于第一页 FETCH 20 IN my_cursor; -- 再取20条，相当于第二页，注意这里不需要OFFSET FETCH 20 IN my_cursor; -- 取完了就关闭游标 CLOSE my_cursor; COMMIT;
有几个关键点需要注意。第一，普通游标必须在事务内使用，事务一旦COMMIT或ROLLBACK，游标就会自动销毁，这就是为什么上面要显式写BEGIN。第二，游标是向前推进的，标准用法中不能回退，想回到第一页就得重新声明游标。第三，游标支持只读声明，写成DECLARE my_cursor CURSOR FOR SELECT ... FOR UPDATE则可以修改当前行，分页场景下一般用不到。
如果想跨事务使用游标，可以加上WITH HOLD选项：
BEGIN; DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT id, name FROM big_table ORDER BY id; COMMIT; -- 事务提交后依然可以继续FETCH FETCH 20 IN hold_cursor; -- 用完记得手动关闭，否则会一直占用资源 CLOSE hold_cursor;
但要注意，WITH HOLD在事务提交时，PostgreSQL会把剩余未读取的结果物化到临时存储中，如果结果集非常庞大，这个物化成本可能很高，甚至撑爆磁盘。所以它适合中等规模的结果集，不适合无限制的全表遍历。
二、LIMIT加OFFSET为什么会越来越慢
理解游标的优势，得先弄清楚传统分页的性能陷阱。执行SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000时，PostgreSQL的执行流程是：按id顺序扫描索引，取出前1000020行，丢弃前1000000行，返回剩下的20行。偏移量越大，丢弃的行越多，扫描时间线性增长。
做一个简单的对比测试就能感受到差距。假设orders表有500万行，取第一页（OFFSET 0）通常几毫秒就返回，而取第50000页（OFFSET 1000000）可能需要几百毫秒甚至更久，具体取决于行宽和缓存状况。更糟的是，这种慢查询往往出现在用户耐心翻页的深度位置，恰好是系统负载较高的时刻，容易形成连锁反应。
-- 传统分页：偏移量越大越慢 EXPLAIN ANALYZE SELECT id, name FROM orders ORDER BY id LIMIT 20 OFFSET 1000000; -- 游标分页：每次FETCH耗时基本恒定 BEGIN; DECLARE page_cursor CURSOR FOR SELECT id, name FROM orders ORDER BY id; FETCH 20 IN page_cursor; -- 第1页 FETCH 20 IN page_cursor; -- 第2页，速度与第1页几乎一致 COMMIT;
游标方案的耗时曲线是平的，因为每次FETCH都从上次的位置继续，没有丢弃行的开销。不过它也有代价：事务需要长时间保持打开状态，期间持有的锁和快照不会释放，MVCC层面的旧版本数据无法清理，长事务本身就是一种风险。
三、连接池环境下的游标使用限制
游标在实际生产中最常见的障碍是连接池。应用通常通过PgBouncer这类连接池中间件访问数据库，而PgBouncer默认工作在事务池模式（transaction pooling）下，连接在事务结束时会被归还给池子复用。游标依赖同一个后端连接的会话状态，一旦连接被换走，游标就失效了，客户端会收到游标不存在的错误。
应对方式有几种。第一种是让游标相关的请求走专门的直连通道，绕过连接池，代价是占用真实连接数。第二种是使用事务级游标，保证声明、FETCH、关闭全部在同一个事务内完成，并且确认连接池在事务期间不会切换后端连接。第三种是PostgreSQL 11之后支持的DECLARE ... CURSOR WITH HOLD配合谨慎的资源评估，但如前所述要小心物化成本。
另外还有一个客户端参数值得了解：JDBC驱动默认会把FETCH结果集分批拉取，其内部机制正是基于游标实现的，需要设置autoCommit=false并控制fetchSize才能生效。psycopg2中则对应server_side_cursor的用法和itersize参数。理解这些驱动的内部行为，有助于排查游标不生效的问题。
四、键集分页：更通用的深度分页方案
游标适合顺序翻页的场景，比如数据导出、后台任务遍历。但如果用户需要随机跳转到某一页，或者希望分页请求之间无状态，键集分页是更好的选择。它的思路是记住上一页最后一条记录的排序键，下一页查询时用WHERE条件直接定位：
-- 第一页 SELECT id, name FROM orders ORDER BY id DESC LIMIT 20; -- 假设上一页最后一条的id是987654，取下一页 SELECT id, name FROM orders WHERE id < 987654 ORDER BY id DESC LIMIT 20;
这样无论翻到第几页，数据库都只需要从id等于987654的位置开始扫描索引，读取20行就结束，耗时恒定且不需要维持任何会话状态，天然兼容连接池和RESTful接口的无状态要求。如果排序键不是唯一列，需要组合一个唯一列保证定位准确，例如WHERE (created_at, id) < (上一页的created_at, 上一页的id) ORDER BY created_at DESC, id DESC，PostgreSQL支持行值比较语法，可以走索引。
总结一下选型建议：后台导出和流式处理用游标，配合事务控制好生命周期；面向用户的Web分页优先考虑键集分页；只有数据量小、翻页深度浅的场景才适合简单的LIMIT加OFFSET。把这三者的适用边界想清楚，深度分页带来的性能问题基本都能化解。
PostgreSQL游标CURSOR分页OFFSET性能优化修改时间：2026-09-07 07:46:36