Статья: Партиціонування як підхід до оптимізації зберігання та вибірки великих масивів даних у інформаційних системах

ДВНЗ «Ужгородський національний університет»

Кафедра комп'ютерних систем та мереж

Партиціонування як підхід до оптимізації зберігання та вибірки великих масивів даних у інформаційних системах

Гедеон Т.С., асистент

Гедеон Г.О., асистент

м. Ужгород

Анотація

У статті розглядаються особливості партиціонування в об'єктно-реляційній системі керування базами даних PostgreSQL. Описано принцип побудови таблиці з діапазонним партиціонуванням, наведено приклад розподілу даних поміж однотипних таблиць і показано різницю між збереженням та вибіркою даних з використанням двох стратегій: з партиціонуванням та без нього. Досліджено наближений до реальності випадок використання партиціонування для оптимізації зберігання та вибірки великих масивів даних, побудовано спрощену базу даних на прикладі банківської системи. Виконано порівняння трьох однаково-логічних таблиць з різними властивостями: звичайна, з індексом та з партиціонуванням, досліджено обсяг на диску, що займають дані. При підході з партиціонуванням таблиці обсяг даних на 30-40% менший. Це пов'язано з різними вимогами до первинного ключа, що впливає на розмірність індексу.

При порівнянні швидкодії виконання запиту до вибірки малої кількості даних (у середньому 86 430 записів) результати показали зниження продуктивності у випадку з партиціонуванням таблиці. При розширенні діапазону вибірки даних (у середньому 2 636 104 записів) швидкість виконання запитів зросла у 3-5 разів, при об'єднанні таблиць - у 2-3 рази.

Виконуючи вибірку даних з об'єднанням таблиць, а також групуванням записів, отримано результати, що свідчать про зростання швидкості виконання запиту на 10-20% у таблиці з використанням партиціонування.

Отже, у статті описано використання діапазонної форми партиціонування, деталізовано її переваги та недоліки, зокрема суттєве збільшення швидкодії у конкретних випадках, а також економію використання дискового простору.

Ключові слова: база даних, Big Data, діапазонне партиціонування, інформаційна система, таблиця баз даних, PostgreSQL Partitioning, query performance, Explain Analyze.

Abstract

Partitioning as an approach to optimizing the storage and retrieval of large data arrays in information systems

Hedeon T.S., Hedeon H.O., Assistant, Uzhhorod National University, Uzhhorod

The article presents the specifics of partitioning in the object- relational database system PostgreSQL. The principle of constructing a table with range partitioning is described. An example of data distribution between tables with the same column structure was provided, demonstrating the difference in storing and retrieving data using two strategies: with and without partitioning.

A realistic case of using partitioning to optimize the storage and retrieval of large data arrays was studied, and a simplified database was built using a banking system as an example. A comparison of three logically identical tables with different properties was performed: normal, with index, and with partitioning. Additionally, the usage of disk space for those cases was investigated. With the table partitioning approach, the data volume is reduced by 30-40%. This is due to the different requirements for the primary key, which affect the properties of the index.

When comparing the performance of a query to retrieve a small amount of data (an average of 86,430 records), the results showed a decrease in performance when using table partitioning. When expanding the range of retrieved data (an average of 2,636,104 records), the query execution speed increased by 3-5 times, and when joining tables, it increased by 2-3 times.

When retrieving data with joined tables and grouped records, the results indicated a 10-20% increase in query execution speed for a partitioned table.

In this manner, the article describes the utilization of range partitioning, elaborating on its advantages and disadvantages, including a significant increase in speed in specific cases and its impact on disk space usage.

Keywords: database, Big Data, range partitioning, information system, database table, PostgreSQL Partitioning, query performance, Explain Analyze.

Постановка проблеми

Сучасні інформаційні системи та різноманітні сервіси потребують детальної хронології змін даних, що дозволяє підвищити їх захист та забезпечує можливість відновлення попередніх версій. Це актуально при навмисних спотвореннях або зміні даних зі сторони користувачів системи із відповідними правами, адже одним із аспектів хронології змін даних є збереження інформації про виконавця дії. Вагомий недолік збереження хронології змін об'єктів у системі - великий обсяг дискового простору, необхідний для цих даних.

При проектуванні архітектури інформаційної системи, важливим етапом є планування структури бази даних. Враховуючи рейтинг систем керування базами даних [1], реляційні бази даних є беззаперечним лідером, тому дослідження буде проводитися на базі системи керування базами даних PostgreSQL.

У даній статті описано стратегію розподілу даних поміж однотипних таблиць і показано різницю між збереженням та вибіркою даних з використанням двох стратегій: з партиціонуванням та без нього.

Аналіз останніх досліджень і публікацій. У статті [2] описано види та переваги різних форм партиціонування, наведено покрокову інструкцію побудови таблиць з прикладами їх цільового використання.

Дослідження продуктивності партиціонування за hash формою наведено у статті [3]. Автор використовує однотабличну базу даних з полем ідентифікатора (id) та цілочисельним полем. Результати тестування показують суттєву різницю у швидкості виконання запитів, однак примітивне представлення бази даних, наведене у статті, а також здійснення запиту без використання поля, яке бере участь у партиціонуванні, не відповідає ідеї використання партиціонування у реальних умовах, а, отже, отримані показники не можуть слугувати базою для подальших досліджень.

Результати швидкості виконання запитів копіювання [4] свідчать про зменшення швидкодії виконання запитів до таблиць, що використовують партиціонування, на 20-25%, однак відсутність деталізованого опису структури бази даних та її властивостей не дозволяє однозначно оцінити якість проведених досліджень.

Мета статті - дослідити наближений до реальності випадок використання партиціонування для оптимізації зберігання та вибірки великих масивів даних на базі системи керування базами даних PostgreSQL і форми партиціонування поділу на діапазони на прикладі бази даних транзакцій у банківській системі.

Виклад основного матеріалу дослідження

партиціонування база даний postgresql банківський система

Партиціонування - це механізм поділу великих масивів даних, зокрема, таблиць баз даних, на менші логічні частини / секції за деякими критеріями. Це забезпечує покращення продуктивності виконання запитів, особливо тоді, коли більшість записів таблиці бази даних, до яких виконується запит або набір запитів, розташована в межах однієї або невеликої кількості секцій. У цьому випадку розбиття ефективно замінює верхні рівні дерева індексів, підвищуючи ймовірність того, що часто використовувані частини індексів будуть знаходитися в пам'яті.

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

Завантаження та видалення великого обсягу даних може бути виконано шляхом додавання або видалення секцій, якщо цей метод передбачений у схемі партиціонування, за допомогою DROP TABLE або ALTER TABLE DETACH PARTITION. Це не тільки зменшує час виконання, а й допомагає уникнути використання операції VACUUM.

Переваги, наведені вище, особливо актуальні при роботі з великими обсягами даних (BIG DATA), проте користь від партиціонування залежить від застосування методу для конкретного випадку.

Об'єктно-реляційна система керування базами даних PostgreSQL підтримує вбудовані форми партиціонування:

- RANGE (Range Partitioning): таблиця розділяється на “діапазони”, визначені за ключовим стовпцем або набором стовпців, без перекриття між діапазонами значень, які призначені для різних розділів. Наприклад, можна розділити за діапазонами дат або ідентифікаторами для конкретних бізнес- об'єктів;

- LIST (List Partitioning): таблиця розділяється шляхом явного переліку значень ключів, які з'являються у кожному розділі, це може бути корисним при розподілу товарів магазину по категоріях;

- HASH (Hash Partitioning): таблиця розділяється за допомогою вказання модулю та залишку для кожного розділу. Кожен розділ буде містити рядки, для яких хеш-значення ключа розділу, поділене на модуль, буде давати вказаний залишок.

У даній статті механізм партиціонування розглядається на прикладі транзакцій у банківській системі у формі діапазонного партиціонування, де транзакція - це послідовність операцій, що виконуються, які не можуть бути розділені і є одним цілим [5].

Перший крок - побудова примітивної бази даних, що містить таблиці (див. рис. 1) для зберігання даних про користувачів, їх банківські рахунки та транзакції. Тип користувача (type) визначається його приналежністю до банку (працівник / клієнт): клієнт може мати банківські рахунки, працівник - бути виключно виконавцем (автором) транзакції. Таблиця даних транзакцій містить зв'язки з двома банківськими рахунками: джерелом та призначенням, а також автором транзакції.

Рис. 1 Структура бази даних

Для порівняння двох підходів, з партиціонуванням та без нього, створена таблиця partitioned transactions, яка має ідентичні зв'язки з таблицею transactions, але створена за принципом партиціонування за полем createdat. Останнє обумовлено тим, що для дослідження у даній роботі найбільш вагомим елементом транзакції для пошуку є час її проведення. Також, порівняння виконання запитів буде проведено без використання індексу та з індексом на полі created at у таблиці transactions.

Таблиця partitioned transactions містить набір розділів, що мають префікс partitioned transactions*, кожен з який відповідає за зберігання даних у певному місяці року, наприклад, partitioned_transactions_y2023m01 містить записи із значення поля created at у діапазоні між '2023-01-01 00:00:00' та '2023-02-01 00:00:00'. Закінчення назви розділу відповідає за певний рік та місяць, за якими буде виконуватись розподіл даних по розділах.

Наступний крок - заповнення бази даних для одного календарного року. Результат відображено у таблиці 1.

Таблиця 1

Таблиці та властивості їх вмісту

Сукупність таблиць з префіксом partitionedtransactions* утворюють єдину partitionedtransactions і мають таку ж кількість і абсолютно ідентичні записи до таблиці transactions. Їх загальний обсяг на диску [6] - 6 149 MB, що приблизно в 1.5 разів менше за обсяг таблиці transactions. Різниця між таблицями полягає у первинному ключі та його індексі, transactions містить первинний ключ за полем id, а таблиця partitioned transactions - первинний ключ за двома полями id та created at.

Використовуючи команду explain [7, 8] для перегляду плану запиту [9, 10], буде проводитися порівняння вищезгаданих підходів. Вибірка всіх транзакцій за день виконується за допомогою запиту:

SELECT *

FROM transactions

WHERE created_at >= '2023-03-01 00:00:00'

AND created_at < '202303-02 00:00:00'

Результати виконання запиту показані у таблиці 2. Результати виконання того ж запиту з вибіркою транзакцій за місяць занесені у таблицю 3.

Таблиця 2

Час вибірки всіх транзакцій за день

Таблиця 3

Час вибірки всіх транзакцій за місяць

Порівнюючи результати двох запитів при різних підходах, можна зробити висновок, що при вибірці більшого числа записів зростає швидкодія виконання запиту до таблиці з партиціонуванням. Зокрема, середня кількість записів у таблиці транзакцій за один день складає 86 430, а за місяць 2 636 104.

Наступний запит для порівняння - вибірка всіх транзакцій користувачів за всіма їхніми банківськими рахунками протягом місяця та кварталу із використанням зовнішнього об'єднання таблиць. Результати представлено у таблиці 4 та таблиці 5 відповідно.