postgresql - Postgres : using timestamps for pagination

Question

我有一张 table created (timestamptz) 属性。现在，我需要根据时间戳创建分页，因为当用户正在观看第一页时，新项目可能会提交到此表中，如果我使用 OFFSET，这会使数据不一致。用于分页。

所以，问题是:我应该保留 created输入为 timestamptz或者最好将其转换为整数(unix，例如 1472031802812 )。如果有，有什么缺点吗？另外，atm 我有 now()作为 created 中的默认值- 是否有创建unix时间戳的替代功能？

Answer 1

让我重写从评论到我的答案。您要使用 timestamp输入而不是 integer仅仅因为这正是它的设计目的。在时间戳整数和 timestamp 之间进行手动转换对象只是一种痛苦，而您一无所获。并且您最终将需要它来进行更复杂的基于日期时间的查询。

回答一个关于分页的问题。你只需做一个查询

SELECT *
FROM table_name
WHERE created < lastTimestamp
ORDER BY created DESC
LIMIT 30

如果是第一次查询，那么你设置说 lastTimestamp = '3000-01-01' .否则你设置 lastTimestamp = last_query.last_row.created .

优化

请注意，如果表很大，则 ORDER BY created DESC可能效率不高(尤其是在不同范围内并行调用时)。在这种情况下，您可以使用移动“时间窗口”，例如:

SELECT *
FROM table_name
WHERE
    created < lastTimestamp
    AND created >= lastTimestamp - interval '1 day'

1 day间隔是任意选择的(根据您的需要调整它)。您还可以在应用程序中对结果进行排序。

如果结果不为空，那么您更新(在您的应用程序中)

lastTimestamp = last_query.last_row.created

(假设您已经完成排序，否则您将使用 min(last_query.row.created) )

如果结果为空，则使用 lastTimestamp = lastTimestamp - interval '1 day' 重复查询直到你拿到东西。如果 lastTimestamp，您也必须停止变低，即当它低于表中的任何其他时间戳时(必须预取)。

所有这些都基于插入的一些假设:

new_row.created >= any_row.created和

new_row.created ~ current_time

new_row.created的分布或多或少是统一的

假设 1 确保分页产生一致的数据，而假设 2 仅适用于默认 3000-01-01日期。假设 3 是为了确保在您必须发出许多空查询时没有大的空白。