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

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

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

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


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

[attachment:partitioning.sql partitioning.sql]


[[BR]]
== 1. Стратегија ==

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

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

[[BR]]
== 2. Имплементација ==

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



=== Чекор 1: Подготовка на зависните табели ===
Додека старата табела сѐ уште постои, во `Stavka_Zapisnik` и `Uplata` додаваме колона `datum_zapisnik` и ја пополнуваме со датумите од записникот. За да не се активираат тригерите милиони пати (на пр. тригерот што пресметува сума за наплата), привремено ги исклучуваме.
{{{
#!sql
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`, па мораме да ги избришеме пред да ја промениме главната табела.
{{{
#!sql
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`. Ја чуваме привремено за да ги префрлиме податоците од неа во новиот систем.
{{{
#!sql
ALTER TABLE Zapisnik RENAME TO Zapisnik_old;
}}}

=== Чекор 4: Нова главна табела ===
Ја креираме новата главна табела со композитниот примарен клуч и ја дефинираме да се партиционира по опсег на датумот (`PARTITION BY RANGE (datum)`). Ја задржуваме и колоната `status_zapisnik` (заедно со нејзиниот `CHECK` и `DEFAULT`), бидејќи таа е дел од шемата и се користи од процедурата за затворање записник.
{{{
#!sql
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: Дефинирање партиции ===
Креираме партиционирани табели за секоја година.
{{{
#!sql
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`. Секој ред автоматски се сместува во соодветната табела (партиција).
{{{
#!sql
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`, ја поврзуваме колоната со постојната секвенца и ја поставуваме на тековниот максимум за да не се судираат новите вредности.
{{{
#!sql
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)`.
{{{
#!sql
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;
}}}


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

=== 3.1 План на извршување ===
Истото барање е извршено врз непартиционираната (`Zapisnik_old`) и врз партиционираната табела, со `EXPLAIN (ANALYZE, BUFFERS)`:
{{{
#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM Zapisnik
WHERE datum BETWEEN '2024-03-01' AND '2024-03-31';
}}}
Пред партиционирањето -планерот изведе `Parallel Seq Scan` врз целата табела `Zapisnik_old` (време ~2325 ms):

[[Image(before.png, width=1000)]]

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

[[Image(after.png, width=1000)]]

=== 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` врз една партиција.

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

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

* Архивирање: Отстранувањето на стари податоци (на пр. од 2021 година) не ја оптоварува базата односно ја тргаме партицијата од табелата со:
{{{
#!sql
ALTER TABLE Zapisnik DETACH PARTITION Zapisnik_2021;
}}}
* Процедура за креирање нови партиции: Процедурата прима само број на година, сама ги пресметува датумите и динамички ја креира потребната табела.
{{{
#!sql
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;
$$;
}}}
* Повик кон процедурата: 
{{{
#!sql

CALL avtomatiziraj_particija(2027);
}}}