Lindorm:Best practices for wide table design

Data model

LindormTable is a row-oriented storage engine. Each row consists of primary key columns and non-primary key columns.

The following CREATE TABLE statement creates an orders table used as the running example throughout this guide:

CREATE TABLE orders (
    channel VARCHAR NOT NULL,       -- Payment channel: alipay, wechat, unionpay
    id      VARCHAR NOT NULL,       -- Order ID
    ts      TIMESTAMP NOT NULL,     -- Payment timestamp
    status  VARCHAR,                -- Order status
    location VARCHAR,               -- Payment location
    PRIMARY KEY (channel, id, ts)
) WITH (DYNAMIC_COLUMNS='true');    -- Allow columns not declared in the schema

After the table is created, the data model looks like this:

Primary key columns have three fixed properties:

• Immutable schema. You cannot add, remove, or reorder primary key columns, or change their data types after the table is created. Design the primary key carefully upfront.

• Unique row identifier. The combination of all primary key columns uniquely identifies each row.

• Clustered index. Data is physically sorted by the primary key columns in left-to-right order. LindormTable matches queries from the leftmost primary key column — similar to a composite index in MySQL.

Because data is clustered by primary key, queries that specify the leftmost column are efficient. If a range condition appears on any primary key column, LindormTable cannot use subsequent columns as an index filter and must scan all matching rows:

-- Scans all rows where channel = 'alipay' AND id > 'a0089',
-- then filters for ts = 1705786502068.
SELECT * FROM orders WHERE channel = 'alipay' AND id > 'a0089' AND ts = 1705786502068;

Omitting the leftmost column forces a full table scan even if other primary key columns are specified:

-- Full table scan — id is not the leftmost primary key column.
SELECT * FROM orders WHERE id = 'a0089';

If most queries filter by id, either make id the leftmost primary key column or create a secondary index on it.

Non-primary key columns behave differently from relational databases:

• Columns can be written without being declared in the schema in advance (see Dynamic columns and Wildcard columns).

• Each row must have at least one non-primary key column value — you cannot write a row containing only primary key values.

• Querying a non-primary key column without an index triggers a full table scan. LindormTable rejects such queries by default. Either narrow the scan with a primary key range or create an index:

-- Rejected by default — full table scan on a non-indexed column.
SELECT * FROM orders WHERE location = 'shanghai';

-- Accepted — narrows the scan to rows where channel = 'alipay'.
SELECT * FROM orders WHERE location = 'shanghai' AND channel = 'alipay';

Data distribution

LindormTable partitions table data into regions based on primary key ranges and distributes them across nodes. All regions are contiguous ranges that together cover the full primary key space.

When a region grows beyond 8 GB (default), or when LindormTable detects a hotspot in that region, it splits the region into two sub-regions and redistributes them across nodes for load balancing. For example, the row {alipay, a100999, 1705786502068} is initially stored in Region_3. If that region exceeds the threshold, it splits into two sub-regions placed on separate nodes.

Because rows with the same primary key prefix can span multiple regions (for example, alipay rows across Region_1, Region_2, and Region_3), LindormTable handles splitting automatically. You do not need to use PARTITION BY when creating a table.

Pre-split regions

By default, LindormTable starts with one region per table and splits automatically as data arrives. If you plan to write a large volume of data immediately after table creation — especially with Bulkload — pre-split the table to distribute the initial write load:

• SQL or ApsaraDB for HBase API writes: Set the initial region count to number of nodes × 4. Adjust based on your actual workload.

• Bulkload imports: Set the initial region count to size of data to import / 8. This distributes the imported data so that the initial regions do not need to split during import.

Make sure the pre-split ranges match your data distribution. If your key distribution does not align with the split points, data can cluster in a few regions even with many pre-splits.

For syntax details, see CREATE TABLE.

Single-value queries

A single-value query specifies equivalent conditions on all primary key columns, identifying one or more specific rows. Examples:

-- Single row
SELECT * FROM orders WHERE channel = 'alipay' AND id = 'a0001' AND ts = 1705786502000;

-- Multiple rows: ts has multiple values — returns multiple rows
SELECT * FROM orders WHERE channel = 'alipay' AND id = 'a0001' AND ts IN (1705786502000, 1705786502222, 1705786502333);

-- Cartesian product: 3 id × 3 ts = 9 rows scanned
SELECT * FROM orders WHERE channel = 'alipay' AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);

-- Cartesian product: 3 channel × 3 id × 3 ts = 27 rows scanned
SELECT * FROM orders WHERE channel IN ('alipay', 'wechat', 'unionpay') AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);

LindormTable returns all results of a single-value query at once. A large number of IN conditions compounds quickly (the rows scanned equal the Cartesian product of all IN sets), risking server memory exhaustion or garbage collection pauses.

The default limit is 2,000 rows per single-value query. Exceeding this returns:

Multi Get Plan query too many rows in one select

To raise this limit while keeping memory usage safe, contact technical support (DingTalk ID: s0s3eg3).

Range queries

A range query omits one or more primary key columns from its conditions. LindormTable streams results back without loading them all into memory, so range queries have no row count limit. You do not need LIMIT or cursor-based pagination.

-- Full table scan
SELECT * FROM orders;

-- Range scan: all rows where channel = 'alipay'
SELECT * FROM orders WHERE channel = 'alipay';

-- Range scan: all rows where channel = 'alipay' AND id = '1705786502001'
SELECT * FROM orders WHERE channel = 'alipay' AND id = '1705786502001';

ORDER BY

Data in LindormTable is physically sorted by the primary key, so SELECT results come back in primary key order even without an ORDER BY clause. If the query hits an index table, results come back in index key order instead.

For efficient sorting:

• Include the leftmost primary key column in ORDER BY. Sorting by non-leftmost or non-primary key columns requires a large in-memory sort.

• For descending sort (ORDER BY ... DESC), add the DESC keyword to the column definition at table creation time. This stores data in descending order and avoids a runtime reversal.

For details, see Use ORDER BY in large result sets.

COUNT queries

LindormTable is built on an LSM-Tree structure that stores multiple versions and delete markers. The row count is not stored in table metadata, so SELECT COUNT(*) requires scanning all data. On large tables this is slow.

For an estimated row count, see Count the number of rows in a table.

If a WHERE clause is specified, scan time depends on how many rows match. For example:

SELECT COUNT(*) FROM orders WHERE channel = 'alipay';

This scans only the rows where channel = 'alipay'.