Sequences are powerful database objects built to generate unique numeric identifiers. While they’re most commonly used to create auto-incrementing primary keys for tables, their versatility makes them ideal for any scenario that requires unique, sequential numbers.
In a distributed database like YugabyteDB, where nodes may span across regions, caching sequence values is essential for maintaining strong performance. When creating a sequence, you can define how many sequence numbers to cache on the client side, with the default set to 100.
The downside of caching sequence values is the potential for gaps in the sequence. By default, caching occurs at the session level, so if a session ends before using all its cached sequence values, the unused numbers are discarded, resulting in gaps.
We saw in the previous tip Create a No-Gap Sequence in YSQL that we can override the caching behavior so that each session only caches one value. However, in practice, that can lead to a signifcant reduction in performance.
Fortunately, there is another method to generate a no-gap sequence of numbers.
First we create table to store our “sequences” along with their current values.
yugabyte=# CREATE TABLE next_nonce(seq TEXT, curr_val INT, PRIMARY KEY (seq));
CREATE TABLE
Next, we create a function that when called will increment the current value for a given sequence by one.
yugabyte=# CREATE OR REPLACE FUNCTION next_nonce(seq_in TEXT) RETURNS INT AS $$
yugabyte$# INSERT INTO next_nonce (seq, curr_val) VALUES (seq_in, 1)
yugabyte$# ON CONFLICT (seq) DO
yugabyte$# UPDATE SET curr_val = next_nonce.curr_val + 1
yugabyte$# RETURNING next_nonce.curr_val;
yugabyte$# $$ LANGUAGE SQL;
CREATE FUNCTION
Here’s the function DDL which you can copy:
CREATE OR REPLACE FUNCTION next_nonce(seq_in TEXT) RETURNS INT AS $$
INSERT INTO next_nonce (seq, curr_val) VALUES (seq_in, 1)
ON CONFLICT (seq) DO
UPDATE SET curr_val = next_nonce.curr_val + 1
RETURNING next_nonce.curr_val;
$$ LANGUAGE SQL;
It works like this…
yugabyte=# SELECT next_nonce('my_seq');
next_nonce
------------
1
(1 row)
yugabyte=# SELECT * FROM next_nonce;
seq | curr_val
--------+----------
my_seq | 1
(1 row)
yugabyte=# SELECT next_nonce('my_seq');
next_nonce
------------
2
(1 row)
yugabyte=# SELECT next_nonce('my_seq');
next_nonce
------------
3
(1 row)
yugabyte=# SELECT * FROM next_nonce;
seq | curr_val
--------+----------
my_seq | 3
(1 row)
Notice that when we first called the function for the sequence named MY_SEQ, a new row was inserted. On the second and third calls, the function updated the existing row instead.
You can even use the function as a default value for a column in a table!
yugabyte=# CREATE TABLE important_data (id INT DEFAULT next_nonce('important_data'), some_data TEXT, create_ts TIMESTAMP);
CREATE TABLE
yugabyte=# INSERT INTO important_data(some_data, create_ts) VALUES ('ABC', statement_timestamp());
INSERT 0 1
yugabyte=# INSERT INTO important_data(some_data, create_ts) VALUES ('DEF', statement_timestamp());
INSERT 0 1
yugabyte=# INSERT INTO important_data(some_data, create_ts) VALUES ('GHI', statement_timestamp());
INSERT 0 1
yugabyte=# INSERT INTO important_data(some_data, create_ts) VALUES ('JKL', statement_timestamp());
INSERT 0 1
yugabyte=# SELECT * FROM important_data ORDER BY id;
id | some_data | create_ts
----+-----------+----------------------------
1 | ABC | 2025-02-11 16:03:40.643424
2 | DEF | 2025-02-11 16:03:46.359642
3 | GHI | 2025-02-11 16:03:53.327953
4 | JKL | 2025-02-11 16:03:59.927312
(4 rows)
yugabyte=# SELECT * FROM next_nonce;
seq | curr_val
----------------+----------
important_data | 4
my_seq | 3
(2 rows)
Keep in mind that this solution may require multiple writes across several tables. The overhead can increase based on the database’s replication factor and the distance between the leader and its nearest follower(s).
Stay tuned for the next YugabyteDB Tip, where we’ll dive into how low-latency geo-partitioning can significantly reduce this overhead!
Have Fun!