Table storage types

Andrey Aksenov

Contents

How to choose a table storage type
Create a table
Check a table storage type
- psql meta-commands
- SQL commands
Alter a table storage type

Greengage DB offers several table storage models optimized for specific workload types, such as OLTP or OLAP. When creating a table, you can select the most suitable storage model based on your workload requirements. This topic explains how to choose the appropriate table storage model for your use case and how to specify that model during table creation.

Greengage DB supports the following table types:

Heap

Heap tables are recommended for OLTP workloads, especially when data is frequently modified after initial loading. They are most suitable for operations involving single-row queries, such as INSERT, UPDATE, or DELETE. Heap tables support row-oriented data storage only.
Append-optimized (AO)

Append-optimized tables are recommended for OLAP workloads. They are particularly suited for bulk data loading and use cases where data is rarely modified after initial loading. These tables are most effective when read-only queries are the most common operation. Unlike heap tables, which are row-oriented, append-optimized tables support two forms of data orientation:
- Row-oriented
  
  This storage model is recommended when most or all table columns are retrieved.
- Column-oriented
  
  This model is suitable for data processing involving a small number of columns. It is also useful for tables that require regular updates to a small subset of columns without affecting the rest.

The following table outlines the recommended characteristics and limitations of different storage models.

Heap AO: row-oriented AO: column-oriented

	Heap	AO: row-oriented	AO: column-oriented
Data orientation	Row	Row	Column
Table size	Small tables, such as dimension tables	Large denormalized fact tables	Large denormalized fact tables
Number of columns	Relatively small	Many columns (dozens or more)	Many columns (dozens or more)
Unique values	`PRIMARY KEY`, `UNIQUE`	Not supported	Not supported
Compression	Not supported	`ZSTD`, `ZLIB` (table level)	`ZSTD`, `ZLIB`, `RLE_TYPE` (table and column level)

Data orientation

Row

Column

Table size

Small tables, such as dimension tables

Large denormalized fact tables

Number of columns

Relatively small

Many columns (dozens or more)

Unique values

PRIMARY KEY, UNIQUE

Not supported

Compression

Not supported

ZSTD, ZLIB (table level)

ZSTD, ZLIB, RLE_TYPE (table and column level)

The following table outlines the recommended table storage models based on different workload types and query patterns.

Heap AO: row-oriented AO: column-oriented

	Heap	AO: row-oriented	AO: column-oriented
Workload type	OLTP	OLAP or mixed	OLAP
Frequency of data inserts	Often	Often	Rarely
Frequency of data updates	Often	Rarely	Rarely
Number of requested columns	All or the majority of columns	All or the majority of columns	A small subset of columns
Typical queries	Single-row updates. Parallel batch operations.	Bulk data loading. Bulk data reading.	Aggregate many values from a single column. Return a small number of columns. Rare `UPDATE` queries that modify only a small subset of columns.

Workload type

OLTP

OLAP or mixed

OLAP

Frequency of data inserts

Often

Rarely

Frequency of data updates

Often

Rarely

Number of requested columns

All or the majority of columns

A small subset of columns

Typical queries

Single-row updates.
Parallel batch operations.

Bulk data loading.
Bulk data reading.

Aggregate many values from a single column.
Return a small number of columns.
Rare UPDATE queries that modify only a small subset of columns.

Prerequisites

To execute commands described in the following sections, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql.

To create a new table with the specified storage model, use the CREATE TABLE command and pass the following storage options in the WITH clause:

appendoptimized — set to true to create an append-optimized table.
orientation — set to row (default) or column for a row or column-oriented storage. This option is only valid if the table is append-optimized.

The gp_default_storage_options server configuration parameter allows you to set the default values for some table storage options, including appendoptimized and orientation.

To create a new heap table, use the CREATE TABLE command without any options:

CREATE TABLE cashback_categories
(
    category_code    VARCHAR(10),
    cashback_percent DECIMAL(4, 2)
)
    DISTRIBUTED REPLICATED;

Sample data

INSERT INTO cashback_categories (category_code, cashback_percent)
VALUES ('FOOD', 2.50),
       ('SPORT', 3.00),
       ('CLOTHES', 5.00);

As mentioned in Workload types, such tables are suited for frequent single-row updates, for example:

UPDATE cashback_categories
SET cashback_percent = 7.5
WHERE category_code = 'CLOTHES';

To create a row-oriented append-optimized table, use the CREATE TABLE command with the following appendoptimized and orientation option values:

CREATE TABLE customers
(
    customer_id   INTEGER,
    name          VARCHAR(25),
    email         VARCHAR(25),
    customer_type VARCHAR(15)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED REPLICATED;

Sample data

INSERT INTO customers (customer_id, name, email, customer_type)
VALUES (1, 'Andrew Fuller', 'andrew@example.com', 'Regular'),
       (2, 'Michael Suyama', 'michael@testmail.com', 'VIP'),
       (3, 'Robert King', 'robert@demo.org', 'Business'),
       (4, 'Laura Callahan', 'laura@example.io', 'Regular');

Such tables suit for queries that retrieve most of the columns, for example:

SELECT customer_id, name, email
FROM customers
WHERE customer_type = 'Business'
ORDER BY name;

To create a column-oriented append-optimized table, set the orientation option to column:

CREATE TABLE orders
(
    order_id    INTEGER,
    customer_id INTEGER,
    category    VARCHAR(10),
    amount      DECIMAL(6, 2)
)
    WITH (appendoptimized = true, orientation = column)
    DISTRIBUTED BY (order_id);

Sample data

INSERT INTO orders (order_id, customer_id, category, amount)
VALUES (1, 1, 'FOOD', 100.50),
       (2, 2, 'FOOD', 200.75),
       (3, 3, 'SPORT', 150.25),
       (4, 4, 'CLOTHES', 250.00),
       (5, 2, 'SPORT', 300.00),
       (6, 1, 'FOOD', 180.50),
       (7, 4, 'CLOTHES', 120.25),
       (8, 3, 'FOOD', 220.00);

Such tables are suitable for queries that aggregate many values of a single column, for example:

SELECT SUM(amount)
FROM orders
WHERE amount > 200;

To check storage types for all tables, use the \dt meta-command:

\dt

The Storage column shows a storage type for each table:

                           List of relations
 Schema |        Name         | Type  |  Owner  |       Storage
--------+---------------------+-------+---------+----------------------
 public | cashback_categories | table | gpadmin | heap
 public | customers           | table | gpadmin | append only
 public | orders              | table | gpadmin | append only columnar

You can also use the \d meta-command to check the used storage type and corresponding options for the specified table:

\d customers

The result might look like this:

       Append-Only Table "public.customers"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 customer_id   | integer               |
 name          | character varying(25) |
 email         | character varying(25) |
 customer_type | character varying(15) |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed Replicated

To get table storage options, query the pg_class system table:

SELECT relname, relkind, relstorage, reloptions
FROM pg_class
WHERE relname IN ('cashback_categories', 'customers', 'orders');

The result might look like this:

       relname       | relkind | relstorage |              reloptions
---------------------+---------+------------+--------------------------------------
 cashback_categories | r       | h          |
 customers           | r       | a          | {appendonly=true,orientation=row}
 orders              | r       | c          | {appendonly=true,orientation=column}

Table storage and orientation can be declared only at creation. To change the storage model, follow the steps below:

Create a new table with the necessary storage options:

CREATE TABLE orders_new
(
    LIKE orders
)
    WITH (appendoptimized = true, orientation = row);

Load the original table data into the new table:

INSERT INTO orders_new
SELECT *
FROM orders;

Drop the original table:
DROP TABLE orders;
Rename the new table with the original table’s name:
ALTER TABLE orders_new RENAME TO orders;

The procedure above applies to changing the storage model of a table that has no dependent objects, such as indexes or views. If dependent objects exist, you must drop them before dropping the original table and then recreate them after the new table is created. For partitioned tables, you must drop the partition, create it with the new storage model, and then reattach it to the parent table.

NOTE

After creating a new table, you need to reapply any table permissions. Learn more in Roles and privileges.

Schemas

Table storage types

How to choose a table storage type

Storage types overview

Table characteristics and limitations

Workload types

Create a table

Heap

AO: row-oriented

AO: column-oriented

Check a table storage type

psql meta-commands

SQL commands

Alter a table storage type