"Making bad assumptions about DB ordering"
That's caught me out before. Postgres is just weird, I had to run the same test in a loop for an hour before it'd randomly change the order.
There's several reasons for potential ordering changes:
- the order of items on the page is different, due to the way tuples have been inserted (different external scheduling, different postgres internal scheduling)
- concurrent sequential scans can coordinate relation scans, which is quite helpful for relations that are larger than the cache
- different query plans, e.g. sequential vs index scans
Unless you specify the ORDER BY, there really isn't any guarantee by postgres. We could make it consistent, but that'd add overhead for everyone.
- the order of items on the page is different, due to the way tuples have been inserted (different external scheduling, different postgres internal scheduling) - concurrent sequential scans can coordinate relation scans, which is quite helpful for relations that are larger than the cache - different query plans, e.g. sequential vs index scans
Unless you specify the ORDER BY, there really isn't any guarantee by postgres. We could make it consistent, but that'd add overhead for everyone.