Re:Things you should do now (2011)
by dragonwriter on Friday February 19, 2021 @12:06PM
(#26195195)
> If you order things by id (often useful for pagination, since incrementing ids are usually ordered by creation date) uuid are of no help.
The use of ordering by a surrogate key is to have a stable ordering with no semantic meaning, so UUIDs work just as well as IDs there.
> You would need to sort by created_at and this would require an additional index.
Yes, if you want to sort by a semantically important data element in a table with surrogate primary key, you'll probably want an index on the data element. So?
> No to speak of pagination by id ranges or whatever is used sometimes.
Assuming a serial column is creation-ordered is not a good idea, but it's usually not too wrong; assuming it's dense, as well, is going to be wrong more often than it is right, except where data is never deleted. Assuming it is dense over the range of a query is even less reliable.
Paginating by serial ID ranges is, almost without exception, a horrible idea.
The use of ordering by a surrogate key is to have a stable ordering with no semantic meaning, so UUIDs work just as well as IDs there.
> You would need to sort by created_at and this would require an additional index.
Yes, if you want to sort by a semantically important data element in a table with surrogate primary key, you'll probably want an index on the data element. So?
> No to speak of pagination by id ranges or whatever is used sometimes.
Assuming a serial column is creation-ordered is not a good idea, but it's usually not too wrong; assuming it's dense, as well, is going to be wrong more often than it is right, except where data is never deleted. Assuming it is dense over the range of a query is even less reliable.
Paginating by serial ID ranges is, almost without exception, a horrible idea.