Типы таблиц

Андрей Аксенов

Contents

Как выбрать тип хранения таблицы
Создание таблицы
Проверка типа хранения таблицы
- Метакоманды psql
- Системные каталоги
Изменение типа хранения таблицы
- Правила наследования при изменении типа хранения партиционированной таблицы

Greengage DB поддерживает несколько моделей хранения таблиц, оптимизированных под определенные типы нагрузок, например OLTP или OLAP. При создании таблицы можно выбрать наиболее подходящую модель хранения, исходя из требований к нагрузке. В этой статье описано, как выбрать подходящую модель хранения таблицы для вашего сценария использования и как указать эту модель при создании таблицы.

Greengage DB поддерживает следующие типы таблиц:

Heap

Heap-таблицы рекомендуются для OLTP-нагрузок, особенно когда данные часто изменяются после первоначальной загрузки. Они лучше всего подходят для операций с запросами к отдельным строкам, таким как INSERT, UPDATE или DELETE. Heap-таблицы поддерживают только строковое хранение данных (row-oriented).
Оптимизированные для добавления (Append-optimized, AO)

Оптимизированные для добавления таблицы предпочтительны для OLAP-нагрузок. Они особенно подходят для пакетной загрузки данных и сценариев, в которых данные изменяются редко после начальной загрузки. Эти таблицы наиболее эффективны, когда преобладают запросы на чтение. В отличие от heap-таблиц, для которых поддерживается только строковая ориентация данных, оптимизированные для добавления таблицы поддерживают две формы хранения данных:
- Строковая (row-oriented)
  
  Эта модель хранения рекомендуется для запросов, в которых извлекается большинство или все столбцы таблицы.
- Колоночная (column-oriented)
  
  Эта модель подходит для вычислений, включающих небольшое количество столбцов таблицы. Кроме того, она полезна для таблиц, в которых требуется регулярно обновлять небольшую часть столбцов без изменения остальных.

В следующей таблице перечислены рекомендуемые характеристики и ограничения различных моделей хранения данных.

Heap AO: строковое хранение AO: колоночное хранение

	Heap	AO: строковое хранение	AO: колоночное хранение
Хранение данных	Строковое	Строковое	Колоночное
Размер таблицы	Небольшие таблицы, такие как таблицы измерений (dimension tables)	Большие денормализованные таблицы фактов (fact tables)	Большие денормализованные таблицы фактов (fact tables)
Количество столбцов	Относительно небольшое	Множество столбцов (десятки и более)	Множество столбцов (десятки и более)
Сжатие	Не поддерживается	`ZSTD`, `ZLIB` (на уровне таблицы)	`ZSTD`, `ZLIB`, `RLE_TYPE` (на уровне таблицы и столбцов)

Хранение данных

Строковое

Колоночное

Размер таблицы

Небольшие таблицы, такие как таблицы измерений (dimension tables)

Большие денормализованные таблицы фактов (fact tables)

Количество столбцов

Относительно небольшое

Множество столбцов (десятки и более)

Сжатие

Не поддерживается

ZSTD, ZLIB (на уровне таблицы)

ZSTD, ZLIB, RLE_TYPE (на уровне таблицы и столбцов)

В следующей таблице представлены рекомендуемые модели хранения таблиц в зависимости от типа нагрузки и характера запросов.

Heap AO: строковое хранение AO: колоночное хранение

	Heap	AO: строковое хранение	AO: колоночное хранение
Тип нагрузки	OLTP	OLAP или смешанные	OLAP
Частота вставки данных	Часто	Часто	Редко
Частота обновления данных	Часто	Редко	Редко
Количество запрашиваемых столбцов	Все или большая часть	Все или большая часть	Небольшая часть
Типичные запросы	Обновление отдельных строк. Параллельные пакетные операции.	Массовая загрузка данных (bulk loading). Массовое чтение данных (bulk reading).	Агрегирование множества значений из одного столбца. Получение относительно небольшого числа столбцов. Редкие запросы `UPDATE`, изменяющие лишь небольшое подмножество столбцов.

Тип нагрузки

OLTP

OLAP или смешанные

OLAP

Частота вставки данных

Часто

Редко

Частота обновления данных

Часто

Редко

Количество запрашиваемых столбцов

Все или большая часть

Небольшая часть

Типичные запросы

Обновление отдельных строк.
Параллельные пакетные операции.

Массовая загрузка данных (bulk loading).
Массовое чтение данных (bulk reading).

Агрегирование множества значений из одного столбца.
Получение относительно небольшого числа столбцов.
Редкие запросы UPDATE, изменяющие лишь небольшое подмножество столбцов.

Предварительные требования

Для выполнения команд, описанных в последующих разделах, подключитесь к координатор-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql.

Чтобы создать новую таблицу с указанной моделью хранения, используйте команду CREATE TABLE и передайте следующие параметры в выражении USING:

heap — создает heap-таблицу.
ao_row — создает оптимизированную для добавления таблицу со строковым хранением.
ao_column — создает оптимизированную для добавления таблицу с колоночным хранением.

Значение по умолчанию — heap, но его можно изменить с помощью конфигурационного параметра default_table_access_method.

Чтобы создать новую heap-таблицу, используйте команду CREATE TABLE без каких-либо параметров:

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

Тестовые данные

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

Как упомянуто в разделе Типы нагрузки, такие таблицы подходят для частого обновления отдельных строк, например:

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

Для создания оптимизированной для добавления таблицы со строковой ориентацией используйте команду CREATE TABLE со следующими значениями параметров appendoptimized и orientation:

CREATE TABLE customers
(
    customer_id   INTEGER,
    name          VARCHAR(25),
    email         VARCHAR(25),
    customer_type VARCHAR(15)
)
    USING ao_row
    DISTRIBUTED REPLICATED;

Тестовые данные

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');

Такие таблицы подходят для запросов, которые извлекают большинство столбцов, например:

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

Чтобы создать оптимизированную для добавления таблицу с колоночным хранением, установите параметр orientation в значение column:

CREATE TABLE orders
(
    order_id    INTEGER,
    customer_id INTEGER,
    category    VARCHAR(10),
    amount      DECIMAL(6, 2)
)
    USING ao_column
    DISTRIBUTED BY (order_id);

Тестовые данные

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);

Такие таблицы подходят для запросов, агрегирующих множество значений из одного столбца, например:

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

Чтобы узнать типы хранения для всех таблиц, используйте метакоманду \dt:

\dt

Столбец Storage показывает тип каждой таблицы:

                     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)

Вы также можете использовать метакоманду \d+ для проверки используемого типа хранения и соответствующих параметров указанной таблицы:

\d+ customers

Результат может выглядеть следующим образом:

                                            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

Чтобы получить параметры хранения таблицы, выполните запрос к системной таблице pg_class и соедините ее с pg_am для получения имени метода доступа:

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');

Результат может выглядеть следующим образом:

       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)

Начиная с Greengage DB 7 тип хранения таблицы можно изменять с помощью команды ALTER TABLE … SET ACCESS METHOD. Это устраняет необходимость пересоздавать таблицу и вручную копировать данные, как требовалось в предыдущих версиях.

В следующем примере изменяется тип хранения таблицы orders:

ALTER TABLE orders
    SET ACCESS METHOD ao_column;

Команда ALTER TABLE … SET ACCESS METHOD перезаписывает таблицу с использованием нового формата хранения и устанавливает блокировку ACCESS EXCLUSIVE на время выполнения операции.

Следующие правила наследования применяются при изменении типа хранения партиционированной таблицы:

Изменение типа хранения родительской таблицы влияет на все существующие дочерние партиции и все будущие партиции.
Изменение типа хранения родительской таблицы с ключевым словом ONLY (ALTER TABLE ONLY … SET ACCESS METHOD) влияет только на будущие дочерние партиции.
Изменение типа хранения листовой партиции влияет только на эту конкретную партицию.

Обзор таблиц

Сжатие данных

Типы таблиц

Как выбрать тип хранения таблицы

Обзор типов хранилищ

Характеристики и ограничения таблиц

Типы нагрузки

Создание таблицы

Heap-таблицы

AO-таблицы: строковое хранение

AO-таблицы: колоночное хранение

Проверка типа хранения таблицы

Метакоманды psql

Системные каталоги

Изменение типа хранения таблицы

Правила наследования при изменении типа хранения партиционированной таблицы