GitHub

Типы таблиц

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

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: колоночное хранение

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

Строковое

Строковое

Колоночное

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

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

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

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

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

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

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

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

Уникальность значений

PRIMARY KEY, UNIQUE

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

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

Сжатие

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

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

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

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

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

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

Тип нагрузки

OLTP

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

OLAP

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

Часто

Часто

Редко

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

Часто

Редко

Редко

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

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

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

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

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

  • Обновление отдельных строк.

  • Параллельные пакетные операции.

  • Массовая загрузка данных (bulk loading).

  • Массовое чтение данных (bulk reading).

  • Агрегирование множества значений из одного столбца.

  • Получение относительно небольшого числа столбцов.

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

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

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

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

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

  • appendoptimized — установите в значение true, чтобы создать оптимизированную для добавления таблицу.

  • orientation — установите в значение row (по умолчанию) или column для выбора строкового или колоночного хранения. Этот параметр применяется только к оптимизированным для добавления таблицам.

Параметр конфигурации сервера gp_default_storage_options позволяет установить значения по умолчанию для некоторых параметров хранения таблиц, включая appendoptimized и orientation.

Heap-таблицы

Чтобы создать новую 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';

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

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

CREATE TABLE customers
(
    customer_id   INTEGER,
    name          VARCHAR(25),
    email         VARCHAR(25),
    customer_type VARCHAR(15)
)
    WITH (appendoptimized = true, orientation = 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;

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

Чтобы создать оптимизированную для добавления таблицу с колоночным хранением, установите параметр orientation в значение 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);
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;

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

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

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

\dt

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

                           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

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

\d customers

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

       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

SQL-команды

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

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

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

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

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

Способ хранения и ориентацию таблицы можно указать только при ее создании. Чтобы изменить модель хранения, выполните следующие шаги:

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

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

  2. Загрузите исходные данные таблицы в новую таблицу:

    INSERT INTO orders_new
SELECT *
FROM orders;

  3. Удалите исходную таблицу:

    DROP TABLE orders;

  4. Переименуйте новую таблицу, присвоив ей имя исходной таблицы:

    ALTER TABLE orders_new
    RENAME TO orders;

Приведенная выше процедура применяется для изменения модели хранения таблицы, у которой нет зависимых объектов, таких как индексы или представления. Если у таблицы есть зависимые объекты, их необходимо предварительно удалить, а затем пересоздать после создания новой таблицы. Для партиционированных таблиц необходимо удалить партицию, создать ее с нужной моделью хранения, а затем повторно присоединить к родительской таблице.

ПРИМЕЧАНИЕ

После создания новой таблицы необходимо заново выдать привилегии на таблицу. Узнайте больше в статье Роли и привилегии.

Предыдущая
Схемы
Открыть страницу поиска