Postgresql Sequence not increase by 1

I use a sequence in the PostgreSQL table with an 8-digits start number like:

20000101
20000102

And execute INSERT query with SQLAlchemy ORM in API (AWS lambda) like:

result = session.query("select nextval('mydb.student_id_seq')").one_or_none()
id = result[0]

student_model = Student(
    id=id,
    name="Name",
    class_id="Class ID",
    grade="A"
)

But sometimes a sequence number increases by a random number or roll-back to the previous number like:

20000200
20000201
20000202
20000214
20000215
20000221
20000222

How can I solve this problem?

I want to know How to use the PostgreSQL Sequence in SQLAlchemy correctly.

>Solution :

It’s because of sequence caching. The client you use can request and reserve next N numbers from the sequence, then discard the ones it didn’t use. Someone else can get those returns later, or have to use numbers above what’s already reserved by others.

Don’t rely on the continuity of nextval() from a non-temp sequence you don’t own/use exclusively and can’t lock. From the doc linked above:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

Unexpected results might be obtained if a cache setting greater than one is used for a sequence object that will be used concurrently by multiple sessions. Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object’s last_value accordingly. Then, the next cache-1 uses of nextval within that session simply return the preallocated values without touching the sequence object. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in “holes” in the sequence.

Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered.

You don’t need to manually handle the id sequence in your model and guard it. Make the field use an integer generated as identity.