POSTGRES: Getting different set of records when added a new column

Question

 $: select id from vehicles limit 5 offset 45
  -- Returns 

  -- 735
  -- 736
  -- 737
  -- 738
  -- 739

 $: select id, state from vehicles limit 5 offset 45;
  -- Returns

  -- 1381 | new
  -- 1327 | new
  -- 1304 | new
  -- 1382 | new
  -- 1317 | new

Even without adding any ordering/grouping, What could possibly be the reason why I am getting different set of records in my Postgresql when I added another column with 'id'?

PostgreSQL Ver - 12.6

Answer 1

Limit 5 offset 45 you're using effectively gives you accidental 5 rows unless you add an order by - without it the order is not guaranteed. PostgreSQL is free to fetch rows in any order it wants and write them in any order it wants. Even if you cluster by an index, forcibly rewriting a table to follow an order implied by a certain index, the choice how to read it back to you remains arbitrary, unless you specify the order by clause. From the doc:

If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

You can experience ordered reads and grow to expect some default sorting behaviour, but unless you use an order by those are pretty much accidental and you can't rely on them keeping their order in the future.

---

If what you were after is in fact some kind of random 5 rows, but in a reliable and stable manner, I don't think you can achieve that easily. You'd have to order by everything in a cte/subquery to get a stable input, setseed() and then order by random() before cutting out your limit 5 offset 45.

This would sort of emulate the tablesample clause:

select id, state 
from vehicles tablesample system(1) repeatable(.2) 
limit 5 offset 45;

Even with a seed specified in repeatable (or setseed()) neither is truly repeatable: if the table changes between statements you will no longer get the same sample.