sqlpostgresqlselectsql-order-by

How does postgres order the results when ORDER BY is not provided


Let's say I have two tables:

user (user_name varchar(50), project_name varchar(50))
project (project_name varchar(50), project_cost(integer))

I have a query that's returns me results which are "de-facto desired" :

select u.user_name, p.project_name 
from user u, project p 
where u.project_name = p.project_name

Postgres says that order of rows is not predictable when ORDER BY is not given. But yet in my local test, postgres returns rows in a same order for repeated tests.

Could you please help me understand what Postgres really does when order by is not provided in the query?

I don't have access to all the possible places where the real table and schema a live, so I really need to know what really happens in order to keep existing ordering intact.


Solution

  • If no order by clause is given, postgres (and any other reasonable database, for that sake), should return the rows in the order it was able to produce them (be it from an internal cache, an index, or directly from the table).

    Since the same algorithm is used on the same data, it isn't surprising you're getting the same rows in the same order. However, this does not mean you should rely on this ordering. If you do something to change the data's layout on the disk (e.g., back it up and restore it, or even rebuild the tables' indexes), you're likely to get a different ordering.