Best practices for wide table design

Primary key

A row's columns are divided into primary key and non-primary key columns. A primary key can be composed of multiple columns and has the following characteristics:

• Immutable schema: The primary key is determined when you create a wide table. After table creation, you cannot add, delete, or reorder primary key columns, nor can you change their data types. Therefore, you must plan your primary key before creating the table. Primary key design is critical to query performance.

• Uniqueness: All primary key columns combine to form a row key, which is unique within a table. The complete primary key uniquely identifies a row in the table.

• The primary key is the clustered index: In a Lindorm wide table, data is stored in the order of the primary key. For example, in the sample table orders, rows with the same value in the channel column are stored together. If the values in the channel column are the same, the rows are then sorted by the values in the id column. If the values in the id column are also the same, the rows are sorted by the values in the ts column.

  This clustered index property enables more efficient queries. Similar to MySQL, Lindorm follows the left-prefix matching principle. You should design your primary key with your most common equality-based query conditions as the leftmost columns to narrow down the search range. If a query includes a range condition on a primary key column, the left-prefix match stops at that point. Subsequent primary key columns with equality checks cannot leverage the sorted storage order and will require extensive filtering.

  Consider the following query on the orders table:

  -- This query reads all data where channel='alipay' and id > 'a0089' to find rows matching ts=1705786502068.
  SELECT * FROM orders WHERE channel='alipay' AND id > 'a0089' AND ts = 1705786502068;  

  If you do not specify the leftmost primary key column in a query, it results in a full table scan, even if other primary key columns are specified.

  -- This is a full table scan query.
  SELECT * FROM orders WHERE id = 'a0089';

  In the statement above, the query condition specifies a primary key id, but this key is in the middle of the composite primary key. Therefore, the system still needs to scan all rows to find the row that matches id= 'a0089', which is very inefficient. If this is a main query, we recommend that you place the id column as the leftmost column of the primary key when you design the table, or create an index table for the id column to speed up queries.

Non-primary key

Lindorm allows you to dynamically define non-primary key columns. This means you do not need to define them in the schema. You can write to any non-primary key column by name, similar to how HBase works. For more information, see Dynamic columns. Lindorm also supports using wildcards to define non-primary key columns. For example, if you define *_str as a STRING type, you can write to any column with a name ending in _str. For more information, see Wildcard columns.

Lindorm supports updates to non-primary key columns. When writing data, you do not need to specify all non-primary key columns, but you must specify at least one. Lindorm does not support writing a row that contains only primary key values. Because data in a wide table is sorted by the primary key, using a non-primary key column as a filter without an index causes a full table scan. Lindorm rejects such queries by default. To query non-primary key columns efficiently, limit the primary key range or create a secondary index on the non-primary key column.

-- This request scans the entire table because it filters only on a non-primary key column without a primary key constraint.
SELECT * FROM table WHERE location = 'shanghai'; 

-- This query scans all rows where channel='alipay' and then filters for location = 'shanghai'.
SELECT * FROM table WHERE location = 'shanghai' and channel='alipay'; 

Pre-split region

Since Lindorm supports automatic partitioning, you do not need to define partition ranges when you create a table. The system automatically splits regions as data is written. However, if you have specific data distribution requirements, you can specify a number of pre-split regions at table creation. The system then distributes these regions across multiple machines to spread out read and write loads from the beginning. Note that this initial number of regions is just a starting point; the system still performs region splitting as data grows. For information on how to specify pre-split regions, see CREATE TABLE.

If you expect a high initial write volume or plan to use Bulkload to import data, we recommend setting up appropriate pre-split regions to prevent a single node from being overloaded at the start.

• If you write data using SQL or the HBase API, you can set the number of pre-split regions to Number of nodes × 4. More initial partitions are not always better, so you should set the number based on your specific needs.

• If you use Bulkload to import data, we recommend setting the number of pre-split regions to Data size (GB) / 8. This helps distribute the imported data evenly across regions and prevents immediate region splitting.

• If you know the distribution characteristics of your primary key, you can specify split keys based on your data. For more information, see Set start keys for multiple regions.

Ensure that the ranges of your pre-split regions match your data write patterns. Otherwise, data skew can still occur, where writes are concentrated in a few regions, leading to poor performance despite having many pre-split regions.

Hotspot

In a distributed system, the goal is to distribute requests evenly across all regions so the system can scale out and handle more data and traffic. A hotspot breaks this model and can turn a single node into a performance bottleneck.

• Single-row hotspot: Frequently reading or writing a single row can create a single-row hotspot. Since requests for the same row are always routed to the same node, that node's capacity becomes the upper limit for the entire system's performance. Scaling out does not solve this problem; you can only scale up the single node. Therefore, design your wide table to avoid single-row hotspots.

• Ranged hotspot: A ranged hotspot can occur if you frequently read from and write to a very small range of data. Because Lindorm partitions data based on primary key ranges, data within a small range is likely to be stored in the same region. As a result, all requests might be sent to the same server. Although Lindorm provides a hotspot self-healing feature that can identify ranged hotspots and distribute the data from a hotspot region to different nodes by automatically splitting the region, you can still select a more distributed primary key to avoid requests that target a small range. For example, in the sample table orders, you can apply a hash function to the id column and store the result to prevent hotspot issues as much as possible.

• Incremental write hotspot: If your primary key is based on an incremental value, new writes will always target the end of your key space. Because Lindorm partitions data by key range, even after a region splits, all subsequent writes will go to only one of the new regions (the one covering the end of the range), preventing the load from being distributed. For example, in the orders table, the id is incremental. If there is a high volume of orders for channel=alipay, even if Region_3 splits into Region_3_a and Region_3_b, only Region_3_b will handle the new write traffic. The system cannot resolve this issue. Therefore, avoid incremental primary keys, and never use an incremental value as the first column of your primary key.

  Important

  If you are migrating data from a system like Cassandra that uses hash-based partitioning, be aware of this difference. In a hash-partitioned system, an incremental key is hashed and distributed evenly. If you migrate that same schema to Lindorm, where the incremental key is used directly for range partitioning, it will create a severe hotspot.

Point queries and range queries

Lindorm wide tables have two common query types: point queries and range queries.

SELECT * FROM orders WHERE channel='alipay' AND id='a0001' AND ts=1705786502000;
SELECT * FROM orders WHERE channel='alipay' AND id='a0001' AND ts IN (1705786502000, 1705786502222, 1705786502333);
SELECT * FROM orders WHERE channel='alipay' AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);
SELECT * FROM orders WHERE channel IN ('alipay', 'wechat', 'unionpay') AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);

SELECT * FROM orders;
SELECT * FROM orders WHERE channel='alipay';
SELECT * FROM orders WHERE channel='alipay' AND id='1705786502001';
SELECT * FROM orders WHERE channel='alipay' AND id IN ('a0001', 'a0002', 'a0003');
SELECT * FROM orders WHERE channel IN ('alipay', 'wechat', 'unionpay') AND id IN ('a0001', 'a0002', 'a0003');

ORDER BY queries

Data in a wide table is sorted by its primary key. Without an ORDER BY clause, a SELECT statement returns results in primary key order. However, if a query uses an index, the results are returned in the order of the index key by default. The order of columns in the index key determines the return order.

The columns in an ORDER BY clause follow the left-prefix matching principle. For efficient sorting, you cannot skip the leftmost primary key fields and use a middle primary key field in the ORDER BY statement. When you use a middle primary key or a non-primary key to sort data in an ORDER BY clause, a large number of sorting computations are involved. Therefore, we recommend that you place the primary key that you want to sort by on the far left, or create a secondary index for the non-primary key that requires sorting. In addition, if you want to sort query results in descending order (ORDER BY DESC), we recommend that you add the DESC keyword when you create the table. This ensures that data is stored in descending order for optimal query performance. For more information about using ORDER BY, see How to use ORDER BY in large result sets.

Count queries

Lindorm is a NoSQL database based on the LSM-Tree storage architecture. Because it stores multiple versions of data and uses delete markers for deletions, the underlying storage does not maintain a real-time row count in the metadata. Obtaining an exact row count requires a full table scan, which can be time-consuming for large tables. If you only need an estimated row count, see How to count the number of rows in a table.

If a COUNT request includes conditions, the query time depends on the amount of table data that must be scanned to meet these conditions. For example, SELECT count(*) FROM table WHERE channel = 'alipay' reads all rows that satisfy channel = 'alipay' to perform the count.