Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

06/16/26 19:09:17 (4 days ago)

Author:

231067

Comment:

--

AdvancedTopics

@@ +1 @@
+= Напредна Тема
+
+== Декларативно партиционирање на податоци
+
+Со цел да се обезбеди висока скалабилност на системот и оптимални перформанси при експоненцијален раст на податоците, во овој проект имплементиравме декларативно партиционирање на најоптоварените трансакциски табели: Message и Mentorship.  Бидејќи овие табели содржат податоци кои природно растат низ времето, искористена е стратегијата RANGE Partitioning, каде податоците се логички поделени врз основа на нивниот датум на креирање (Timestamp за пораки и CreatedAt за менторства).  
+
+За да не се наруши интегритетот на постоечките податоци, креирани се паралелни партиционирани табели (Mentorship_Partitioned и Message_Partitioned). За овие табели дефинирани се следните физички партиции: 
+ 
+- Годишни партиции: изолирани физички табели за тековниот и идниот оперативен период на факултетот (за 2024, 2025 и 2026 година). Ова овозможува лесно архивирање на старите податоци во иднина.  
+- DEFAULT партиција: како безбедносен механизам, креирани се табелите mentorship_future и message_future. Сите податоци чиј датум не припаѓа во експлицитно дефинираните години ќе бидат рутирани тука, со што се спречува системско паѓање при внес на непланирани датуми.  
+
+== Оптимизација преку локални индекси
+
+Наместо еден масивен глобален индекс кој би ја забавил работата на целата база, на секоја физичка партиција се поставени посебни локални индекси. На пример, креирани се B-Tree индекси на колоните ChatID и UserID во секоја годишна партиција за пораките, што драстично го намалува времето за пребарување и го минимизира таканаречениот Write Overhead.  
+
+== Миграција и Доказ за перформанси
+
+За да се докаже функционалноста на архитектурата, извршена е симулација на миграција на податоци преку префрлање на 100,000 записи од оригиналната во партиционираната структура. При самиот INSERT, PostgreSQL интелигентно ги рутираше податоците во соодветните физички табели. Оваа рутирана дистрибуција е верификувана преку повик кон системската колона tableoid::regclass, каде јасно се гледа физичката локација на секој поединечен запис.
+
+Крајната придобивка од оваа архитектура е Partition Pruning. При извршување на аналитички пребарувања (на пр. барање пораки исклучиво за 2024 година), EXPLAIN ANALYZE планот потврдува дека Query Optimizer-от ја скенира единствено табелата message_2024, додека останатите физички партиции целосно ги игнорира. 
+
+
+{{{
+CREATE TABLE Mentorship_Partitioned
+(
+    ID                  BIGSERIAL,
+    StudentID           BIGINT NOT NULL,
+    MentorID            BIGINT NOT NULL,
+    TopicSuggestionID   BIGINT NOT NULL,
+    MentorshipTypeID    BIGINT NOT NULL,
+    CreatedAt           DATE NOT NULL,
+
+    PRIMARY KEY(ID, CreatedAt)
+)
+PARTITION BY RANGE (CreatedAt);
+
+
+CREATE TABLE mentorship_2024
+PARTITION OF Mentorship_Partitioned
+FOR VALUES FROM ('2024-01-01')
+TO ('2025-01-01');
+
+
+CREATE TABLE mentorship_2025
+PARTITION OF Mentorship_Partitioned
+FOR VALUES FROM ('2025-01-01')
+TO ('2026-01-01');
+
+
+CREATE TABLE mentorship_2026
+PARTITION OF Mentorship_Partitioned
+FOR VALUES FROM ('2026-01-01')
+TO ('2027-01-01');
+
+
+
+CREATE TABLE mentorship_future
+PARTITION OF Mentorship_Partitioned
+DEFAULT;
+
+
+
+CREATE INDEX idx_m2024_student
+ON mentorship_2024(StudentID);
+
+CREATE INDEX idx_m2025_student
+ON mentorship_2025(StudentID);
+
+CREATE INDEX idx_m2026_student
+ON mentorship_2026(StudentID);
+
+
+INSERT INTO Mentorship_Partitioned
+(
+    StudentID,
+    MentorID,
+    TopicSuggestionID,
+    MentorshipTypeID,
+    CreatedAt
+)
+VALUES
+(
+    1,
+    2,
+    3,
+    1,
+    '2025-06-15'
+);
+
+
+SELECT tableoid::regclass, *
+FROM Mentorship_Partitioned;  CREATE TABLE Message_Partitioned
+(
+    ID BIGSERIAL,
+    Content TEXT,
+    isRead BOOLEAN,
+    Timestamp TIMESTAMP NOT NULL,
+    ChatID BIGINT NOT NULL,
+    UserID BIGINT NOT NULL,
+
+    PRIMARY KEY (ID, Timestamp)
+)
+PARTITION BY RANGE (Timestamp);
+
+
+CREATE TABLE message_2024
+PARTITION OF Message_Partitioned
+FOR VALUES FROM ('2024-01-01')
+TO ('2025-01-01');
+
+CREATE TABLE message_2025
+PARTITION OF Message_Partitioned
+FOR VALUES FROM ('2025-01-01')
+TO ('2026-01-01');
+
+CREATE TABLE message_2026
+PARTITION OF Message_Partitioned
+FOR VALUES FROM ('2026-01-01')
+TO ('2027-01-01');
+
+CREATE TABLE message_future
+PARTITION OF Message_Partitioned
+DEFAULT;
+
+
+CREATE INDEX idx_msg2024_chat
+ON message_2024(ChatID);
+
+CREATE INDEX idx_msg2025_chat
+ON message_2025(ChatID);
+
+CREATE INDEX idx_msg2026_chat
+ON message_2026(ChatID);
+
+CREATE INDEX idx_msg2024_user
+ON message_2024(UserID);
+
+CREATE INDEX idx_msg2025_user
+ON message_2025(UserID);
+
+CREATE INDEX idx_msg2026_user
+ON message_2026(UserID);
+
+
+
+
+INSERT INTO Message_Partitioned
+(
+    Content,
+    isRead,
+    Timestamp,
+    ChatID,
+    UserID
+)
+VALUES
+(
+    'Testing partitioning',
+    false,
+    '2025-04-10 12:00:00',
+    1,
+    1
+);
+
+
+SELECT tableoid::regclass, *
+FROM Message_Partitioned;
+
+
+
+
+INSERT INTO Message_Partitioned (ID, Content, isRead, Timestamp, ChatID, UserID)
+SELECT ID, Content, isRead, Timestamp, ChatID, UserID
+FROM Message
+LIMIT 100000; 
+
+ANALYZE Message_Partitioned;
+
+
+EXPLAIN ANALYZE
+SELECT *
+FROM Message_Partitioned
+WHERE Timestamp BETWEEN
+'2024-01-01'
+AND
+'2024-12-31';
+
+
+SELECT ID, Timestamp, Content 
+FROM message_2024 
+LIMIT 20;
+}}}