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

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

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

SQL Скрипти

1. Партиционирање на табелата Mentorship

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');

-- Default партиција
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;

2. Партиционирање на табелата Message

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');

-- Default партиција
CREATE TABLE message_future PARTITION OF Message_Partitioned DEFAULT;

-- Локални индекси за ChatID
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);

-- Локални индекси за UserID
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;

3. Масовна миграција и Анализа на перформанси

-- Симулација на масовна миграција (100,000 записи)
INSERT INTO Message_Partitioned (ID, Content, isRead, Timestamp, ChatID, UserID)
SELECT ID, Content, isRead, Timestamp, ChatID, UserID
FROM Message
LIMIT 100000; 

-- Освежување на статистиките на планот
ANALYZE Message_Partitioned;

-- Верификација на Partition Pruning
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;