Why Postgres id column serial4 type not increment by 1 even not deleting

Question

In postgres 11.2 I created a table with id serial4 NOT NULL, I thought the id would always increment by 1 but I noticed it's not.

Table Order creation SQL

CREATE TABLE public.order (
    "createdAt" timestamptz NOT NULL DEFAULT now(),
    "updatedAt" timestamptz NOT NULL DEFAULT now(),
    "deletedAt" timestamptz NULL,
    id serial4 NOT NULL,
    .....
);

For example, when I queried select id from device d where id < 4000000 order by "id" desc limit 10, some ids are not incremented by 1

Although it looks like in some id ranges, the ids are always increment added by 1.

For example:

select id from device d where id < 2000 order by "id" desc limit 10 or

select id from device d where id < 5000000 order by "id" desc limit 10

are all sequence id rows added by 1.

Since I always use soft delete by the deletedAt column and I've checked my code that it would never delete the rows.

Would postgres 11.2 increment data column serial4 not by 1 but other values in some conditions? How can we prevent it happening?

The **only** job for a generated value is to be unique. Gaps are expected and not a problem. It's possible to "waste" sequence values when you call `nextval()` but never use the value. Or because of caching of sequence values. There is nothing you need to worry about. — , Aug 25 '22 at 05:37
See https://stackoverflow.com/questions/2095917/sequences-not-affected-by-transactions — clamp, Aug 25 '22 at 05:48
See [here](https://www.cybertec-postgresql.com/en/gaps-in-sequences-postgresql/) for a detailed answer. — Laurenz Albe, Aug 25 '22 at 08:18
Offtopic: Are you sure about version 11.2? That would mean that your server is over 3 years without maintenance. You're missing hundreds of bug fixes, including fixes for data corruption and security issues. Latest version (as of today) is version 11.17 — Frank Heikens, Aug 25 '22 at 13:41
@FrankHeikens Yes, I chose this version for using plv8 ( function in javascript ) about 2 years ago. I haven't updated since then. — JasonHsieh, Aug 26 '22 at 01:59

score 0 · Answer 1 · answered Aug 26 '22 at 02:26

Thanks @Laurenz Albe for the link to the awesome article GAPS IN SEQUENCES IN POSTGRESQL .

After testing, in my case, the gaps are caused by column unique constraint, whenever inserting a row of data violate the unique constraint, the serial4 column would go to the next value.

Some scripts below for reproducing my case:

Run postgres11.2 in docker:

#!/bin/bash

docker rm -f pg-test
docker run -d --name pg-test postgres:11.2
sleep 5
docker exec -it pg-test bash -c 'psql -U postgres'

In psql

\e

CREATE TABLE public.order (
    "createdAt" timestamptz NOT NULL DEFAULT now(),
    "updatedAt" timestamptz NOT NULL DEFAULT now(),
    "deletedAt" timestamptz NULL,
    id serial4 NOT NULL,
    n1 varchar UNIQUE
);

INSERT INTO "order" (n1) VALUES (1); 
// return INSERT 0 1
INSERT INTO "order" (n1) VALUES (1); 
// return ERROR:  duplicate key value violates unique constraint "order_n1_key" DETAIL:  Key (n1)=(4) already exists.

INSERT INTO "order" (n1) VALUES (2);
// return INSERT 0 1

select * from "order";

The select result would be:

           createdAt           |           updatedAt           | deletedAt | id | n1
-------------------------------+-------------------------------+-----------+----+----
 2022-08-26 02:18:03.716084+00 | 2022-08-26 02:18:03.716084+00 |           |  1 | 1
 2022-08-26 02:18:46.831221+00 | 2022-08-26 02:18:46.831221+00 |           |  3 | 2

The id serial4 column has a gap from 1 to 3 because of INSERT that violate the unique contraint.

Why Postgres id column serial4 type not increment by 1 even not deleting

1 Answers1