Order for restful api pagination

Hello,

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;

Hi @ivizeely,

The behaviour of LIMIT/OFFSET is not defined unless you specify an ORDER BY clause. You could make your query work by adding ORDER BY id.

However, OFFSET is usually not what you want for this - the database has to process all the rows you’re offsetting by in order to service your query, and this can become inefficient. The better approach is to remember the last record you saw before and search for records that came after it:

SELECT id, name FROM mytable WHERE id > '<last-id>' ORDER BY id LIMIT 10

Hope this helps!
Justin

Hmm, so do you recommend using SERIAL instead of a uuid for the primary key (or adding a SERIAL column) for cases like this?

Edit: And i’m assuming WITH ORDINALITY is not going to help me with this case?

It depends on what you want to order by - UUID is fine if you don’t particularly care what the ordering is, only that it’s stable. If you want to specify something else to order by you can create a new column with an index on it and then order by that (sorry, initially my previous example didn’t include the necessary ORDER BY clause). WITH ORDINALITY won’t help you here, no.

What is it you’re trying to do that ordering by UUID won’t accomplish?

I don’t care about the actual order just as long as it is deterministic for pagination. If ORDER BY id is best practice for uuid then I’ll use that (I assume it’ll be alphabetical?). If WHERE id > 'my-last-uuid' is best practice then I’ll do that but I’m curious as to how cockroach decides what is greater than something that looks like a string.