StarRocks Best Practices: Data Modeling

Over my years as a DBA and StarRocks contributor, I've gained a lot of experience working alongside a diverse array of community members and picked up plenty of best practices. In this time, I've found five specific models that stand out as absolutely critical: deployment, data modeling, data ingestion, querying, and monitoring.

In my previous article I shared some tips on StarRocks deployment, in this one, I'll be explaining the finer points of data modeling.

Data Modeling

When it comes to data modeling, you'll find there are a lot of decisions to make as you move through the process. I've tried to cover each of those key decisions below, but if you still have questions, I encourage you to reach out on the StarRocks Slack.

Table Creation Standards to Know

• Only UTF8 encoding is supported.

• Column renaming is not supported (support coming soon).

• Maximum VARCHAR length: 1048576

• KEY columns cannot use FLOAT, DOUBLE types.

• Data directory names, database names, table names, view names, usernames, role names are case-sensitive; column names and partition names are not case-sensitive.

• In the primary key table, the total size of primary key columns should not exceed 128 bytes.

What Table Type Should You Use?

• Use the duplicated key table to retain detailed records (fact table).

• use the primary key table for tables that have a unique column that is non-null, have frequent write and read, and non-primary key columns utilize indexes.

• Use the unique key table for tables with a unique column that may be null, have frequent write, and infrequent read.

• use the aggregate table to retain only aggregated data.

• See the doc for a detailed comparison between different table types.

Choosing Sorting Columns and Prefix Indexes

• For versions before StarRocks 3.0, the primary key table specifies sorting columns through PRIMARY KEY. For StarRocks 3.0 and later, sorting columns in primary key models are specified through ORDER BY.

• Prefix index is a sparse index introduced on top of sorting columns to further improve query efficiency. All prefix indexes are loaded into memory for optimal performance. When using prefix indexes, consider:

  • Columns frequently used as query conditions should be sorting columns. For instance, placing user_id first if queries often filter by user_id.

  • Sorting columns should ideally be 3-5 columns. Too many can increase sorting overhead and slow down data ingestion.

  • Prefix indexes should not exceed 36 bytes and must not include more than 3 columns. VARCHAR columns will be truncated in prefix indexes, and FLOAT or DOUBLE types are not allowed.

In combination with actual business query scenarios, when determining the Key columns and the order of fields, the advantages of prefix indexes should be fully considered. Order the Key columns that are frequently queried at the front as much as possible, and try to choose data types like date or integer types such as int for these fields

Here's an example:

CREATE TABLE site_access (
site_id BIGINT DEFAULT '10',
city_code INT,
site_name VARCHAR(50),
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(site_id, city_code, site_name)
DISTRIBUTED BY HASH(site_id);

In the 'site_access' table, the prefix index consists of site_id (8 Bytes) + city_code (4 Bytes) + site_name (first 24 Bytes).

If the query condition only includes the columns site_id and city_code, as shown below, it can significantly reduce the number of data rows that need to be scanned:

select sum(pv) from site_access where site_id = 123 and city_code = 2;

If the query condition only includes the column site_id, as shown below, it can specifically target rows containing only site_id:

select sum(pv) from site_access where site_id = 123;

If the query condition only includes the column city_code, as shown below, all data rows need to be scanned, and the sorting effect is greatly reduced:

select sum(pv) from site_access where city_code = 2;

If the ratio of combined queries of site_id and city_code to queries of city_code alone is about the same, consider creating a synchronized materialized view to adjust the column order to enhance query performance. In this case, we place the city_code column first in the materialized view.

create materialized view site_access_city_code_mv asselect
city_code,
site_id,
site_name,
pv
from
site_access;

Here's a bad case:

CREATE TABLE site_access_bad
(
site_name VARCHAR(20),
site_id BIGINT DEFAULT '10',
city_code INT,
pv BIGINT DEFAULT '0'
)
PRIMARY KEY(site_id)
DISTRIBUTED BY HASH(site_id)
ORDER BY(site_id,city_code);

In the 'site_access_bad' table, the prefix index only includes site_name.

Like I mentioned at the start, data modeling comes with a lot of decisions. Fortunately, you'll no longer have to think about many of this thanks to this guide.

This sums up my advice for data modeling, but there's a lot more to share. Head on over to my third article in this series that will take a look at data ingestion with StarRocks. If you have additional questions, reach out to me on the StarRocks' Slack channel.

Read Part 3