Оптимізація сховищ даних з PostgreSQL: Зіркова схема, матеріалізовані погляди та налаштування продуктивності - javascript.org.ua

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

У цьому блозі я розгляну складнощі оптимізації сховища даних за допомогою PostgreSQL, зосередивши увагу на ключових стратегіях, таких як проектування зіркової схеми (star schema), впровадження матеріалізованих поглядів (materialized views) і застосування передових технік налаштування продуктивності (performance tuning techniques).

джерело: https://www.flaticon.com/free-icon/data-warehouse7200873_

Розуміння проблеми

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

Дані

Набір даних проекту — це комбінація записів з Kaggle та eBay, загальна кількість яких перевищує 18 мільйонів записів, розбитих на три категорії: планшети (tablets), смартфони (smartphones) та навушники (headphones). Зосередившись на цих категоріях, дані були структуровані для значущого аналізу тенденцій продуктів та поведінки користувачів.

джерело набору даних kaggle: https://www.kaggle.com/datasets/mkechinov/ecommerce-behavior-data-from-multi-category-store
набір даних ebay: зібрано через веб-скрапінг для збору відповідних даних про продукти та продавців.

Чому була потрібна оптимізація?

З понад 18 мільйонами записів з Kaggle та eBay, обробка великих і складних даних вимагала ефективного зберігання та вибірки. Зіркова схема (star schema) спростила запити, матеріалізовані погляди (materialized views) покращили продуктивність, а налаштування (tuning) забезпечило масштабованість.

Проектування зіркової схеми

Основою високопродуктивного сховища даних є його проектування схеми. Для цього проекту я реалізував зіркову схему (star schema), яка організовує дані в таблиці фактів та вимірів. Ось як це працювало:

Таблиця фактів (Fact Table): Таблиця фактів (Sales) є основною частиною схеми, яка зберігає вимірювані дані, такі як price, quantity та total_price. Вона відповідає на питання що сталося, наприклад, "Скільки було продано і коли?"

Таблиці вимірів (Dimension Tables): Таблиці вимірів (Product, User, Time та Seller) надають контекст до фактів:

Продукт (Product): Описує, що було продано (brand_name, category_name).
Користувач (User): Визначає, хто придбав товар (user_name, email).
Час (Time): Відстежує, коли сталася покупка (date, month, year).
Продавець (Seller): Деталі про того, хто продав товар (seller_name, seller_rating).

Типи повільно змінюваних вимірів (SCD): Типи повільно змінюваних вимірів (Slowly Changing Dimensions, SCD) керують тим, як зміни в даних відслідковуються:

Тип SCD 1 (перезаписування): Оновлюється останнє значення; історичні дані не зберігаються (наприклад, таблиці User, Time).
Тип SCD 2 (новий запис): Додаються нові записи для змін, зберігаючи всю історію (наприклад, таблиці Product, Seller).
Тип SCD 3 (обмежена історія): Відстежує лише одне попереднє значення, додаючи стовпці (наприклад, previous_category_name), але тут не використовується, оскільки тип SCD 2 є кращим для повного відстеження історії.

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

Зіркова схема (Star Schema)

Впровадження матеріалізованих поглядів (Materialized Views)

Матеріалізовані погляди (materialized views) стали справжнім проривом для оптимізації продуктивності запитів. Замість того, щоб кожного разу перераховувати складні агрегації при виконанні запиту, матеріалізовані погляди зберігали заздалегідь обчислені результати.
Ось що я досяг:

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

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

CREATE MATERIALIZED VIEW sales_view_platform_time AS  
SELECT  
 time.year,  
 time.month,  
 price.price_source,  
 SUM(sales.total_price) AS total_revenue,  
 COUNT(sales.order_id) AS total_orders,  
 AVG(sales.price) AS average_price_per_order  
FROM sales  
JOIN time ON sales.time_id = time.time_id  
JOIN price ON sales.product_id = price.product_id  
GROUP BY time.year, time.month, price.price_source;

Мета:

Відстежує ключові метрики: total_revenue, total_orders та average_price_per_order за year, month та platform.
Забезпечує швидкі висновки щодо місячної продуктивності, тенденцій платформ і стратегій ціноутворення.

Переваги:

Зменшує час відповіді запитів завдяки попередньо обчисленим результатам.
Підтримує прийняття рішень на основі даних, надаючи дієві висновки щодо продуктивності платформ.

Налаштування продуктивності (Performance Tuning)

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

Індекси (Indexing): Створення індексів на часто запитуваних стовпцях, таких як ключі у таблицях фактів та вимірів, для покращення швидкості пошуку та з'єднань.
Розбиття на частини (Partitioning): Розподіл великих таблиць на частини за логічними критеріями (наприклад, за діапазонами дат) для пришвидшення вибірки даних для конкретних запитів.
Оптимізація запитів (Query Optimization): Переписування неефективних запитів і використання інструментів, таких як EXPLAIN, для виявлення та усунення проблем з продуктивністю.
Команди обслуговування (Maintenance Commands): Регулярне виконання специфічних для бази даних команд обслуговування (наприклад, VACUUM і ANALYZE в PostgreSQL) для покращення стану бази даних та оптимізації планування запитів.

У даних, що інтенсивно обробляються, продуктивність запитів є критично важливою для забезпечення швидких і надійних результатів. Я оптимізував складний SQL-запит, скоротивши час виконання з 2 хвилин 23 секунд до 1 хвилини 31 секунди.

Ось як:

Індекси (Indexing): Додав індекси на стовпці, які часто фільтруються або з'єднуються (category_id, order_id, product_id, order_date), що прискорило пошук.
CTE (CTEs): Модульно розділив завдання (наприклад, фільтрація категорій, обчислення середніх значень), що дозволило зменшити розмір проміжних наборів даних.
Попереднє фільтрування (Early Filtering): Фільтрував дані на ранньому етапі, мінімізуючи кількість рядків, що передаються до з'єднань, і зменшуючи час обробки.
4.
План виконання (Execution Plan): Структуровані запити з використанням CTE (Common Table Expressions), щоб допомогти оптимізатору створити ефективні та паралельні плани.

Результат: Покращення продуктивності на 40%, що дозволяє швидше та ефективніше аналізувати дані.

Запит до налаштування (Pre-Tuning Query):

Цей запит займав близько 2 хвилин 23 секунд, що є занадто повільно.

SELECT  
Orders.order_id,  
Product.title AS product_name,  
Seller.seller_id,  
Brand.brand_name,  
Price.price,  
Price.price_source AS platform_source,  
AVG(Price.price) OVER (PARTITION BY Price.product_id) AS avg_product_price  
FROM Orders  
JOIN Order_Detail ON Orders.order_id = Order_Detail.order_id  
JOIN Product ON Order_Detail.product_id = Product.product_id  
JOIN Product_Category ON Product.product_id = Product_Category.product_id JOIN Category ON Product_Category.category_id = Category.category_id  
JOIN Price ON Product.product_id = Price.product_id  
LEFT JOIN Seller ON Price.seller_id = Seller.seller_id  
LEFT JOIN Brand ON Product.brand_id = Brand.brand_id  
WHERE Product_Category.category_id = 1  
ORDER BY Orders.order_date, Product.title;

Запит після налаштування (Post-Tuning Query):

Цей запит займає близько 1 хвилини 31 секунди, що значно краще.

-- Додати індекси для оптимізації продуктивності  
CREATE INDEX idx_orders_order_id ON Orders (order_id);  
CREATE INDEX idx_product_category ON Product (category_id); CREATE INDEX idx_price_product ON Price (product_id, seller_id); CREATE INDEX idx_orders_date ON Orders (order_date);  
CREATE INDEX idx_seller ON Seller (seller_id);  

WITH FilteredCategory AS (  
SELECT DISTINCT Product_Category.product_id FROM Product_Category  
WHERE Product_Category.category_id = 1  
),  
ProductDetails AS (  
SELECT  
Product.product_id,  
Product.title AS product_name,  
Brand.brand_name,  
Price.price,  
Price.price_source,  
AVG(Price.price) OVER (PARTITION BY Price.product_id) AS avg_product_price  
FROM Product  
LEFT JOIN Brand ON Product.brand_id = Brand.brand_id  
JOIN Price ON Product.product_id = Price.product_id  
WHERE Product.product_id IN (SELECT FilteredCategory.product_id FROM FilteredCategory)  
),  
FinalDetails AS (  
SELECT  
Orders.order_id, Orders.order_date, ProductDetails.product_name, Price.seller_id, ProductDetails.brand_name, ProductDetails.price, ProductDetails.price_source, ProductDetails.avg_product_price  
FROM Orders  
JOIN Order_Detail ON Orders.order_id = Order_Detail.order_id  
JOIN ProductDetails ON Order_Detail.product_id = ProductDetails.product_id JOIN Price ON ProductDetails.product_id = Price.product_id  
LEFT JOIN Seller ON Price.seller_id = Seller.seller_id  
) SELECT  
FinalDetails.order_id, FinalDetails.product_name, FinalDetails.seller_id, FinalDetails.brand_name, FinalDetails.price, FinalDetails.price_source, FinalDetails.avg_product_price  
FROM FinalDetails  
ORDER BY FinalDetails.order_date, FinalDetails.product_name;

Примітка: Подальше налаштування та масштабування може ще більше покращити продуктивність, залежно від змінюваних даних та робочого навантаження.

Уроки, які я засвоїв (Lessons Learned)

Проектування схеми має критичне значення
Правильно спроектована зіркова схема закладає основу для інтуїтивно зрозумілого та ефективного сховища даних, що значно полегшує запити та аналіз даних.
Матеріалізовані погляди значно покращують продуктивність
Використання матеріалізованих поглядів значно зменшує час обчислень, особливо для складних агрегацій, що забезпечує швидше виконання запитів.
Оптимізація продуктивності — це постійний процес
Регулярний моніторинг та оптимізація є важливими для підтримки високої продуктивності системи, оскільки обсяг даних і вимоги бізнесу змінюються.
Співпраця сприяє успіху
Співпраця з зацікавленими сторонами допомагає забезпечити, щоб сховище даних відповідало вимогам бізнесу, надаючи можливість приймати обґрунтовані рішення та отримувати корисні висновки.

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

Повний покроковий аналіз з SQL кодом, панеллю візуалізації Tableau доступний на моєму GitHub. Сподіваюся, цей пост надав корисні ідеї, діліться своїми думками чи досвідом у коментарях, рекомендаціями або пропозиціями щодо покращення коду.

Продовжуймо навчатися разом!

Перекладено з: Optimizing Data Warehousing with PostgreSQL: Star Schema, Materialized Views, and Performance Tuning