Hybrid row-column store

Overview

SelectDB uses columnar storage by default, which excels at analytical queries such as aggregations and filtering. However, point queries that fetch a few rows by primary key must read and merge data column by column, generating random I/O that degrades performance.

The hybrid row-column store maintains a separate row-format replica. Point queries read complete rows directly from this replica, avoiding cross-column random I/O.

Ideal use cases:

High-concurrency primary key lookups, such as order details or user profiles.
SELECT * queries that return all or most columns of a row.

Important

Enabling the row store increases storage by approximately 2x–4x depending on column count and data characteristics. Enable it only for tables that require high-concurrency point queries.

Syntax

Configure the following parameters in the PROPERTIES clause of a CREATE TABLE statement:

Parameter	Description	Default
`store_row_column`	Enables the row store. Set to `true` to store an additional row-format copy of all columns.	`false`
`row_store_columns`	Comma-separated list of columns to include in the row store. Use this to reduce storage by storing only frequently queried columns. If not set, all columns are included when `store_row_column` is `true`.	Empty (all columns are stored if not specified)
`row_store_page_size`	Size in bytes of a row store data page. A page is the smallest I/O unit; reading a row requires at least one page read. Smaller values (for example, 4096 or 4 KB) improve point query performance but increase storage. Larger values (for example, 65536 or 64 KB) save storage but increase I/O per query.	`16384` (16 KB)

When the row store is used

The optimizer routes queries to the row store in two scenarios.

High-concurrency primary key point queries

The following conditions must be met:

The table uses the Unique Key model with Merge-on-Write (MOW), enabled by setting enable_unique_key_merge_on_write = true at table creation.
The row store is enabled by setting store_row_column = true or by specifying columns in row_store_columns.
The WHERE clause contains AND-connected equality conditions on all primary key columns. Example: SELECT * FROM tbl WHERE k1 = 1 AND k2 = 2.

If a query requests columns not in the row store (when using row_store_columns), those columns are fetched from columnar storage. Check the EXPLAIN plan for the SHORT-CIRCUIT flag to verify this optimization. High-Concurrency Point Queries.

**General SELECT * queries**

The following conditions must be met:

The table uses the Duplicate Key model or the Unique Key model (MOW table).
The row store is enabled for all columns (store_row_column = true).
The query is a SELECT * statement with ORDER BY and LIMIT clauses, which triggers late materialization.

Example:

SELECT * FROM tbl WHERE k < 10 ORDER BY k LIMIT 10;

Check the EXPLAIN plan for FETCH ROW STORE and OPT TWO PHASE markers to confirm row store usage.

Analytical queries such as aggregations and range scans continue to use columnar storage.

Examples

Note

Row store properties are set during table creation and cannot be modified later.

Enable full row store

Store all columns in row format. Ideal for high-concurrency SELECT * point queries.

CREATE TABLE user_profile (
    user_id BIGINT,
    user_name VARCHAR(64),
    age INT,
    city VARCHAR(32),
    register_date DATE
)
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES (
    "store_row_column" = "true",
    "enable_unique_key_merge_on_write" = "true"
);

Enable partial row store

Store only frequently queried columns in row format to balance performance and storage.

CREATE TABLE order_info (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(12, 2),
    status INT,
    create_time DATETIME,
    detail VARCHAR(1024)
)
UNIQUE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 32
PROPERTIES (
    "row_store_columns" = "user_id,amount,status,create_time",
    "enable_unique_key_merge_on_write" = "true"
);

Adjust row store page size

For wide tables, increase row_store_page_size to reduce page count and improve read efficiency.

CREATE TABLE wide_table (
    id BIGINT,
    col1 VARCHAR(256),
    col2 VARCHAR(256),
    col3 VARCHAR(256)
)
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 8
PROPERTIES (
    "store_row_column" = "true",
    "row_store_page_size" = "65536",
    "enable_unique_key_merge_on_write" = "true"
);

Usage notes

Enabling the row store increases storage by 2x–4x in typical scenarios. Test with your actual data to determine the exact overhead.
The row_store_page_size parameter also affects storage. Balance point query performance against storage cost based on your requirements.
To reduce storage overhead, use row_store_columns to store only the columns that need row-format access.
Row store properties are set during table creation and cannot be modified later.