Search code examples
sqldatabasepostgresqldatabase-performance

Order by datetime field DESC or order by primary key DESC?

This should be a fairly regular operation: when retrieving records from a table in DB, I need them to be ordered by descending creation date time. But if I use a AUTOINCREMENT key, I can also order by descending key value.

My question is: which is better?

  1. Performance-wise, I know primary keys and datatime fields use different type of indice, which one is more efficient?
  2. Do they always return the same result? Does AUTO-INCREMENT key wrap around when it reaches maximum?
Solution

  • I think you should identify the sorted columns clearly. If not, the order of result can be changed if you update 1 line of data