PostgreSQL - Keyset pagination query not working with UUID v4 searchAfter

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.

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.