Напредна Тема

Декларативно партиционирање на податоци

Со цел да се обезбеди висока скалабилност на системот и оптимални перформанси при експоненцијален раст на податоците, во овој проект имплементиравме декларативно партиционирање на најоптоварените трансакциски табели: 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;