Use columnar storage and hybrid row-column storage

Storage formats in OceanBase Database

OceanBase Database uses an LSM-tree architecture. User data is broadly divided into baseline data and incremental data:

• Baseline data: Generated after a major compaction using a global version number selected periodically or on demand by the tenant. The baseline data of all replicas at the same version is physically identical. Depending on the storage mode specified during table creation, baseline data can exist in three forms: row-based, columnar, or hybrid row-column.
• Incremental data: All writes made after the latest baseline data (either data in the MemTable or minor-compacted SSTables) are considered incremental data. Each replica independently maintains its own copy of incremental data, which contains multiple versions. Incremental data is always stored in a row-based format.

Based on baseline and incremental data, row-based, columnar, and hybrid row-column storage differ in baseline organization, write and major compaction paths, and query access characteristics. The following table compares the three formats from dimensions such as storage and write characteristics, query characteristics, typical scenarios, and recommended approach, facilitating selection based on business workload.

When not to enable columnar storage based solely on data volume: When the data volume is large but mainly involves high-concurrency point queries and short-transaction updates, and there is a lack of need for large-scale scanning and aggregation, a row-based storage format is typically more appropriate. Forcing the use of a pure columnar storage table often results in suboptimal query performance, as it relies heavily on designs such as row-based indexes.

For the overall architecture and core features of columnar storage, see Columnar storage.

OceanBase Database creates tables in row-based storage by default. For AP workloads requiring columnar storage or hybrid row-column storage, you can convert the storage format using the WITH COLUMN GROUP clause in the table creation statement, the tenant-level parameter default_table_store_format, or by adding or dropping column groups via a ALTER TABLE statement after table creation.

How to create a columnar storage table or a hybrid row-column storage table

Method 1: Specify the storage format during table creation

Explicitly specify the storage format using the WITH COLUMN GROUP clause in the CREATE TABLE statement:

When the tenant's default_table_store_format is not row, you must explicitly include WITH COLUMN GROUP(all columns) to create a row-based table.

Method 2: Set the tenant parameter for the default storage format

You can specify the default format for table creation statements without WITH COLUMN GROUP in a tenant using the tenant-level parameter default_table_store_format:

• row (default): Creates a row-based table by default.
• column: Automatically adds WITH COLUMN GROUP(each column) during table creation, creating a pure columnar storage table by default.
• compound: Automatically adds WITH COLUMN GROUP(all columns, each column) during table creation, creating a hybrid row-column storage table by default.

Example:

-- Set the default storage format for tables in the tenant to columnar storage
ALTER SYSTEM SET default_table_store_format = "column";

-- Set the default storage format for tables in the tenant to hybrid row-column storage.
ALTER SYSTEM SET default_table_store_format = "compound";

This parameter only takes effect for statements that do not explicitly specify WITH COLUMN GROUP during table creation and does not apply to index tables.

Conversion between row-based and columnar storage

After a table is created, you can convert its storage format by adding or dropping column groups using an ALTER TABLE statement (for syntax, see Modify a table (MySQL-compatible mode)):

-- Example: Convert a row-based payment transaction table to a pure columnar storage table
CREATE TABLE payment_ledger (
  txn_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  pay_time DATETIME NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  PRIMARY KEY (txn_id, pay_time)
) WITH COLUMN GROUP(all columns);

ALTER TABLE payment_ledger ADD COLUMN GROUP(each column);
ALTER TABLE payment_ledger DROP COLUMN GROUP(all columns);

-- Pure columnar storage -> Hybrid row-column storage
ALTER TABLE payment_ledger ADD COLUMN GROUP(all columns);

After conversion or bulk loading, it is recommended to perform a major compaction and collect statistics. Then, use EXPLAIN to confirm whether the query analysis uses the columnar storage path.

Example

The following example uses the payment transaction business scenario to illustrate the complete process from selection to table creation and verification.

Scenario analysis

The following compares three table creation methods using the same set of business fields (order number, payment time, channel, status, amount, etc.) to help understand the differences of WITH COLUMN GROUP:

Table creation

Pure columnar storage table

The baseline is organized only by columns, suitable for aggregate SQL statements that scan only a few columns (such as summing payment amounts by channel):

CREATE TABLE payment_ledger_cs (
    txn_id       BIGINT        NOT NULL COMMENT 'Transaction ID',
    order_id     BIGINT        NOT NULL COMMENT 'Order ID',
    user_id      BIGINT        NOT NULL,
    pay_time     DATETIME      NOT NULL,
    pay_channel  VARCHAR(32)   NOT NULL COMMENT 'Payment Channel',
    txn_status   TINYINT       NOT NULL COMMENT '0 Pending Payment 1 Successful 2 Failed',
    amount       DECIMAL(18,2) NOT NULL,
    merchant_id  BIGINT        NOT NULL
)
PARTITION BY RANGE COLUMNS(pay_time) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00')
)
WITH COLUMN GROUP(each column);

WITH COLUMN GROUP(each column) indicates the baseline is stored by columns; when queries read only a few columns like pay_channel and amount, IO and vectorized execution are more efficient.

Query (after import, major compaction, and statistics collection):

-- Sum of Successful Payment Amounts by Channel on the Current Day
SELECT pay_channel, SUM(amount) AS total_amt, COUNT(*) AS cnt
FROM payment_ledger_cs
WHERE pay_time >= '2025-01-15 00:00:00'
  AND pay_time <  '2025-01-16 00:00:00'
  AND txn_status = 1
GROUP BY pay_channel;

If the business occasionally requires point queries by order_id, a pure columnar storage table is usually not advantageous. You can create a separate row-store index, see Columnar storage tables and indexes.

Hybrid row-columnar storage table

The baseline retains both row-store and columnar storage organization, allowing the same table to handle both point queries and analysis:

CREATE TABLE payment_ledger_htap (
    txn_id       BIGINT       NOT NULL,
    order_id     BIGINT       NOT NULL,
    user_id      BIGINT       NOT NULL,
    pay_time     DATETIME     NOT NULL,
    pay_channel  VARCHAR(32)  NOT NULL,
    txn_status   TINYINT      NOT NULL,
    amount       DECIMAL(18,2) NOT NULL,
    merchant_id  BIGINT       NOT NULL,
    PRIMARY KEY (order_id, pay_time, txn_id)
)
PARTITION BY HASH(order_id) PARTITIONS 64
WITH COLUMN GROUP(all columns, each column);

WITH COLUMN GROUP(all columns, each column) declares both rowstore and columnstore column groups, with two baseline redundancies. The optimizer can choose the rowstore or columnstore path based on cost for point queries or wide-range scans.

Query:

-- TP: Query the last successful payment by order number (prefer row-based storage path)
SELECT txn_id, pay_time, amount, pay_channel
FROM payment_ledger_htap
WHERE order_id = 10086001 AND txn_status = 1
ORDER BY pay_time DESC
LIMIT 1;

-- AP: Total payments in the last 7 days, aggregated by merchant (tends to use columnar storage)
SELECT merchant_id, DATE(pay_time) AS dt, SUM(amount) AS daily_amt
FROM payment_ledger_htap
WHERE pay_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  AND txn_status = 1
GROUP BY merchant_id, DATE(pay_time);

Verification

After writing sample data and completing major compactions and statistics collection, execute EXPLAIN for analytical SQL queries to check if columnstore-related operators such as COLUMN TABLE FULL SCAN appear (subject to the execution plan output of the current version):

EXPLAIN SELECT pay_channel, SUM(amount) FROM payment_ledger_cs
WHERE pay_time >= '2025-01-01' GROUP BY pay_channel;

Considerations when using columnstore tables

• Major compaction and statistics: After bulk import or when large-scale data is ready, it is recommended to perform a major compaction and collect statistics to improve read performance and help the optimizer generate effective execution plans. For columnstore tables with large data volumes, the time required for a major compaction may be relatively longer than for rowstore tables.
• Updates and major compaction: If a columnstore table has a large number of updates and the major compaction is not performed promptly, query performance will be affected. Performing a major compaction after a bulk import can result in better query performance.
• Columnstore and columnstore replicas: The columnstore tables described in this topic refer to the storage format on full-featured (F/R) replicas. If you use independent columnstore replicas (C replicas) to host applications (APs), refer to AP deployment overview.

References

• Columnstore
• default_table_store_format
• Create a table (MySQL-compatible mode)
• Modify a table (MySQL-compatible mode)
• Column skip index attribute (MySQL-compatible mode)
• Practice of columnstore tables and indexes