Sequences

Sequences are a standard SQL feature that allow for generating monotonically increasing numbers typically used to form an ID column value. To create a sequence, use the following command:

CREATE SEQUENCE my_schema.my_sequence;

This will start the sequence from 1, increment by 1 each time, and cache on your session the default number of sequence values based on the phoenix.sequence.cacheSize config parameter. The specification of a sequence schema is optional. Caching sequence values on your session improves performance, as we don't need to ask the server for more sequence values until we run out of cached values. The tradeoff is that you may end up with gaps in your sequence values when other sessions also use the same sequence.

All of these parameters can be overridden when the sequence is created like this:

CREATE SEQUENCE my_schema.my_sequence START WITH 100 INCREMENT BY 2 CACHE 50;

Sequences are incremented using the NEXT VALUE FOR <sequence_name> expression in an UPSERT VALUES, UPSERT SELECT, or SELECT statement as shown below:

UPSERT VALUES INTO my_table(id, col1, col2) VALUES( NEXT VALUE FOR my_schema.my_sequence, 'foo', 'bar');

This will allocate a BIGINT