= Фаза 5: Напредна тема - Партиционирање =




[[BR]]
== 1. Опис  ==
Партиционирана е централната табела '''Zapisnik''' (~10.000.000 записи) по '''RANGE''' стратегија врз колоната `datum`, со гранулација по '''година''' (партиции 2021–2026 + `DEFAULT`). Партиционирањето повлече измени и врз двете зависни табели што референцираат `Zapisnik` - '''Stavka_Zapisnik''' и '''Uplata''' - за да се зачува интегритетот, како и дополнување на процедурите што вметнуваат во нив.


[[BR]]
== 2. Избор на табела и стратегија ==
`Zapisnik` е најголемата и најфреквентно пребарувана табела во системот. Записите се додаваат постојано, се групираат природно по `datum`, а голем дел од погледите и прегледите филтрираат по временски период. Со партиционирање по `datum`:
* при филтрирање по период, планерот пристапува само до релевантната година (партиција), наместо до сите ~10М редови;
* архивирање на стари записници станува отстранување на цела партиција, наместо `DELETE` врз милиони редови.


[[BR]]
== 3. Измени во шемата ==

=== 3.1 Примарен клуч ===
Оригиналниот PK беше само `id_na_zapisnik`. Бидејќи PostgreSQL бара партициониониот клуч да биде дел од PK, тој е направен '''композитен''': `(id_na_zapisnik, datum)`.

=== 3.2 Надворешни клучеви ===
Поради композитниот PK, веќе нема уникатност само на `id_na_zapisnik`, па старите FK од `Stavka_Zapisnik` и `Uplata` не важат. За да се зачува интегритетот:
* во `Stavka_Zapisnik` и во `Uplata` е додадена нова колона `datum_zapisnik`;
* Foreign keys се преправени во '''композитни''': `(id, datum_zapisnik) → Zapisnik(id_na_zapisnik, datum)`.

=== 3.3 Погледи ===
Сите погледи (`v_site_zapisnici`, `v_zapisnici_detalno`, `v_neplateni_kazni`, `v_povtoreni_prekrsoci`, `v_policajci_prosek_zapisnici`, `v_istorija_gragjanin`) '''не бараат никаква измена''' — продолжуваат да работат, бидејќи пристапот до партиционираната табела е ист како до обична.

=== 3.4 Процедури ===
Процедурите `kreiraj_zapisnik_so_prekrsok` и `dodadi_stavka_zapisnik` се '''дополнети''' да ја пополнуваат `datum_zapisnik` при вметнување во `Uplata`/`Stavka_Zapisnik`, инаку композитниот FK би бил прекршен.

=== 3.5 Тригери ===
Тригерот `azuriraj_iznos_uplata` врз `Stavka_Zapisnik` е исклучен само за време на масовното пополнување на `datum_zapisnik`, за да не се активира 20М пати.


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

=== Чекор 1 - Додавање и пополнување на datum_zapisnik во децата ===
Се прави '''додека старата `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 - Отстранување на старите FK ===
Не можат да опстанат покрај новиот композитен PK.
{{{
#!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` се задржува како baseline копија. Новата се создава со композитен PK и `PARTITION BY RANGE (datum)`.
{{{
#!sql
ALTER TABLE Zapisnik RENAME TO Zapisnik_old;

CREATE TABLE Zapisnik (
    id_na_zapisnik     int,
    vreme              time,
    datum              date NOT NULL,
    lokacija           varchar(100),
    Potpis             boolean DEFAULT false,
    id_slucaj          int,
    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);
}}}

=== Чекор 4 - Креирање на партициите по година ===
{{{
#!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;
}}}

=== Чекор 5 - Миграција на податоците ===
PostgreSQL автоматски го распоредува секој од ~10М редови во соодветната партиција. `ANALYZE` за свежа статистика.
{{{
#!sql
INSERT INTO Zapisnik (id_na_zapisnik, vreme, datum, lokacija, Potpis,
                      id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
SELECT id_na_zapisnik, vreme, datum, lokacija, Potpis,
       id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec
FROM Zapisnik_old;


}}}

=== Чекор 6 - Враќање на интегритетот (композитни FK) ===
{{{
#!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;
}}}

=== Чекор 7 - Дополнување на процедурите ===
За процедурите да продолжат да работат со композитниот FK, при вметнување се предава и `datum_zapisnik`. Пример од `kreiraj_zapisnik_so_prekrsok`:
{{{
#!sql
INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik, datum_zapisnik)
VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik, p_datum);

INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok, datum_zapisnik)
VALUES (1, v_id_zapisnik, p_id_prekrsok, p_datum);
}}}
Истото важи и за `dodadi_stavka_zapisnik` — прво се чита `datum` на записникот, па се запишува во `datum_zapisnik`.


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

=== 5.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)]]

=== 5.3 Споредба на перформанси ===
||='''Аспект'''=||='''Пред (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 (≈ '''5 пати побрзо'''), а проценетата цена од ~362.000 на ~60.000 (≈ '''6 пати помала'''). Дополнително, baseline-от мораше да вклучи паралелизам за да ја совлада целата табела, додека партиционираната верзија беше побрза и со едноставен `Seq Scan` врз една партиција.


[[BR]]
== 6. Одржување ==
Архивирање на цела година и додавање нова партиција, без `DELETE`:
{{{
#!sql
ALTER TABLE Zapisnik DETACH PARTITION Zapisnik_2021;

CREATE TABLE Zapisnik_2027 PARTITION OF Zapisnik
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
}}}


[[BR]]
== 7. Прилози ==
* [attachment:particioniranje.sql Скрипта за партиционирање] — целосна имплементација.
* before.png, after.png — слики од плановите на извршување (вметнати во секција 5.2).