I’m writing an API that supports paging and I’m not sure the best way to guarantee order for pagination.
The table in question is basically metadata with a UUID as it’s primary key. There are no other indexes to order by. I don’t necessarily need the information ordered alphabetically by PK. What is the best way to guarantee order so LIMIT/OFFSET work for pagination on an API.
An example table:
CREATE TABLE mytable ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING(255) )
And an example query I want to use from the API:
SELECT id, name FROM mytable LIMIT 10 OFFSET 20;