PostgreSQL中UUID v4的Keyset分页查询异常问题求助

问题描述 #

编写Keyset分页查询语句时出现异常：前两次执行能正常获取数据，第三次无返回结果，但表中仍存在有效数据。查询语句如下：

SELECT 
p.id as Id,
p.first_name as FirstName,
p.last_name as LastName,
p.age as Age,
p.created_at as CreatedAt,
p.updated_at as UpdatedAt,
a.address as Address,
a.id as Id,
a.person_id as PersonId,
a.created_at as CreatedAt,
a.updated_at as UpdatedAt
FROM persons p
LEFT JOIN addresses a
ON a.person_id = p.id
WHERE p.id < @searchAfter
ORDER BY p.created_at DESC
LIMIT 1;

表结构及初始化脚本：

CREATE TABLE persons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name CHARACTER varying(200) NOT NULL,
  last_name CHARACTER varying(200) NOT NULL,
  age INTEGER NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT (current_timestamp AT TIME ZONE 'UTC'),
  updated_at TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    person_id UUID,
    address CHARACTER varying(500) NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT (current_timestamp AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT fk_persons_id FOREIGN KEY(person_id) REFERENCES persons(id) ON DELETE CASCADE,
    CONSTRAINT unique_person_id UNIQUE (person_id)
);

INSERT INTO persons (id, first_name, last_name, age, created_at, updated_at)
VALUES 
('298df3d3-9c26-4b1b-a269-4a4a36a7a4b4', 'John', 'Doe', 35, '2023-04-17 12:00:01', NULL),
('f99645e4-14a7-42d4-a539-86a12a37b1e1', 'Jane', 'Smith', 27, '2023-04-17 12:00:20', NULL),
('1a3d7b3e-8b13-49b1-bc31-7a774a30a8a7', 'Bob', 'Johnson', 42, '2023-04-17 12:00:21', NULL),
('4499b79a-c710-45e4-ba87-083d22c4d6ad', 'Alice', 'Williams', 23, '2023-04-17 12:00:25', NULL);


INSERT INTO addresses (id, person_id, address, created_at, updated_at)
VALUES
('df1a0582-8c84-47c1-8441-57c54e9a8767', '298df3d3-9c26-4b1b-a269-4a4a36a7a4b4', '123 Main St, Anytown, USA', '2023-04-17 12:07:00', NULL),
('a46b6f7f-2dc4-4dfe-9a90-0aa1d2eeabf8', 'f99645e4-14a7-42d4-a539-86a12a37b1e1', '456 Oak St, Anycity, USA', '2023-04-17 12:08:00', NULL),
('6d2f6e31-6bf5-4ca5-ae67-13b0595c5f53', '1a3d7b3e-8b13-49b1-bc31-7a774a30a8a7', '789 Elm St, Anystate, USA', '2023-04-17 12:09:00', NULL),
('8b011064-04b4-4f85-a4ad-f7b45e78b6f7', '4499b79a-c710-45e4-ba87-083d22c4d6ad', '456 Pine St, Anytown, USA', '2023-04-17 12:10:00', NULL);

怀疑PostgreSQL对UUID列按字母顺序排序而非字节序进行小于比较，需解决该查询异常。

问题根源分析 #

你的怀疑成立：PostgreSQL中UUID类型的比较默认按字符串（字母）顺序执行，而非底层字节序。但更核心的问题是当前分页逻辑的WHERE筛选字段与ORDER BY排序字段不匹配——你按p.created_at DESC排序，却用p.id < @searchAfter作为分页游标，而created_at的时间顺序和id的字符串顺序没有必然关联，这直接导致分页逻辑混乱，出现数据漏取。

解决思路 #

1. 对齐Keyset分页的筛选与排序字段 #

Keyset分页的核心要求是：筛选条件必须与排序字段完全对应，确保分页游标基于排序后的结果定位。由于created_at可能存在重复，需搭配唯一的id保证排序的唯一性：

SELECT 
p.id as PersonId,
p.first_name as FirstName,
p.last_name as LastName,
p.age as Age,
p.created_at as PersonCreatedAt,
p.updated_at as PersonUpdatedAt,
a.address as Address,
a.id as AddressId,
a.person_id as AddressPersonId,
a.created_at as AddressCreatedAt,
a.updated_at as AddressUpdatedAt
FROM persons p
LEFT JOIN addresses a
ON a.person_id = p.id
WHERE (p.created_at < @lastCreatedAt) 
   OR (p.created_at = @lastCreatedAt AND p.id < @lastId)
ORDER BY p.created_at DESC, p.id DESC
LIMIT 1;

注：修改了原查询中重复的列名，避免结果集字段冲突。

2. 按UUID字节序比较（特定场景使用） #

若确实需要基于UUID的字节序比较，可将UUID转换为bytea类型后操作：

WHERE p.id::bytea < @searchAfter::bytea

需确保参数@searchAfter也同步转换为bytea类型，此方案仅适用于必须依赖UUID字节序的场景，分页场景更推荐第一种方案。

3. 验证UUID比较顺序差异 #

通过以下SQL可直观对比UUID的字符串顺序与字节序差异：

-- 字符串顺序排序
SELECT id FROM persons ORDER BY id ASC;
-- 字节序排序
SELECT id FROM persons ORDER BY id::bytea ASC;

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