Key-set分页中JavaScript时间精度不足引发的PostgreSQL查询异常

在实现Key-set Pagination功能时遇到以下问题：从API获取的timestamp仅支持毫秒精度，这是JavaScript Date的固有精度限制导致的，但数据库（PostgreSQL v15）存储的时间精度更高，最终引发分页查询匹配异常。

数据库Schema（PostgreSQL v15） #

CREATE TABLE IF NOT EXISTS public.activities
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),    
    created_at timestamp with time zone DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE INDEX activities_created_at_id ON activities (created_at, id);

排序规则 #

以下是按created_at asc、id asc排序后的表数据：

select id, created_at from activities order by created_at asc, id asc;

精度不匹配问题 #

由于JavaScript无法支持毫秒以上的精度，传入查询的时间精度低于数据库存储值，导致以下查询返回了所有数据：

select id, created_at from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;

临时方案：用date_trunc限制查询精度 #

这个方法能得到预期结果，但需要根据字段类型动态构建查询，可能存在性能问题，因此不想仅依赖此方案：

select id, created_at from activities
where (date_trunc('milliseconds', created_at::timestamptz), id) < (date_trunc('milliseconds', '2024-03-14T07:23:56.474Z'::timestamptz), 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at desc, id desc;

寻求替代方案 #

目前已知一种解决方案是在客户端与服务端之间传输bigint字符串，但希望在进行耗时改造前，找到其他更优的方案，恳请社区提供建议。

内容的提问来源于stack exchange，提问作者Bjørn Bråthen