Row Store vs Column Store in openGauss: Comparison, Advantages, Disadvantages and Experimental Evaluation

Many beginners first learn relational databases where data is stored in tables; this is a typical row‑based store (Row‑store) that writes each record as a contiguous block on disk. Some databases also support column‑based storage (Column‑store), where each column’s values are stored together.

Storage Comparison

In row‑store, the values of a record are stored next to each other, while in column‑store all values of a single attribute are stored contiguously. This leads to different trade‑offs for queries and modifications.

Write Performance

Row‑store writes the whole record in one operation, guaranteeing atomicity and integrity.

Column‑store must split a record into separate columns, resulting in more write operations and higher I/O latency.

Updates behave like writes, so row‑store generally has an advantage.

Read Performance

Row‑store reads the entire row; if only a few columns are needed, extra data must be filtered in memory.

Column‑store reads only the required columns, making projection queries very efficient.

Because each column contains homogeneous data types, parsing is simpler and faster, which benefits analytical workloads.

Column‑store also enables better compression, improving I/O performance.

Advantages & Disadvantages

Row Store

Column Store

Pros

Data kept together; INSERT/UPDATE easy.

Only accessed columns are read.

Projection highly efficient.

Any column can be indexed.

Cons

All columns are read even for selective queries.

Selected columns must be reassembled.

INSERT/UPDATE more complex.

Point queries less suitable.

Suitable Scenarios

Point queries, frequent INSERT/UPDATE, small tables.

Analytical (OLAP) queries with few columns.

Ad‑hoc queries where predicates are uncertain.

Experiment

openGauss supports hybrid storage; tables can be created with orientation=row or orientation=column . Two tables (custom1 row‑store, custom2 column‑store) were created and populated with 500,000 rows.

-- create row‑store table
CREATE TABLE custom1 (id integer, name varchar2(20));
-- create column‑store table
CREATE TABLE custom2 (id integer, name varchar2(20)) WITH (orientation = column);
-- insert data
INSERT INTO custom1 SELECT n, 'testtt' || n FROM generate_series(1,500000) n;
INSERT INTO custom2 SELECT * FROM custom1;

Size comparison shows the column‑store table occupies roughly 1/7 of the row‑store space. Insertion of a single new row is slightly slower for the column‑store (≈0.207 ms) than for the row‑store (≈0.135 ms).

-- explain analyze insert into row‑store
EXPLAIN ANALYZE INSERT INTO custom1 VALUES (1,'zhang3');
-- Total runtime: 0.135 ms

-- explain analyze insert into column‑store
EXPLAIN ANALYZE INSERT INTO custom2 VALUES (1,'zhang3');
-- Total runtime: 0.207 ms

After testing, the tables are dropped.

DROP TABLE custom1;
DROP TABLE custom2;

Selection Recommendations

If data is updated frequently, choose row‑store.

Frequent small inserts favor row‑store; bulk inserts favor column‑store.

Wide tables with many columns but queries touch few columns benefit from column‑store.

If queries usually involve most columns, row‑store is preferable.

Column‑store offers higher compression at the cost of extra CPU.

Notes

Column‑store tables have restrictions: they do not support arrays, generated columns, global temporary tables, foreign keys, and have a limited set of supported data types. Consult the database documentation before use.

Feel free to discuss, ask questions, or share your own test scenarios.