Секционирование в PostgreSQL 12
Протестируем новый функционал секционирования в PG12.
- Описание
- Подробнее
Описание
Важно
Данный материал не несет рекомендательный характер, а только ознакомительный. Все, что будет описано ниже, это следствие свободного времени на самоизоляции и желания познать новое. Еще две недели назад максимум, что я делал в PostgreSQL это правил настройки согласно различным рекомендациям в интернете. Следуя из выше описанного, в данной статье могут присутствовать ошибки и работы на рабочей базе могут привести к необратимым последствиям. Цель публикации осветить новый функционал в PG12. Надеюсь, появятся новые статьи от более опытных администраторов. (например: YPermitin).
Пояснения и определения
Вкратце для тех, кто не знает, что такое секционирование и для чего оно необходимо. Секционированием данных называется разбиение одной большой логической таблицы на несколько меньших физических секций. Какая польза от секционирования?
- При разделении таблицы на несколько мелких, можно актуальные данные выносить на отдельный жесткий диск.
- Несколько раз в день принудительно просчитывать по ним статистику.
- Если есть необходимость перестроить индекс в технологическое окно, то можно сделать только на данных, которые чаще используются.(Хотя с выходом 12 версии это можно делать не в монопольном режиме прям на рабочей базе.).
- Закрыть на уровне СУБД редактирование таблиц старых периодов и 100% быть уверенным, что пользователи не отредактируют данные за прошлые года.
Где-то мною было прочитано, что секционирование актуально когда размер таблицы около 30 ГБ, так что большинству изложенная ниже информацию вряд ли пригодится (мне кстати тоже в работе это не понадобится).
Задумка заключается в следующем: в чистой базе мы создадим два идентичных оборотных регистра накопления. Таблицу одного из регистров разобьем на более мелкие секции. Наполним таблицу данными (несколько десятков миллионов строк), а затем сравним скорости записи и чтения по этим регистрам. Постараюсь описать выполняемые мною действия максимально подробно, так как опыта в написании статей нет, может получится слишком объемно 🙂
Используемые инструменты и материалы
- Компьютер: Windows 10, Диски: hdd-ОС, hdd-WAL файлы, sdd-база, temp_stat на RAM DISK 40 MB, intel core i5-4460 3.2 Ghz, RAM 8 Ggb
- Платформа 1С версии 8.3.15
- PgAdmin4 https://www.pgadmin.org/download/pgadmin-4-windows/
- Обработка просмотра структуры 1с на сервере (в интернете их много, прикреплять не буду)
- Яндекс поиск
- Справка по секциям PostgreSQL https://postgrespro.ru/docs/postgrespro/12/ddl-partitioning#DDL-PARTITIONING-DECLARATIVE
- Справка по значениям: MINVALUE, MAXVALUE и DEFAULT https://postgrespro.ru/docs/postgrespro/12/sql-createtable
- Справка по типу данных Bytea https://postgrespro.ru/docs/postgrespro/12/functions-binarystring
- Справка по работе с датами и временем в PostgreSQL https://postgrespro.ru/docs/postgrespro/12/functions-datetime
- Статья по секционированию https://habr.com/ru/company/postgrespro/blog/353472/
- Некоторые скрипты из статьи //sale.itcity.ru/public/1148863/
- PotgreSQL_1C 12 от разработчиков PostgresPro https://1c.postgres.ru/
Параметры PostgreSQL
shared_buffers | 1 GB |
temp_buffers | 256 MB |
work_mem | 256 MB |
maintenance_work_mem | 1 GB |
wal_sync_method | open_datasync |
max_wal_size | 10 GB |
effective_cache_size | 4 GB |
autovacuum_max_workers | 2 |
autovacuum_vacuum_cost_limit | 800 |
autovacuum_vacuum_scale_factor | 0.01 |
autovacuum_analyze_scale_factor | 0.01 |
max_locks_per_transaction | 512 |
enable_partition_pruning | on |
bytea_output | ‘hex’ |
Начнем
Оглавление
- Создаем таблицу с данными до 2019 года
- Создаем таблицы на каждое подразделение, что присутствуют в базе, для данных до 2019 года, а так же индексы на каждую такую таблицу
- Создание секций на каждый месяц за текущий и прошлый год.
4. Тестирование произведенной модернизации
Создание базы 1с для теста
В новой базе создадим 2 оборотных регистра накопления. Регистры будут иметь следующую структуру:
В первом регистре в измерениях "Подразделение" и "Поле" установим признак "Индексировать", во втором этого делать не будем, так-как индексы будем настраивать в PostgreSQL. Далее создадим 5 элементов в справочнике "Подразделение", 100 элементов "Поля" и 20 Элементов "Статья затрат". Работая в сфере "Сельского хозяйства", мне проще привести пример из данной области. В данных регистрах будем собирать затраты рабочих бригад на каждое поле.
Следующим шагом будет создание документов для формирования движений по этим регистрам.