Types of data storage in Greenplum®

Greenplum® supports two types of table data storage:

• Heap storage.

  This type of table supports row-oriented data storage only. Such tables work best for online transaction processing (OLTP), when table data is often modified by INSERT, UPDATE, and DELETE operations.

  All tables created in Greenplum® use this data storage type by default.

• Append-optimized storage.

  Tables of this type support both row and column-oriented storage. Tables for row and column-oriented storage are called append-optimized (AO) and append-optimized column-oriented (AOCO) tables, respectively. Both such table types are implemented based on auxiliary heap tables. AO and AOCO tables store data in segment files, which are made up of blocks of arbitrary size.

  AO and AOCO tables are good for online analytical processing (OLAP) of large data arrays, when data is loaded in large batches and mainly accessed by read queries.

  Column-oriented data storage reduces read and write overhead significantly when a query only accesses a small number of columns from the entire table field set. Column-oriented tables also get compressed better. When working with Greenplum®, we recommend using column-oriented tables with standard compression of level 1 as an analytical DBMS.

  Yandex Cloud developed Yezzey for AO and AOCO tables, which allows you to export data from disks within the Managed Service for Greenplum® cluster to a cold storage in Yandex Object Storage. The data is securely stored in a service bucket. To learn more about using cold storage, see Exporting Greenplum® data to a cold storage in Yandex Object Storage.

Creating column-oriented tablesCreating column-oriented tables

To create a column-oriented table that supports compression, provide the parameters referring to the appropriate settings in the CREATE TABLE command:

CREATE TABLE tableName (...)
WITH (
    appendonly = true,
    orientation = column,
    compresstype = zstd,
    compresslevel = 1
);

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.