Changes between Version 1 and Version 2 of AdvancedTopics


Ignore:
Timestamp:
06/16/26 19:12:46 (3 days ago)
Author:
231067
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedTopics

    v1 v2  
    33== Декларативно партиционирање на податоци
    44
    5 Со цел да се обезбеди висока скалабилност на системот и оптимални перформанси при експоненцијален раст на податоците, во овој проект имплементиравме декларативно партиционирање на најоптоварените трансакциски табели: Message и Mentorship.  Бидејќи овие табели содржат податоци кои природно растат низ времето, искористена е стратегијата RANGE Partitioning, каде податоците се логички поделени врз основа на нивниот датум на креирање (Timestamp за пораки и CreatedAt за менторства). 
     5Со цел да се обезбеди висока скалабилност на системот и оптимални перформанси при експоненцијален раст на податоците, во овој проект имплементиравме декларативно партиционирање на најоптоварените трансакциски табели: Message и Mentorship. Бидејќи овие табели содржат податоци кои природно растат низ времето, искористена е стратегијата RANGE Partitioning, каде податоците се логички поделени врз основа на нивниот датум на креирање (Timestamp за пораки и CreatedAt за менторства). 
    66
    77За да не се наруши интегритетот на постоечките податоци, креирани се паралелни партиционирани табели (Mentorship_Partitioned и Message_Partitioned). За овие табели дефинирани се следните физички партиции:
    88 
    9 - Годишни партиции: изолирани физички табели за тековниот и идниот оперативен период на факултетот (за 2024, 2025 и 2026 година). Ова овозможува лесно архивирање на старите податоци во иднина. 
    10 - DEFAULT партиција: како безбедносен механизам, креирани се табелите mentorship_future и message_future. Сите податоци чиј датум не припаѓа во експлицитно дефинираните години ќе бидат рутирани тука, со што се спречува системско паѓање при внес на непланирани датуми. 
     9 * Годишни партиции: изолирани физички табели за тековниот и идниот оперативен период на факултетот (за 2024, 2025 и 2026 година). Ова овозможува лесно архивирање на старите податоци во иднина. 
     10 * DEFAULT партиција: како безбедносен механизам, креирани се табелите `mentorship_future` и `message_future`. Сите податоци чиј датум не припаѓа во експлицитно дефинираните години ќе бидат рутирани тука, со што се спречува системско паѓање при внес на непланирани датуми. 
    1111
    1212== Оптимизација преку локални индекси
    … …  
    1616== Миграција и Доказ за перформанси
    1717
    18 За да се докаже функционалноста на архитектурата, извршена е симулација на миграција на податоци преку префрлање на 100,000 записи од оригиналната во партиционираната структура. При самиот INSERT, PostgreSQL интелигентно ги рутираше податоците во соодветните физички табели. Оваа рутирана дистрибуција е верификувана преку повик кон системската колона tableoid::regclass, каде јасно се гледа физичката локација на секој поединечен запис.
     18За да се докаже функционалноста на архитектурата, извршена е симулација на миграција на податоци преку префрлање на 100,000 записи од оригиналната во партиционираната структура. При самиот `INSERT`, PostgreSQL интелигентно ги рутираше податоците во соодветните физички табели. Оваа рутирана дистрибуција е верификувана преку повик кон системската колона `tableoid::regclass`, каде јасно се гледа физичката локација на секој поединечен запис.
    1919
    20 Крајната придобивка од оваа архитектура е Partition Pruning. При извршување на аналитички пребарувања (на пр. барање пораки исклучиво за 2024 година), EXPLAIN ANALYZE планот потврдува дека Query Optimizer-от ја скенира единствено табелата message_2024, додека останатите физички партиции целосно ги игнорира.
     20Крајната придобивка од оваа архитектура е Partition Pruning. При извршување на аналитички пребарувања (на пр. барање пораки исклучиво за 2024 година), `EXPLAIN ANALYZE` планот потврдува дека *Query Optimizer*-от ја скенира единствено табелата `message_2024`, додека останатите физички партиции целосно ги игнорира.
    2121
     22== SQL Скрипти
     23
     24=== 1. Партиционирање на табелата Mentorship
    2225
    2326{{{
     27#!sql
    2428CREATE TABLE Mentorship_Partitioned
    2529(
    26     ID                  BIGSERIAL,
    27     StudentID           BIGINT NOT NULL,
    28     MentorID            BIGINT NOT NULL,
    29     TopicSuggestionID   BIGINT NOT NULL,
    30     MentorshipTypeID    BIGINT NOT NULL,
    31     CreatedAt           DATE NOT NULL,
     30    ID                BIGSERIAL,
     31    StudentID         BIGINT NOT NULL,
     32    MentorID          BIGINT NOT NULL,
     33    TopicSuggestionID BIGINT NOT NULL,
     34    MentorshipTypeID  BIGINT NOT NULL,
     35    CreatedAt         DATE NOT NULL,
    3236
    3337    PRIMARY KEY(ID, CreatedAt)
    … …  
    3539PARTITION BY RANGE (CreatedAt);
    3640
     41-- Креирање на партиции по години
     42CREATE TABLE mentorship_2024 PARTITION OF Mentorship_Partitioned
     43    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
    3744
    38 CREATE TABLE mentorship_2024
    39 PARTITION OF Mentorship_Partitioned
    40 FOR VALUES FROM ('2024-01-01')
    41 TO ('2025-01-01');
     45CREATE TABLE mentorship_2025 PARTITION OF Mentorship_Partitioned
     46    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
    4247
     48CREATE TABLE mentorship_2026 PARTITION OF Mentorship_Partitioned
     49    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
    4350
    44 CREATE TABLE mentorship_2025
    45 PARTITION OF Mentorship_Partitioned
    46 FOR VALUES FROM ('2025-01-01')
    47 TO ('2026-01-01');
     51-- Default партиција
     52CREATE TABLE mentorship_future PARTITION OF Mentorship_Partitioned DEFAULT;
    4853
     54-- Локални индекси
     55CREATE INDEX idx_m2024_student ON mentorship_2024(StudentID);
     56CREATE INDEX idx_m2025_student ON mentorship_2025(StudentID);
     57CREATE INDEX idx_m2026_student ON mentorship_2026(StudentID);
    4958
    50 CREATE TABLE mentorship_2026
    51 PARTITION OF Mentorship_Partitioned
    52 FOR VALUES FROM ('2026-01-01')
    53 TO ('2027-01-01');
     59-- Тест на внесување и верификација
     60INSERT INTO Mentorship_Partitioned (StudentID, MentorID, TopicSuggestionID, MentorshipTypeID, CreatedAt)
     61VALUES (1, 2, 3, 1, '2025-06-15');
    5462
     63SELECT tableoid::regclass, * FROM Mentorship_Partitioned;
     64}}}
    5565
     66=== 2. Партиционирање на табелата Message
    5667
    57 CREATE TABLE mentorship_future
    58 PARTITION OF Mentorship_Partitioned
    59 DEFAULT;
    60 
    61 
    62 
    63 CREATE INDEX idx_m2024_student
    64 ON mentorship_2024(StudentID);
    65 
    66 CREATE INDEX idx_m2025_student
    67 ON mentorship_2025(StudentID);
    68 
    69 CREATE INDEX idx_m2026_student
    70 ON mentorship_2026(StudentID);
    71 
    72 
    73 INSERT INTO Mentorship_Partitioned
     68{{{
     69#!sql
     70CREATE TABLE Message_Partitioned
    7471(
    75     StudentID,
    76     MentorID,
    77     TopicSuggestionID,
    78     MentorshipTypeID,
    79     CreatedAt
    80 )
    81 VALUES
    82 (
    83     1,
    84     2,
    85     3,
    86     1,
    87     '2025-06-15'
    88 );
    89 
    90 
    91 SELECT tableoid::regclass, *
    92 FROM Mentorship_Partitioned;  CREATE TABLE Message_Partitioned
    93 (
    94     ID BIGSERIAL,
    95     Content TEXT,
    96     isRead BOOLEAN,
     72    ID        BIGSERIAL,
     73    Content   TEXT,
     74    isRead    BOOLEAN,
    9775    Timestamp TIMESTAMP NOT NULL,
    98     ChatID BIGINT NOT NULL,
    99     UserID BIGINT NOT NULL,
     76    ChatID    BIGINT NOT NULL,
     77    UserID    BIGINT NOT NULL,
    10078
    10179    PRIMARY KEY (ID, Timestamp)
    … …  
    10381PARTITION BY RANGE (Timestamp);
    10482
     83-- Креирање на партиции по години
     84CREATE TABLE message_2024 PARTITION OF Message_Partitioned
     85    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
    10586
    106 CREATE TABLE message_2024
    107 PARTITION OF Message_Partitioned
    108 FOR VALUES FROM ('2024-01-01')
    109 TO ('2025-01-01');
     87CREATE TABLE message_2025 PARTITION OF Message_Partitioned
     88    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
    11089
    111 CREATE TABLE message_2025
    112 PARTITION OF Message_Partitioned
    113 FOR VALUES FROM ('2025-01-01')
    114 TO ('2026-01-01');
     90CREATE TABLE message_2026 PARTITION OF Message_Partitioned
     91    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
    11592
    116 CREATE TABLE message_2026
    117 PARTITION OF Message_Partitioned
    118 FOR VALUES FROM ('2026-01-01')
    119 TO ('2027-01-01');
     93-- Default партиција
     94CREATE TABLE message_future PARTITION OF Message_Partitioned DEFAULT;
    12095
    121 CREATE TABLE message_future
    122 PARTITION OF Message_Partitioned
    123 DEFAULT;
     96-- Локални индекси за ChatID
     97CREATE INDEX idx_msg2024_chat ON message_2024(ChatID);
     98CREATE INDEX idx_msg2025_chat ON message_2025(ChatID);
     99CREATE INDEX idx_msg2026_chat ON message_2026(ChatID);
    124100
     101-- Локални индекси за UserID
     102CREATE INDEX idx_msg2024_user ON message_2024(UserID);
     103CREATE INDEX idx_msg2025_user ON message_2025(UserID);
     104CREATE INDEX idx_msg2026_user ON message_2026(UserID);
    125105
    126 CREATE INDEX idx_msg2024_chat
    127 ON message_2024(ChatID);
     106-- Тест на поединечен внес
     107INSERT INTO Message_Partitioned (Content, isRead, Timestamp, ChatID, UserID)
     108VALUES ('Testing partitioning', false, '2025-04-10 12:00:00', 1, 1);
    128109
    129 CREATE INDEX idx_msg2025_chat
    130 ON message_2025(ChatID);
     110SELECT tableoid::regclass, * FROM Message_Partitioned;
     111}}}
    131112
    132 CREATE INDEX idx_msg2026_chat
    133 ON message_2026(ChatID);
     113=== 3. Масовна миграција и Анализа на перформанси
    134114
    135 CREATE INDEX idx_msg2024_user
    136 ON message_2024(UserID);
    137 
    138 CREATE INDEX idx_msg2025_user
    139 ON message_2025(UserID);
    140 
    141 CREATE INDEX idx_msg2026_user
    142 ON message_2026(UserID);
    143 
    144 
    145 
    146 
    147 INSERT INTO Message_Partitioned
    148 (
    149     Content,
    150     isRead,
    151     Timestamp,
    152     ChatID,
    153     UserID
    154 )
    155 VALUES
    156 (
    157     'Testing partitioning',
    158     false,
    159     '2025-04-10 12:00:00',
    160     1,
    161     1
    162 );
    163 
    164 
    165 SELECT tableoid::regclass, *
    166 FROM Message_Partitioned;
    167 
    168 
    169 
    170 
     115{{{
     116#!sql
     117-- Симулација на масовна миграција (100,000 записи)
    171118INSERT INTO Message_Partitioned (ID, Content, isRead, Timestamp, ChatID, UserID)
    172119SELECT ID, Content, isRead, Timestamp, ChatID, UserID
    … …  
    174121LIMIT 100000;
    175122
     123-- Освежување на статистиките на планот
    176124ANALYZE Message_Partitioned;
    177125
    178 
     126-- Верификација на Partition Pruning
    179127EXPLAIN ANALYZE
    180128SELECT *
    181129FROM Message_Partitioned
    182 WHERE Timestamp BETWEEN
    183 '2024-01-01'
    184 AND
    185 '2024-12-31';
     130WHERE Timestamp BETWEEN '2024-01-01' AND '2024-12-31';
    186131
    187 
     132-- Проверка на директни записи во специфична партиција
    188133SELECT ID, Timestamp, Content
    189134FROM message_2024