if I sort by price
IDs might come in order 3, 1, 2, 4 or 3, 2, 1, 4.
This causes problems with pagination because when changing offset, it sometimes returns same record multiple times.
One possible fix is to always provide sort by id, if sort is provided, but IDK how much would that affect performance.
Since PG does not guarantee sort order without order by, maybe with CRUD always sort by createdAt if possible.
If id is int, that it's safe by id. IDK about uuid
Should this be handled in app, or that responsability should fall on db?
If user is sorting by value, and it has multiple same values, other row might change. https://stackoverflow.com/questions/11263715/is-postgresql-order-fully-guaranteed-if-sorting-on-a-non-unique-attribute
if I sort by price IDs might come in order
3, 1, 2, 4
or3, 2, 1, 4
. This causes problems with pagination because when changing offset, it sometimes returns same record multiple times.One possible fix is to always provide sort by id, if sort is provided, but IDK how much would that affect performance. Since PG does not guarantee sort order without order by, maybe with CRUD always sort by
createdAt
if possible. If id is int, that it's safe by id. IDK about uuidShould this be handled in app, or that responsability should fall on db?