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

Запит:

SELECT u.*

FROM users u

LEFT OUTER JOIN bank_accounts ba ON ba.client_id = u.id LEFT

OUTER JOIN transactions t ON ba.id = t.source_bank_account_id OR ba.id

= t.destination_bank_account_id

WHERE t.created_at >= '2023-03-01 00:00:00' AND t.created_at <

'202304-01 00:00:00'

Таблиця 4

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

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

Таблиця 5

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

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

SELECT u.*, SUM(t.amount)

FROM users u

LEFT OUTER JOIN bank_accounts ba ON ba.client_id = u.id LEFT

OUTER JOIN transactions t ON ba.id = t.source_bank_account_id OR ba.id

= t.destination_bank_account_id

WHERE t.created_at >= '2023-01-01 00:00:00' AND t.created_at <

'202304-01 00:00:00'

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.