Партиционирање во BlinkBuy

partitioning.sql

Партиционирање.pdf

Вовед

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

Во BlinkBuy партиционирањето е применето на најголемите табели со цел да се подобрат:

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

Користени се два типа на партиционирање:

• LIST Partitioning – за табели со ограничен број познати категории.
• RANGE Partitioning – за временски податоци кои континуирано растат.

PRODUCT_ATTRIBUTE_VALUES (LIST Partitioning)

Причина

PRODUCT_ATTRIBUTE_VALUES е најголемата табела во системот со околу 15 милиони записи. Корисниците најчесто пребаруваат производи според нивните спецификации, како што се:

• RAM
• Процесор
• Боја

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

Решение

Табелата е партиционирана со LIST Partitioning според колоната attribute_id.

Најчесто користените атрибути се сместени во посебни партиции:

• pav_ram
• pav_processor
• pav_color итн.

Предности

Кога корисникот пребарува, на пример, производи со 16 GB RAM, PostgreSQL ја чита само партицијата pav_ram, додека останатите партиции целосно се прескокнуваат.

Ова значително го намалува бројот на прочитани записи и времето на извршување на SQL барањата.

SQL

CREATE TABLE PRODUCT_ATTRIBUTE_VALUES_P (
  variant_id   int8 NOT NULL,
  attribute_id int8 NOT NULL,
  attr_value   varchar(255) NOT NULL,
  -- pk contains attribute_id
  PRIMARY KEY (variant_id, attribute_id)
) PARTITION BY LIST (attribute_id);

PRODUCT_PRICE_HISTORY (RANGE Partitioning)

Причина

PRODUCT_PRICE_HISTORY содржи околу 2.25 милиони записи и ги чува сите промени на цените.

Бидејќи се работи за временски (time-series) податоци, природен избор е RANGE партиционирање.

Решение

Партиционирањето е извршено по месеци според колоната change_date.

Предности

• Анализите за конкретен месец или квартал пребаруваат само една или неколку партиции.
• Старите податоци можат лесно да се архивираат или избришат со откачување (detach) на цела партиција, без големи DELETE операции.

SQL

CREATE TABLE PRODUCT_PRICE_HISTORY_P (
  history_id  int8 NOT NULL,
  variant_id  int8 NOT NULL,
  old_price   numeric(12, 2) NOT NULL,
  new_price   numeric(12, 2) NOT NULL,
  change_date timestamp with time zone NOT NULL,
  -- pk includes date
  PRIMARY KEY (history_id, change_date)
) PARTITION BY RANGE (change_date);

ORDER_STATUS_HISTORY (RANGE Partitioning)

Причина

ORDER_STATUS_HISTORY содржи околу 2.06 милиони записи и го следи целиот животен циклус на една нарачка.

Решение

Табелата е партиционирана по месеци користејќи ја колоната change_date.

Предности

• Историјата на една нарачка најчесто се наоѓа во една месечна партиција.
• Новите статуси се запишуваат само во тековната партиција, што овозможува побрзо ажурирање.
• Индексите остануваат мали и поефикасни за читање и запишување.

Забелешка

Иако табелата ги содржи сите статуси, вклучувајќи го и иницијалниот статус CART, при анализи на логистичките перформанси намерно се исклучува.

Причината е што статусот CART претставува пред-продажна активност која може да трае подолг временски период. Вклучувањето на овие записи би довело до неточни пресметки за времето потребно за обработка и испорака на нарачките.

SQL

CREATE TABLE ORDER_STATUS_HISTORY_P (
  history_id  int8 NOT NULL,
  order_id    int8 NOT NULL,
  old_status  varchar(255) NOT NULL,
  new_status  varchar(255) NOT NULL,
  change_date timestamp with time zone NOT NULL,
  -- PK мора да го содржи датумот поради партиционирањето
  PRIMARY KEY (history_id, change_date)
) PARTITION BY RANGE (change_date);

Заклучок

Со примената на партиционирањето во BlinkBuy се постигнуваат значителни подобрувања во:

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

LIST партиционирањето е применето за категоријални податоци, додека RANGE партиционирањето е искористено за сите временски зависни табели, што претставува најсоодветно решение според природата на податоците.