Table storage types

Andrey Aksenov

Contents

How to choose a table storage type
Create a table
Check a table storage type
- psql meta-commands
- System catalogs
Alter a table storage type
- Inheritance rules for changing a partitioned table’s 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)
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)

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 coordinator 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 table access method in the USING clause:

heap — creates a heap table.
ao_row — creates an append-optimized table with row-oriented storage.
ao_column — creates an append-optimized table with column-oriented storage.

The default value is heap, but it can be changed using the default_table_access_method server configuration parameter.

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)
)
    USING ao_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)
)
    USING ao_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 | ao_row
 public | orders              | table | gpadmin | ao_column
(3 rows)

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:

                                            Table "public.customers"
    Column     |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 customer_id   | integer               |           |          |         | plain    |              |
 name          | character varying(25) |           |          |         | extended |              |
 email         | character varying(25) |           |          |         | extended |              |
 customer_type | character varying(15) |           |          |         | extended |              |
Distributed Replicated
Access method: ao_row
Options: blocksize=32768, compresslevel=0, compresstype=none, checksum=true

To get table storage options, query the pg_class system table and join it with pg_am to resolve the access method name:

SELECT c.relname, c.relkind, am.amname, c.reloptions
FROM pg_class c
         LEFT JOIN pg_am am ON am.oid = c.relam
WHERE c.relname IN ('cashback_categories', 'customers', 'orders');

The result might look like this:

       relname       | relkind |  amname   |                            reloptions
---------------------+---------+-----------+-------------------------------------------------------------------
 cashback_categories | r       | heap      |
 customers           | r       | ao_row    | {blocksize=32768,compresslevel=0,compresstype=none,checksum=true}
 orders              | r       | ao_column | {blocksize=32768,compresslevel=0,compresstype=none,checksum=true}
(3 rows)

Starting with Greengage DB 7, the table storage type can be changed using the ALTER TABLE … SET ACCESS METHOD command. This eliminates the need to recreate the table and manually copy data, as was required in earlier versions.

The following example changes the storage type of the orders table:

ALTER TABLE orders
    SET ACCESS METHOD ao_column;

The ALTER TABLE … SET ACCESS METHOD command rewrites the table using the new storage format and acquires an ACCESS EXCLUSIVE lock for the duration of the operation.

The following inheritance rules apply to the storage type of a partitioned table:

Changing the storage type at the partition root affects all existing child partitions and all future partitions.
Changing the storage type at the partition root with the ONLY keyword (ALTER TABLE ONLY … SET ACCESS METHOD) affects only future child partitions.
Changing the storage type of a leaf partition affects only that specific partition.

Tables overview

Data compression

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

System catalogs

Alter a table storage type

Inheritance rules for changing a partitioned table’s storage type