5

I am using the keyset pagination method for uuids on my Postgres database described in this post:

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?

2
  • Time stamps generally go to microseconds, but you show only milliseconds. I suspect your problem is a mismatch in rounding, leading to values appearing equal when they are not.
    – jjanes
    May 24, 2022 at 20:18
  • Right. I cannot reproduce your result with the data in the question. May 25, 2022 at 6:21

1 Answer 1

1

Either of your shown queries should return two rows, as expected:

db<>fiddle here

If you see a different result, then the likely cause is index corruption. Test with:

SELECT id, created_at
FROM collection
WHERE (created_at + interval '1 ms', id)
    < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at + interval '1 ms' DESC, id DESC
LIMIT 3;

That "disables" index support and gets the result of a sequential scan. See:

If so, fix with:

REINDEX TABLE collection;

Or just the involved index on (created_at, id).

I have hardly ever seen index corruption myself. Typically, there is a troubling root cause, like failing hardware (RAM, storage) or a very old version of Postgres. Try to find and fix the cause.

This might be a good time for a backup of your database first.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.