3 ms·
> some code comes to accidentally depend on a coincidental ordering of the results It's not really "coincidental". It's insertion ordering on-disk. And that
by VWWHFSfQ 4y ago
> some code comes to accidentally depend on a coincidental ordering of the results
It's not really "coincidental". It's insertion ordering on-disk. And that varies between servers. So if you have 10 replicas they might persist records to disk in a slightly different order. So, if you don't specify an explicit ordering in the query then you will get records back in the order the server finds them on-disk.
- avianlyric 4y agoIt is coincidental if the insertion ordering happens to match the ordering your business logic expects. Additionally it’s not even insertion ordering, more like update ordering. All row updates in Postgres result in a new tuple being written, which may be near the original tuple if there’s space, or may be at the top of the table on-disk. But either way, updates will also change your row ordering. With replicas you would expect them all to have very similar, possibly identical on-disk ordering, depending on the replication method. If you’re using plain WAL replication, not logical replication, the the on-disk order is probably going to be preserved because the basic WAL only contains the resulting storage layer operations, not the higher level queries. Which means that playing the WAL back else where should result in identical on disk results.