Postgesql non-deterministic order of result with LIMIT and ORDER BY

Question

I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely different results. So the suggestion for safety is to use ORDER BY with LIMIT.

However, what if we use ORDER BY on an non-unique column name? Will this produce a deterministic result or no? Let me demonstrate with an example:

We have the following data:

id | name | index
1    foo     5
1    bar     2
1    test    5

If I query SELECT * FROM <table> WHERE id = 1 ORDER BY index LIMIT 1;. Will this produce the same result every time?

Answer 1

If there are multiple rows with the lowest value for index, then no, there is no guarantee you will always get the same row. If you only specify ordering for a column that contains duplicates, then the database does not have to provide anything more than any row that meets the criteria.

Same would be true if you were to say ORDER BY index DESC - which row with 5 would you expect? Why?

In order to force determinism / predictability, add a tie-breaker on a column that is unique (or at least unique within the same values of index). With your sample data, you could say:

ORDER BY index, name LIMIT 1;

But you haven’t shared enough about your table structure and constraints to get a more specific suggestion than that.

Answer 2

I'm not sure that those earlier answers are correct. Per the Postgres documentation on Indexes and Order By, Postgres with use the tuple identifier (TID), which is unique for each row, as a tie-breaker.

This was changed starting with v 12 of Postgres (or any version with BTREE_VERSION 4 indexes).