Changes between Version 1 and Version 2 of AdvancedTopics
- Timestamp:
- 06/14/26 22:18:34 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedTopics
v1 v2 1 = Фаза 5: Напредна тема - Партиционирање =1 = Фаза 5: Имплементација на напредна тема - Партиционирање = 2 2 3 [[BR]] 4 5 За нашиот систем V-Track избравме напредна тема-партиционирање на централната табела `Zapisnik`. Оваа табела ја избравме бидејќи претставува главна компонента на нашиот систем: во неа се чува секој запишан прекршок од страна на полицијата, таа расте континуирано и секојдневно, и веќе содржи околу 10.000.000 записи. 6 7 За време на развојот на системот, забележавме дека речиси сите наши прегледи и извештаи (на пример, преглед на неплатени казни, анализа на историјата на еден граѓанин или пресметка на активноста на полицајците) секогаш се филтрираат според `datum`, односно бараат податоци за специфичен временски период. 8 9 Одлуката да имплементираме партиционирање ја донесовме од два клучни аспекта, директно поврзани со потребите на проектот: 10 11 * '''Подобрување на перформансите:''' Без партиционирање, секогаш кога ќе побараме извештај за еден месец, базата мора да пребара низ сите 10 милиони редови. Со партиционирање, системот ги бара и ги чита само податоците за таа конкретна година. 12 13 14 * '''Одржување и архивирање:''' Како што минуваат годините, старите записници повеќе не се активни. Наместо да извршуваме `DELETE` команди врз милиони редови за да ги архивираме, партиционирањето ни овозможува едноставно да тргнеме цела една година како посебна табела. 3 15 4 16 5 17 6 18 [[BR]] 7 == 1. Опис == 8 Партиционирана е централната табела '''Zapisnik''' (~10.000.000 записи) по '''RANGE''' стратегија врз колоната `datum`, со гранулација по '''година''' (партиции 2021–2026 + `DEFAULT`). Партиционирањето повлече измени и врз двете зависни табели што референцираат `Zapisnik` - '''Stavka_Zapisnik''' и '''Uplata''' - за да се зачува интегритетот, како и дополнување на процедурите што вметнуваат во нив. 19 == 1. Стратегија == 20 21 Бидејќи записите природно се групираат и пребаруваат по време, избравме стратегија на партиционирање по опсег (RANGE) врз колоната `datum`. 22 23 Гранулацијата ја поставивме да биде по година. Креиравме партиции за 2021, 2022, 2023, 2024, 2025 и 2026 година, како и една `DEFAULT` партиција за секој запис што случајно би имал датум надвор од овие опсези. Оваа гранулација е идеална за нашиот домен, бидејќи извештаите најчесто се прават на годишно ниво, а една година по партиција ни дава еднаква поделба на огромниот број податоци. 24 25 [[BR]] 26 == 2. Имплементација == 27 28 За да го имплементираме партиционирањето, моравме да направиме неколку клучни промени. За да ја партиционираме табелата `Zapisnik` мора (`datum`) да биде дел од Примарниот Клуч (PK). Затоа, оригиналниот клуч `id_na_zapisnik` го променивме во композитен клуч: `(id_na_zapisnik, datum)`. Поради таа промена, моравме да ги ажурираме и табелите зависни од `Zapisnik` (`Stavka_Zapisnik` и `Uplata`) за да содржат датум и да ги поврземе преку композитни Надворешни Клучеви (FK). 9 29 10 30 11 [[BR]]12 == 2. Избор на табела и стратегија ==13 `Zapisnik` е најголемата и најфреквентно пребарувана табела во системот. Записите се додаваат постојано, се групираат природно по `datum`, а голем дел од погледите и прегледите филтрираат по временски период. Со партиционирање по `datum`:14 * при филтрирање по период, планерот пристапува само до релевантната година (партиција), наместо до сите ~10М редови;15 * архивирање на стари записници станува отстранување на цела партиција, наместо `DELETE` врз милиони редови.16 31 17 18 [[BR]] 19 == 3. Измени во шемата == 20 21 === 3.1 Примарен клуч === 22 Оригиналниот PK беше само `id_na_zapisnik`. Бидејќи PostgreSQL бара партициониониот клуч да биде дел од PK, тој е направен '''композитен''': `(id_na_zapisnik, datum)`. 23 24 === 3.2 Надворешни клучеви === 25 Поради композитниот PK, веќе нема уникатност само на `id_na_zapisnik`, па старите FK од `Stavka_Zapisnik` и `Uplata` не важат. За да се зачува интегритетот: 26 * во `Stavka_Zapisnik` и во `Uplata` е додадена нова колона `datum_zapisnik`; 27 * Foreign keys се преправени во '''композитни''': `(id, datum_zapisnik) → Zapisnik(id_na_zapisnik, datum)`. 28 29 === 3.3 Погледи === 30 Сите погледи (`v_site_zapisnici`, `v_zapisnici_detalno`, `v_neplateni_kazni`, `v_povtoreni_prekrsoci`, `v_policajci_prosek_zapisnici`, `v_istorija_gragjanin`) '''не бараат никаква измена''' — продолжуваат да работат, бидејќи пристапот до партиционираната табела е ист како до обична. 31 32 === 3.4 Процедури === 33 Процедурите `kreiraj_zapisnik_so_prekrsok` и `dodadi_stavka_zapisnik` се '''дополнети''' да ја пополнуваат `datum_zapisnik` при вметнување во `Uplata`/`Stavka_Zapisnik`, инаку композитниот FK би бил прекршен. 34 35 === 3.5 Тригери === 36 Тригерот `azuriraj_iznos_uplata` врз `Stavka_Zapisnik` е исклучен само за време на масовното пополнување на `datum_zapisnik`, за да не се активира 20М пати. 37 38 39 [[BR]] 40 == 4. Имплементација == 41 42 === Чекор 1 - Додавање и пополнување на datum_zapisnik во децата === 43 Се прави '''додека старата `Zapisnik` сè уште постои''' (за да се извлече датумот). Тригерите се исклучуваат за време на пополнувањето. 32 === Чекор 1: Подготовка на зависните табели === 33 Додека старата табела сѐ уште постои, во `Stavka_Zapisnik` и `Uplata` додаваме колона `datum_zapisnik` и ја пополнуваме со датумите од записникот. За да не се активираат тригерите милиони пати (на пр. тригерот што пресметува сума за наплата), привремено ги исклучуваме. 44 34 {{{ 45 35 #!sql 46 36 ALTER TABLE Stavka_Zapisnik DISABLE TRIGGER USER; 37 47 38 ALTER TABLE Stavka_Zapisnik ADD COLUMN datum_zapisnik date; 48 39 UPDATE Stavka_Zapisnik sz … … 50 41 FROM Zapisnik z 51 42 WHERE sz.id_na_zapisnik = z.id_na_zapisnik; 43 52 44 ALTER TABLE Stavka_Zapisnik ENABLE TRIGGER USER; 53 45 … … 59 51 }}} 60 52 61 === Чекор 2 - Отстранување на старите FK===62 Не можат да опстанат покрај новиот композитен PK.53 === Чекор 2: Бришење стари клучеви === 54 Старите надворешни клучеви покажуваат само кон `id_na_zapisnik`, па мораме да ги избришеме пред да ја промениме главната табела. 63 55 {{{ 64 56 #!sql … … 67 59 }}} 68 60 69 === Чекор 3 - Преименување и креирање на партициониранататабела ===70 Старата `Zapisnik_old` се задржува како baseline копија. Новата се создава со композитен PK и `PARTITION BY RANGE (datum)`.61 === Чекор 3: Преименување табела === 62 Оригиналната табела ја преименуваме во `Zapisnik_old`. Ја чуваме привремено за да ги префрлиме податоците од неа во новиот систем. 71 63 {{{ 72 64 #!sql 73 65 ALTER TABLE Zapisnik RENAME TO Zapisnik_old; 66 }}} 74 67 68 === Чекор 4: Нова главна табела === 69 Ја креираме новата главна табела со композитниот примарен клуч и ја дефинираме да се партиционира по опсег на датумот (`PARTITION BY RANGE (datum)`). 70 {{{ 71 #!sql 75 72 CREATE TABLE Zapisnik ( 76 77 78 79 80 81 82 83 84 85 86 87 88 89 73 id_na_zapisnik int, 74 vreme time, 75 datum date NOT NULL, 76 lokacija varchar(100), 77 Potpis boolean DEFAULT false, 78 id_slucaj int, 79 EMBG_Prekrsuvach char(13), 80 Vozilo_Broj_Sasija varchar(17), 81 EMBG_Policaec char(13), 82 PRIMARY KEY (id_na_zapisnik, datum), 83 FOREIGN KEY (id_slucaj) REFERENCES Slucaj(id_slucaj) ON UPDATE CASCADE ON DELETE RESTRICT, 84 FOREIGN KEY (EMBG_Prekrsuvach) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT, 85 FOREIGN KEY (Vozilo_Broj_Sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT, 86 FOREIGN KEY (EMBG_Policaec) REFERENCES Policaec(EMBG_P) ON UPDATE CASCADE ON DELETE RESTRICT 90 87 ) PARTITION BY RANGE (datum); 91 88 }}} 92 89 93 === Чекор 4 - Креирање на партициите по година === 90 === Чекор 5: Дефинирање партиции === 91 Креираме партиционирани табели за секоја година. 94 92 {{{ 95 93 #!sql … … 103 101 }}} 104 102 105 === Чекор 5 - Миграција на податоците===106 PostgreSQL автоматски го распоредува секој од ~10М редови во соодветната партиција. `ANALYZE` за свежа статистика.103 === Чекор 6: Префрлање податоци === 104 Ги префрламе сите ~10 милиони редови. Секој ред автоматски се сместува во соодветната табела (партиција). 107 105 {{{ 108 106 #!sql 109 107 INSERT INTO Zapisnik (id_na_zapisnik, vreme, datum, lokacija, Potpis, 110 108 id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec) 111 109 SELECT id_na_zapisnik, vreme, datum, lokacija, Potpis, 112 110 id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec 113 111 FROM Zapisnik_old; 114 115 116 112 }}} 117 113 118 === Чекор 6 - Враќање на интегритетот (композитни FK) === 114 === Чекор 7: Нови надворешни клучеви === 115 Го враќаме интегритетот на базата. Сега `Stavka_Zapisnik` и `Uplata` ги поврзуваме со `Zapisnik` користејќи го композитниот клуч `(id_na_zapisnik, datum)`. 119 116 {{{ 120 117 #!sql 121 118 ALTER TABLE Stavka_Zapisnik 122 123 124 125 119 ADD CONSTRAINT stavka_zapisnik_zapisnik_fk 120 FOREIGN KEY (id_na_zapisnik, datum_zapisnik) 121 REFERENCES Zapisnik (id_na_zapisnik, datum) 122 ON UPDATE CASCADE ON DELETE CASCADE; 126 123 127 124 ALTER TABLE Uplata 128 129 130 131 125 ADD CONSTRAINT uplata_zapisnik_fk 126 FOREIGN KEY (id_zapisnik, datum_zapisnik) 127 REFERENCES Zapisnik (id_na_zapisnik, datum) 128 ON UPDATE CASCADE ON DELETE RESTRICT; 132 129 }}} 133 134 === Чекор 7 - Дополнување на процедурите ===135 За процедурите да продолжат да работат со композитниот FK, при вметнување се предава и `datum_zapisnik`. Пример од `kreiraj_zapisnik_so_prekrsok`:136 {{{137 #!sql138 INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik, datum_zapisnik)139 VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik, p_datum);140 141 INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok, datum_zapisnik)142 VALUES (1, v_id_zapisnik, p_id_prekrsok, p_datum);143 }}}144 Истото важи и за `dodadi_stavka_zapisnik` — прво се чита `datum` на записникот, па се запишува во `datum_zapisnik`.145 130 146 131 147 132 [[BR]] 148 == 5. Резултати и споредба ==133 == 3. Резултати и споредба == 149 134 150 === 5.1 План на извршување ===135 === 3.1 План на извршување === 151 136 Истото барање е извршено врз непартиционираната (`Zapisnik_old`) и врз партиционираната табела, со `EXPLAIN (ANALYZE, BUFFERS)`: 152 137 {{{ … … 156 141 WHERE datum BETWEEN '2024-03-01' AND '2024-03-31'; 157 142 }}} 158 '''Пред партиционирањето''' —планерот изведе `Parallel Seq Scan` врз целата табела `Zapisnik_old` (време ~2325 ms):143 Пред партиционирањето -планерот изведе `Parallel Seq Scan` врз целата табела `Zapisnik_old` (време ~2325 ms): 159 144 160 145 [[Image(before.png, width=1000)]] 161 146 162 '''По партиционирањето''' — благодарение на '''partition pruning''', планерот пристапи '''само'''до партицијата `Zapisnik_2024` (`Seq Scan`, време ~449 ms), отфрлајќи ги сите останати партиции:147 По партиционирањето -благодарение на partition pruning, планерот пристапи само до партицијата `Zapisnik_2024` (`Seq Scan`, време ~449 ms), отфрлајќи ги сите останати партиции: 163 148 164 149 [[Image(after.png, width=1000)]] 165 150 166 === 5.3Споредба на перформанси ===151 === 3.2 Споредба на перформанси === 167 152 ||='''Аспект'''=||='''Пред (Zapisnik_old)'''=||='''Потоа (партиционирана)'''=|| 168 153 || Тип на пристап || Parallel Seq Scan врз цела табела || Seq Scan само на Zapisnik_2024 || … … 172 157 || Вратени редови || ~168.330 || ~168.330 || 173 158 174 Резултатот е ист (~168.330 редови за месецот), но '''количината на работа е драстично помала''': времето падна од ~2325 ms на ~449 ms (≈ '''5 пати побрзо'''), а проценетата цена од ~362.000 на ~60.000 (≈ '''6 пати помала'''). Дополнително, baseline-от мораше да вклучи паралелизам за да ја совлада целата табела, додека партиционираната верзија беше побрза и со едноставен `Seq Scan` врз една партиција. 175 159 Резултатот е ист (~168.330 редови за месецот), но количината на работа е драстично помала: времето падна од ~2325 ms на ~449 ms, а проценетата цена од ~362.000 на ~60.000. Дополнително, непартиционираната Zapisnik табела мораше да вклучи паралелизам за да ја совлада целата табела, додека партиционираната верзија беше побрза и со едноставен `Seq Scan` врз една партиција. 176 160 177 161 [[BR]] 178 == 6. Одржување == 179 Архивирање на цела година и додавање нова партиција, без `DELETE`: 162 == 5. Одржување и автоматизација == 163 164 Архитектурата со партиционирање ни нуди две клучни предности за понатамошното менаџирање со системот, кои значително ја олеснуваат работата на администраторите: 165 166 * Архивирање: Отстранувањето на стари податоци (на пр. од 2021 година) не ја оптоварува базата односно ја тргаме партицијата од табелата со: 180 167 {{{ 181 168 #!sql 182 169 ALTER TABLE Zapisnik DETACH PARTITION Zapisnik_2021; 170 }}} 171 * Процедура за креирање нови партиции: Процедурата прима само број на година, сама ги пресметува датумите и динамички ја креира потребната табела. 172 {{{ 173 #!sql 174 CREATE OR REPLACE PROCEDURE avtomatiziraj_particija(p_godina int) 175 LANGUAGE plpgsql 176 AS $$ 177 BEGIN 183 178 184 CREATE TABLE Zapisnik_2027 PARTITION OF Zapisnik 185 FOR VALUES FROM ('2027-01-01') TO ('2028-01-01'); 179 EXECUTE format( 180 'CREATE TABLE IF NOT EXISTS Zapisnik_%s PARTITION OF Zapisnik FOR VALUES FROM (%L) TO (%L);', 181 p_godina, 182 p_godina::text || '-01-01', 183 (p_godina + 1)::text || '-01-01' 184 ); 185 RAISE NOTICE 'Партицијата за % година е успешно креирана.', p_godina; 186 END; 187 $$; 186 188 }}} 189 * Повик кон процедурата: 190 {{{ 191 #!sql 187 192 188 189 [[BR]] 190 == 7. Прилози == 191 * [attachment:particioniranje.sql Скрипта за партиционирање] — целосна имплементација. 192 * before.png, after.png — слики од плановите на извршување (вметнати во секција 5.2). 193 CALL avtomatiziraj_particija(2027); 194 }}}
