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.
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.