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 by a major compaction based on a global version selected periodically or on demand by the tenant. Baseline data at the same version is physically identical across all replicas. Depending on the storage format specified when a table is created, baseline data can be stored in three formats: rowstore, columnstore, or hybrid rowstore-columnstore.
- Incremental data: All writes made after the latest baseline version, including data in MemTables and data flushed to SSTables, are incremental data. Each replica maintains incremental data independently, and the data contains multiple versions. Incremental data is always stored in rowstore format.
Given this separation of baseline and incremental data, rowstore, columnstore, and hybrid rowstore-columnstore formats differ in how they organize baseline data, process writes and major compactions, and access data during queries. The following table compares the three formats in terms of storage and write characteristics, query characteristics, typical scenarios, and recommended approaches to help you choose a format based on your workload.
Storage format |
Storage and write characteristics |
Query characteristics |
Typical scenarios |
Recommended approach and description |
|---|---|---|---|---|
| Rowstore | Baseline data is stored by row, with all columns stored together in each microblock. Incremental data is also stored by row. Writes go only to rowstore MemTables and are later flushed to disk. | Suitable for point lookups and high-concurrency TP workloads. | Pure OLTP workloads dominated by point lookups, with no strong analytical requirements. | Tables are rowstore by default. If the tenant's default storage format has been changed to columnstore or hybrid rowstore-columnstore, explicitly specify WITH COLUMN GROUP(all columns) to create a rowstore table. |
| Columnstore | In the columnstore baseline, each column has its own SSTable, and the SSTables for multiple columns form a virtual SSTable. During writes and minor compactions, incremental data still follows the rowstore path. During major compaction, incremental data is merged with the existing columnstore baseline to generate a new columnstore baseline. | Pure columnstore tables generally do not perform point lookups as efficiently as rowstore tables, but rowstore indexes can improve these lookups. Analytical queries benefit because only the referenced columns need to be scanned. | Primarily OLAP workloads. | Specify WITH COLUMN GROUP(each column) in the CREATE TABLE statement, or set the tenant-level parameter default_table_store_format to column. For combinations such as a rowstore base table with a columnstore index, see Best practices for columnstore tables and indexes. |
| Hybrid rowstore-columnstore | Rowstore and columnstore baselines coexist as two redundant copies. Incremental data is stored by row. Writes go to rowstore MemTables and are later flushed to disk. During major compaction, both the rowstore and columnstore baselines are maintained. | Point lookups use the rowstore path. Range scans are generally better suited to the columnstore path, but the optimizer makes the final choice based on cost. | HTAP workloads in which the same table must serve both point lookups and analytical queries through a unified storage organization. | Specify WITH COLUMN GROUP(all columns, each column), or set the tenant's default format to compound. By default, range scans use the columnstore path, while point lookups fall back to the rowstore path. The trade-offs are two copies of baseline data and higher storage and major compaction resource consumption. You can tune access paths by using statistics and hints. |
When data volume alone does not justify columnstore: If a workload has a large data volume but is still dominated by high-concurrency point lookups and short-transaction updates, with no strong need for large-scale scans or aggregations, rowstore is usually the better default. A pure columnstore table often makes point lookups less efficient and requires additional designs such as rowstore indexes.
For an overview of the columnstore architecture and its core features, see Columnar storage.
OceanBase Database creates rowstore tables by default. For AP workloads that require columnstore or hybrid rowstore-columnstore storage, specify the format by using the WITH COLUMN GROUP clause when creating a table, setting the tenant-level parameter default_table_store_format, or adding or dropping column groups with an ALTER TABLE statement after table creation.
Create columnstore and hybrid rowstore-columnstore tables
Method 1: Specify the storage format when creating a table
Explicitly specify the storage format using the WITH COLUMN GROUP clause in the CREATE TABLE statement:
Storage format |
WITH COLUMN GROUP syntax |
|---|---|
| Rowstore | WITH COLUMN GROUP(all columns) |
| Columnstore | WITH COLUMN GROUP(each column) |
| Hybrid rowstore-columnstore | WITH COLUMN GROUP(all columns, each column) |
When the tenant's default_table_store_format is not row, you must explicitly include WITH COLUMN GROUP(all columns) to create a rowstore table.
Method 2: Set the tenant-level default storage format
Use the tenant-level parameter default_table_store_format to specify the default format for CREATE TABLE statements that omit WITH COLUMN GROUP in the tenant:
row(default): Creates rowstore tables by default.column: Automatically appliesWITH COLUMN GROUP(each column)and creates pure columnstore tables by default.compound: Automatically appliesWITH COLUMN GROUP(all columns, each column)and creates hybrid rowstore-columnstore tables by default.
Example:
-- Set the tenant's default table storage format to columnstore
ALTER SYSTEM SET default_table_store_format = "column";
-- Set the tenant's default table storage format to hybrid rowstore-columnstore
ALTER SYSTEM SET default_table_store_format = "compound";
This parameter affects only CREATE TABLE statements that do not explicitly specify WITH COLUMN GROUP. It does not apply to index tables.
Convert between rowstore and columnstore
After creating a table, you can convert its storage format by adding or dropping column groups with an ALTER TABLE statement. For the syntax, see Change a table (MySQL-compatible mode).
-- Convert a rowstore payment transaction table to a pure columnstore 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 columnstore -> hybrid rowstore-columnstore
ALTER TABLE payment_ledger ADD COLUMN GROUP(all columns);
After conversion or a bulk load, we recommend running a major compaction and collecting statistics. Then, use EXPLAIN to verify whether analytical queries use the columnstore path.
Example
The following example uses a payment transaction workload to illustrate the complete process from selecting a format to creating and verifying a table.
Scenario analysis
The following table compares three ways to create a table with the same set of business fields, such as order number, payment time, channel, status, and amount, to illustrate the differences between the WITH COLUMN GROUP options:
Workload characteristics |
Recommended format |
|---|---|
| The workload mainly involves large-scale scans and aggregations, such as aggregating amounts and calculating success rates by day or channel, with infrequent point lookups by order number. | Pure columnstore table: WITH COLUMN GROUP(each column) |
The workload requires both frequent point lookups of individual transactions by order_id and analytical reports on the entire table. |
Hybrid rowstore-columnstore table: WITH COLUMN GROUP(all columns, each column) |
| The workload primarily involves individual payment writes and status updates, with limited analytical requirements. | Rowstore table (the default, or explicitly specify WITH COLUMN GROUP(all columns)) |
Table creation
Pure columnstore table
Baseline data is organized only by column, which is suitable for aggregate SQL statements that scan only a few columns, such as those that sum 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 that baseline data is stored by column. When a query reads only a few columns, such as pay_channel and amount, it can benefit from more efficient I/O and vectorized execution.
Query (after data import, major compaction, and statistics collection):
-- Aggregate the current day's successful payment amounts by channel
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 workload occasionally requires point lookups by order_id, a pure columnstore table is generally not ideal. You can create a separate rowstore index. For more information, see Best practices for columnstore tables and indexes.
Hybrid rowstore-columnstore table
Baseline data is stored in both rowstore and columnstore formats, allowing the same table to handle both point lookups and analytical queries:
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, creating two copies of baseline data. Based on cost, the optimizer can select the rowstore path for point lookups or the columnstore path for large-scale scans.
Query:
-- TP: Query the most recent successful payment by order number (favors the rowstore 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: Aggregate payment amounts over the last seven days by merchant (favors the columnstore path)
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 loading sample data, running a major compaction, and collecting statistics, run EXPLAIN on analytical SQL statements to check whether columnstore-related operators such as COLUMN TABLE FULL SCAN appear. The specific operators depend on 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 a bulk import or after loading a large volume of data, we recommend running a major compaction and collecting statistics to improve read performance and help the optimizer generate effective execution plans. Major compactions may take longer for large columnstore tables than for rowstore tables.
- Updates and major compaction: If a columnstore table receives many updates and a major compaction is not run promptly, query performance may degrade. Running a major compaction after a bulk import can improve query performance.
- Columnstore tables and columnstore replicas: The columnstore tables described in this topic use the columnstore format on full (F) or read-only (R) replicas. If you use dedicated columnstore replicas (C replicas) to host AP workloads, see Overview of OceanBase AP deployment.