PostgreSQL - Keyset pagination query not working with UUID v4 searchAfter

Question

I am trying to write a query to do keyset pagination.

But I see an unusual behavior here.

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;

Note this is the seed script for the tables -

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, U

The first two times I run my query I get the data as usual

SELECT p.id,
       p.first_name AS firstname,
       p.last_name AS lastname,
       p.age,
       p.created_at AS createdat,
       p.updated_at AS updatedat,
       a.address,
       a.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.created_at < @searchAfterTime
ORDER BY p.create_at DESC
LIMIT 1;

But when I run this query for the 3rd time, I don't see any rows whereas my table has data.

I am suspecting that the UUID column is sorted in alphabetical order by Postgres and it is not doing the less than comparison using the byte order.

Any pointers to make this query work will be appreciated.

Hewlett

You sort by created_at and query the uuid, how should this work for paging? – Towhead

I want the data to be sorted in descending order based on the created_at – Hewlett

then you have to query created_at to be smaller then the last created_at retrieved – Towhead

I cannot use created_at because there is a possibility that some rows can get inserted at the very same time having the same created_at values. That is the sole reason I am using the id column to traverse. – Hewlett

then you have to query and order by created_at and the uuid – Towhead

can you show an example – Hewlett

Answer 1

You have to use both fields, id and created_at, for sorting and querying, using the id as a tiebreaker:

SELECT 
...
FROM persons p
LEFT JOIN addresses a
ON a.person_id = p.id
WHERE 
     p.created_at < @searchAfterCreatedat or 
    (p.created_at = @searchAfterCreatedat and
      p.id < @searchAfterid)  
ORDER BY p.created_at DESC, p.id desc
LIMIT 1;

By this you have a total order on your records.

Towhead

Can you please add more details to the intuition behind the tie breaker logic. How it will work And why comparing uuid is not working, are the UUIDs being compared in alphabetic or lexographic order. – Hewlett

It's like just sorting two letter words, order by the first letter, when equal by the second, and its granted that the second is not equal – Towhead