SingleStore configurations

Performance Optimizations

SingleStore Physical Database Schema Design documentation is helpful if you want to use specific options (that are described below) in your dbt project.

Storage type

SingleStore supports two storage types: In-Memory Rowstore and Disk-based Columnstore (the latter is default). See the docs for details. The dbt-singlestore adapter allows you to specify which storage type your table materialization would rely on using storage_type config parameter.

rowstore_model.sql

{{ config(materialized='table', storage_type='rowstore') }}

select ...

Keys

SingleStore tables are sharded and can be created with various column definitions. The following options are supported by the dbt-singlestore adapter, each of them accepts column_list (a list of column names) as an option value. Please refer to Creating a Columnstore Table for more informartion on various key types in SingleStore.

• primary_key (translated to PRIMARY KEY (column_list))
• sort_key (translated to KEY (column_list) USING CLUSTERED COLUMNSTORE)
• shard_key (translated to SHARD KEY (column_list))
• unique_table_key (translated to UNIQUE KEY (column_list))

primary_and_shard_model.sql

{{
    config(
        primary_key=['id', 'user_id'],
        shard_key=['id']
    )
}}

select ...

unique_and_sort_model.sql

{{
    config(
        materialized='table',
        unique_table_key=['id'],
        sort_key=['status'],
    )
}}

select ...

Indexes

Similarly to the Postgres adapter, table models, incremental models, seeds, and snapshots may have a list of indexes defined. Each index can have the following components:

• columns (list, required): one or more columns on which the index is defined
• unique (boolean, optional): whether the index should be declared unique
• type (string, optional): a supported index type, hash or btree

As SingleStore tables are sharded, there are certain limitations to indexes creation, see the docs for more details.

indexes_model.sql

{{
    config(
        materialized='table',
        shard_key=['id'],
        indexes=[{'columns': ['order_date', 'id']}, {'columns': ['status'], 'type': 'hash'}]
    )
}}

select ...

Other options

You can specify the character set and collation for the table using charset and/or collation options. Supported values for charset are binary, utf8, and utf8mb4. Supported values for collation can be viewed as the output of SHOW COLLATION SQL query. Default collations for the corresponding charcter sets are binary, utf8_general_ci, and utf8mb4_general_ci.

utf8mb4_model.sql

{{
    config(
        charset='utf8mb4',
        collation='utf8mb4_general_ci'
    )
}}

select ...