Sample Header Ad - 728x90

Inconsistent keyset pagination when using (timestamp, uuid) fields

5 votes
1 answer
1788 views
I am using the keyset pagination method for uuids on my Postgres database described in this post: - https://dba.stackexchange.com/questions/267794/how-to-do-pagination-with-uuid-v4-and-created-time-on-concurrent-inserted-data However, I have noticed when I have two records where the date is the same, rows are being skipped from the result. For example when I run the query:
SELECT id, created_at FROM collection
ORDER BY created_at DESC, id DESC
I get the records back as I expect them, with created_at being the primary order, then id acting as a tiebreaker: |id|created_at| |--|----------| |e327847a-7058-49cf-bd91-f562412aedd9|2022-05-23 23:07:22.592| |d35c6bb8-06dd-4b86-b5c6-d123340520e2|2022-05-23 23:07:22.592| |5167cf95-953f-4f7b-9881-03ef07adcf3c|2022-05-23 23:07:22.592| |d14f48dc-df22-4e98-871a-a14a91e8e3c1|2022-05-23 23:07:21.592| However when I run a query to paginate through like:
SELECT id, created_at
FROM collection
WHERE (created_at, id) < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC
LIMIT 3
I would expect to get back the last two records, but my result set is instead |id|created_at| |--|----------| |d14f48dc-df22-4e98-871a-a14a91e8e3c1|2022-05-23 23:07:21.592| I've also tried some variations on the query to try to fix it, such as:
SELECT id, created_at
FROM collection
WHERE created_at < '2022-05-23 23:07:22.592' OR
     (created_at = '2022-05-23 23:07:22.592' AND id < 'd35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC
But I still get back the same result set. What's going on with my query?
Asked by Daniel (51 rep)
May 24, 2022, 05:53 PM
Last activity: May 24, 2022, 07:53 PM