Why in PostgreSQL when you delete a row in a table the id number of the future inserted row is not sequential?
database, nextval, postgresql, sql
Solution
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.
Problem
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?