Autogenerated sequences and PRIMARY KEY values in CrateDB¶

As you begin working with CrateDB, you might be puzzled why CrateDB does not have a built-in, auto-incrementing “serial” data type as PostgreSQL or MySQL.

As a distributed database, designed to scale horizontally, CrateDB needs as many operations as possible to complete independently on each node without any coordination between nodes.

Maintaining a global auto-increment value requires that a node checks with other nodes before allocating a new value. This bottleneck would be hindering our ability to achieve extremely fast ingestion speeds.

That said, there are many alternatives available and we can also implement true consistent/synchronized sequences if we want to.

Using a timestamp as a primary key¶

This option involves declaring a column as follows:

BIGINT DEFAULT now() PRIMARY KEY

Pros:: Always increasing number - ideal if we need to timestamp records creation anyway
Cons:: gaps between the numbers, not suitable if we may have more than one record on the same millisecond

Using UUIDs¶

This option involves declaring a column as follows:

TEXT DEFAULT gen_random_text_uuid() PRIMARY KEY

Pros:: Globally unique, no risk of conflicts if merging things from different tables/environments
Cons:: No order guarantee. Not as human-friendly as numbers. String format may not be applicable to cover all scenarios. Range queries are not possible.

Use UUIDv7 identifiers¶

Version 7 UUIDs are a relatively new kind of UUIDs which feature a time-ordered value. We can use these in CrateDB with an UDF with the code from UUIDv7 in N languages.

Pros:: Same as gen_random_text_uuid above but almost sequential, which enables range queries.
Cons:: not as human-friendly as numbers and slight performance impact from UDF use

Use IDs from an external system¶

In cases where data is imported into CrateDB from external systems that employ identifier governance, CrateDB does not need to generate any identifier values and primary key values can be inserted as-is from the source system.

See Replicating data from other databases to CrateDB with Debezium and Kafka for an example.

Implement sequences¶

This approach involves a table to keep the latest values that have been consumed and client side code to keep it up-to-date in a way that guarantees unique values even when many ingestion processes run in parallel.

Pros:: Can have any arbitrary type of sequences, (we may for instance want to increment values by 10 instead of 1 - prefix values with a year number - combine numbers and letters - etc)
Cons:: Need logic for the optimistic update implemented client-side, the sequences table becomes a bottleneck so not suitable for high-velocity ingestion scenarios

We will first create a table to keep the latest values for our sequences:

CREATE TABLE sequences (
        name TEXT PRIMARY KEY,
        last_value BIGINT
) CLUSTERED INTO 1 SHARDS;

We will then initialize it with one new sequence at 0:

INSERT INTO sequences (name,last_value)
VALUES ('mysequence',0);

And we are going to do an example with a new table defined as follows:

CREATE TABLE mytable (
        id BIGINT PRIMARY KEY,
        field1 TEXT
);

The Python code below reads the last value used from the sequences table, and then attempts an optimistic UPDATE with a RETURNING clause, if a contending process already consumed the identity nothing will be returned so our process will retry until a value is returned, then it uses that value as the new ID for the record we are inserting into the mytable table.

# /// script
# requires-python = ">=3.8"
# dependencies = [
#     "records",
#     "sqlalchemy-cratedb",
# ]
# ///

import time

import records

db = records.Database("crate://")
sequence_name = "mysequence"

max_retries = 5
base_delay = 0.1  # 100 milliseconds

for attempt in range(max_retries):
        select_query = """
   SELECT last_value,
                   _seq_no,
                   _primary_term
   FROM sequences
   WHERE name = :sequence_name;
   """
        row = db.query(select_query, sequence_name=sequence_name).first()
        new_value = row.last_value + 1

        update_query = """
                                           UPDATE sequences
                                           SET last_value = :new_value
                                           WHERE name = :sequence_name
                                                 AND _seq_no = :seq_no
                                                 AND _primary_term = :primary_term
                                           RETURNING last_value;
                           """
        if (
                str(
                        db.query(
                                update_query,
                                new_value=new_value,
                                sequence_name=sequence_name,
                                seq_no=row._seq_no,
                                primary_term=row._primary_term,
                        ).all()
                )
                != "[]"
        ):
                break

        delay = base_delay * (2**attempt)
        print(f"Attempt {attempt + 1} failed. Retrying in {delay:.1f} seconds...")
        time.sleep(delay)
else:
        raise Exception(f"Failed after {max_retries} retries with exponential backoff")

insert_query = "INSERT INTO mytable (id, field1) VALUES (:id, :field1)"
db.query(insert_query, id=new_value, field1="abc")
db.close()