AI 时代重学 PostgreSQL 实战:从 OFFSET 到 Keyset Pagination

后端写列表接口，最常见的参数是：

page=1&page_size=20

然后 SQL 很自然：

SELECT *FROM postsORDER BY created_at DESCLIMIT 20 OFFSET 0;

第一页没问题。

第十页也没问题。

到第一千页，问题就来了：

LIMIT 20 OFFSET 20000

很多人对 OFFSET 的理解是：“数据库直接跳到第 20001 条，然后取 20 条。”

但数据库不是这么工作的。

更接近真实情况的是：它要按排序顺序找到前 20020 条，然后丢掉前 20000 条，把最后 20 条返回给你。

这就是 OFFSET 越翻越慢的根本原因。

完整代码在 postgres-keyset-pagination-lab^[1]。这篇文章里的代码都来自这个仓库。

先别急着骂数据库。

OFFSET 的语义本来就是“跳过多少行”。

SELECT id, title, created_atFROM keyset_lab_postsWHERE status = 'published'ORDER BY created_at DESC, id DESCLIMIT 20 OFFSET 20000;

即使有索引，PostgreSQL 也不能凭空知道“第 20001 条”在哪里。它仍然要沿着排序结果往后走，跳过前面的 20000 行。

有索引时，它可能走索引扫描。

没索引时，它可能排序后再跳。

但不管执行计划怎么变，OFFSET N 的语义决定了它必须处理被跳过的那一段。

所以 OFFSET 的成本会随着页码变大而变大。

这不是 bug，是语义。

先建一张实验表

实验里只有一张 posts 表：

CREATE TABLE keyset_lab_posts (    id         BIGSERIAL PRIMARY KEY,    title      TEXT NOT NULL,    status     TEXT NOT NULL,    created_at TIMESTAMPTZ NOT NULL);

索引很关键：

CREATE INDEX keyset_lab_posts_created_id_idxON keyset_lab_posts (created_at DESC, id DESC);

这里故意用 (created_at, id) 两个字段。

如果只用 created_at 排序，会有一个问题：同一秒内可能有多条记录。排序不唯一，分页边界就不稳定。

所以我们加上 id 作为 tie-breaker：

ORDER BY created_at DESC, id DESC

这句话的意思是：

• • 新的排前面。
• • created_at 一样时，id 大的排前面。

分页必须有稳定顺序。没有稳定顺序，就不要谈稳定分页。

OFFSET 版本怎么写

代码很普通：

func ListOffset(ctx context.Context, pool *pgxpool.Pool, limit int, offset int) (Page, error) {    if limit <= 0 {        limit = 20    }    if offset < 0 {        offset = 0    }    start := time.Now()    rows, err := pool.Query(ctx, `SELECT id, title, created_atFROM keyset_lab_postsWHERE status = &#x27;published&#x27;ORDER BY created_at DESC, id DESCLIMIT $1 OFFSET $2`, limit, offset)    if err != nil {        return Page{}, fmt.Errorf("list offset: %w", err)    }    defer rows.Close()    items, err := scanPosts(rows)    if err != nil {        return Page{}, err    }    return Page{Items: items, Elapsed: time.Since(start)}, nil}

运行：

go run ./cmd/offset

输出类似：

mode=offset limit=20 offset=20000 rows=20 elapsed=3.671084msfirst_id=20001 last_id=20020

本地 30000 条数据，几毫秒还不吓人。

但这个实验重点不是“几毫秒”，而是查询形状：offset 越大，数据库要跳过的行越多。

如果是几百万、几千万行，或者列表查询还带复杂过滤、join、权限条件，这个成本会变得很明显。

OFFSET 还有一个稳定性问题

性能只是一个问题。

OFFSET 还有一个更容易被忽略的问题：列表会漂。

假设现在第一页是：

1, 2, 3, 4, 5

用户看完第一页，准备请求第二页：

OFFSET 5 LIMIT 5

就在这中间，有人插入了一条新数据，排到了最前面：

new, 1, 2, 3, 4, 5

这时 OFFSET 5 跳过的是：

new, 1, 2, 3, 4

第二页第一条变成 5。

用户会重复看到 5。

删除也类似，可能漏数据。

所以 OFFSET 不只是“慢”，它还不适合高频变化的信息流、推荐流、消息列表、订单流水这类场景。

Keyset 的思路

Keyset pagination 不问“第几页”。

它问的是：

上一页最后一条是谁？从它后面继续取。

比如第一页最后一条是：

created_at = 2026-05-17 10:00:00id = 100

下一页就查：

SELECT id, title, created_atFROM keyset_lab_postsWHERE status = &#x27;published&#x27;  AND (created_at, id) < ($1, $2)ORDER BY created_at DESC, id DESCLIMIT 20;

这里的 (created_at, id) < ($1, $2) 是 PostgreSQL 的行构造比较。

因为我们的排序是：

ORDER BY created_at DESC, id DESC

所以“下一页”就是：

• • created_at 更早。
• • 或者 created_at 相同但 id 更小。

这个条件正好能用复合索引：

CREATE INDEX keyset_lab_posts_created_id_idxON keyset_lab_posts (created_at DESC, id DESC);

Keyset 版本怎么写

先定义返回结构：

type Post struct {    ID        int64    Title     string    CreatedAt time.Time}type Page struct {    Items      []Post    NextCursor *Cursor    Elapsed    time.Duration}

核心查询：

func ListKeyset(ctx context.Context, pool *pgxpool.Pool, limit int, cursor *Cursor) (Page, error) {    if limit <= 0 {        limit = 20    }    start := time.Now()    var (        rows pgx.Rows        err  error    )    if cursor == nil {        rows, err = pool.Query(ctx, `SELECT id, title, created_atFROM keyset_lab_postsWHERE status = &#x27;published&#x27;ORDER BY created_at DESC, id DESCLIMIT $1`, limit)    } else {        rows, err = pool.Query(ctx, `SELECT id, title, created_atFROM keyset_lab_postsWHERE status = &#x27;published&#x27;  AND (created_at, id) < ($1, $2)ORDER BY created_at DESC, id DESCLIMIT $3`, cursor.CreatedAt, cursor.ID, limit)    }    if err != nil {        return Page{}, fmt.Errorf("list keyset: %w", err)    }    defer rows.Close()    items, err := scanPosts(rows)    if err != nil {        return Page{}, err    }    var next *Cursor    if len(items) > 0 {        last := items[len(items)-1]        next = &Cursor{CreatedAt: last.CreatedAt, ID: last.ID}    }    return Page{Items: items, NextCursor: next, Elapsed: time.Since(start)}, nil}

第一页没有 cursor。

从第二页开始，带上上一页最后一条记录的 (created_at, id)。

这就是 keyset 的核心。

cursor 不能直接暴露成两个参数吗

当然可以。

比如：

?created_at=2026-05-17T10:00:00Z&id=100

但 API 一般会包装成一个 cursor 字符串。

实验代码里用 JSON + base64：

type Cursor struct {    CreatedAt time.Time `json:"created_at"`    ID        int64     `json:"id"`}func EncodeCursor(cursor Cursor) (string, error) {    payload, err := json.Marshal(cursor)    if err != nil {        return "", fmt.Errorf("marshal cursor: %w", err)    }    return base64.RawURLEncoding.EncodeToString(payload), nil}func DecodeCursor(raw string) (Cursor, error) {    payload, err := base64.RawURLEncoding.DecodeString(raw)    if err != nil {        return Cursor{}, fmt.Errorf("decode cursor: %w", err)    }    var cursor Cursor    if err := json.Unmarshal(payload, &cursor); err != nil {        return Cursor{}, fmt.Errorf("unmarshal cursor: %w", err)    }    if cursor.ID <= 0 || cursor.CreatedAt.IsZero() {        return Cursor{}, fmt.Errorf("invalid cursor")    }    return cursor, nil}

注意：base64 不是加密。

它只是让 cursor 更适合放进 URL。

如果你不希望客户端篡改 cursor，可以加签名：

base64(json_payload) + "." + hmac_sha256(payload, secret)

但大多数后台列表场景，先把 cursor 解析和参数校验做好就够了。

跑一次 keyset

运行：

go run ./cmd/keyset

输出类似：

first_page rows=10 first_id=1 last_id=10 elapsed=1.456583msnext_cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0xN1QxMTo0NToyMS43OTg1OTkrMDg6MDAiLCJpZCI6MTB9second_page rows=10 first_id=11 last_id=20 elapsed=814.666µs

你不用关心 cursor 里面具体长什么样。

客户端只要把 next_cursor 原样带回来：

GET /posts?limit=10&cursor=eyJjcmVhdGVkX2F0...

服务端 decode 之后继续查下一页。

OFFSET 和 Keyset 对比

为了让对比更直观，实验里有一个 compare 命令。

它先用 OFFSET 查第 20001 条开始的 20 条：

const depth = 20000offsetPage, err := pagination.ListOffset(ctx, pool, 20, depth)if err != nil {    log.Fatal(err)}

然后构造同一位置的 cursor：

cursor, err := pagination.CursorAtOffset(ctx, pool, depth-1)if err != nil {    log.Fatal(err)}keysetPage, err := pagination.ListKeyset(ctx, pool, 20, &cursor)if err != nil {    log.Fatal(err)}

这里 CursorAtOffset 只是为了实验对齐，不是生产写法。生产环境里 cursor 来自上一页最后一条。

运行：

go run ./cmd/compare

输出类似：

offset rows=20 elapsed=3.651375ms first_id=20001 last_id=20020keyset rows=20 elapsed=1.381167ms first_id=20001 last_id=20020

本地数据量小，差距不会特别夸张。

但查询形状已经不一样了。

OFFSET 是：

ORDER BY created_at DESC, id DESCLIMIT 20 OFFSET 20000

Keyset 是：

WHERE (created_at, id) < ($1, $2)ORDER BY created_at DESC, id DESCLIMIT 20

前者要跳过一段。

后者从边界继续往后扫。

Keyset 的几个硬规则

Keyset 不是把 OFFSET 改成 WHERE id < ? 就完了。

我会按下面几条检查。

排序必须稳定。

不要只写：

ORDER BY created_at DESC

要写：

ORDER BY created_at DESC, id DESC

如果排序字段可能重复，就必须加唯一字段兜底。

cursor 字段必须和 ORDER BY 对齐。

排序是 (created_at DESC, id DESC)，cursor 也必须带 created_at 和 id。

如果排序是 (score DESC, created_at DESC, id DESC)，cursor 就要带这三个字段。

不要 ORDER BY 三个字段，cursor 只存一个字段。

比较方向要和排序方向一致。

DESC 下一页通常用 <：

AND (created_at, id) < ($1, $2)

ASC 下一页通常用 >：

AND (created_at, id) > ($1, $2)

如果有混合排序，比如 score DESC, id ASC，就不要偷懒用行比较，老老实实写展开条件。

cursor 字段最好 NOT NULL。

行比较遇到 NULL 会变麻烦。

分页字段如果允许 NULL，你要明确 NULL 排前还是排后，并写清楚条件。大多数业务列表里，created_at、id 这种字段就应该是 NOT NULL。

Keyset 的代价

Keyset 不是银弹。

它有几个代价必须提前接受。

第一，它不适合随机跳页。

如果产品强需求是“跳到第 100 页”，OFFSET 更直接。Keyset 的思路是“从当前页继续往后翻”。

所以它更适合：

• • feed 流。
• • 消息列表。
• • 订单流水。
• • 操作日志。
• • 搜索结果的下一页。
• • 后台无限滚动列表。

不太适合：

• • 明确要显示第 1/2/3/4 页的传统分页器。
• • 要快速跳到任意页码的报表。

第二，总数不天然便宜。

很多接口喜欢返回：

{  "total": 1234567,  "items": []}

大表上每次都 COUNT(*)，也可能很贵。

Keyset 通常返回的是：

{  "items": [],  "next_cursor": "..."}

如果业务一定要 total，可以异步算、缓存、估算，或者只在筛选条件很小的时候算。

第三，排序条件变了，cursor 就失效。

用户第一页按“最新”排序，第二页切成“最热”，旧 cursor 不能继续用。

所以 cursor 最好绑定查询条件：

filter_hash + sort_key + cursor_payload

至少服务端要知道这个 cursor 是在哪个排序和筛选条件下生成的。

真实接口我会怎么返回

一个比较干净的响应结构是：

{  "items": [    {      "id": 1,      "title": "post-1",      "created_at": "2026-05-17T10:00:00Z"    }  ],  "next_cursor": "eyJjcmVhdGVkX2F0Ijoi..."}

请求下一页：

GET /posts?limit=20&cursor=eyJjcmVhdGVkX2F0Ijoi...

如果已经到末页，可以返回：

{  "items": [    {      "id": 100,      "title": "post-100",      "created_at": "2026-05-17T09:00:00Z"    }  ],  "next_cursor":null}

实验代码里只要 len(items) > 0 就生成 cursor：

var next *Cursorif len(items) > 0 {    last := items[len(items)-1]    next = &Cursor{CreatedAt: last.CreatedAt, ID: last.ID}}

生产里我更常用 limit + 1：

LIMIT page_size + 1

如果多取到一条，说明还有下一页。

返回时丢掉多出来的那一条，并用当前返回列表最后一条生成 cursor。

这样 next_cursor 才能准确表达“还有下一页”。

和上一篇 Outbox 的共同点

表面看，Outbox 和 Keyset 没关系。

一个是可靠事件投递。

一个是分页性能。

但它们背后有同一个思路：不要用看起来简单的 API 掩盖真实语义。

db.Commit(); mq.Publish() 看起来简单，但中间有崩溃窗口。

LIMIT 20 OFFSET 20000 看起来简单，但数据库要跳过 20000 行，列表还会因为插入删除漂移。

后端工程很多坑不是“不会写语法”，而是没有把语义想完。

最后给一个选择标准

我一般这么判断：

后台低频管理页，页数不深。

用 OFFSET 可以。它简单，产品也容易理解。

用户端信息流、订单流水、消息列表。

优先 Keyset。用户通常只需要继续往下翻，不需要跳第 100 页。

数据变化频繁。

优先 Keyset。它能减少重复和漏读。

排序字段不稳定，或者业务必须随机跳页。

别硬套 Keyset。先把产品交互和查询语义讲清楚。

OFFSET 不是错。

错的是明明在做无限滚动、深翻页和高频变化列表，却还把 page/page_size 当成唯一方案。

分页不是两个参数。

分页是排序、边界和一致性的设计。

引用链接

[1] postgres-keyset-pagination-lab: https://github.com/arixbit/postgres-keyset-pagination-lab