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

В примерах я буду использовать набор данных, содержащий 52 608 000 записей.

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

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

Если читатель без труда может расшифровать аббревиатуры OLTP и OLAP, возможно есть смысл перейти сразу к разделу Columnstore

Два подхода к работе с данными

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

Итак, на самом верхнем уровне существует всего два подхода к работе с данными: OLTP и OLAP.

OLTP — можно перевести, как моментальная обработка транзакций. На самом деле, речь идёт об онлайн обработке коротких транзакций, работающих с небольшим объёмом данных. Например, запись, обновление или удаление заказа. В подавляющем большинстве случаев заказ — это крайне малый объём данных, при обработке которого можно не бояться длительных блокировок, накладываемых современными РСУБД.

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

Для каждого из подходов существуют свои технологии. Например, для OLTP — это PostgreSQL, а для OLAP — это Microsoft SQL Server Analysis Services. В то время как PostgresSQL использует широко известный формат хранения данных в таблицах, для OLAP придумали несколько разных форматов. Это многомерные таблицы, бакеты наполненные парами ключ-значение и любимый мною columnstore. О последнем более подробно ниже.

Зачем нужны именно два подхода?

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

Итак, в OLTP мы имеем таблицы и индексы. Эти два инструмента прекрасно подходят для записи активности кассы со всеми деталями. Индексы обеспечивают быстрый поиск записанного ранее заказа, поэтому изменить заказ не составляет труда. Но для, того, чтобы удовлетворить потребности руководителя, нам необходимо считать весь объём накопленных за день данных. К тому же, как правило, руководителю не нужны все детали всех заказов. Что ему действительно нужно знать это то, сколько денег заработала касса в общем. При этом не важно, где касса стояла, когда был перерыв на обед, кто за ней работал, и т.д. OLAP существует затем, чтобы за короткий временной отрезок система могла ответить на вопрос — сколько в целом заработало предприятие без последовательного чтения каждого заказа и всех его деталей. Может ли OLAP использовать те же таблицы и индексы, что и OLTP? Ответ нет, по крайней мере, не должна. Во-первых, потому что OLAP просто не нужны все детали, записанные в таблицах. Эта проблема решается путем хранения данных в иных форматах, отличных от двумерных таблиц. Во-вторых, анализируемая информация, часто, рассредоточена по разным таблицам, что влечет за собой множественные их объединения включая объединения типа self-join. Для решения этой проблемы, как правило, разрабатывают специальную схему БД. Эта схема получается оптимизированной для OLAP нагрузки, так же хорошо, как обычная нормализованная схема для OLTP нагрузки.

Что происходит, когда OLAP использует OLTP схему

На самом деле, я ввёл этот раздел, чтобы эта статья четко удовлетворяла моим собственным требованиям к формату подобного материала, т.е. проблема, решение, заключение.

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

• Слишком много индексов.
  
  Нередко для поддержки отчетов приходится делать специальные индексы. Такие индексы реализуют схему хранения данных OLAP. Они не используются OLTP частью приложения, при этом оказывают нагрузку именно на неё, требуя постоянной поддержки и занимая место на диске.
• Объём считываемых данных превышает необходимый.
• Отсутствие четкой схемы данных.
  
  Дело в том, что часто информация, подаваемая отчетами в едином виде разнесена по разным таблицам. Такая информация требует постоянной трансформации на лету. Простейший пример — это сумма выручки, которая складывается из наличных и безналичных денег. Еще один яркий пример — это иерархии данных. Т.к. развитие приложения происходит поступательно и не всегда известно, что потребуется в будущем, одна и та же по смыслу иерархия может храниться в разных таблицах. И хотя получение схемы налету активно применяется в OLAP это немного разные вещи.
• Чрезмерная сложность запросов.
  
  Т.к. OLTP схема отличается от OLAP необходим сильно связанный программный слой, который приводит схему данных OLTP к нужному виду.
• Сложность поддержки, отладки и развития.
  
  В целом можно сказать, что чем сложнее кодовая база, тем сложнее её поддерживать в работоспособном состоянии. Это аксиома.
• Сложность покрытия тестами.
  
  Много копий сломано на рассуждениях на тему, как получить базу данных, наполненную всеми тестовыми сценариями, но лучше сказать, что имея более простую схему данных задача покрытия тестами упрощается многократно.
• Бесконечная отладка производительности.
  
  Велика вероятность того, что пользователь закажет «неподъёмный» для сервера БД отчет. Эта вероятность растет с течением времени. Необходимо заметить, что и OLAP подвержена этой проблеме, но в отличии от OLTP, ресурс OLAP в этом вопросе гораздо выше.

Columnstore

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

На самом деле формат columnstore известен уже лет 30. Но в РСУБД он не был реализован до недавнего времени. Суть columnstore, в том, что хранение данных осуществляется не в строках, но в колонках. Т.е. на одну страницу (всем известные 8 Кб) сервер записывает данные только одного поля. И так с каждым полем в таблице по очереди. Необходимо это для того, чтобы не приходилось считывать лишнюю информацию. Давайте представим таблицу с 10 полями и запрос, у которого в операторе SELECT указано только одно поле. Если бы это была обычная таблица сохранённая в row-based формате, сервер был бы вынужден считывать все 10 полей, но при этом возвращать только одно. Получилось бы, что сервер считал в 9 раз больше информации, чем было необходимо. Columnstore полностью решает эту проблему, т.к. формат хранения позволяет считывать только одно заказанное поле. Все это происходит, потому что единица хранения в РСУБД — это страница. Т.е. сервер всегда записывает и считывает, как минимум одну страницу. Вопрос только в том, сколько полей присутствует на ней.

Чем реально Columnstore может помочь

Для ответа на этот необходимо иметь точные цифры. Давайте же получим их. Но какие цифры могут дать точную картину?

1. Объём дискового пространства.
2. Производительность запросов.
3. Отказоустойчивость.
4. Простота внедрения.
5. Какие новые навыки должны быть у разработчика для работы с новыми структурами.

Объём дискового пространства

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

create foreign table cstore_table 
( 
  trd date, 
  org int, 
  op int, 
  it int, 
  wh int, 
  m1 numeric(32, 2), 
  m2 numeric(32, 2), 
  m3 numeric(32, 2), 
  m4 numeric(32, 2), 
  m5 numeric(32, 2) 
) 
server cstore_server 
options(compression 'pglz'); 

Как вы заметили, я создал внешнюю таблицу. Дело в том, что PostgreSQL не имеет встроенной поддержки columnstore. Зато PostgreSQL имеет мощную систему для расширений. Одно из них как раз и даёт возможность создавать columnstore таблицы. Ссылки в конце статьи.

• pglz — сообщает расширению, что данные должны быть сжаты встроенным в PostgreSQL алгоритмом;
• trd — время транзакции;
• op, it, wh — аналитические разрезы или измерения;
• m1, m2, m3, m4, m5 — числовые показатели или меры;

Давайте вставим «приличный» объём данных и посмотрим, сколько места он займёт на диске. Заодно проверим производительность вставки. Т.к. свои эксперименты я ставлю на домашнем ноутбуке, я слегка органичен в объёмах данных. К тому же, что даже хорошо, я буду использовать HDD под управлением гостевой OS Fedora 30. Хост OS — Windows 10 Home Edition. Процессор Intel Core 7. Гостевая OS получила 4 Гб RAM. Версия PostgreSQL — PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit. Экспериментировать буду с набором данных с количеством записей 52 608 000.

explain (analyze) 
insert into cstore_table 
select  
  '2010-01-01'::date + make_interval(days => d) as trd 
  , op 
  , org 
  , wh 
  , it 
  , 100 as m1 
  , 100 as m2 
  , 100 as m3 
  , 100 as m4 
  , 100 as m5 
from generate_series(0, 1) as op 
cross join generate_series(1, 2) as org 
cross join generate_series(1, 3) as wh 
cross join generate_series(1, 4000) as it 
cross join generate_series(0, 1095) as d; 

План выполнения будет следующим

Insert on cstore_table (cost=0.01..24902714242540.01 rows=1000000000000000 width=150) (actual time=119560.456..119560.456 rows=0 loops=1)
----> Nested Loop (cost=0.01..24902714242540.01 rows=1000000000000000 width=150) (actual time=1.823..22339.976 rows=52608000 loops=1)
----------> Function Scan on generate_series d (cost=0.00..10.00 rows=1000 width=4) (actual time=0.151..2.198 rows=1096 loops=1)
----------> Materialize (cost=0.01..27284555030.01 rows=1000000000000 width=16) (actual time=0.002..3.196 rows=48000 loops=1096)
----------------> Nested Loop (cost=0.01..17401742530.01 rows=1000000000000 width=16) (actual time=1.461..15.072 rows=48000 loops=1)
----------------------> Function Scan on generate_series it (cost=0.00..10.00 rows=1000 width=4) (actual time=1.159..2.007 rows=4000 loops=1)
----------------------> Materialize (cost=0.01..26312333.01 rows=1000000000 width=12) (actual time=0.000..0.001 rows=12 loops=4000)
----------------------------> Nested Loop (cost=0.01..16429520.01 rows=1000000000 width=12) (actual time=0.257..0.485 rows=12 loops=1)
----------------------------------> Function Scan on generate_series wh (cost=0.00..10.00 rows=1000 width=4) (actual time=0.046..0.049 rows=3 loops=1)
----------------------------------> Materialize (cost=0.01..28917.01 rows=1000000 width=8) (actual time=0.070..0.139 rows=4 loops=3)
---------------------------------------> Nested Loop (cost=0.01..20010.01 rows=1000000 width=8) (actual time=0.173..0.366 rows=4 loops=1)
-------------------------------------------> Function Scan on generate_series op (cost=0.00..10.00 rows=1000 width=4) (actual time=0.076..0.079 rows=2 loops=1)
---------------------------------------------> Function Scan on generate_series org (cost=0.00..10.00 rows=1000 width=4) (actual time=0.043..0.047 rows=2 loops=2)
Planning time: 0.439 ms
Execution time: 119692.051 ms

Общее время выполнения — 1.994867517 минут

Время создания набора данных — 22.339976 секунд

Время вставки — 1.620341333 минут

Оценить занятое таблицей место на диске с помощью функций PostgreSQL у меня не получилось. Не уверен, почему, но показывает 0. Возможно это стандартное поведение для внешних таблиц. Использовал для этого файловый менеджер. Итак, объём занятого на диске места — 226.2 Мб. Чтобы оценить много это или мало давайте сравним с обычной таблицей.

explain (analyze) 
create table rbstore_table as  
select  
  '2010-01-01'::date + make_interval(days => d) as trd 
  , op 
  , org 
  , wh 
  , it 
  , 100 as m1 
  , 100 as m2 
  , 100 as m3 
  , 100 as m4 
  , 100 as m5 
from generate_series(0, 1) as op 
cross join generate_series(1, 2) as org 
cross join generate_series(1, 3) as wh 
cross join generate_series(1, 4000) as it 
cross join generate_series(0, 1095) as d; 

План выполнения будет следующим

Nested Loop (cost=0.01..22402714242540.01 rows=1000000000000000 width=44) (actual time=0.585..23781.942 rows=52608000 loops=1)
---> Function Scan on generate_series d (cost=0.00..10.00 rows=1000 width=4) (actual time=0.091..2.130 rows=1096 loops=1)
---> Materialize (cost=0.01..27284555030.01 rows=1000000000000 width=16) (actual time=0.001..3.574 rows=48000 loops=1096)
----------> Nested Loop (cost=0.01..17401742530.01 rows=1000000000000 width=16) (actual time=0.489..14.044 rows=48000 loops=1)
----------------> Function Scan on generate_series it (cost=0.00..10.00 rows=1000 width=4) (actual time=0.477..1.352 rows=4000 loops=1)
----------------> Materialize (cost=0.01..26312333.01 rows=1000000000 width=12) (actual time=0.000..0.001 rows=12 loops=4000)
----------------------> Nested Loop (cost=0.01..16429520.01 rows=1000000000 width=12) (actual time=0.010..0.019 rows=12 loops=1)
----------------------------> Function Scan on generate_series wh (cost=0.00..10.00 rows=1000 width=4) (actual time=0.003..0.003 rows=3 loops=1)
----------------------------> Materialize (cost=0.01..28917.01 rows=1000000 width=8) (actual time=0.002..0.004 rows=4 loops=3)
----------------------------------> Nested Loop (cost=0.01..20010.01 rows=1000000 width=8) (actual time=0.006..0.009 rows=4 loops=1)
----------------------------------------> Function Scan on generate_series op (cost=0.00..10.00 rows=1000 width=4) (actual time=0.002..0.002 rows=2 loops=1)
----------------------------------------> Function Scan on generate_series org (cost=0.00..10.00 rows=1000 width=4) (actual time=0.001..0.001 rows=2 loops=2)
Planning time: 0.569 ms
Execution time: 378883.989 ms

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

Итак, cstore_table — 226 Мб, rbstore_table — 3.75 Гб. Разница в 16.99 раз поражает, но вряд ли такую же разницу можно получить в продакшен, в первую очередь из-за распределения данных. Как правило, эта разница будет меньше и будет составлять около 5 раз.

Но постойте, никто не использует «сырые» данные в row-based формате для целей анализа. К примеру, для отчетности стараются использовать индексированные данные. И т.к. «сырые» данные будут всегда, нужно сравнивать размеры с размерами индексов. Давайте же создадим хотя бы один индекс. Пусть это будет индекс по полю даты и типу операции — trd + op.

Итак, я проиндексировал всего два поля, и индекс занял 1583 MB, что на много больше, чем таблица cstore_table. А ведь, как правило, для OLAP нагрузки требуется далеко не один индекс. Здесь будет уместным заметить, что таблица cstore_table не нуждается в дополнительном индексировании. Эта таблица действует, как индекс, покрывающий любые запросы.

Из всего выше сказанного можно сделать простой вывод — с помощью columnstore таблиц можно сократить используемый объём дискового пространства.

Производительность запросов

Для оценки производительности давайте выполним запрос, возвращающий сводные данные за определённый месяц по конкретному типу операции.

explain (analyze, costs, buffers) 
select
  sum(m1)
from cstore_table 
where 
  trd = '2011-01-01' 
  and 
  op = 1; 

План выполнения будет следующим

Aggregate (cost=793602.69..793602.70 rows=1 width=32) (actual time=79.708..79.708 rows=1 loops=1)
--Buffers: shared hit=44226
---> Foreign Scan on cstore_table (cost=0.00..793544.70 rows=23197 width=5) (actual time=23.209..76.628 rows=24000 loops=1)
--------Filter: ((trd = '2011-01-01'::date) AND (op = 1))
--------Rows Removed by Filter: 26000
--------CStore File: /var/lib/pgsql/10/data/cstore_fdw/14028/16417
--------CStore File Size: 120818897
--------Buffers: shared hit=44226
Planning time: 0.165 ms
Execution time: 79.887 ms

И

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from rbstore_table 
where 
  trd = '2011-01-01' 
  and 
  op = 1; 

План выполнения будет следующим

Aggregate (cost=40053.80..40053.81 rows=1 width=8) (actual time=389.183..389.183 rows=1 loops=1)
--Buffers: shared read=545
---> Index Scan using trd_op_ix on rbstore_table (cost=0.56..39996.70 rows=22841 width=4) (actual time=55.955..385.283 rows=24000 loops=1)
--------Index Cond: ((trd = '2011-01-01 00:00:00'::timestamp without time zone) AND (op = 1))
--------Buffers: shared read=545
Planning time: 112.175 ms
Execution time: 389.219 ms

389.219 ms против 79.887 ms. Здесь мы видим, что даже на относительно небольшом объеме данных columnstore таблица существенно быстрее, чем индекс на row-based таблице.

Давайте изменим запрос и попробуем получить агрегат за весь 2011 год.

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from cstore_table 
where 
  trd between '2011-01-01' and '2011-12-31' 
  and 
  op = 1; 

План выполнения будет следующим

Aggregate (cost=946625.58..946625.59 rows=1 width=32) (actual time=3123.604..3123.604 rows=1 loops=1)
--Buffers: shared hit=44226
---> Foreign Scan on cstore_table (cost=0.00..925064.70 rows=8624349 width=5) (actual time=21.728..2100.665 rows=8760000 loops=1)
--------Filter: ((trd >= '2011-01-01'::date) AND (trd <= '2011-12-31'::date) AND (op = 1))
--------Rows Removed by Filter: 8760000
--------CStore File: /var/lib/pgsql/10/data/cstore_fdw/14028/16411
--------CStore File Size: 120818897
--------Buffers: shared hit=44226
Planning time: 0.212 ms
Execution time: 3123.960 ms

И

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from rbstore_table 
where 
  trd between '2011-01-01' and '2011-12-31' 
  and 
  op = 1; 

План выполнения будет следующим

Finalize Aggregate (cost=885214.33..885214.34 rows=1 width=8) (actual time=98512.560..98512.560 rows=1 loops=1)
--Buffers: shared hit=2565 read=489099
---> Gather (cost=885214.12..885214.33 rows=2 width=8) (actual time=98427.034..98523.194 rows=3 loops=1)
--------Workers Planned: 2
--------Workers Launched: 2
--------Buffers: shared hit=2565 read=489099
---------> Partial Aggregate (cost=884214.12..884214.13 rows=1 width=8) (actual time=97907.608..97907.608 rows=1 loops=3)
--------------Buffers: shared hit=2565 read=489099
---------------> Parallel Seq Scan on rbstore_table (cost=0.00..875264.00 rows=3580047 width=4) (actual time=40820.004..97405.250 rows=2920000 loops=3)
---------------------Filter: ((trd >= '2011-01-01 00:00:00'::timestamp without time zone) AND (trd <= '2011-12-31 00:00:00'::timestamp without time zone) AND (op = 1))
--------------------Rows Removed by Filter: 14616000
--------------------Buffers: shared hit=2565 read=489099
Planning time: 7.899 ms
Execution time: 98523.278 ms

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

Агрегаты вручную

Подходящей структурой для ручных агрегатов может быть обычная row_based таблица, содержащая предвычисленные значения. Например, в ней может быть запись, относящаяся к 2011 году с типом операции равным 1, при этом в полях m1, m2, m3, m4 и m5 будут храниться агрегированное значение именно по этим аналитическим разрезам. Таким образом, имея достаточный набор агрегатов и индексов, аналитические запросы приобретают небывалую производительность. Что интересно, в Microsoft SQL Server Analysis Services есть специальный мастер, который позволяет настраивать количество и глубину заранее вычисленных значений.

Данное решение имеет следующие плюсы:

• Аналитика реального времени.
  
  Пожалуйста не путайте с термином «аналитика в реальном времени». Здесь речь идёт о том, что приращение агрегата происходит за приемлемый отрезок времени в подавляющем большинстве случаев.
  
  На самом деле этот плюс спорный, но не будем об этом. Факт остаётся фактом. Архитектура решения такова, что агрегаты остаются «свежими» практически всегда.
• Полная независимость от объёма данных.
  
  Это очень серьёзный плюс. Не важно сколько данных попадёт в обработку, рано или поздно они будут обработаны, а агрегаты получены.
• Относительная сложность.
  
  Чтобы получить аналитику реального времени и независимость от объёма данных, решение должно использовать продвинутые технологии, такие как многопоточность и ручное управление блокировками на уровне СУБД.
• Сложность тестирования.
  
  Здесь речь, как о юнит-тестировании, так и о ручном. Думаю, читателю не стоит объяснять, что выявление ошибок многопоточности занятие не из простых.
• Повышенные требования к дисковому пространству.
  
  

Реальное применение columnstore

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

Возьмём среднестатистического руководителя предприятия. Как правило, его/её волнуют два глобальных вопроса: «Как идут дела в данный момент?» и «Что изменилось за последнее время?».

Для ответа на вопрос «Как идут дела в данный момент» нам совершенно не нужны исторические данные. Т.е. не важно, как дела шли месяц назад.

Дабы держать руку на пульсе вопрос задаётся часто. Такой вид анализа данных называется оперативным.

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

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

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

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

Применительно к columnstore есть возможность смешивать секции в row-based и columnstore форматах. Известно, что данные оперативного анализа подвержены частому изменению, что препятствует их хранению в columnstore формате. А с учетом, того, что оперативных данных не бывает слишком много их вполне можно хранить в формате row-based.

Исторические же данные не меняются. Этих данных много, и поэтому формат columnstore им подходит лучше. Вспомните, что производительность «жирных» запросов к источнику columnstore выше, чем к источнику row-based.

Давайте рассмотрим на примере все вышесказанное.

Ниже я создаю главную таблицу warehouse и присоединяю к ней секции оперативного и исторического анализа.

create table warehouse 
( 
  trd date, 
  org int, 
  op int, 
  it int, 
  wh int, 
  m1 numeric(32, 2), 
  m2 numeric(32, 2), 
  m3 numeric(32, 2), 
  m4 numeric(32, 2), 
  m5 numeric(32, 2) 
) 
partition by range(trd); 

create foreign table historycal_data 
( 
  trd date, 
  org int, 
  op int, 
  it int, 
  wh int, 
  m1 numeric(32, 2), 
  m2 numeric(32, 2), 
  m3 numeric(32, 2), 
  m4 numeric(32, 2), 
  m5 numeric(32, 2) 
) 
server cstore_server 
options(compression 'pglz'); 
 
insert into historycal_data 
select  
  '2010-01-01'::date + make_interval(days => d) as trd 
  , op 
  , org 
  , wh 
  , it 
  , 100 as m1 
  , 100 as m2 
  , 100 as m3 
  , 100 as m4 
  , 100 as m5 
from generate_series(0, 1) as op 
cross join generate_series(1, 2) as org 
cross join generate_series(1, 3) as wh 
cross join generate_series(1, 4000) as it 
cross join generate_series(0, (1095 - 31)) as d; 
 
analyze historycal_data; 

create table operational_data as 
select  
  ('2012-12-01'::date + make_interval(days => d))::date as trd 
  , op 
  , org 
  , wh 
  , it 
  , 100::numeric(32, 2) as m1 
  , 100::numeric(32, 2) as m2 
  , 100::numeric(32, 2) as m3 
  , 100::numeric(32, 2) as m4 
  , 100::numeric(32, 2) as m5 
from generate_series(0, 1) as op 
cross join generate_series(1, 2) as org 
cross join generate_series(1, 3) as wh 
cross join generate_series(1, 4000) as it 
cross join generate_series(0, 30) as d; 

create index trd_op_ix on operational_data (trd, op); 

analyze operational_data; 

alter table warehouse attach partition operational_data for values from ('2012-12-01') to ('2112-01-01'); 

alter table warehouse attach partition historycal_data for values from ('2010-01-01') to ('2012-12-01'); 

Всё готово. Давайте попробуем заказать пару отчетов. Начнём с заказа данных за один день текущего месяца.

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from warehouse 
where 
  trd = '2012-12-01' 
  and 
  op = 1; 

Aggregate (cost=15203.37..15203.38 rows=1 width=32) (actual time=17.320..17.320 rows=1 loops=1)
--Buffers: shared hit=3 read=515
---> Append (cost=532.59..15140.89 rows=24991 width=5) (actual time=1.924..13.838 rows=24000 loops=1)
-------Buffers: shared hit=3 read=515
---------> Bitmap Heap Scan on operational_data (cost=532.59..15140.89 rows=24991 width=5) (actual time=1.924..11.992 rows=24000 loops=1)
---------------Recheck Cond: ((trd = '2012-12-01'::date) AND (op = 1))
---------------Heap Blocks: exact=449
---------------Buffers: shared hit=3 read=515
----------------> Bitmap Index Scan on trd_op_ix (cost=0.00..526.34 rows=24991 width=0) (actual time=1.877..1.877 rows=24000 loops=1)
---------------------Index Cond: ((trd = '2012-12-01'::date) AND (op = 1))
---------------------Buffers: shared hit=2 read=67
Planning time: 0.388 ms
Execution time: 100.941 ms

Теперь закажем данные за весь 2012 год, в котором количество транзакций равно 8 784 000.

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from warehouse 
where 
  trd between '2012-01-01' and '2012-12-31' 
  and 
  op = 1; 

Aggregate (cost=960685.82..960685.83 rows=1 width=32) (actual time=4124.681..4124.681 rows=1 loops=1)
--Buffers: shared hit=45591 read=11282
---> Append (cost=0.00..938846.60 rows=8735687 width=5) (actual time=66.581..3036.394 rows=8784000 loops=1)
---------Buffers: shared hit=45591 read=11282
----------> Foreign Scan on historycal_data (cost=0.00..898899.60 rows=7994117 width=5) (actual time=66.579..2193.801 rows=8040000 loops=1)
---------------Filter: ((trd >= '2012-01-01'::date) AND (trd <= '2012-12-31'::date) AND (op = 1))
---------------Rows Removed by Filter: 8040000
---------------CStore File: /var/lib/pgsql/10/data/cstore_fdw/14028/16448
---------------CStore File Size: 117401470
---------------Buffers: shared hit=42966
----------> Seq Scan on operational_data (cost=0.00..39947.00 rows=741570 width=5) (actual time=0.019..284.824 rows=744000 loops=1)
---------------Filter: ((trd >= '2012-01-01'::date) AND (trd <= '2012-12-31'::date) AND (op = 1))
---------------Rows Removed by Filter: 744000
---------------Buffers: shared hit=2625 read=11282
Planning time: 0.256 ms
Execution time: 4125.239 ms

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

explain (analyze, costs, buffers) 
select  
  sum(m1) 
from warehouse

Aggregate (cost=672940.20..672940.21 rows=1 width=32) (actual time=15907.886..15907.886 rows=1 loops=1)
--Buffers: shared hit=17075 read=11154
---> Append (cost=0.00..541420.20 rows=52608000 width=5) (actual time=0.192..9115.144 rows=52608000 loops=1)
---------Buffers: shared hit=17075 read=11154
----------> Foreign Scan on historycal_data (cost=0.00..512633.20 rows=51120000 width=5) (actual time=0.191..5376.449 rows=51120000 loops=1)
---------------CStore File: /var/lib/pgsql/10/data/cstore_fdw/14028/16448
---------------CStore File Size: 117401470
---------------Buffers: shared hit=14322
----------> Seq Scan on operational_data (cost=0.00..28787.00 rows=1488000 width=5) (actual time=0.032..246.978 rows=1488000 loops=1)
---------------Buffers: shared hit=2753 read=11154
Planning time: 0.157 ms
Execution time: 15908.096 ms

Прошу заметить, что я все ещё пишу свою статью, как ни в чем не бывало. Мне даже не пришлось перезагружать мой не самый мощный ноутбук с HDD и 4 Гб RAM. Хотя вопрос потребления ресурсов требует более тщательной проработки.

Отказоустойчивость

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

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

Простота внедрения

Как выяснилось при создании таблицы, хранящей данные в columnstore формате, опций, кроме алгоритма компрессии, нет. Сама компрессия совершенно необходима.

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

Как было продемонстрированно выше, создание таблицы в формате columnstore совершенно не затруднительный процесс. Расширение может работать с 40 типами данных PostgreSQL. На вебинарах говорили обо всех типах поддерживаемых PostgreSQL.

Какие новые навыки должны быть у разработчика для работы с новыми структурами

Разработчику SQL никаких специальных навыков для написания запросов к columnstore таблицам иметь не нужно. Такая таблица видна во всех запросах, как обычная row-based таблица. Хотя это не исключает необходимость оптимизации запросов.

Заключение

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

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

Ссылки на дополнительные материалы

Краткий обзор cstore_fdw

cstore_fdw на GitHub

Дорожная карта cstore_fdw