Sequences are specialized database objects in YSQL that generate unique numeric identifiers. While they are often used to create auto-incrementing primary keys for tables, they can also be applied to other scenarios that require unique, sequential numbers.
By default, 100 sequence values are cached per connection which is set via the ysql_sequence_cache_minval T-Server gFlag:
[root@cloud-server-0 yb2]# curl -s http://$(hostname -I | sed 's/ //g'):9000/varz?raw | grep ysql_sequence_cache_minval
--ysql_sequence_cache_minval=100
I will get an error if I try to cretae a sequence less that 100:
[root@cloud-server-0 yb2]# ysqlsh -h $(hostname -I) -c "CREATE SEQUENCE s CACHE 1;"
NOTICE: Overriding cache option with cache flag or previous cache value.
HINT: Cache option cannot be set lower than cache flag or previous cache value.
CREATE SEQUENCE
You can override that behaviour by setting ysql_sequence_cache_minval to zero (0).
This change requires a rolling restart of the cluster. Once done, you’ll be able to create a sequence with a cache of 1.
[root@cloud-server-0 yb2]# curl -s http://$(hostname -I | sed 's/ //g'):9000/varz?raw | grep ysql_sequence_cache_minval
--ysql_sequence_cache_minval=0
[root@cloud-server-0 yb2]# ysqlsh -h $(hostname -I) -c "CREATE SEQUENCE s CACHE 1;"
CREATE SEQUENCE
Each new session will now cache only one sequence, ensuring a gapless sequence.
[root@cloud-server-0 yb2]# ysqlsh -h $(hostname -I) -c "SELECT host FROM yb_servers() ORDER BY 1;"
host
---------------
127.0.0.1
127.0.0.2
127.0.0.3
127.0.0.4
127.0.0.5
(5 rows)
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.1 -c "SELECT nextval('s');"
nextval
---------
1
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.2 -c "SELECT nextval('s');"
nextval
---------
2
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.3 -c "SELECT nextval('s');"
nextval
---------
3
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.4 -c "SELECT nextval('s');"
nextval
---------
4
(1 row)
[root@cloud-server-0 yb2]# ysqlsh -h 127.0.0.5 -c "SELECT nextval('s');"
nextval
---------
5
(1 row)
Remember, in a distributed database like YugabyteDB, disabling sequence caching can significantly impact performance at scale.
In the next YugabyteDB Tip, Create a No-Gap Sequence in YSQL without using a Sequence!, we’ll dive into an intriguing alternative approach!
Have Fun!