All geek questions in one place

Is it a poor design to assume that an auto-incremented primary key is time-ordered?

Or rather, the positives outweigh the negatives?

The goal is to get the N most recent entries.

pros:

  • No need to index created_at column
  • Your ORDER BY will use the clustered index

minuses:

  • You rely on the time-ordered nature of the primary key to stay true to the life of the development cycle.

Thoughts?

+3
source share
3 answers

Due to database transactions, several sessions can make transactions at different times, you will find that it is not always ordered by time.

Another con, without creating an index on created_on, you forgot to mention:

  • / .

, .

+4

, , . . created_at, .

+1

, , , .

If you use a surrogate key to determine the sequence and manage the business logic, you also undermine the main advantage of having a surrogate key in the first place. The surrogate is supposed to have no commercial value, so for example, you can easily update / redistribute values ​​during database maintenance and schema changes.

+1
source

Source: https://habr.com/ru/post/1792402/

More articles:


All Articles