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

Question

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.

Answer 1

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.

Answer 2

To know what really DBMS does one should look at the PLAN. The output order will depend upon it too. However there are two things to remember: first, if the plan includes 'full (heap) table scan' then the order is undefined (as DBMS may freely reorder heap data); second, the plan may change significantly if you change your SQL statement or update DB stats. This is why you should not rely on output order's stability in a long run.