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

1. Опис

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

2. Избор на табела и стратегија

Zapisnik е најголемата и најфреквентно пребарувана табела во системот. Записите се додаваат постојано, се групираат природно по datum, а голем дел од погледите и прегледите филтрираат по временски период. Со партиционирање по datum:

• при филтрирање по период, планерот пристапува само до релевантната година (партиција), наместо до сите ~10М редови;
• архивирање на стари записници станува отстранување на цела партиција, наместо DELETE врз милиони редови.

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М пати.

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

Чекор 1 - Додавање и пополнување на datum_zapisnik во децата

Се прави додека старата 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 - Отстранување на старите FK

Не можат да опстанат покрај новиот композитен PK.

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).

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 - Креирање на партициите по година

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 за свежа статистика.

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)

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:

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.

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

5.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), отфрлајќи ги сите останати партиции:

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

6. Одржување

Архивирање на цела година и додавање нова партиција, без DELETE:

ALTER TABLE Zapisnik DETACH PARTITION Zapisnik_2021;

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

7. Прилози

• Скрипта за партиционирање — целосна имплементација.
• before.png, after.png — слики од плановите на извршување (вметнати во секција 5.2).