Why doesn't Django/PostgreSQL reuse primary key values after objects with that primary key have been deleted? [duplicate]

Question

I'm trying to figure out why Django (or PostgreSQL, not to sure at which level it is) doesn't reuse primary keys from objects that have been deleted. When objects are created, they all have an id value that is auto incremented and ordered by the id value in the database. For example:

ID

5

4

3

2

1

Let's say I happen to delete the object with an ID of 5:

ID

4

3

2

1

When I create a new object, it has an ID of 6:

ID

6

4

3

2

1

Why wasn't "5" assigned as the primary key to the new object I just created? Why does it have 6 instead?

Answer 1

The primary key assigned is often based on a sequence because it's easier to store and calculate a single value and the next value in the sequence. Consider a table with 1 million records and a delete that removes 50 thousand of those records. To fill in the newly opened key values you either have to use memory to store those options or scan the table for the lowest open value. Both are expensive compared to just assigning the next value in the sequence.