Секционированная таблица

   База данных PostgreSQL предоставляет возможность организации данных в виде секционированных таблиц. Секционированная таблица - это оболочка/алиас к набору таблиц/секций, которые имеют структуру соот. описанию в таблице обертке, таблице при создании которой использовался ключевой модификатор - PARTITION. К таблице созданной с таким модификатором можно присоединять и отключать таблицы секции, данные в которых распределены по какому либо принципу, например, в таблице имеется поле, от значения которого будет зависеть в какую именно физическую таблицу попадут данные или физическая таблица хранения конкретной записи может определяться на основании вычисляемого значения. Достоинство секционированных таблиц в том, что можно работать с таблицей обёрткой не задумываясь в какую физическую таблицу надо положить данные, PostgreSQL сам вычислит физическую таблицу и положит в нужную, при этом сами таблицы секции являются полноценными таблицами и к ним можно обращаться на прямую по имени, минуя таблицу обертку, т.е. одна или несколько секций может быть перемещена на/в другой сервер/базу/схему, где может быть создана своя обертка и все алгоритмы продолжат работать без каких либо изменений.

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

   Рассмотрим использование секционированных таблиц на примере работы с Федеральной информационной адресной системой, она же ФИАС ГАР, которую ведет и распространяет ФНС России. ФИАС ГАР распространяется в формате XML, в виде срезов на дату и в виде изменений относительно предыдущего среза. Клиентам нашей компании интересна возможность ввода адресов с точностью до дома, поэтому нами была реализована загрузка следующих XML файлов из состава ФИАС ГАР:

1. AS_ADDR_OBJ_* - описание адресных объектов до уровня улиц. Размер XML файлов 631MB;

2. AS_ADDR_OBJ_PARAMS_* - параметры адресных объектов упомянутых в AS_ADDR_OBJ_*. Размер XML файлов 4.4GB;

3. AS_ADM_HIERARCHY_* - дерево адресных объектов упомянутых в AS_ADDR_OBJ_* и AS_HOUSES_* в административном формате. Размер XML файлов 34.2GMB;

4. AS_HOUSES_* - описание адресных объектов уровня здание. Размер XML файлов 21GB;

5. AS_HOUSES_PARAMS_* - параметры адресных объектов упомянутых в AS_HOUSES_*. Размер XML файлов 88GB;

6. AS_MUN_HIERARCHY_* - дерево адресных объектов упомянутых в AS_ADDR_OBJ_* и AS_HOUSES_* в муниципальном формате. Размер XML файлов 34GB.

   Не будем здесь рассматривать всякого рода несуразности, ошибки, противоречия данных предоставляемым правилам, это возможно будет предметом отдельной статьи, в этой статье мы используем ФИАС ГАР как достаточно большую базу данных имеющую практическое значение и как следствие дающую понимание всех получаемых плюшек.

   База распространяется в виде zip файла, в котором под каждый регион России создана директория, внутри директории лежит 18 файлов описывающих различные адресные объекты этого субъекта РФ. Выше были приведены 6 файлов из 18, которые мы загружаем, общий размер этих XML файлов составляет 182.4GB.

Для обработки данных XML файлов не в секционированном формате использовался сервер:

• Количество ядер - 80

• Количество памяти - 256GB

• Хранилище данных - tablespace pg_default - RAID5 из 5 SAS дисков, общая производительность массива как у одиночного NVME диска

• Хранилище временных данных и индексов - 3 tablespace, каждая на выделенном NVME диске

Для обработки в секционированном формате используется сервер:

• Количество ядер - 48

• Количество памяти - 224GB

• Хранилище данных - tablespace pg_default - одиночный диск NVME

• Хранилище временных данных и индексов - одиночный диск NVME

Оба сервера работают под ОС OpenSuSE 15.2

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

   Изначально, под хранение каждого вида XML файла была создана отдельная обыкновенная таблица, в результате загрузка XML в 6 таблиц проходила 7-8 суток, при этом на выходе у нас получались следующие размеры:

• addr_obj - 325MB данных + 285MB индексов - 21.19M записей

• addr_obj_params - 2.7GB данных + 3.3GB индексов - 252M записей

• adm_hierarchy - 25GB данных + 22GB индексов - 134M записей

• houses - 10.6GB данных + 11.4GB индексов - 76.7M записей

• houses_params - 53.3GB данных + 66.4GB индексов - 500M записей

• mun_hierarchy - 23.5GB данных + 21.3GB индексов - 134M записей

Технически загрузка XML проводилась следующим способом:

1. XML файлы бились на куски по 10MB, для этого была написана специальная программа, которая била плоские XML файлы на файлы размером не более указанного с сохранением структуры XML файла, т.е. каждый файл содержал законченный тег, а следующий файл начинался с нового тега. Время работы этой программы в расчет не берется, т.к. оно не зависит от способа организации данных в PostgreSQL - обычные или секционированные таблицы;

2. Полученные файлы загружались во временную таблицу командой copy;

3. Загруженные файлы бились на теги по комбинации символов ><;

4. В выделенных выделялись пары ИМЯ_АТРИБУТА=ЗНАЧЕНИЕ АТРИБУТА функцией regexp_matches

5. Полученные пары ИМЯ_АТРИБУТА=ЗНАЧЕНИЕ АТРИБУТА разбивались и складывались в массивы имен атрибутов и их значений

6. После разбора всего XML файла, из массивов атрибутов и их значений собирался один insert со всеми values собранным при разборе XML

Выше указано время работы пп. 2-6.

   Здесь требует пояснение необходимости п.1, изначально его у нас не было, мы решили грузить сразу весь XML и не привлекать какие-то внешние средства, однако выяснилось, что в силу своих внутренних ограничений, PostgresSQL не только не может работать с числами длиной больше 8 байт минус бит знака, т.е. длиной более 31 бита, что соответствует значению 2^31 - чуть более 2 миллиардов, но и со тестовыми данными содержащими большее количество байтов, что в нашем случае ровно 2GB. Это ограничение относится и к длине текстового поля и относится к максимальной длине файла, с которым могут работать встроенные функции PostgreSQL. Никакие ухищрения обмануть не привели к успеху, поэтому мы побили файлы сначала на 1GB, т.к. XML в кодировке UTF8, потом на 500MB, потом на 100MB, но каждый раз мы упирались в проблему, что если файл начал грузиться уже при размере в 1GB, то дальше PostgreSQL не хватало памяти для того, чтобы этот файл побить на теги, не физической памяти, а сервер падал с попыткой выделить непрерывный кусок памяти длиной более 2GB. После 100MB мы не стали больше экспериментировать и решили попробовать 10MB, это заработало.

   Как видно, таблицы занимают много места, а в самих таблицах очень много записей, это связано с тем, что срез содержит всю историю изменения записей, каждая историческая запись - это полноценная запись в таблице, у которой одно или несколько полей заполнено определенным образом, что говорит о том, это последняя актуальная запись или запись из истории и по ней, например, можно выяснить как называлась улица до этого. Например, таблица addr_obj содержит 21.19М записей, при этом актуальные только менее 1.69M, для целей наших заказчиков эти почти 20М записей не нужны. Таблицы *_params организованы еще более расточительно, в них хранится не только история, но еще и параметры в формате один параметр одна запись, т.е. почтовый индекс это запись, ИФНС для юрлиц это запись, ИФНС для физлиц запись и т.д., для одного адресного объекта может быть до 13 параметров, для дома таких параметров может быть до 15 параметров и по каждому параметру ведется история его изменения по принципам описанным выше. Всё это вызывает желание выбросить не нужное, сформировав актуальный кешь, который будет в десятки раз меньше среза и за счет этого будет занимать не только меньше места на боевом сервере, но и будет работать гораздо быстрее за счет более оптимальной структуры хранения данных и в принципе меньшего количества данных, т.к. даже если все индексы настроены правильно, все равно чем больше индекс, тем медленее он работает и больше времени требуется на его обслуживание.

Для хранения кеша мы строим следующие таблицы:

• fg_addr_obj - 115MB данных + 183MB индексов - 1.55M записей

• fg_adm - 379.27MB данных + 723.18MB индексов - 1.53M записей

• fg_adm_index - 429.88МB данных + 85.64MB индексов - 7.07M записей. Таблица используется для быстрого поиска, состоит из пар адрес - слово из полного адреса

• fg_houses - 2.56GB данных + 3.91GB индексов - 33.2M записей

• fg_mun - 390.12MB данных + 648.76MB индексов - 1.55M записей

• fg_mun_index - 496.38MB данных + 95.04MB индексов - 8.11M записей. Таблица используется для быстрого поиска, состоит из пар адрес - слово из полного адреса

   Подводя итог, изначальный срез имеет размер данных 115.43GB + 124.69GB = 240.12GB и 1117.89M записей, сформированный кешь 4.37GB + 5.65GB = 10.02 и 53.01М записей, из которых 15М записей это специализированный поисковый индекс. Получается что кешь 240.12/10.02 - в 24 раза меньше по размеру и имеет в 1117.89/53.01 - в 21 раз меньше записей. Для поиска адреса без поискового кеша необходимо 5-6 минут времени, в зависимости от адреса и поисковой строки, при использовании поискового кеша требуется не более 10 секунд, обычно это 2-3 секунды.

   Мы разобрали вариант загрузки среза базы ФИАС ГАР и работы с ней с помощью подготовленного кеша в случае, когда используются обычные  таблицы, теперь рассмотрим как была доработана структура данных для хранения данных и параметры решения. Ясно, что т.к. это одни и те же данные, то параметры размеров данных, индексов и количества записей не изменятся, но есть некоторые нюансы, которые мы позже рассмотрим более подробно, из анализа которых станет понятно, почему решение на секционированных таблица работает быстрее и почему применение секционированных таблиц для решения прикладных задач хорошо, а не только задач логирования.

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

   Переход на секционированные таблицы сразу же дал результат, время загрузки среза 20 часов, что более чем в 14 раз быстрее на сервере минимум в 2 раза медленном. Загрузка проводилась в один поток, однако при использовании секционирования, транзакция блокирует только одну секцию, с которой она работает, таким образом загрузку можно проводить в несколько потоков, в нашем случае исходя из количества памяти на сервере и количества ядер процессора можно запустить 16 потоков, оставив остальные ядра для работы операционной системы и служебных процессов PostgreSQL, таких как autovacuum. Конечно же 16 параллельных потоков не увеличат скорость в 16 раз, с таким потоком данных не справится дисковая подсистема, но по моим оценкам это увеличит скорость в 3-4 раза. Этот эксперимент мы проведем позже.

   Время формирования кештаблиц fg_* уменьшилось с 25 часов до 3, что означает ускорение более чем в 8 раз. Время отработки поискового запроса улицы 129мс, дома 69мс при поиске адреса Москва, Волоколамское шоссе дом 88 строение 8, ускорение составило более 10 раз, но самое главное, для пользователя это комфортная скорость, он получает варианты выбора в AJAX элементе ввода по мере того, как вводит образец для поиска.

   Теперь разберемся, в чем причина таких волшебных цифр, когда на пустом месте скорость работы с базой данных в 232GB на сервере в 2 раза менее мощном увеличилась более чем в 10 раз. Секрет очень прост, рассмотрим параметры несекционированной таблицы кеша fg_house, она содержит 2.56GB данных и 9 индексов, общим размером 3.91GB, по факту это 10 файлов, минимум 2 из которых надо загрузить в память с диска для выполнения запроса и если данные могут быть загружены посредством загрузки конкретных страниц файла в которых лежат нужные данные, то файл с индексом скорее всего придется грузить весь.

   Теперь посмотрим параметры секционированной таблицы, её общие параметры точно  такие же, однако за оберткой стоит 99 секций, у каждой секции есть 10 файлов, т.е. физически таблица размещается в 990 файлах, размер файлов с данными находится в диапазоне от 80kB до 146.16MB, средний размер 29.8MB , общий размер индексов таблиц секций находится от 80kB до 221.43MB, средний размер 41.43MB, т.е размер файла данных минимум в 17 раз меньше, в среднем 86 раз, файлы индексов меньше минимум 17 раз, в среднем в 95 раз. Получается, что при работе с секционированными таблицами необходимо читать с диска почти в 100 раз меньше данных для поиска по индексу, индекс меньшего размер так же обрабатывается быстрее.

Но не всё так хорошо с секционированными таблицами, есть неприятные моменты при их обслуживании.

pg_dump -t fg_house

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

pg_dump -t fg_house -t fg_house_1 -t fg_house_2 -t fg_house_3 -t fg_house_4 -t fg_house_5 -t fg_house_6 ...

   Во всей этой проблеме радует то, что по крайней мере OpenSuSE, позволяет таким образом передать имена 602 таблиц, sh файл с такой командой занимает почти 10kB.