CODEWITHSUNDEEP
sqldatabasepostgresqlnextval
buterfly85
buterfly85

Reputation: 193

Why in PostgreSQL when you delete a row in a table the id number of the future inserted row is not sequential?

I create the following table

CREATE TABLE dogs (
  id serial,
  name VARCHAR(15),
  age integer;

I have the table looking like this

                                  Table "public.birds"
 Column  |         Type          |                     Modifiers                      
---------+-----------------------+-------------------------------------    
 id      | integer               | not null default nextval('birds_id_seq'::regclass)
 name    | character varying(25) | 
 age     | integer               |

I insert two rows

INSERT INTO dogs (name, age) 
VALUES ('puffy', 13),
 ('fluffy', 15);

The table now looks like this

  id |  name  | age 
 ----+--------+-----
   1 | puffy  |  13
   2 | fluffy |  15
 (2 rows)

Then I delete the row with id = 2

DELETE FROM dogs WHERE id = 2;

And add another row instead

INSERT INTO dogs (name, age) VALUES('mimi', 20);

The table is

  id | name  | age 
 ----+-------+-----
   1 | puffy |  13
   3 | mimi  |  20
 (2 rows)

My question is why the next number for id in the second row is not 2 but 3? I guess that somewhere underneath something stores the last value in a memory and it doesn't matter that the row with that id was deleted. I know I can insert value for id explicitly if I need to. But I would like to be clear why it happens in this case. And what functionality or feature is responsible for that? How does it work?

Upvotes: 3

Views: 7612

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324621

PostgreSQL makes no effort to keep track of deleted sequence IDs. It just uses a counter to get the next ID to generate.

Gaps will also appear if you generate values then ROLLBACK a transaction, the client connection crashes before committing, or the server crashes.

The only property you can rely on from generated IDs is uniqueness. You cannot even rely on them appearing in the table in the same order they're generated, since commit order isn't necessarily the same as ID allocation order.

If you need gapless sequences there are ways to implement them, but they have terrible performance in concurrent write loads. That's why PostgreSQL does things the way it does.

For more info, Google "gapless sequence postgresql" and read the documentation chapter on sequences and the "nextval" function.

Upvotes: 7

Related Questions