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

Размещено на http://www.Allbest.Ru/

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

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

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

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

Анотація

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

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

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

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

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

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

партиціонування база даний 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

Таблиця 2

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

Таблиця 3

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

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

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

Запит:

SELECT u.*

Таблиця 4

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

При виконанні цього запиту до таблиці без партиціонування (transactions, але з індексом на полі createdat) індекс не застосовується. Як результат, середній час виконання запиту є більшим, ніж до таблиці без індексу. Натомість, середня швидкість виконання запиту є вдвічі більшою до таблиці з партиціонуванням.

Таблиця 5

Час вибірки транзакцій клієнтів за квартал

При вибірці квартального обороту всіх користувачів за всіма банківськими рахунками використано запит із групуванням та агрегатною функцією:

SELECT u.*, SUM(t.amount)

GROUP BY u.id

Згідно з результатами (див. табл. 6), підхід з партиціонуванням показав пришвидшення на 11 і 23 секунди, у порівнянні з підходами без партиціонув ання.

Таблиця 6

Час вибірки транзакцій клієнтів за квартал

Висновки

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

Розглянувши можливості партиціонування у системі керування базами даних PostgreSQL, було побудовано базу даних на прикладі банківської системи, в якій порівнювалися три однаково-логічні таблиці з різними властивостями: звичайна, з індексом та з партиціонуванням.

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

Обсяг даних на диску при підході з партиціонуванням приблизно в 1.5-1.6 разів менший, це може бути пов'язано з різним первинним ключем та його індексом.

Література

1. DB-Engines Ranking [Електронний ресурс].

2. Suhail, R. (2023). Guide to PostgreSQL Table Partitioning [Електронний ресурс].

3. Schonig, H-J. (2023). Killing Performance with PostgreSQL Partitioning [Електронний ресурс].

4. Emerson, B. (2023). How table partitioning in PostgreSQL affects bulk load performance [Електронний ресурс].

5. Вознюк Г. Термінологічна орфографія: трансакція чи транзакція? / Г. Вознюк, І. Ментинська // Вісник Нац. ун-ту «Львівська політехніка». Серія «Проблеми української термінології» - 2012. - №733. - С. 6-9.

6. PostgreSQL wiki. Disk Usage [Електронний ресурс].

7. PostgreSQL. EXPLAIN [Електронний ресурс].

8. PostgreSQL. Using EXPLAIN [Електронний ресурс].

9. Pganalyze. The Basics of Postgres Query Planning [Електронний ресурс].

10. Medium. (2023). How to Read and Understand EXPLAIN Query Plans in PostgreSQL.

References

1. DB-Engines Ranking.

2. Suhail R. (2023). Guide to PostgreSQL Table Partitioning.

3. Schonig, H-J. (2023). Killing Performance with PostgreSQL Partitioning.

4. Emerson, B. (2023). How table partitioning in PostgreSQL affects bulk load performance.

5. Voznyuk, H. & Mentynska I. (2012). Terminological orthography: transaktsiya or tranzaktsiya? Bulletin of the National University «Lviv Polytechnic». Series «Problems of Ukrainian Terminology», 733, 6-9 [in Ukrainian].

6. PostgreSQL wiki. Disk Usage.

7. PostgreSQL. EXPLAIN.

8. PostgreSQL. Using EXPLAIN.

9. Pganalyze. The Basics of Postgres Query Planning.

10. Medium. (2023). How to Read and Understand EXPLAIN Query Plans in PostgreSQL.

Размещено на Allbest.Ru