Postgres ordering is not consistent

Question

I'm having an issue where postgres ordered results are not ordered consistently. I'm ordering by multiple fields: ORDER BY categories.position ASC, photos.display_priority

I'm noticing this because as you browse around the site the results are paginated. I found a case where I go from page 1 to page 2, and at the top of page 2 I see a photo that was near the bottom of page 1.

Here is my page 1 query:

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 25 OFFSET 0;

And my page 2 query:

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 25 OFFSET 25;

When I try getting both pages at once (offset 0, limit 50) and inspect the threshold between the two sets there is no duplicate, no surprise.

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 50 OFFSET 0;

Is there something wrong with my query? Is there an order of operations with limit and order by which I'm not understanding?

Answer 1

It sounds like categories.position and photos.display_priority are not unique for all of the result rows. The database server does not specify the order for rows when the values used to order them are all equal; it is free to return them in any order, even if the table data has not changed between queries.

To get consistent ordering you will have to add a third sorting key that is guaranteed to be unique for all rows, such as the identity value for that particular row.