Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

06/13/26 19:50:54 (7 days ago)

Author:

231069

Comment:

--

AdvancedTopics

@@ +1 @@
+= Фаза 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).