Selecting a Proper Table Type to Boost Development and Queries

Table Storage Formats

In terms of data storage structure, DWS supports row-store tables and column-store tables.

Hybrid row- and column-store tables are essentially column-store tables.

• A row-store table stores each row as its smallest physical unit. In this format, all fields within a row are stored contiguously, including placeholders for null values. To find a complete row, you can use its unique row identifier (tid).

  An example of physical data storage in a row-store table:

  1 2 3 4

  | ID | Name | Age | Salary | Department | |-----|-------|-----|--------|------------| | 001 | Jack | 30 | 15000 | Technology Dept | ←Continuous storage of data in a row | 002 | John | 28 | 12000 | Marketing Dept |

• A column-store table stores data by columns instead of rows. The values of each column are stored contiguously, one after the other. Separate columns are managed as distinct storage units.

  An example of physical data storage in a column-store table:

  1 2 3

  Column: ID → | 001 | 002 | 003 |... Column: Name → | Jack | John | Tom |... Column: Age → | 30 | 28 | 35 |...

Table Types Supported by DWS

Currently, DWS supports row-store tables, column-store 2.0 tables, HStore Opt tables, tables with decoupled storage and compute, and hybrid row- and column-store tables.

• By default, DWS system tables and service tables are row-store tables.
• Column-store 2.0 tables, HStore Opt tables, tables with decoupled storage and compute, and hybrid row- and column-store tables are all variations of column-store tables. These tables are evolved based on data column storage and apply to different scenarios.

Row-Store Tables

In a row-store table, you can quickly locate a specific data row using an index, and each operation reads at least an entire row at once.

Such a table offers several benefits due to its design.

• Good point query performance: In a point query, the tuple of a row can be directly indexed.
• High update efficiency: Row storage excels at handling real-time imports and concurrent updates.

However, there are some disadvantages:

• High storage usage: Currently, DWS uses multiple versions to store data before and after updates. As a result, dirty data also occupies storage space.
• Read amplification: Accessing only one or two columns from a wide table often results in high read amplification because it requires reading the entire page and all its tuples.

Row-store tables perform efficiently in traditional OLTP systems where tasks like adding, deleting, updating, and querying data are performed frequently.

Example:

1
2
3
4
5
6

-- Default type. Set ORIENTATION to ROW or retain the default value.
CREATE TABLE table_row
(
    c1 INT,
    c2 TEXT
) WITH (ORIENTATION = ROW);

Column-Store Tables

Row-store tables may face performance issues during analysis and complex queries, particularly with large datasets. In such cases, you can consider using column-store tables.

Column-store tables offer these benefits:

• Efficient batch queries: Column storage performs better for analyzing specific columns, especially in large tables with many columns (for example, 1,000 columns).
• Reduced storage space: Column storage supports high-compression rates due to uniform data types within each column.

For OLAP scenarios, you can prioritize column-store tables. For details about the use cases of column-store 2.0 tables, HStore Opt tables, tables with decoupled storage and compute (column-store 3.0 tables), and hybrid row- and column-store tables, see the following parts.

Column-Store 2.0 Tables

A column-store 2.0 table begins as a basic column-store table. Each column's data is grouped into a compression unit (CU) based on size. Once compressed, this data is stored in a physical file. A helper table manages transaction visibility at the CU level.

Column-store 2.0 tables have been widely used in many applications.

Example:

1
2
3
4
5
6

-- Set ORIENTATION to COLUMN.
CREATE TABLE table_col
(
    c1 INT,
    c2 TEXT                                     
) WITH (ORIENTATION = COLUMN);

HStore Opt Tables

Column-store 2.0 tables outperform row-store tables for large complex queries but have limitations. They do not support concurrent updates or imports since transaction visibility relies on CUs.

HStore Opt tables address these limitations by managing increasing workload complexity and meeting the growing need for real-time data updates and imports. They combine the benefits of both row-store tables and column-store tables. Built on column-store tables 2.0, HStore Opt tables improve concurrent import performance. Real-time changes like additions, deletions, and updates are performed in a row-store table, and then a background thread merges this data into the CUs. This ensures data compression and analysis capabilities.

In recent years, increasing demands for real-time processing have made HStore Opt tables more popular than column-store 2.0 tables.

Example:

1
2
3
4
5
6

--Set ENABLE_HSTORE_OPT to TRUE for a column-store table.
CREATE TABLE table_hstore
(
    c1 INT,
    c2 TEXT                                     
) WITH (ORIENTATION = COLUMN, ENABLE_HSTORE_OPT = TRUE);

Tables with Decoupled Storage and Compute

HStore Opt tables can process requests in real time. However, column-store tables have the AppendOnly feature, so frequent concurrent inserts or updates can quickly increase CU file sizes. The EVS storage is costly.

Tables with decoupled storage and compute resolve the issues by storing CU files on OBS (a low-cost, distributed object storage from Huawei). By utilizing local EVS disks as a cache, these tables maintain high read/write performance while significantly reducing costs. OBS allows you to scale up storage without limits and change compute resources quickly.

The tables are ideal for workloads that prioritize storage costs and elasticity.

Example:

1
2
3
4
5
6
7

-- Select a cluster with decoupled storage and compute.
-- Set COLVERSION to 3.0 for a HStore table.
CREATE TABLE table_v3
(
    c1 INT,
    c2 TEXT                                     
) WITH (ORIENTATION = COLUMN, ENABLE_HSTORE_OPT = TRUE, COLVERSION = 3.0);

Hybrid Row- and Column-Store Tables

Column-store tables 2.0, HStore Opt tables, and tables with decoupled storage and compute excel at batch queries, real-time imports, and space management, but still fall short of matching the point query speed of row-store tables. Hybrid row- and column-store tables save data in both row and column formats. This approach uses extra space but boosts point query speed.

The two formats are independently maintained and synchronously updated. The query optimizer selects an optimal way to access data by analyzing the query path. It leverages column storage's built-in filtering to automatically manage I/O path and choose between the row and column formats for data access. Hybrid row- and column-store tables combine the strong compression and high throughput of column storage with the flexible local data access of row storage.

Such tables work well for scheduling data in mixed queries. They lower total I/O costs, boost data hit ratios, and enhance query speed.

Example:

1
2
3
4
5
6

-- Set STORAGE_MODE to MIX for an HStore table.
CREATE TABLE table_mix
(
    c1 INT,
    c2 TEXT                                     
) WITH (ORIENTATION = COLUMN, ENABLE_HSTORE_OPT = TRUE, STORAGE_MODE = MIX);

Table Type Selection

Row and column storage each have their own unique advantages and disadvantages. Row storage excels in transaction processing, whereas column storage performs better for big data analysis. Advancing technology has blurred the line between row and column storage, prompting modern databases to adopt hybrid approaches for diverse requirements. When selecting a storage method for your database, you need to consider your specific use cases, query types, and desired performance.

More Documents

• Using Hybrid Data Warehouses and HStore Tables
• Using Hybrid Row-Column Storage