Фаза 5: Имплементација на напредна тема - Партиционирање

За нашиот систем V-Track избравме напредна тема-партиционирање на централната табела Zapisnik. Оваа табела ја избравме бидејќи претставува главна компонента на нашиот систем: во неа се чува секој запишан прекршок од страна на полицијата, таа расте континуирано и секојдневно, и веќе содржи околу 10.000.000 записи.

За време на развојот на системот, забележавме дека речиси сите наши прегледи и извештаи (на пример, преглед на неплатени казни, анализа на историјата на еден граѓанин или пресметка на активноста на полицајците) секогаш се филтрираат според datum, односно бараат податоци за специфичен временски период.

Одлуката да имплементираме партиционирање ја донесовме од два клучни аспекти, директно поврзани со потребите на проектот:

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

• Одржување и архивирање: Како што минуваат годините, старите записници повеќе не се активни. Наместо да извршуваме DELETE команди врз милиони редови за да ги архивираме, партиционирањето ни овозможува едноставно да тргнеме цела една година како посебна табела.
  

partitioning.sql​

1. Стратегија

Бидејќи записите природно се групираат и пребаруваат по време, избравме стратегија на партиционирање по опсег (RANGE) врз колоната datum.

Гранулацијата ја поставивме да биде по година. Креиравме партиции за 2021, 2022, 2023, 2024, 2025 и 2026 година, како и една DEFAULT партиција за секој запис што случајно би имал датум надвор од овие опсези. Оваа гранулација е идеална за нашиот домен, бидејќи извештаите најчесто се прават на годишно ниво, а една година по партиција ни дава еднаква поделба на огромниот број податоци.

2. Имплементација

За да го имплементираме партиционирањето, моравме да направиме неколку клучни промени. За да ја партиционираме табелата Zapisnik мора (datum) да биде дел од Примарниот Клуч (PK). Затоа, оригиналниот клуч id_na_zapisnik го променивме во композитен клуч: (id_na_zapisnik, datum). Поради таа промена, моравме да ги ажурираме и табелите зависни од Zapisnik (Stavka_Zapisnik и Uplata) за да содржат датум и да ги поврземе преку композитни Надворешни Клучеви (FK).

Чекор 1: Подготовка на зависните табели

Додека старата табела сѐ уште постои, во Stavka_Zapisnik и Uplata додаваме колона datum_zapisnik и ја пополнуваме со датумите од записникот. За да не се активираат тригерите милиони пати (на пр. тригерот што пресметува сума за наплата), привремено ги исклучуваме.

ALTER TABLE Stavka_Zapisnik DISABLE TRIGGER USER;

ALTER TABLE Stavka_Zapisnik ADD COLUMN datum_zapisnik date;
UPDATE Stavka_Zapisnik sz
SET datum_zapisnik = z.datum
FROM Zapisnik z
WHERE sz.id_na_zapisnik = z.id_na_zapisnik;

ALTER TABLE Stavka_Zapisnik ENABLE TRIGGER USER;

ALTER TABLE Uplata ADD COLUMN datum_zapisnik date;
UPDATE Uplata u
SET datum_zapisnik = z.datum
FROM Zapisnik z
WHERE u.id_zapisnik = z.id_na_zapisnik;

Чекор 2: Бришење стари клучеви

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

ALTER TABLE Stavka_Zapisnik DROP CONSTRAINT IF EXISTS stavka_zapisnik_id_na_zapisnik_fkey;
ALTER TABLE Uplata          DROP CONSTRAINT IF EXISTS uplata_id_zapisnik_fkey;

Чекор 3: Преименување табела

Оригиналната табела ја преименуваме во Zapisnik_old. Ја чуваме привремено за да ги префрлиме податоците од неа во новиот систем.

ALTER TABLE Zapisnik RENAME TO Zapisnik_old;

Чекор 4: Нова главна табела

Ја креираме новата главна табела со композитниот примарен клуч и ја дефинираме да се партиционира по опсег на датумот (PARTITION BY RANGE (datum)). Ја задржуваме и колоната status_zapisnik (заедно со нејзиниот CHECK и DEFAULT), бидејќи таа е дел од шемата и се користи од процедурата за затворање записник.

CREATE TABLE Zapisnik (
id_na_zapisnik     int,
vreme              time,
datum              date NOT NULL,
lokacija           varchar(100),
Potpis             boolean DEFAULT false,
id_slucaj          int,
status_zapisnik    varchar(20) DEFAULT 'Otvoren' CHECK (status_zapisnik IN ('Otvoren', 'Zatvoren')),
EMBG_Prekrsuvach   char(13),
Vozilo_Broj_Sasija varchar(17),
EMBG_Policaec      char(13),
PRIMARY KEY (id_na_zapisnik, datum),
FOREIGN KEY (id_slucaj)          REFERENCES Slucaj(id_slucaj)      ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (EMBG_Prekrsuvach)   REFERENCES Gragjanin(EMBG)        ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (Vozilo_Broj_Sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (EMBG_Policaec)      REFERENCES Policaec(EMBG_P)       ON UPDATE CASCADE ON DELETE RESTRICT
) PARTITION BY RANGE (datum);

Чекор 5: Дефинирање партиции

Креираме партиционирани табели за секоја година.

CREATE TABLE Zapisnik_2021 PARTITION OF Zapisnik FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE Zapisnik_2022 PARTITION OF Zapisnik FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE Zapisnik_2023 PARTITION OF Zapisnik FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE Zapisnik_2024 PARTITION OF Zapisnik FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE Zapisnik_2025 PARTITION OF Zapisnik FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE Zapisnik_2026 PARTITION OF Zapisnik FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE Zapisnik_default PARTITION OF Zapisnik DEFAULT;

Чекор 6: Префрлање податоци

Ги префрламе сите ~10 милиони редови, вклучувајќи ја и колоната status_zapisnik. Секој ред автоматски се сместува во соодветната табела (партиција).

INSERT INTO Zapisnik (id_na_zapisnik, vreme, datum, lokacija, Potpis,
id_slucaj, status_zapisnik, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
SELECT id_na_zapisnik, vreme, datum, lokacija, Potpis,
id_slucaj, status_zapisnik, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec
FROM Zapisnik_old;

ANALYZE Zapisnik;

Чекор 7: Враќање на автоматската нумерација

Колоната id_na_zapisnik беше serial пред партиционирањето, но новата табела ја дефиниравме како обичен int. За процедурите што вметнуваат нови записници (на пр. kreiraj_zapisnik_so_prekrsok) повторно да добиваат автоматско id, ја поврзуваме колоната со постојната секвенца и ја поставуваме на тековниот максимум за да не се судираат новите вредности.

ALTER TABLE Zapisnik ALTER COLUMN id_na_zapisnik SET DEFAULT nextval('zapisnik_id_na_zapisnik_seq');
ALTER SEQUENCE zapisnik_id_na_zapisnik_seq OWNED BY Zapisnik.id_na_zapisnik;
SELECT setval('zapisnik_id_na_zapisnik_seq', (SELECT COALESCE(MAX(id_na_zapisnik), 1) FROM Zapisnik));

Чекор 8: Нови надворешни клучеви

Го враќаме интегритетот на базата. Сега Stavka_Zapisnik и Uplata ги поврзуваме со Zapisnik користејќи го композитниот клуч (id_na_zapisnik, datum).

ALTER TABLE Stavka_Zapisnik
ADD CONSTRAINT stavka_zapisnik_zapisnik_fk
FOREIGN KEY (id_na_zapisnik, datum_zapisnik)
REFERENCES Zapisnik (id_na_zapisnik, datum)
ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE Uplata
ADD CONSTRAINT uplata_zapisnik_fk
FOREIGN KEY (id_zapisnik, datum_zapisnik)
REFERENCES Zapisnik (id_na_zapisnik, datum)
ON UPDATE CASCADE ON DELETE RESTRICT;

3. Резултати и споредба

3.1 План на извршување

Истото барање е извршено врз непартиционираната (Zapisnik_old) и врз партиционираната табела, со EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM Zapisnik
WHERE datum BETWEEN '2024-03-01' AND '2024-03-31';

Пред партиционирањето -планерот изведе Parallel Seq Scan врз целата табела Zapisnik_old (време ~2325 ms):

По партиционирањето -благодарение на partition pruning, планерот пристапи само до партицијата Zapisnik_2024 (Seq Scan, време ~449 ms), отфрлајќи ги сите останати партиции:

3.2 Споредба на перформанси

Аспект	Пред (Zapisnik_old)	Потоа (партиционирана)
Тип на пристап	Parallel Seq Scan врз цела табела	Seq Scan само на Zapisnik_2024
Скенирани партиции	цела табела	само Zapisnik_2024 (pruning)
Цена (cost)	1000.00 – 362222.83	0.00 – 60456.30
Време	2325.078 ms	449.092 ms
Вратени редови	~168.330	~168.330

Резултатот е ист (~168.330 редови за месецот), но количината на работа е драстично помала: времето падна од ~2325 ms на ~449 ms, а проценетата цена од ~362.000 на ~60.000. Дополнително, непартиционираната Zapisnik табела мораше да вклучи паралелизам за да ја совлада целата табела, додека партиционираната верзија беше побрза и со едноставен Seq Scan врз една партиција.

4. Одржување и автоматизација

Архитектурата со партиционирање ни нуди две клучни предности за понатамошното менаџирање со системот, кои значително ја олеснуваат работата на администраторите:

• Архивирање: Отстранувањето на стари податоци (на пр. од 2021 година) не ја оптоварува базата односно ја тргаме партицијата од табелата со:

  ALTER TABLE Zapisnik DETACH PARTITION Zapisnik_2021;
  

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

  CREATE OR REPLACE PROCEDURE avtomatiziraj_particija(p_godina int)
  LANGUAGE plpgsql
  AS $$
  BEGIN
  
  EXECUTE format(
  'CREATE TABLE IF NOT EXISTS Zapisnik_%s PARTITION OF Zapisnik FOR VALUES FROM (%L) TO (%L);',
  p_godina,
  p_godina::text || '-01-01',
  (p_godina + 1)::text || '-01-01'
  );
  RAISE NOTICE 'Партицијата за % година е успешно креирана.', p_godina;
  END;
  $$;
  

• Повик кон процедурата:

  CALL avtomatiziraj_particija(2027);