Types of data storage in Greenplum®

Greenplum® offers 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 updated with INSERT, UPDATE, or 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.

  For AO and AOCO tables in Yandex MPP Analytics for PostgreSQL clusters, hybrid storage is available: with the Yezzey extension, you can move some of your tables from the cluster storage to a cold storage, and vice versa. Cold storage is a convenient option if you need to store your table data for a long time without using it much. This will make data storage less costly.

Creating column-oriented tablesCreating column-oriented tables

To create a column-oriented table with compression, provide the parameters with the appropriate settings in the CREATE TABLE command:

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

Use casesUse cases

• Migrating data from Yandex Managed Service for MySQL® to Yandex MPP Analytics for PostgreSQL using Yandex Data Transfer

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