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

Many beginners first encounter databases as relational tables where each row represents a record, which is a typical row‑based (row store) storage model that writes whole rows to disk.

Some databases also support column‑based (column store) storage, where each column’s values are stored contiguously on disk.

Storage Method Comparison

The diagram below illustrates the physical layout of row versus column storage.

In row storage, the attribute values of a record are placed next to each other, followed by the next record’s values. In column storage, all values of a single attribute are stored together, giving each column its own contiguous space.

Write‑time comparison:

Row store writes a whole row in a single operation, relying on the OS file system to guarantee atomicity and data integrity.

Column store must split a row into separate columns, resulting in more write operations and additional disk‑head movement, so write performance is generally slower.

Data modification is effectively a write operation, so row store also has an advantage here.

Read‑time comparison:

Row store reads an entire row even if only a few columns are needed, leading to redundant data that must be filtered in memory.

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

Because each column stores homogeneous data types, parsing is simpler and faster than the mixed‑type rows, which reduces CPU overhead for large‑scale analysis.

Uniform column data also enables better compression, improving I/O performance.

Pros and Cons

The choice of storage type should be driven by the business workload. Row stores excel at frequent inserts/updates and point queries, while column stores shine for analytical (OLAP) queries that touch few columns of wide tables.

Experimental Evaluation

openGauss supports hybrid storage; the following experiment creates a row‑store table custom1 and a column‑store table custom2 , each populated with 500,000 rows.

openGauss=# create table custom1 (id integer, name varchar2(20));
openGauss=# create table custom2 (id integer, name varchar2(20)) with (orientation = column);
openGauss=# insert into custom1 select n,'testtt'||n from generate_series(1,500000) n;
INSERT 0 500000
openGauss=# insert into custom2 select * from custom1;
INSERT 0 500000

Querying the system catalog shows that the column‑store table occupies roughly one‑seventh the space of the row‑store table.

public | custom1 | table | omm | 24 MB   | {orientation=row,compression=no}
public | custom2 | table | omm | 3104 kB | {orientation=column,compression=low}

Insert performance was measured with EXPLAIN ANALYZE :

-- Row store insert
INSERT 0 1
Total runtime: 0.135 ms
-- Column store insert
INSERT 0 1
Total runtime: 0.207 ms

Finally, the test tables were dropped.

openGauss=# drop table custom1;
openGauss=# drop table custom2;

Selection Recommendations

Frequent updates: prefer row store.

Frequent small inserts: row store; bulk inserts: column store.

Wide tables with few queried columns: column store.

When most columns are needed in queries: row store.

Higher compression needs: column store (at the cost of extra CPU).

Precautions

Column stores have limitations: they do not support arrays, generated columns, global temporary tables, foreign keys, and have a reduced set of supported data types. Always consult the database documentation before use.