Sequence having big gaps in YugabyteDB YSQL

[Question posted by a user on YugabyteDB Community Slack]

Using YugabyteDB 2.11, and having a simple table like below:

create table my_table (id bigserial primary key, a text);

But while inserting data, sequence is having big gaps. eg.:, while call insert services, 'id' column gets values as 1,2,3,101,201,202,301,.......

This happens because of --ysql_sequence_cache_minval glfag https://docs.yugabyte.com/latest/reference/configuration/yb-tserver/#ysql-sequence-cache-minval.

All sequences are cached by 100 in each yb-tserver so that it can scale without querying the 'single point of truth' each time. Sequences are part of the postgres catalog, and therefore stored on the yb-master. A sequence with a cache of 1 (no cache), will have to reach out to the master for each value, a cache of 100 allows a backend to obtain the sequence number from the master and use the number as indicated by the cache from that number. This will make a next session pick its own range of 100, etc.

Note that even if you remove the cache there will be gaps in sequences because they are not transactional by nature.

The problem of gaps in sequences exists since database sequences exist. It's a computer science problem, not a YB (or implementation) problem. If you want no gaps, you need a serial (not scalable) process. This is not worth the trade-off, especially if you implement YB for scalability.