Advanced Concepts

Документација за имплементација на Time-Series и OLAP

Цел на скриптата

Целта на оваа скрипта е да ја подобри аналитичката ефикасност и скалабилност на системот за управување со магацин преку имплементација на TimescaleDB и OLAP (Online Analytical Processing) архитектура.

Системот содржи голем број трансакции и движења на залихи кои континуирано се генерираат со текот на времето. Поради тоа, класичните PostgreSQL табели стануваат неефикасни за аналитички прашања кои опфаќаат големи временски периоди.

Со оваа имплементација се постигнува:

• Подобри перформанси при аналитички пребарувања.
• Оптимизирано складирање на временски податоци.
• Автоматско партиционирање на податоците според време.
• Брзо генерирање на извештаи преку претходно агрегирани податоци.
• Намалување на оптоварувањето врз оперативната база.

1. Иницијализација на TimescaleDB

CREATE EXTENSION IF NOT EXISTS timescaledb;

Во првиот чекор се активира TimescaleDB екстензијата.

TimescaleDB претставува надградба на PostgreSQL специјализирана за работа со временски серии (time-series data). Бидејќи сите движења и трансакции во системот содржат временска ознака (created_at), овие податоци природно се вклопуваат во time-series модел.

2. Подготовка на табелите

Отстранување на постоечки ограничувања

Пред конверзијата се отстрануваат:

• примарни клучеви;
• надворешни клучеви кои зависат од табелите што ќе се мигрираат.

Причината е што TimescaleDB бара временската колона да биде дел од примарниот клуч на hypertable.

3. Редефинирање на примарните клучеви

Се воведуваат композитни примарни клучеви:

ALTER TABLE inventory_movements
ADD PRIMARY KEY (created_at, id);

и

ALTER TABLE inventory_transactions
ADD PRIMARY KEY (created_at, id);

Зошто е имплементирано?

TimescaleDB бара partition колоната (created_at) да биде дел од примарниот клуч.

Ова овозможува:

• правилна распределба на податоците во chunk-ови;
• побрзо пребарување по временски интервали;
• одржување на уникатност на записите.

4. Миграција кон Hypertables

Креирање Hypertables

SELECT create_hypertable(
    'inventory_transactions',
    'created_at',
    chunk_time_interval => INTERVAL '1 month',
    migrate_data => TRUE
);

Зошто е имплементирано?

Hypertable претставува логичка табела која физички е поделена на повеќе помали партиции (chunks).

Во овој случај:

• секој chunk содржи податоци за еден месец;
• пребарувањата врз одреден временски период ги читаат само релевантните chunks;
• значително се намалува количината на податоци која PostgreSQL треба да ја скенира.

5. OLAP Слој

По оптимизацијата на складирањето се имплементира аналитички слој.

Основната идеја е оперативните податоци да не се пресметуваат повторно при секој извештај.

Наместо тоа се користат Continuous Aggregates.

6. Daily Cube – cube_movements_daily

Се креира материјализиран поглед:

CREATE MATERIALIZED VIEW cube_movements_daily
WITH (timescaledb.continuous)

Овој агрегат ги групира податоците по:

• производ;
• бренд;
• магацин;
• ден.

Метрики

total_moved

Пресметува нето движење на залихите.

Излезни движења:

WHEN mv.from_bin_id IS NOT NULL
THEN -mv.quantity

Влезни движења:

WHEN mv.to_bin_id IS NOT NULL
THEN mv.quantity

Ова овозможува следење на реалниот проток на роба низ магацините.

movement_count

COUNT(*)

Го претставува бројот на движења.

transaction_count

COUNT(DISTINCT mv.inventory_transactions_id)

Го претставува бројот на уникатни трансакции.

7. Monthly Cube – cube_movements_monthly

Над дневниот агрегат се гради месечен агрегат:

CREATE MATERIALIZED VIEW cube_movements_monthly

Овој пристап е познат како:

Aggregation-on-Aggregation

Наместо секој пат да се читаат милиони записи од inventory_movements, системот чита веќе агрегирани дневни резултати.

Со тоа се добива:

• помал број прочитани записи;
• побрзо извршување на извештаи;
• подобра скалабилност.

8. Автоматско освежување

Се конфигурира политика:

SELECT add_continuous_aggregate_policy(...)

Политиката автоматски ги освежува агрегатите секој час.

Причина

На овој начин:

• аналитичките податоци остануваат речиси во реално време;
• нема потреба од рачно освежување;
• извештаите секогаш користат ажурирани податоци.

Дополнително се извршува:

CALL refresh_continuous_aggregate(...)

за иницијално пополнување на агрегатите.

9. Аналитички извештаи

Извештај 1: Топ 25% производи

Целта е да се идентификуваат најактивните производи според количината на движење.

Се користат:

• месечниот cube;
• агрегирање по производ;
• квартилна анализа преку NTILE(4).

Производите во првиот квартил претставуваат најдвижени производи во избраниот период.

Овој извештај е корисен за:

• оптимизација на залихи;
• планирање на набавки.

Извештај 2: Bottom 25% Warehouses by Inventory Turnover

Целта е да се идентификуваат магацини со најнизок коефициент на обртот на залихите.

Коефициентот се пресметува како:

Turnover Ratio =
Вкупно движење / Вкупна количина на залиха

Потоа магацините се распределуваат во квартили.

Првиот квартил ги претставува магацините со најслаби перформанси.

Овој извештај може да помогне при:

• откривање неефикасни магацини;
• оптимизација на складишниот простор;
• подобрување на логистичките процеси.

Заклучок

Со оваа имплементација оперативните табели за движење и трансакции се трансформираат во TimescaleDB hypertables, што овозможува значително подобри перформанси при работа со големи количини временски податоци.

Дополнително, преку имплементација на Continuous Aggregates се создава OLAP слој кој обезбедува брзо генерирање на аналитички извештаи без потреба од повторно процесирање на суровите податоци.

Резултатот е архитектура која истовремено поддржува оперативни трансакции (OLTP) и аналитичко известување (OLAP), со висока скалабилност, подобри перформанси и поефикасно користење на ресурсите на базата на податоци.