This topic introduces columnstore tables and indexes in OceanBase Database and explains how they can be combined with rowstore tables and indexes. Using a payment transaction scenario, it provides practical examples of creating tables and indexes and running queries, helping you translate design decisions into concrete SQL statements.
Concepts of columnstore tables and indexes
Columnstore table: A columnstore table stores the base table's baseline data in columnstore format. Use
WITH COLUMN GROUP(each column)to create a pure columnstore table, orWITH COLUMN GROUP(all columns, each column)to create a hybrid rowstore-columnstore table. For more information, see Columnstore overview — Create columnstore and hybrid rowstore-columnstore tables.Columnstore index: A columnstore index stores the index table itself in columnstore format; it is not a special index type built on a columnstore table. Specify
WITH COLUMN GROUP(each column)when creating the index. You can likewise specify rowstore or columnstore column groups for the index table.Rowstore index: A rowstore index organizes the index table in rowstore format, either by default or through
WITH COLUMN GROUP(all columns). It is suitable for point lookups and can serve as a covering index to avoid base-table lookups.
All examples below use the same wide business table, payment_ledger (payment transactions). It contains fields such as order_id, pay_time, pay_channel, txn_status, and amount, making it easy to compare the read and write characteristics of different combinations.
Scenario combinations
Scenario |
Applicable workloads |
Strategy |
Advantages |
Disadvantages |
|---|---|---|---|---|
| Rowstore table + columnstore index | Primarily transactional workloads that also require some analytical queries, where the table is large and wide. | Create a columnstore index to improve analytical query performance. | Only selected columns are stored redundantly, in columnstore format. | Appropriate columns must be selected based on query patterns. In addition, writes must maintain the index table, resulting in slightly lower write performance. |
| Columnstore table + rowstore index | Primarily analytical workloads that also require efficient simple queries, such as point lookups. | Create a covering index to avoid base-table lookups. | Only selected columns are stored redundantly. Currently, online conversion of a columnstore table to a rowstore table or hybrid rowstore-columnstore table is not supported. Rowstore indexes can be added to optimize queries. | Appropriate columns must be selected based on query patterns. In addition, writes must maintain the index table, resulting in slightly lower write performance. |
| Hybrid rowstore-columnstore table | Workloads that need to support both transactional and analytical queries with diverse query patterns. | Store two redundant copies of baseline data and let the system generate an appropriate execution plan based on the query characteristics. By default, range scans use columnstore, while point lookups fall back to rowstore. | There is no need to analyze the workload's query patterns, and analytical query results are strongly consistent. | This approach consumes more disk space and may result in suboptimal execution plans. |
| Pure columnstore table (without indexes) | AP workloads | Use WITH COLUMN GROUP(each column) to create a columnstore table that stores data in columnar format. |
Efficient bulk imports and scans of a small number of columns in wide tables. | Slow point lookups by primary key or order number. |
Examples
Example 1: Rowstore table + columnstore index
The core payment workflow consists of INSERT operations and status lookups by order_id. Operational reports aggregate amount by channel and date using SUM and COUNT. The table has many columns, but analytical queries typically scan only a few of them.
Create the table and columnstore index:
CREATE TABLE payment_ledger (
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,
remark VARCHAR(256) DEFAULT NULL,
PRIMARY KEY (order_id, pay_time, txn_id)
)
PARTITION BY HASH(order_id) PARTITIONS 64
WITH COLUMN GROUP(all columns);
-- Columnstore index: Include frequently analyzed columns to avoid scanning every column in a wide table.
CREATE INDEX idx_pay_analytics
ON payment_ledger (pay_time, pay_channel, txn_status, amount)
WITH COLUMN GROUP(each column);
Typical TP query (uses the rowstore base table and primary key):
SELECT txn_id, txn_status, amount, pay_channel
FROM payment_ledger
WHERE order_id = 10086001
ORDER BY pay_time DESC
LIMIT 1;
Typical analytical query (the optimizer may select the columnstore index and scan only the indexed columns):
SELECT pay_channel,
SUM(amount) AS succ_amt,
COUNT(*) AS succ_cnt
FROM payment_ledger
WHERE pay_time >= '2025-01-01'
AND pay_time < '2025-02-01'
AND txn_status = 1
GROUP BY pay_channel;
Keeping the base table in rowstore format preserves high-concurrency write and point-lookup performance. The columnstore index handles analytical workloads, making this approach less disruptive to TP workloads than converting the entire table to pure columnstore. Index columns should cover the fields required for filtering and aggregation to avoid scanning wide rows during base-table lookups.
Example 2: Columnstore table + rowstore index (analytical workloads with occasional point lookups)
Transaction records are primarily loaded in batches and processed for reconciliation. Most daily SQL queries scan and aggregate entire partitions; only customer service, risk control, and similar use cases look up individual transactions by order_id.
Create the table and rowstore covering index:
CREATE TABLE payment_ledger_cs (
txn_id BIGINT NOT NULL,
order_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 (txn_id, pay_time)
)
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);
-- Rowstore index + STORING: Avoids fetching wide rows from the base table during point lookups
CREATE INDEX idx_order_lookup
ON payment_ledger_cs (order_id)
STORING (txn_status, amount, pay_time, pay_channel)
WITH COLUMN GROUP(all columns);
Analytical query (uses the columnstore base table):
SELECT merchant_id, SUM(amount) AS total
FROM payment_ledger_cs
WHERE pay_time >= '2025-01-01' AND txn_status = 1
GROUP BY merchant_id;
Point query (uses the rowstore index):
SELECT txn_status, amount, pay_time, pay_channel
FROM payment_ledger_cs
WHERE order_id = 10086001
ORDER BY pay_time DESC
LIMIT 5;
The each column setting on the base table preserves the benefits of columnstore scans for aggregate SQL, while the rowstore index handles point lookups.
Example 3: Pure columnstore table (large-scale analytics with limited TP support)
The table is used only for batch workloads such as end-of-day reconciliation and regulatory reporting. It does not require online lookups by order number and can omit a primary key to improve import performance. For more information, see Primary key design practices in AP scenarios.
CREATE TABLE payment_ledger_dw (
txn_id BIGINT,
order_id BIGINT,
pay_time DATETIME,
pay_channel VARCHAR(32),
txn_status TINYINT,
amount DECIMAL(18,2),
merchant_id BIGINT
)
WITH COLUMN GROUP(each column);
-- After a bulk import, we recommend running a major compaction and collecting statistics.
SELECT DATE(pay_time) AS dt,
COUNT(*) AS txn_cnt,
SUM(CASE WHEN txn_status = 1 THEN amount ELSE 0 END) AS succ_amt
FROM payment_ledger_dw
WHERE pay_time >= '2025-01-01' AND pay_time < '2025-02-01'
GROUP BY DATE(pay_time);
Example 4: Hybrid rowstore-columnstore table
The business requires a single payment_ledger_htap table to handle both payment queries and operational analytics and can accept duplicate baseline storage and higher major compaction costs.
CREATE TABLE payment_ledger_htap (
txn_id BIGINT NOT NULL,
order_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);
-- Point lookup (the optimizer favors the rowstore access path)
SELECT amount, txn_status FROM payment_ledger_htap
WHERE order_id = 10086001 AND txn_status = 1
ORDER BY pay_time DESC LIMIT 1;
-- Analytical query (the optimizer favors the columnstore access path)
SELECT pay_channel, SUM(amount) FROM payment_ledger_htap
WHERE pay_time >= '2025-01-15' AND txn_status = 1
GROUP BY pay_channel;
References
- Overview of tables — Index types
- Columnstore overview — Create columnstore and hybrid rowstore-columnstore tables
- Create a table in MySQL-compatible mode (including columnstore tables)
- Create a columnstore index in MySQL-compatible mode
- Data table design and query optimization in AP scenarios
- Columnstore FAQ
- Columnar storage