Auto-Increment in SingleStore

Background: I landed a job back in 2016 in MongoDB because of Auto-Increment.

MySQL:

• Table created with auto increment column
• Inserting empty value for the column results in a value generated automatically
• If we put a value ourselves, then the auto increment value gets reset to the last maximum, and newly auto generated values start from there

SingleStore:

By default SingleStore creates Columnstore tables with a Shard Key.

So, a simple CREATE TABLE statement like below

CREATE TABLE s2t1(c1 INT AUTO_INCREMENT PRIMARY KEY);

gets converted to

CREATE TABLE `s2t1` ( `c1` bigint(11) NOT NULL AUTO_INCREMENT, UNIQUE KEY `PRIMARY` (`c1`) USING HASH, SHARD KEY `__SHARDKEY` (`c1`), SORT KEY `__UNORDERED` () ) AUTO_INCREMENT=2251799813685252 AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES'

You can check this using the command

SHOW CREATE TABLE <table_name>

Notice the keywords SHARD KEY and SORT KEY.

Sort key is unordered by default.

Our datatype of INT changes to BIGINT to accommodate the values generated by auto increment.

Also, notice the value of AUTO_INCREMENT is set to a large number in this command. This value changes every time the database generates a new value for the auto increment column.

From docs:

AUTO_INCREMENT values in sharded tables are assigned using the high 14 bits to encode the aggregator ID and the bottom 50 bits for a per-aggregator unique value.

However, if you insert a value yourself, this AUTO_INCREMENT value doesn’t change with it. Let’s see the behavior.

Inserted xxx194

auto_increment is now xxx195

We insert xxx198

auto_increment is still at xxx195

This can lead to a collision with future generated values.

In order to get this value to the latest highest value + 1, we need to use AGGREGATOR SYNC command.

AGGREGATOR SYNC AUTO_INCREMENT ON s2test.s2t1 ALL;

Now the value is xxx199

However, if you’re using REFERENCE TABLEs in SingleStore, they are unsharded and exhibit the similar behavior as MySQL tables.

CREATE REFERENCE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY);

From docs:

The AUTO_INCREMENT value for a reference table is tracked by the master aggregator. It is guaranteed that the next AUTO_INCREMENT value will always be greater than any value previously seen in this column.

Contrarily to the behavior for sharded tables, explicitly setting a value in an INSERT or UPDATE statement will not create a collision with future automatically generated values.

The good thing about auto increment in SingleStore even being a distributed database is that the numeric datatype is preserved. In traditional industries like Banking and Telecom, the developers feel their life gets a little easier with this.

I hope you enjoyed reading this.

Tell me about your auto increment experiences.