Working with Databases-Planning a Storage Model
Tags: opengauss
Published: 2023-03-13 08:41:46

openGauss supports hybrid row storage and column storage. Each storage model applies to specific scenarios. Select an appropriate model when creating a table. Generally, openGauss is used for transactional processing databases. By default, row storage is used. Column storage is used only when complex queries in large data volume are performed.

Row-store stores tables to disk partitions by row, and column-store stores tables to disk partitions by column. By default, a row-store table is created. For details about differences between row storage and column storage, see Figure 1.

Figure 1 Differences between row storage and column storage


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oer7plq4d6115b2m49r8.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3cz5z2ppxjcq6yemu5b8.png)

In the preceding figure, the upper left part is a row-store table, and the upper right part shows how the row-store table is stored on a disk; the lower left part is a column-store table, and the lower right part shows how the column-store table is stored on a disk.

Both storage models have benefits and drawbacks.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/25joux97bmjmlohrrvlh.png)

Generally, if a table contains many columns (called a wide table) and its query involves only a few columns, column storage is recommended. Row storage is recommended if a table contains only a few columns and a query involves most of the fields.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kmlr1wdp5j0dinm35lsf.png)

**Row-Store Tables**
Row-store tables are created by default. In a row-store table, data is stored by row, that is, data in each row is stored continuously. Therefore, this storage model applies to scenarios where data needs to be updated frequently.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9pyizgc2m4lz3popkvuq.png)

**Column-Store Tables**
In a column-store table, data is stored by column, that is, data in each column is stored continuously. The I/O of data query in a single column is small, and column-store tables occupy less storage space than row-store tables. This storage model applies to scenarios where data is inserted in batches, less updated, and queried for statistical analysis. A column-store table cannot be used for point queries.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qonm366z55ramunvip1u.png)

**Selecting a Storage Model**
**·** Update frequency

If data is frequently updated, use a row-store table.

**·** Data insertion frequency

If a small amount of data is frequently inserted each time, use a row-store table. If a large amount of data is inserted at a time, use a column-store table.

**·** Number of columns

If a table is to contain many columns, use a column-store table.

**·** Number of columns to be queried

If only a small number of columns (less than 50% of the total) is queried each time, use a column-store table.

**·** Compression ratio

The compression ratio of a column-store table is higher than that of a row-store table. High compression ratio consumes more CPU resources.