F.21. Hydra Оптимизированный Строково-Колонковый (ORC)#

F.21. Hydra Оптимизированный Строково-Колонковый (ORC)

F.21. Hydra Оптимизированный Строково-Колонковый (ORC)
Назад	Наверх	Предметный указатель F. Дополнительные поставляемые модули и расширения	Начало	Далее

F.21. Hydra Оптимизированный Строково-Колонковый (ORC) #

F.21.1. О Hydra Columnar
F.21.2. Введение
F.21.3. Установка в столбцовом формате
F.21.4. Основные принципы и вопросы
F.21.5. Использование колоночного формата
F.21.6. Тип таблицы по умолчанию
F.21.7. Микротесты производительности
F.21.8. Строковые vs Столбцовые Таблицы
F.21.9. Обновления и Удаления
F.21.10. Оптимизация производительности запросов
F.21.11. Материализованные представления
F.21.12. Векторное выполнение
F.21.13. Параллелизация Запросов
F.21.14. Общие рекомендации
F.21.15. Работа с Time Series-данными

F.21.1. О Hydra Columnar #

Версия: 1.1.2

Страница проекта www.hydra.so

GitHub

Авторские права © Hydra.

F.21.2. Введение #

В традиционных аналитических (OLAP) системах размер и объем данных имеют тенденцию к увеличению со временем, что потенциально приводит к различным проблемам с производительностью. Колонковое хранение предлагает значительные преимущества, особенно в сценариях, где системы управления базами данных (СУБД) используются для аналитических задач, которые преимущественно включают пакетную загрузку данных.

Колонковое хранение позволяет значительно сжимать таблицы, существенно снижая использование ресурсов при сохранении высокой производительности. Это достижимо без использования распределенных MPP (Массивно Параллельная Обработка) систем СУБД. Встроенная структура ORC в колонковом хранении способствует эффективному чтению с диска и предлагает превосходное сжатие данных, улучшая общую производительность.

Примечание

Метод колоночного хранения не полностью заменяет распределенные массивно параллельные СУБД, но исключает их использование в некоторых случаях. Решение о переходе на массивно параллельные СУБД зависит от конкретных объемов данных и требований к рабочей нагрузке. Хотя пороговое значение варьируется, обычно оно находится в пределах десятков терабайт.

F.21.3. Установка в столбцовом формате #

Метод колонкового хранения устанавливается как расширение. Чтобы добавить Columnar в вашу локальную базу данных Tantor SE, подключитесь с помощью psql и выполните:

CREATE EXTENSION pg_columnar;

F.21.4. Основные принципы и вопросы #

F.21.4.1. Основные принципы колоночного хранения #

Стандартный метод хранения данных heap в Tantor SE хорошо работает для нагрузки OLTP:

Поддержка операций UPDATE/DELETE,
Эффективный поиск одиночных кортежей

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

Сжатие
Не читаются ненужные столбцы
Эффективное использование VACUUM.

F.21.4.2. Почему Hydra Columnar такой быстрый? #

Существует несколько причин для этого:

Колонковое хранение
Векторное выполнение
Параллелизация запросов
Кэширование на уровне столбцов
Настройка Tantor SE

F.21.4.3. Для каких операций предназначена Hydra Columnar? #

Агрегаты (COUNT, SUM, AVG), WHERE условия, массовые INSERT, UPDATE, DELETE и другие.

F.21.4.4. Для чего не предназначен колоночный формат? #

Частые крупные обновления, небольшие транзакции.

Hydra Columnar поддерживает как строковые, так и колоночные таблицы, и любой формат хранения имеет свои компромиссы.

Колоннарный формат не подходит для:

Наборы данных, которые часто подвергаются крупным обновлениям. Хотя обновления в Hydra Columnar поддерживаются, они медленнее, чем в куче таблиц.
Маленькие транзакции, особенно когда запрос должен выполняться очень быстро, так как каждая колоночная транзакция значительно медленнее, чем транзакция с кучей.
Запросы, где традиционные индексы обеспечивают высокую кардинальность (т.е. очень эффективны), например, “иголка в стоге сена” проблемы, такие как “поиск по ID.” По этой причине таблицы соединений ссылок лучше всего хранить, используя кучу.
Очень большие наборы данных с одной таблицей могут требовать особого внимания для хорошей производительности, например, использование секционирования таблиц. Мы планируем улучшить это в будущем. Если вы решаете проблему “больших данных”, свяжитесь с нами за помощью!
Сценарии использования, где требуется высокая параллельность. Аналитические запросы на Hydra Columnar активно используют параллелизацию, и таким образом потребляют большое количество ресурсов, ограничивая возможность выполнения запросов одновременно.

Где столбчатая структура не справляется, традиционные кучи таблиц могут помочь. На Hydra Columnar вы можете использовать оба!

F.21.4.5. Какие возможности Tantor SE не поддерживаются в колонках? #

Колонковые таблицы не поддерживают логическую репликацию.
Внешние ключи не поддерживаются.

F.21.4.6. Как Hydra Columnar обрабатывает сложные запросы? #

Попробуйте инкрементные материализованные представления.

Материализованные представления - это предварительно вычисленные таблицы базы данных, которые хранят результаты запроса. Hydra Columnar предлагает автоматическое обновление материализованных представлений на основе изменений в базовых таблицах. Этот подход устраняет необходимость пересчитывать все представление, что приводит к улучшению производительности запросов и исключает трудоемкий пересчет.

F.21.5. Использование колоночного формата #

F.21.5.1. Следует ли использовать строковую или колонковую таблицу? #

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

F.21.5.2. Включение колоночного хранения #

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

CREATE EXTENSION IF NOT EXISTS pg_columnar;

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

F.21.5.3. Использование колоночной таблицы #

Создание колоночной таблицы, указывая USING columnar опцию:

CREATE TABLE my_columnar_table
(
    id INT,
    i1 INT,
    i2 INT8,
    n NUMERIC,
    t TEXT
) USING columnar;

Вставить данные в таблицу и прочитать их как обычно (с учетом ограничений, перечисленных выше).

Чтобы просмотреть внутреннюю статистику для таблицы, используйте VACUUM VERBOSE. Обратите внимание, что VACUUM (без FULL) гораздо быстрее на колоночной таблице, потому что он сканирует только метаданные, а не фактические данные.

Установите параметры с помощью ALTER TABLE:

ALTER TABLE my_columnar_table SET
  (columnar.compression = none, columnar.stripe_row_limit = 10000);

Доступны следующие опции:

columnar.compression : [none|pglz|zstd|lz4|lz4hc] - установите тип сжатия для новых вставленных данных. Существующие данные не будут пересжаты/распакованы. Значение по умолчанию - zstd (если поддержка была указана при компиляции).
columnar.compression_level : <integer> - устанавливает уровень сжатия. Допустимые значения от 1 до 19. Если метод сжатия не поддерживает выбранный уровень, будет выбран ближайший уровень. Значение по умолчанию - 3.
columnar.stripe_row_limit : <integer> - максимальное количество строк в полосе для вновь вставленных данных. Существующие полосы данных не будут изменены и могут содержать больше строк, чем это максимальное значение. Допустимые значения от 1000 до 100000000. Значение по умолчанию - 150000.
columnar.chunk_group_row_limit : <integer> - максимальное количество строк в блоке для вновь вставленных данных. Существующие блоки данных не будут изменены и могут содержать больше строк, чем это максимальное значение. Допустимые значения от 1000 до 100000000. Значение по умолчанию - 10000.
columnar.enable_parallel_execution : <boolean> - включает параллельное выполнение. Значение по умолчанию - true.
columnar.min_parallel_processes : <integer> - минимальное количество параллельных процессов. Допустимые значения от 1 до 32. Значение по умолчанию - 8.
columnar.enable_vectorization : <boolean> - включает векторное выполнение. Значение по умолчанию - true.
columnar.enable_dml : <boolean> - включает DML. Значение по умолчанию - true.
columnar.enable_column_cache : <boolean> - включает кэширование на уровне столбцов. Значение по умолчанию - false.
columnar.column_cache_size : <integer> - размер кэш-памяти столбца в мегабайтах. Допустимые значения от 20 до 20000. Значение по умолчанию - 200. Вы также можете указать значение с единицей измерения, например, '300MB' или '4GB'.
enable_columnar_index_scan : <boolean> - включает пользовательское сканирование колоночного индекса. Значение по умолчанию - false.

Вы можете установить значения с помощью команды SET для одного из следующих параметров GUC:

columnar.compression
columnar.compression_level
columnar.stripe_row_limit
columnar.chunk_group_row_limit
columnar.enable_parallel_execution
columnar.min_parallel_processes
columnar.enable_vectorization
columnar.enable_dml
columnar.enable_column_cache
columnar.column_cache_size
columnar.enable_columnar_index_scan

Опции GUC влияют только на вновь созданные таблицы, а не на вновь созданные полосы в существующих таблицах.

F.21.5.4. Преобразование между кучей и колоночным форматом #

Примечание

Убедитесь, что вы понимаете все дополнительные функции, которые могут использоваться в таблице перед ее преобразованием (такие как защита на уровне строк, параметры хранения, ограничения, наследование и т.д.) и убедитесь, что они воспроизводятся в новой таблице или секции соответствующим образом. LIKE, использованный ниже, является сокращением, которое работает только в простых случаях.

CREATE TABLE my_table(i INT8 DEFAULT '7');
INSERT INTO my_table VALUES(1);
-- convert to columnar
SELECT alter_table_set_access_method('my_table', 'columnar');
-- back to row
SELECT alter_table_set_access_method('my_table', 'heap');

F.21.6. Тип таблицы по умолчанию #

Тип таблицы по умолчанию для базы данных по умолчанию - heap. Вы можете изменить это, используя ALTER DATABASE оператор:

ALTER DATABASE database_name SET default_table_access_method = 'columnar';

Для любой базы данных, которую вы создаете вручную с помощью CREATE DATABASE, тип таблицы по умолчанию — columnar.

F.21.6.1. Секционирование #

Табличные столбцы могут использоваться как секции; и секционированная таблица может состоять из любой комбинации кучи и разделов столбцов.

CREATE TABLE parent(ts timestamptz, i int, n numeric, s text)
  PARTITION BY RANGE (ts);

-- columnar partition
CREATE TABLE p0 PARTITION OF parent
  FOR VALUES FROM ('2020-01-01') TO ('2020-02-01')
  USING COLUMNAR;
-- columnar partition
CREATE TABLE p1 PARTITION OF parent
  FOR VALUES FROM ('2020-02-01') TO ('2020-03-01')
  USING COLUMNAR;
-- row partition
CREATE TABLE p2 PARTITION OF parent
  FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

INSERT INTO parent VALUES ('2020-01-15', 10, 100, 'one thousand'); -- columnar
INSERT INTO parent VALUES ('2020-02-15', 20, 200, 'two thousand'); -- columnar
INSERT INTO parent VALUES ('2020-03-15', 30, 300, 'three thousand'); -- row

При выполнении операций с секционированной таблицей, состоящей из строк и столбцов, обратите внимание на следующие особенности операций, которые поддерживаются для строк, но не для столбцов (например, UPDATE, DELETE, блокировки строк и т.д.):

Если операция пытается выполниться на определенной таблице с методом хранения кучи (например, UPDATE p2 SET i = i + 1), она будет успешной; но если она пытается выполниться на таблице, организованной в столбцы (например UPDATE p1 SET i = i + 1), она не пройдет.
Если операция пытается выполниться на секционированной таблице и имеет предложение WHERE, которое исключает все таблицы, сохраненные в виде столбцов (например, UPDATE parent SET i = i + 1 WHERE ts = '2020-03-15'), она будет успешно выполнена.
Если операция пытается выполниться на секционированной таблице, но не исключает все разделы столбцов, она не пройдет; даже если фактические данные для обновления затрагивают только кучные таблицы (например, UPDATE parent SET i = i + 1 WHERE n = 300).

Обратите внимание, что Columnar поддерживает индексы btree и hash (и ограничения, которые требуют их), но не поддерживает индексы gist, gin, spgist и brin. По этой причине, если некоторые таблицы имеют колоночный формат и если индекс не поддерживается, невозможно создать индексы непосредственно на секционированной (родительской) таблице. В таком случае вам нужно создать индекс на отдельных кучных таблицах. То же самое касается ограничений, которые требуют индексы, например:

CREATE INDEX p2_ts_idx ON p2 (ts);
CREATE UNIQUE INDEX p2_i_unique ON p2 (i);
ALTER TABLE p2 ADD UNIQUE (n);

F.21.7. Микротесты производительности #

F.21.7.1. Небольшой тест производительности #

Важно

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

Схема:

CREATE TABLE perf_row(
    id INT8,
    ts TIMESTAMPTZ,
    customer_id INT8,
    vendor_id INT8,
    name TEXT,
    description TEXT,
    value NUMERIC,
    quantity INT4
);

CREATE TABLE perf_columnar(LIKE perf_row) USING COLUMNAR;

Функции для генерации данных:

CREATE OR REPLACE FUNCTION random_words(n INT4) RETURNS TEXT LANGUAGE plpython2u AS $$
import random
t = ''
words = ['zero','one','two','three','four','five','six','seven','eight','nine','ten']
for i in xrange(0,n):
  if (i != 0):
    t += ' '
  r = random.randint(0,len(words)-1)
  t += words[r]
return t
$$;

Вставьте данные используя функцию random_words:

INSERT INTO perf_row
   SELECT
    g, -- id
    '2020-01-01'::timestamptz + ('1 minute'::interval * g), -- ts
    (random() * 1000000)::INT4, -- customer_id
    (random() * 100)::INT4, -- vendor_id
    random_words(7), -- name
    random_words(100), -- description
    (random() * 100000)::INT4/100.0, -- value
    (random() * 100)::INT4 -- quantity
   FROM generate_series(1,75000000) g;

INSERT INTO perf_columnar SELECT * FROM perf_row;

Проверьте уровень сжатия:

=> SELECT pg_total_relation_size('perf_row')::numeric/pg_total_relation_size('perf_columnar') AS compression_ratio;

 compression_ratio
--------------------
 5.3958044063457513
(1 row)

Общий коэффициент сжатия столбцовой таблицы по сравнению с теми же данными, хранящимися в куче, составляет 5.4X.

=> VACUUM VERBOSE perf_columnar;
INFO:  statistics for "perf_columnar":
storage id: 10000000000
total file size: 8761368576, total data size: 8734266196
compression rate: 5.01x
total row count: 75000000, stripe count: 500, average rows per stripe: 150000
chunk count: 60000, containing data for dropped columns: 0, zstd compressed: 60000

VACUUM VERBOSE сообщает о более низком коэффициенте сжатия, потому что он усредняет коэффициент сжатия отдельных блоков и не игнорирует экономию метаданных формата столбца.

Характеристики системы:

Azure VM: Стандарт D2s v3 (2 виртуальных процессора, 8 ГиБ памяти)
Linux (ubuntu 18.04)
Дисковый накопитель: Стандартный HDD (512ГБ, макс. 500 IOPS, макс. 60 МБ/с)
PostgreSQL 13 (--with-llvm, --with-python)
shared_buffers = 128MB
max_parallel_workers_per_gather = 0
jit = on

Примечание

Поскольку это было выполнено на системе с достаточным объемом физической памяти для хранения большой части таблицы, преимущества столбцового ввода-вывода не были бы полностью реализованы во время выполнения запроса, если размер данных значительно не увеличивается.

Запросы приложения:

-- OFFSET 1000 so that no rows are returned, and we collect only timings

SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;

Timing (median of three runs):

строка: 436с
колоночный: 16s
ускорение: 27X

F.21.7.2. Колонные агрегаты #

Диаграмма F.1. Производительность

Запрос 0 выполняется в 512 раз быстрее:

SELECT COUNT(*) FROM hits;

Запрос 2 выполняется в 283 раза быстрее:

SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;

Postgres COUNT медленные? Больше нет! Hydra Columnar параллелизирует и векторизует агрегаты (COUNT, SUM, AVG), чтобы обеспечить аналитическую скорость, которую вы всегда хотели на Postgres.

Фильтрация (WHERE условия):

Запрос 1 выполняется в 1,412 раза быстрее:

SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;

Фильтры на колонном хранилище Hydra привели к улучшению производительности в 1412 раз. Это скорость как у дорожного бегуна: бип, бип!

F.21.7.2.1. Таблицы с большим количеством столбцов, где доступ осуществляется только к некоторым столбцам #

Когда у вас есть большое количество столбцов в вашей таблице (например, денормализованная таблица) и вам нужен быстрый доступ к подмножеству столбцов, Hydra Columnar может обеспечить очень быстрый доступ только к этим столбцам без чтения каждого столбца в память.

F.21.8. Строковые vs Столбцовые Таблицы #

F.21.8.1. Введение в колоночный формат #

Колонковое хранение является ключевой частью хранилища данных, но почему это так? В этой статье мы рассмотрим, что такое колонковое хранение и почему оно является такой важной частью хранилища данных.

F.21.8.1.1. Куча таблица #

В традиционном Postgres данные в Postgres хранятся в куче таблиц. Таблицы кучи организованы построчно, аналогично тому, как вы организовали бы данные при создании большой электронной таблицы. Данные могут добавляться бесконечно путем добавления данных в конец таблицы.

В Postgres таблицы кучи организованы в страницы. Каждая страница содержит 8 КБ данных. Каждая страница содержит указатели на начало каждой строки в данных.

Для получения дополнительной информации см. Структура кучи таблицы, из Внутреннее устройство PostgreSQL.

Преимущества

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

Недостатки

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

Кроме того, сканирование с помощью индекса может быть ограничено, если вы запрашиваете большой объем данных. Например, если вы хотите узнать среднее значение за данный месяц и у вас есть индекс по временной метке, индекс поможет Postgres найти соответствующие данные, но все равно потребуется прочитать каждую целевую строку отдельно в таблице для вычисления среднего значения.

F.21.8.1.2. Ввод столбчатый #

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

| a | b | c | d |
| a | b | c | d |
| a | b | c | d |

Эти данные будут храниться следующим образом в колонках:

| a | a | a |
| b | b | b |
| c | c | c |
| d | d | d |

В колоннарном формате вы можете представить каждую полосу как строку метаданных, которая также содержит до 150,000 строк данных. В пределах каждой полосы данные делятся на блоки по 10,000 строк, и затем в каждом блоке есть “строка” для каждого столбца данных, которые вы в нем сохранили. Кроме того, в колоннарном формате хранятся минимальное, максимальное значения и количество для каждого столбца в каждом блоке.

Преимущества

Колоннарный формат оптимизирован для сканирования таблиц — фактически, он вообще не использует индексы. Используя колоннарный формат, гораздо быстрее получить все данные для определенного столбца. Базе данных не нужно читать данные, которые вас не интересуют. Он также использует метаданные о значениях в чанках, чтобы исключить чтение данных. Это форма “автоиндексации” данных.

Поскольку аналогичные данные хранятся рядом друг с другом, возможна очень высокая степень сжатия данных. Сжатие данных является важным преимуществом, поскольку колоночный формат часто используется для хранения огромных объемов данных. Сжимая данные, вы можете эффективно быстрее читать данные с диска, что одновременно уменьшает ввод-вывод и увеличивает скорость выборки. Это также позволяет более эффективно использовать кэширование диска, так как данные кэшируются в сжатом виде. Наконец, вы значительно снижаете свои затраты на хранение.

Недостатки

Hydra Columnar storage не предназначено для выполнения общих транзакционных запросов, таких как “поиск по ID” - базе данных потребуется просканировать гораздо больший объем данных, чтобы получить эту информацию, чем в таблице строк.

Hydra Columnar является только добавляемым. Хотя он поддерживает обновления и удаления (также известные как язык модификации данных или DML), пространство не восстанавливается при удалении, а обновления вставляют новые данные. Обновления и удаления блокируют таблицу, так как Columnar не имеет конструкта уровня строки, который можно заблокировать. В целом, DML значительно медленнее на колонных таблицах, чем на строковых таблицах. Пространство может быть восстановлено позже с помощью columnar.vacuum.

Наконец, колоночные таблицы необходимо вставлять пакетно, чтобы создать эффективные полосы. Это делает их идеальными для долгосрочного хранения данных, которые у вас уже есть, но не идеальными, когда данные все еще поступают в базу данных. По этим причинам лучше хранить данные в строковых таблицах, пока они не будут готовы для архивирования в колоночные. Вы можете сжать маленькие полосы, вызвав VACUUM.

F.21.8.2. Рекомендуемая схема #

F.21.8.2.1. Звёздная схема #

Звёздная схема — это логическая организация таблиц в многомерной базе данных, при которой диаграмма отношений сущностей напоминает форму звезды. Это фундаментальный подход, который широко используется для разработки или построения хранилища данных. Он требует классификации таблиц модели как измерения или факты.

Таблицы фактов фиксируют измерения или метрики для конкретного события. Таблица фактов содержит столбцы ключей измерений, которые связаны с таблицами измерений, и числовые столбцы измерений. Таблицы измерений описывают бизнес-сущности - объекты, которые вы моделируете. Сущности могут включать продукты, людей, места и концепции, включая само время. Таблица измерений содержит ключевой столбец (или столбцы), который действует как уникальный идентификатор, и другие описательные столбцы. Таблицы фактов лучше всего использовать как колоночные таблицы, в то время как таблицы измерений могут быть лучше как строковые таблицы из-за их размера и частоты обновлений.

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

Диаграмма F.2. звёздная-схема

F.21.8.2.2. Гидра Колонная Схема #

Схема Hydra Columnar концептуально идентична звездной схеме, где таблицы фактов фиксируют измерения / метрики, а таблицы измерений представляют собой моделируемые сущности, такие как люди и места. Ключевое отличие схемы Hydra Columnar заключается в том, что таблицы измерений могут быть локальными в базе данных Hydra Columnar или существовать как внешние таблицы. Схемы Hydra Columnar позволяют быстро обновлять наборы данных для избежания дублирования данных в хранилище, но они доступны для анализа мгновенно. Для оптимизации производительности внешние таблицы измерений должны содержать менее 1 миллиона строк (хотя это зависит от случая использования и источника данных). Более крупные таблицы следует импортировать и синхронизировать в Hydra Columnar.

F.21.9. Обновления и Удаления #

Таблицы Hydra Columnar поддерживают обновления и удаления, но остаются хранилищем данных только для добавления. Для достижения этого, Hydra Columnar поддерживает метаданные о том, какие строки в таблице были удалены или изменены. Измененные данные переписываются в конец таблицы; вы можете рассматривать UPDATE как DELETE, за которым следует INSERT.

При выполнении запроса Hydra Columnar автоматически вернет только последнюю версию ваших данных.

F.21.9.1. Производительность чтения #

Чтобы максимизировать производительность запросов SELECT, колоночные таблицы должны иметь максимум данных в каждой полосе. Как и INSERT, каждая транзакция с запросом UPDATE будет записывать новую полосу. Чтобы максимизировать размер этих полос, обновляйте как можно больше данных в одной транзакции. В качестве альтернативы, вы можете запустить VACUUM на таблице, что объединит самые последние полосы в объединенную полосу максимального размера.

F.21.9.2. Производительность записи #

Каждый запрос на обновление или удаление будет блокировать таблицу, что означает, что несколько запросов UPDATE или DELETE на одной и той же таблице будут выполняться последовательно (один за другим). Запросы UPDATE переписывают любые измененные строки и, следовательно, относительно медленные. Запросы DELETE изменяют только метаданные и, следовательно, выполняются довольно быстро.

F.21.9.3. Освобождение пространства с помощью VACUUM #

Колонночное хранилище предоставляет несколько методов для очистки и уборки таблиц. Среди них стандартные функции VACUUM и VACUUM FULL, а также предоставляются UDF, или пользовательские функции, которые помогают для инкрементальной очистки больших таблиц или таблиц с множеством пробелов.

F.21.9.3.1. UDFs #

Вакуумирование требует эксклюзивной блокировки, пока данные, которые являются частью таблицы, реорганизуются и консолидируются. Это может вызвать приостановку других запросов до завершения вакуума, тем самым задерживая другую активность в базе данных.

Используя вакуум UDF, вы можете указать количество полос для консолидации, чтобы уменьшить время блокировки таблицы.

SELECT columnar.vacuum('mytable', 25);

Используя необязательный аргумент количества полос, вакуумирование может быть выполнено поэтапно. Будет возвращено значение, показывающее, сколько полос было изменено. Повторный вызов этой функции допустим, и она будет продолжать вакуумировать таблицу, пока не останется ничего больше для выполнения, и вернет 0 в качестве количества.

Параметр	Описание	По умолчанию
таблица	Имя таблицы для вакуумирования	нет, обязательно
stripe_count	Количество полос для вакуумирования	`0`, или все

F.21.9.3.2. Вакуумирование всех #

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

SELECT columnar.vacuum_full();

По умолчанию это будет выполнять очистку схемы public, но существуют другие параметры для управления процессом очистки.

Параметр	Описание	По умолчанию
схема	Какую схему вакуумировать, если у вас более одной схемы, потребуется сделать несколько вызовов	`public`
sleep_time	Время сна в секундах между вызовами vacuum	`0.1`
stripe_count	Количество полос для очистки на таблицу между вызовами	`25`

F.21.9.3.2.1. Примеры #

SELECT columnar.vacuum_full('public');
SELECT columnar.vacuum_full(sleep_time => 0.5);
SELECT columnar.vacuum_full(stripe_count => 1000);

F.21.9.4. Изоляция #

Для терминов, используемых в этом разделе, пожалуйста, обратитесь к Раздел 13.2.

Обновления и удаления в столбцах Hydra будут соответствовать уровню изоляции, запрашиваемому для вашей текущей транзакции (по умолчанию это READ COMMITTED). Имейте в виду, что запрос UPDATE реализуется как DELETE, за которым следует INSERT. Поскольку новые данные, вставленные в одной транзакции, могут появиться во второй транзакции в READ COMMITTED, это может повлиять на параллельные транзакции, даже если первая транзакция была UPDATE. Хотя это удовлетворяет READ COMMITTED, это может привести к неожиданному поведению. Это также возможно в кучах (таблицах на основе строк), но таблицы кучи содержат дополнительные метаданные, которые ограничивают влияние этого случая.

Для более сильных гарантий изоляции, REPEATABLE READ рекомендуется. На этом уровне изоляции ваша транзакция будет отменена, если она ссылается на данные, которые были изменены другой транзакцией. В этом случае ваше приложение должно быть готово повторить транзакцию.

F.21.10. Оптимизация производительности запросов #

F.21.10.1. Кэш столбцов #

Колонковое хранилище широко использует сжатие, позволяя эффективно хранить ваши данные на диске и в памяти в сжатом виде.

Хотя это очень полезно, в некоторых случаях длительный аналитический запрос может многократно извлекать и декомпрессировать одни и те же столбцы. Чтобы противодействовать этому, доступен механизм кэширования столбцов, который позволяет хранить не сжатые данные в памяти, до определенного объема памяти на одного рабочего.

Эти не сжатые столбцы затем доступны в течение всего времени выполнения запроса SELECT и управляются непосредственно колонным хранилищем. ПРИМЕЧАНИЕ: этот кэш не используется для UPDATE, DELETE или INSERT и освобождается после завершения запроса SELECT. #### Включение кэша

Кэширование можно включить или отключить с помощью GUC: columnar.enable_column_cache. Для запросов, где кэш не полезен, вы можете обнаружить немного лучшую производительность, отключив кэш.

-- enable the cache
set columnar.enable_column_cache = 't';

-- disable the cache
set columnar.enable_column_cache = 'f';

Кроме того, размер кэша можно установить, по умолчанию 200MB на процесс.

set columnar.column_cache_size = '2000MB';

Этот размер может варьироваться от 20MB до 20000MB и потребляется каждым параллельным процессом. Это означает, что если у вас есть 8 параллельных процессов и установлен размер кэша 2000MB, то запрос может потреблять до 8 * 2000000 байт, или до 16GB оперативной памяти, поэтому очень важно выбрать размер кэша, который подходит для вашей рабочей нагрузки.

F.21.10.2. Очистка таблиц #

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

VACUUM table перепишет недавно созданные полосы в оптимальные полосы максимального размера. Если выполняется много небольших вставок или обновлений в колонночную таблицу, запускайте VACUUM часто для оптимизации таблицы.
SELECT columnar.vacuum_all() освободит пространство от обновленных и удаленных строк, записывая новые оптимальные полосы и уплотняя объем занимаемого таблицей пространства.
VACUUM FULL table выполнит полную перепись таблицы, создавая полностью оптимизированные полосы. Эта операция очень затратная, но дает наилучшие результаты.

F.21.11. Материализованные представления #

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

Материализованные представления обычно используются в сценариях, где производительность запросов критична, запросы выполняются часто на больших и сложных наборах данных, а данные, к которым выполняются запросы, не изменяются так часто. В случаях, когда ваши данные, к которым выполняются запросы, требуют множественных агрегаций, соединений и операций, которые часто вычисляются для отчетности или аналитики, вы можете эффективно улучшить производительность и сократить время выполнения, используя предварительно вычисленные материализованные представления.

Вы можете создать материализованное представление, используя следующую команду:

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    AS query
    [ WITH [ NO ] DATA ]

Вы можете установить USING columnar, чтобы хранить большие материализованные представления в колоннарном формате для быстрой аналитики
Вы можете установить USING heap для традиционных материализованных представлений на основе строк

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

CREATE MATERIALIZED VIEW sales_summary
  USING columnar
AS
SELECT
    date_trunc('month', order_date) AS month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * price) AS total_sales
FROM
    sales
GROUP BY
    date_trunc('month', order_date),
    product_id;

Вы можете обновить ваше материализованное представление с последними данными, используя REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] view_name. Использование опции CONCURRENTLY позволит обновить представление, продолжая выполнять запросы к текущим данным, но требует уникального индекса на представлении.

F.21.11.1. Лучшие практики для материализованных представлений #

Есть несколько вещей, которые вы можете сделать, чтобы убедиться, что вы максимально используете материализованные представления:

Обновите ваше материализованное представление: Частота и методы, которые вы используете для обновления вашего материализованного представления, являются ключевыми факторами, которые влияют на общую эффективность вашего представления. Поскольку обновление материализованного представления может быть ресурсоемкой операцией, оно лучше подходит для данных, которые обновляются нечасто.
Следите за дисковым пространством и производительностью: Материализованные представления могут потреблять значительные объемы дискового пространства, поэтому важно следить за их размером и удалять неиспользуемые, чтобы освободить место. Также следует следить за их производительностью обновления, чтобы убедиться, что они не оказывают негативного влияния на общую производительность.
Добавьте индексы для оптимизации ваших запросов: Материализованные представления предлагают явное преимущество, так как хранятся как обычные таблицы в Postgres. Это означает, что они могут в полной мере использовать преимущества индексации, что приводит к повышенной производительности и эффективной обработке больших наборов данных.

F.21.11.2. Инкрементальные материализованные представления с pg_ivm #

Документация в этом разделе предоставлена pg_ivm.

pg_ivm предоставляет способ поддерживать актуальность материализованных представлений, где вычисляются и применяются только инкрементальные изменения, а не пересчитывается содержимое с нуля, как это делает REFRESH MATERIALIZED VIEW. Этот процесс называется “инкрементальное обслуживание представлений” (или IVM), и он может обновлять материализованные представления более эффективно, чем пересчет, когда изменяются только небольшие части представления.

Существует два подхода к времени обновления представлений: немедленный и отложенный. При немедленном обновлении представления обновляются в той же транзакции, в которой изменяется базовая таблица. При отложенном обновлении представления обновляются после фиксации транзакции, например, когда к представлению осуществляется доступ, в ответ на команду пользователя, такую как REFRESH MATERIALIZED VIEW, или периодически в фоновом режиме и так далее. pg_ivm предоставляет своего рода немедленное обновление, при котором материализованные представления обновляются немедленно в триггерах AFTER, когда изменяется базовая таблица.

Чтобы начать использовать pg_ivm, вы должны сначала включить его в вашей базе данных:

CREATE EXTENSION IF NOT EXISTS pg_ivm;

Мы называем материализованное представление, поддерживающее IVM, инкрементально поддерживаемым материализованным представлением (IMMV). Чтобы создать IMMV, вам необходимо вызвать функцию create_immv с именем отношения и запросом определения представления. Например:

SELECT create_immv('sales_test', 'SELECT * FROM sales');

создает IMMV с именем ‘sales_test’, определенный как SELECT * FROM sales. Это соответствует следующей команде для создания обычного материализованного представления:

CREATE MATERIALIZED VIEW sales_test AS SELECT * FROM sales;

Когда создается IMMV, некоторые триггеры создаются автоматически, чтобы содержимое представления немедленно обновлялось при изменении его базовых таблиц.

postgres=# SELECT create_immv('m', 'SELECT * FROM t0');
NOTICE:  could not create an index on immv "m" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv
-------------
           3
(1 row)

postgres=# SELECT * FROM m;
 i
---
 1
 2
 3
(3 rows)

postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECT * FROM m; -- automatically updated
 i
---
 1
 2
 3
 4
(4 rows)

F.21.11.2.1. IMMV и Колонный #

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

SET default_table_access_method = 'columnar';
SET max_parallel_workers = 1;
SELECT create_immv('sales_test', 'SELECT * FROM sales');

После создания IMMV вы можете восстановить настройки по умолчанию. (Эти настройки не влияют на другие соединения и также будут восстановлены до значений по умолчанию в вашей следующей сессии.)

SET max_parallel_workers = DEFAULT;
SET default_table_access_method = DEFAULT;

F.21.11.2.2. Функции #

F.21.11.2.2.1. create_immv #

Используйте функцию create_immv для создания IMMV.

create_immv(immv_name text, view_definition text) RETURNS bigint

create_immv определяет новый IMMV для запроса. Создается таблица с именем immv_name, и выполняется запрос, указанный в view_definition, который используется для заполнения IMMV. Запрос сохраняется в pg_ivm_immv, чтобы его можно было обновить позже при инкрементальном обслуживании представлений. create_immv возвращает количество строк в созданном IMMV.

Когда создается IMMV, триггеры создаются автоматически, чтобы содержимое представления немедленно обновлялось при изменении его базовых таблиц. Кроме того, уникальный индекс создается на IMMV автоматически, если это возможно. Если в запросе определения представления есть предложение GROUP BY, уникальный индекс создается на столбцах выражений GROUP BY. Также, если в представлении есть предложение DISTINCT, уникальный индекс создается на всех столбцах в целевом списке. В противном случае, если IMMV содержит все атрибуты первичного ключа своих базовых таблиц в целевом списке, уникальный индекс создается на этих атрибутах. В других случаях индекс не создается.

F.21.11.2.2.2. refresh_immv #

Используйте функцию refresh_immv для обновления IMMV.

refresh_immv(immv_name text, with_data bool) RETURNS bigint

refresh_immv полностью заменяет содержимое IMMV, как команда REFRESH MATERIALIZED VIEW делает для материализованного представления. Чтобы выполнить эту функцию, вы должны быть владельцем IMMV. Старое содержимое удаляется.

Флаг with_data соответствует WITH [NO] DATA опции команды REFRESH MATERIALIZED VIEW. Если with_data истинно, выполняется поддерживающий запрос для предоставления новых данных, и если IMMV не заполнен, создаются триггеры для поддержания представления. Также создается уникальный индекс для IMMV, если это возможно и если представление его еще не имеет. Если with_data ложно, новые данные не генерируются и IMMV становится незаполненным, а триггеры удаляются из IMMV. Обратите внимание, что незаполненный IMMV все еще может быть просканирован, хотя результат будет пустым. Это поведение может быть изменено в будущем, чтобы вызывать ошибку при сканировании незаполненного IMMV.

F.21.11.2.2.3. get_immv_def #

get_immv_def реконструирует исходную команду SELECT для IMMV. (Это декомпилированная реконструкция, а не оригинальный текст команды.)

get_immv_def(immv regclass) RETURNS text

F.21.11.2.2.4. Каталог метаданных IMMV #

Каталог pg_ivm_immv хранит информацию IMMV.

Имя	Тип	Описание
immvrelid	regclass	OID IMMV
viewdef	text	Дерево запроса (в виде представления nodeToString()) для определения представления
ispopulated	bool	Истина, если IMMV в настоящее время заполнен

F.21.11.2.3. Пример #

В общем, IMMVs позволяют более быстрые обновления, чем REFRESH MATERIALIZED VIEW, за счет более медленных обновлений их базовых таблиц. Обновление базовых таблиц медленнее, потому что будут вызываться триггеры, и IMMV обновляется в триггерах для каждого оператора модификации.

Например, предположим, что обычное материализованное представление определено как ниже:

test=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.aid, b.bid, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
SELECT 10000000

Обновление кортежа в базовой таблице этого материализованного представления происходит быстро, но команда REFRESH MATERIALIZED VIEW для этого представления занимает много времени:

test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 9.052 ms

test=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00:20.576)

С другой стороны, после создания IMMV с тем же определением представления, как показано ниже:

test=# SELECT create_immv('immv',
        'SELECT a.aid, b.bid, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv
-------------
    10000000
(1 row)

обновление кортежа в базовой таблице занимает больше времени, чем в обычном представлении, но его содержимое обновляется автоматически, и это быстрее, чем команда REFRESH MATERIALIZED VIEW.

test=# UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;
UPDATE 1
Time: 15.448 ms

test=# SELECT * FROM immv WHERE aid = 1;
 aid | bid | abalance | bbalance
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

Необходим соответствующий индекс на IMMV для эффективного IVM, потому что нам нужно искать кортежи для обновления в IMMV. Если индексов нет, это займет много времени.

Поэтому, когда IMMV создается с помощью функции create_immv, уникальный индекс создается автоматически, если это возможно. Если в запросе определения представления есть предложение GROUP BY, уникальный индекс создается на столбцах выражений GROUP BY. Также, если в представлении есть предложение DISTINCT, уникальный индекс создается на всех столбцах в целевом списке. В противном случае, если IMMV содержит все атрибуты первичного ключа своих базовых таблиц в целевом списке, уникальный индекс создается на этих атрибутах. В других случаях индекс не создается.

В предыдущем примере уникальный индекс “immv_index” создается на столбцах aid и bid таблицы “immv”, и это позволяет быстро обновлять представление. Удаление этого индекса делает обновление представления более длительным.

test=# DROP INDEX immv_index;
DROP INDEX

test=# UPDATE pgbench_accounts SET abalance = 9876 WHERE aid = 1;
UPDATE 1
Time: 3224.741 ms (00:03.225)

F.21.11.2.4. Поддерживаемые Определения Представлений и Ограничения #

В настоящее время определение представления IMMV может содержать внутренние соединения, оператор DISTINCT, некоторые встроенные агрегатные функции, простые подзапросы в FROM секции и простые CTE (WITH запрос). Поддерживаются внутренние соединения, включая самосоединение, но внешние соединения не поддерживаются. Поддерживаемые агрегатные функции: count, sum, avg, min и max. Другие агрегаты, подзапросы, содержащие агрегат или оператор DISTINCT, подзапросы в других секциях, кроме FROM, оконные функции, HAVING, ORDER BY, LIMIT/OFFSET, UNION/INTERSECT/EXCEPT, DISTINCT ON, TABLESAMPLE, VALUES и FOR UPDATE/SHARE не могут использоваться в определении представления.

Базовые таблицы должны быть простыми таблицами. Представления, материализованные представления, родительские таблицы наследования, секционированные таблицы, секции и внешние таблицы не могут быть использованы.

Целевой список не может содержать системные столбцы, столбцы, имя которых начинается с __ivm_.

Логическая репликация не поддерживается, то есть даже когда базовая таблица на узле издателя изменяется, IMMVs на узлах подписчиков, определенные на этих базовых таблицах, не обновляются.

F.21.11.2.5. Ограничения и ограничения #

F.21.11.2.5.1. Агрегаты #

Поддерживаемые агрегатные функции: count, sum, avg, min и max. В настоящее время поддерживаются только встроенные агрегатные функции, и пользовательские агрегаты не могут быть использованы.

Когда создается IMMV, включающий агрегат, некоторые дополнительные столбцы, имена которых начинаются с __ivm, автоматически добавляются в целевой список. __ivm_count__ содержит количество кортежей, агрегированных в каждой группе. Кроме того, для каждого столбца агрегированного значения добавляется более одного дополнительного столбца для поддержания значения. Например, столбцы с именами, такими как __ivm_count_avg__ и __ivm_sum_avg__, добавляются для поддержания среднего значения. Когда базовая таблица изменяется, новые агрегированные значения вычисляются инкрементально, используя старые агрегированные значения и значения связанных дополнительных столбцов, хранящихся в IMMV.

Обратите внимание, что для min или max новые значения могут быть пересчитаны из базовых таблиц с учетом затронутых групп, когда кортеж, содержащий текущие минимальные или максимальные значения, удаляется из базовой таблицы. Поэтому обновление IMMV, содержащего эти функции, может занять много времени.

Также обратите внимание, что использование sum или avg для типа real (float4) или double precision (float8) в IMMV небезопасно, потому что агрегированные значения в IMMV могут отличаться от результатов, вычисленных из базовых таблиц, из-за ограниченной точности этих типов. Чтобы избежать этой проблемы, используйте тип numeric.

F.21.11.2.5.2. Ограничения на агрегаты #

Если у нас есть GROUP BY выражение, то выражения, указанные в GROUP BY, должны появляться в целевом списке. Это то, как кортежи, которые будут обновлены в IMMV, идентифицируются. Эти атрибуты используются в качестве ключей сканирования для поиска кортежей в IMMV, поэтому индексы на них необходимы для эффективного IVM.

Targetlist не может содержать выражения, которые содержат агрегат в нем.

F.21.11.2.5.3. Подзапросы #

Простые подзапросы в FROM предложении поддерживаются.

F.21.11.2.5.4. Ограничения на подзапросы #

Подзапросы могут использоваться только в FROM предложении. Подзапросы в списке целей или WHERE предложении не поддерживаются.

Подзапросы, содержащие агрегатную функцию или DISTINCT, не поддерживаются.

F.21.11.2.6. CTE #

Простые CTE (WITH запросы) поддерживаются.

F.21.11.2.6.1. Ограничения на CTE #

WITH запросы, содержащие агрегатную функцию или DISTINCT, не поддерживаются.

Рекурсивные запросы (WITH RECURSIVE) не разрешены. Нессылочные CTE также не разрешены, то есть CTE должен быть упомянут хотя бы один раз в запросе определения представления.

F.21.11.2.6.2. DISTINCT #

DISTINCT разрешен в определяющих запросах IMMV. Предположим, что IMMV определен с DISTINCT на базовой таблице, содержащей дублирующиеся кортежи. Когда кортежи удаляются из базовой таблицы, кортеж в представлении удаляется, если и только если кратность кортежа становится равной нулю. Более того, когда кортежи вставляются в базовую таблицу, кортеж вставляется в представление только в том случае, если такой же кортеж уже не существует в нем.

Физически, IMMV, определенный с DISTINCT, содержит кортежи после устранения дубликатов, и кратность каждого кортежа хранится в дополнительной колонке с именем __ivm_count__, которая добавляется при создании такого IMMV.

F.21.11.2.6.3. TRUNCATE #

Когда базовая таблица усечена, IMMV также усечен, и содержимое становится пустым, если запрос определения представления не содержит агрегат без GROUP BY выражения. Агрегатные представления без GROUP BY выражения всегда имеют одну строку. Поэтому в таких случаях, если базовая таблица усечена, IMMV просто обновляется вместо усечения.

F.21.11.2.6.4. Одновременные транзакции #

Предположим, что IMMV определен на двух базовых таблицах, и каждая таблица была изменена в разных параллельных транзакциях одновременно. В транзакции, которая была зафиксирована первой, IMMV может быть обновлен, учитывая только изменения, произошедшие в этой транзакции. С другой стороны, для правильного обновления IMMV в транзакции, которая была зафиксирована позже, нам нужно знать изменения, произошедшие в обеих транзакциях. По этой причине, ExclusiveLock удерживается на IMMV сразу после того, как базовая таблица изменена в режиме READ COMMITTED, чтобы убедиться, что IMMV обновляется в последующей транзакции после фиксации предыдущей транзакции. В режиме REPEATABLE READ или SERIALIZABLE ошибка возникает сразу, если захват блокировки не удается, потому что любые изменения, произошедшие в других транзакциях, не видны в этих режимах, и IMMV не может быть обновлен правильно в таких ситуациях. Однако, как исключение, если IMMV имеет только одну базовую таблицу и не использует DISTINCT или GROUP BY, и таблица изменяется с помощью INSERT, то блокировка, удерживаемая на IMMV, является RowExclusiveLock.

F.21.11.2.6.5. Уровень безопасности строк #

Если некоторые базовые таблицы имеют политику безопасности на уровне строк, строки, которые не видны владельцу материализованного представления, исключаются из результата. Кроме того, такие строки также исключаются, когда представления поддерживаются инкрементно. Однако, если новая политика определена или политики изменены после создания материализованного представления, новая политика не будет применена к содержимому представления. Чтобы применить новую политику, необходимо воссоздать IMMV.

F.21.11.2.6.6. Как отключить или включить немедленное обслуживание #

IVM эффективен, когда мы хотим поддерживать IMMV в актуальном состоянии, и небольшая часть базовой таблицы изменяется нечасто. Из-за накладных расходов на немедленное обслуживание, IVM не эффективен, когда базовая таблица изменяется часто. Также, когда большая часть базовой таблицы изменяется или в базовую таблицу вставляются большие данные, IVM не эффективен, и стоимость обслуживания может быть выше, чем обновление с нуля.

В такой ситуации мы можем использовать функцию refesh_immv с with_data = false, чтобы отключить немедленное обслуживание перед изменением базовой таблицы. После изменения базовой таблицы вызовите refresh_immv с with_data = true, чтобы обновить данные представления и включить немедленное обслуживание.

F.21.12. Векторное выполнение #

Векторное выполнение - это техника, которая используется для улучшения производительности запросов к базе данных путем одновременного выполнения нескольких операций. Это контрастирует с традиционным выполнением, где каждая операция выполняется поочередно.

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

Например, рассмотрим запрос, который вычисляет сумму столбца чисел в таблице. При традиционном выполнении база данных проходила бы по каждой строке таблицы поочередно, добавляя каждое число к текущей сумме. При векторизованном выполнении база данных делила бы данные на векторы, а затем использовала бы несколько ядер процессора для одновременного сложения чисел в каждом векторе. Это может значительно сократить время выполнения запроса.

Используя возможности современных процессоров и выполняя несколько операций одновременно, векторизированное выполнение может значительно повысить скорость и эффективность запросов к базе данных.

F.21.13. Параллелизация Запросов #

Параллелизация запросов — это метод, используемый для улучшения производительности запросов к базе данных путем их выполнения параллельно на нескольких ядрах процессора или машинах. Это контрастирует с традиционным последовательным выполнением, где каждая операция выполняется поочередно.

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

Например, рассмотрим запрос, который вычисляет сумму столбца чисел в таблице. В традиционном последовательном выполнении база данных проходит по каждой строке таблицы поочередно, добавляя каждое число к текущей сумме. При параллелизации запроса база данных делит данные на меньшие части и распределяет их между несколькими ядрами или машинами, которые затем вычисляют сумму своих соответствующих частей одновременно. Это может значительно сократить время выполнения запроса.

Используя возможности современных процессоров и выполняя запросы параллельно, параллелизация запросов может значительно улучшить скорость и эффективность операций с базой данных.

F.21.14. Общие рекомендации #

F.21.14.1. Выбор размера полосы (Stripe Size) и размера блока (Chunk Size) #

Определение оптимальных размеров полос и блоков для columnar таблиц в Tantor SE зависит от различных факторов, таких как характер данных, шаблоны запросов и характеристики оборудования. Вот некоторые рекомендации:

Размер полосы Оптимальный размер «полосы» может зависеть от типичного размера ваших запросов. Если запросы обычно возвращают большое количество строк, больший размер «полосы» может быть более эффективным, поскольку он сокращает количество операций ввода/вывода. С другой стороны, если запросы часто возвращают небольшое подмножество данных, предпочтительнее может быть меньший размер «полосы».
Размер блока : Определяет, сколько данных будет сжиматься за раз. Меньший размер блока может привести к более высокому коэффициенту сжатия, но может увеличить издержки на сжатие. Больший размер блока может снизить издержки, но потенциально уменьшить коэффициент сжатия.
Тестирование и настройка : Важно проводить тестирование с реальными данными и запросами, чтобы определить оптимальные настройки для вашей конкретной ситуации. Можно начать с рекомендуемых настроек по умолчанию, а затем экспериментировать с различными размерами полос и блоков, чтобы увидеть, как это влияет на производительность запросов и коэффициенты сжатия.
Свойства оборудования : Стоит также учесть характеристики оборудования, такие как пропускная способность диска и процессора, так как это может влиять на то, какие размеры полос и блоков будут наиболее эффективными.

В конечном итоге, оптимальные размеры полосы и блока зависят от уникальных характеристик вашей среды, данных и шаблонов запросов.

F.21.14.2. Алгоритмы сжатия #

Выбор алгоритма сжатия для columnar таблиц в Tantor SE зависит от нескольких факторов, включая характер данных, требования к производительности, и технические характеристики оборудования. Вот несколько рекомендаций, которые могут помочь вам принять решение:

none: Этот тип сжатия не применяет никакого сжатия к данным. Он может быть полезен, если ваши данные уже сжаты, или если у вас очень высокие требования к производительности и достаточно дискового пространства.
lz4: обеспечивает быстрое сжатие и распаковку данных. Это может быть полезно, если у вас высокие требования к производительности, но вы все же хотите сэкономить немного дискового пространства.
zstd: предлагает более высокое соотношение сжатия по сравнению с LZ4, но требует больше процессорного времени для сжатия и распаковки данных. Этот алгоритм может быть полезен, если у вас ограниченное дисковое пространство и вы готовы потратить немного больше процессорного времени на сжатие данных.

Важно отметить, что выбор алгоритма сжатия является компромиссом между производительностью (скоростью сжатия и распаковки) и дисковым пространством. Кроме того, эффективность каждого алгоритма сжатия может значительно зависеть от характера ваших данных. Поэтому рекомендуется провести некоторые тесты с реальными данными и запросами, чтобы определить наиболее подходящий алгоритм сжатия в вашей ситуации.

F.21.15. Работа с Time Series-данными #

F.21.15.1. Создание таблиц #

Time Series-данные характеризуются последовательной записью значений по возрастанию во времени. Для таких данных важна упорядоченность.

Создайте необходимые расширения:

create extension pg_columnar;
create extension plpython3u;

Создайте тестовую таблицу:

CREATE TABLE perf_columnar(id INT8,
ts TIMESTAMPTZ,
customer_id INT8,
vendor_id INT8,
name TEXT,
description TEXT,
value NUMERIC,
quantity INT4) USING columnar;

ALTER TABLE public.perf_columnar SET
  (columnar.compression = lz4, columnar.stripe_row_limit = 100000, columnar.chunk_group_row_limit = 10000);

Создайте функцию для генерации случайного текста:

CREATE OR REPLACE FUNCTION random_words(n INT4) RETURNS TEXT LANGUAGE plpython3u AS $$
import random
t = ''
words = ['zero','one','two','three','four','five','six','seven','eight','nine','ten']
for i in range(0, n):
  if (i != 0):
    t += ' '
  r = random.randint(0, len(words) - 1)
  t += words[r]
return t
$$;

Сгенерируйте тестовые данные:

INSERT INTO perf_columnar
   SELECT
    g, -- id
    '2023-06-01'::timestamptz + ('1 minute'::interval * g), -- ts
    (random() * 1000000)::INT4, -- customer_id
    (random() * 100)::INT4, -- vendor_id
    random_words(7), -- name
    random_words(100), -- description
    (random() * 100000)::INT4/100.0, -- value
    (random() * 100)::INT4 -- quantity
   FROM generate_series(1,7500000) g;

Как видно из приведенного выше запроса, мы вставили данные, отсортированные по ts. Соберите статистику с помощью VACUUM (ANALYZE, VERBOSE):

VACUUM ANALYZE perf_columnar;

Создайте копию таблицы с другими параметрами:

CREATE TABLE perf_columnar2(LIKE perf_columnar) USING COLUMNAR;

ALTER TABLE public.perf_columnar2 SET
  (columnar.compression = zstd, columnar.stripe_row_limit = 10000, columnar.chunk_group_row_limit = 1000);

INSERT INTO perf_columnar2 SELECT * FROM perf_columnar;

VACUUM ANALYZE perf_columnar2;

Проверьте размер получившихся таблиц:

test_db=# \dt+
                                        List of relations
 Schema |      Name      | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------------+-------+----------+-------------+---------------+---------+-------------
 public | perf_columnar  | table | postgres | permanent   | columnar      | 1886 MB | 
 public | perf_columnar2 | table | postgres | permanent   | columnar      | 850 MB  | 
(2 rows)

Как видно, таблица perf_columnar в два раза больше, чем perf_columnar2.

Проверьте параметры таблиц:

test_db=# select * from columnar.options;

    relation    | chunk_group_row_limit | stripe_row_limit | compression | compression_level 
----------------+-----------------------+------------------+-------------+-------------------
 perf_columnar  |                 10000 |           100000 | lz4         |                 3
 perf_columnar2 |                  1000 |            10000 | zstd        |                 3

(2 rows)

F.21.15.2. Сравнение производительности запросов #

Выполните типичный запрос для чтения набора записей из таблицы perf_columnar за определенный промежуток времени:

explain (analyze, verbose, buffers) select ts from perf_columnar
where ts < '2023-06-01 10:00:00'::timestamp with time zone and ts > '2023-06-01 10:00:05'::timestamp with time zone;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar  (cost=0.00..401.12 rows=4 width=8) (actual time=18.838..49.001 rows=4 loops=1)
   Output: ts
   Filter: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 9996
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 749     <-----------
   Buffers: shared hit=3833 read=264                <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=52 read=7
 Planning Time: 12.789 ms
 Execution Time: 49.188 ms
(13 rows)

Выполните типичный запрос для чтения набора записей из таблицы perf_columnar2 за определенный промежуток времени:

explain (analyze, verbose, buffers) select ts from perf_columnar2
where ts < '2020-01-01 10:00:00'::timestamp with time zone and ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar2  (cost=0.00..17.95 rows=52 width=8) (actual time=5.175..58.532 rows=49 loops=1)
   Output: ts
   Filter: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 951
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 7499    <-----------
   Buffers: shared hit=40824 read=1                 <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=155
 Planning Time: 1.086 ms
 Execution Time: 58.717 ms
(13 rows)

Обратите внимание на Columnar Chunk Groups Removed by Filter: 749 в первом случае и 7499 во втором. Однако, количество прочитанных буферов во втором запросе гораздо выше.

Терминология для понимания поведения этих запросов:

Stripe: Этот тип не применяет никакого сжатия к данным. Он может быть полезен, если ваши данные уже сжаты, или если у вас очень высокие требования к производительности и достаточно дискового пространства.
Chunk Group: полосы разбиваются на группы блоков по 10 000 строк (по умолчанию).
Chunk: Каждая группа блоков состоит из одного блока для каждого столбца. Chunk является единицей сжатия, и для каждого блока отслеживаются минимальные/максимальные значения, что позволяет фильтровать группы блоков (Chunk Group Filtering).
Chunk Group Filtering: когда предложение запроса WHERE не соответствует ни одному кортежу в блоке, и можно определить это по минимальному/максимальному значению для блока, тогда фильтрация группы блоков просто пропускает всю группу блоков без ее распаковки.

Как мы видим, были отфильтрованы группы блоков 749/7499, что означает, что строки 7490000/7499000 были отфильтрованы без необходимости извлечения или распаковки данных. Только одна группа блоков (10 000 и 1 000 строк) требовала извлечения и распаковки, поэтому запрос занял всего несколько миллисекунд.

Но, как видно из планов запросов, в первом случае было использовано 30 МБ, а во втором - в десять раз больше - 319 МБ.

F.21.15.3. Использование индексов #

Columnar поддерживает индексы btree и hash (и ограничения, требующие их), но не поддерживает типы индексов, такие как gist, gin, spgist и brin.

Рассмотрите возможность создания индексов для таблиц perf_columnar и perf_columnar2:

create index test on perf_columnar2 (ts);
create index test_h on perf_columnar (ts);

Проверьте размер индексов:

test_db=# \di+
                                            List of relations

 Schema |  Name  | Type  |  Owner   |     Table      | Persistence | Access method |  Size  | Description 
--------+--------+-------+----------+----------------+-------------+---------------+--------+-------------
 public | test   | index | postgres | perf_columnar2 | permanent   | btree         | 161 MB | 
 public | test_h | index | postgres | perf_columnar  | permanent   | btree         | 161 MB | 
(2 rows)

Размер индексов одинаковый.

Отключите принудительное использование Custom Scan:

SET columnar.enable_custom_scan TO OFF;

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

explain (analyze, verbose, buffers) select ts from perf_columnar where ts < '2023-06-01 10:00:00'::timestamp with time zone and ts > '2023-06-01 10:00:05'::times
tamp with time zone;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_h on public.perf_columnar  (cost=0.43..3217.48 rows=4 width=8) (actual time=402.144..402.204 rows=4 loops=1)
   Output: ts
   Index Cond: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=181 read=3232      <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=20 read=5
 Planning Time: 16.278 ms
 Execution Time: 402.386 ms
(9 rows)

Почти такое же количество буферов было просканировано, как при использовании Custom Scan.

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

explain (analyze, verbose, buffers) select ts from perf_columnar2 where ts < '2020-01-01 10:00:00'::timestamp with time zone and ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test on public.perf_columnar2  (cost=0.43..153.05 rows=52 width=8) (actual time=14.620..14.821 rows=49 loops=1)
   Output: ts
   Index Cond: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=372 read=145     <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=97
 Planning Time: 0.813 ms
 Execution Time: 14.978 ms
(13 rows)

Для второй таблицы было просканировано гораздо меньше буферов, и запрос выполняется наиболее оптимальным образом.

F.21.15.4. Заключения #

Можно сделать следующие выводы:

Самым эффективным методом сжатия данных является zstd;
Размер полосы и блока влияет на количество буферов, сканируемых с использованием метода Chunk Group Filtering;
При чтении небольшого объема данных использование индекса может оказаться более эффективным;
Для проверки гипотезы необходимо принудительно включить использование индексов с помощью команды SET columnar.enable_custom_scan TO OFF;
Последовательная запись данных TS может значительно сократить размер индексов и объем распакованных блоков (Chunk). Поэтому рекомендуется сортировать данные перед их вставкой в базу данных или использовать кластеризацию (cluster).

Назад	Наверх	Далее
F.20. hstore — hstore ключ/значение тип данных	Начало	F.22. HypoPG