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

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?

http://stackoverflow.com/questions/9984196/rails-postgres-does-not-re-use-deleted-ids-but-mysql-does — akonsu, Nov 19 '13 at 21:23

score 15 · Accepted Answer · answered Nov 19 '13 at 21:23

15

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.

answered Nov 19 '13 at 21:23

NL - Apologize to Monica

493
11
17

I have voted to close this question since akonsu has pointed out there is a duplicate. Thank you for taking the time to answer this. – deadlock Nov 19 '13 at 21:29
No worries, it only took a moment. – NL - Apologize to Monica Nov 19 '13 at 21:30

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

1 Answers1