Changes between Initial Version and Version 1 of DBA_partitioning

Timestamp:

06/07/25 11:08:21 (6 days ago)

Author:

216049

Comment:

--

DBA_partitioning

@@ +1 @@
+= Партиционирање
+
+Партиционирањето на бази на податоци во PostgreSQL е техника за поделба на големи табели на помали, полесно управливи делови наречени партиции. Секоја партиција содржи дел од податоците врз основа на одредени критериуми како што се опсег на датуми, хеш вредности или листа на вредности. Оваа техника значително ги подобрува перформансите на пребарувањето бидејќи PostgreSQL може да пребарува само во релевантните партиции наместо во целата табела. Партиционирањето исто така олеснува одржувањето на податоците, овозможувајќи побрзо бришење на стари записи и поефикасно архивирање. PostgreSQL поддржува неколку типови партиционирање вклучувајќи опсег (range), листа (list) и хеш (hash) партиционирање.
+
+Во PostgreSQL, клучот за партиционирање мора да биде дел од примарниот клуч или од било кој уникатен индекс (unique index). Ова е потребно бидејќи PostgreSQL мора да може да гарантира единственост низ сите партиции. Ако примарниот клуч не го содржи клучот за партиционирање, базата не може да провери дали вредноста е единствена без да ги провери сите партиции, со што се губи смислата на партиционирањето. На пример, ако партиционираме по датум, примарниот клуч мора да содржи и датум колона за да се обезбеди ефикасна проверка на уникатност.
+
+Партиционирањето е паметно за многу големи табели (обично милиони или милијарди записи) кои содржат временски податоци или други предвидливи шеми на пристап. Најчесто се користи за табели со логови, трансакции или историски податоци каде што барањата редовно филтрираат по датум, регион или категорија. Особено е корисно ако редовно се бришат или архивираат стари податоци, бидејќи може да се избришат цели партиции наместо да се брише запис по запис, и кога се извршуваат операции кои можат да се паралелизираат низ партиции.
+
+== Предности
+
+Главната предност е значително подобрување на перформансите - барањата стануваат побрзи бидејќи PostgreSQL пребарува само во релевантните партиции наместо во целата табела. Одржувањето на податоците станува поедноставно, особено за бришење на стари записи или архивирање на одредени периоди. Индексирањето е поефикасно бидејќи секоја партиција има помали индекси. Паралелното процесирање е подобрено кога се извршуваат операции на повеќе партиции истовремено.
+
+== Недостатоци
+
+Сложеноста на системот значително се зголемува, потребно е внимателно планирање и одржување. Пребарубањето кое не го вклучува клучот за партиционирање може да биде побавно бидејќи мора да се пребаруваат сите партиции. Прераспределувањето на податоци помеѓу партиции може да биде сложено и скапо.
+
+
+== Постапка за патиционирање
+
+Кога работиме со реални податоци и реален систем, табелата која ја партиционираме веќе постои и има милиони податоци во неа. Така што не би можеле само да креираме партиции за веќе постоечката табела. Еве една пример постапка за партиционирање на табела во активен продукциски ситстем:
+
+1. Таргет табелата треба да се преименува (table_name -> table_name_old)
+2. Индексите на таргет табелата треба да се преименуваат
+3. Направи нова партиционирана табела со старото име на таргет табелата
+4. Напрви ги потребните индекси на новата партиционирана таблеа
+5. Напрви партиција за идните податоци
+6. Закачи ја старата табела како партиција на новата табела
+7. Инкрементално распределувај ги стартите податоци по партиции (бидејќи се милиони записи ова е спор процес)
+
+{{{#!div
+{{{#!sql
+
+begin;
+
+-- чекор 1
+alter table large_table rename to large__table_legacy;
+
+-- чекор 2
+alter index large_table_idx rename to large_table_legacy_idx;
+...
+
+-- чекор 3
+create table large_table (
+  id   bigserial not null,
+  date date      not null,
+  ...
+) partition by range (date);
+
+-- чекор 4
+create index large_table_idx on large_table (some_column);
+...
+
+-- чекор 5
+create table large_table_2025_06
+partition of large_table
+for values from ('2025-06-01') to ('2025-07-01');
+
+-- чекор 6
+do
+$$
+declare
+  earliest date;
+  latest date;
+begin
+
+  select min(date) into earliest from large_table_legacy;
+  latest := '2025-06-01'::date;
+
+  -- мора да сме сигурни дека нема податоци што не го задоволуваат овој услов!!!
+  alter table large_table_legacy 
+  add constraint large_table_legacy_date
+  check (date >= earliest and date < latest)
+  not valid;
+
+  -- не би требало рачно да се менува ова но бидејќи сме сигурни дека е исполнет овој услов
+  -- можеме да го направиме ова, со ова избегнуваме full table scan што би требало да се 
+  -- случи при додавање на constraint (и би траело предолго да се валидира поради обемот 
+  -- на табелата)
+  update pg_constraint
+  set convalidated = true
+  where conname = 'large_table_legacy_date';
+
+  alter table large_table
+  attach partition large_table_legacy
+  for values from (earliest) to (latest);
+
+end;
+$$ language plpgsql;
+
+commit;
+
+-- чекор 7
+with ltl_rows as (
+    delete from large_table_legacy ltl
+    where (date >= '2024-01-01' and date < '2025-01-01')
+    returning ltl.*)
+insert
+into large_table
+select *
+from ltl_rows;
+
+}}}
+}}}
+
+== Пример од базата за полагање на испити
+
+==== test_instance:
+
+Оваа табела е партиционирана по колоната scheduled_for користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по година (2024, 2025, 2026...), или уште подобро по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира на годишно ниво.
+
+* Креирање на партициите
+{{{#!div
+{{{#!sql
+-- Академска година 2024/2025 (октомври 2024 - септември 2025)
+CREATE TABLE test_instance_2024_2025 PARTITION OF test_instance
+  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
+CREATE INDEX test_instance_test_collection_id_idx_2024_2025 ON test_instance_2024_2025 USING btree (test_collection_id);
+
+-- Академска година 2025/2026 (октомври 2025 - септември 2026)
+CREATE TABLE test_instance_2025_2026 PARTITION OF test_instance
+  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
+CREATE INDEX test_instance_test_collection_id_idx_2025_2026 ON test_instance_2025_2026 USING btree (test_collection_id);
+
+-- Академска година 2026/2027 (октомври 2026 - септември 2027)
+CREATE TABLE test_instance_2026_2027 PARTITION OF test_instance
+  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
+CREATE INDEX test_instance_test_collection_id_idx_2026_2027 ON test_instance_2026_2027 USING btree (test_collection_id);
+
+-- Default партиција за сите останати датуми
+CREATE TABLE test_instance_default PARTITION OF test_instance
+  DEFAULT;
+CREATE INDEX test_instance_test_collection_id_idx_default ON test_instance_default USING btree (test_collection_id);
+}}}
+}}}
+
+
+Главна партиционирана табела - test_instance
+||test_instance_id||title||scheduled_for||test_template_id||description||
+||1||Midterm Database||2024-11-15 10:00:00||101||Database concepts midterm||
+||2||Final Programming||2025-05-20 14:00:00||102||Final programming exam||
+||3||Spring Math Test||2025-11-10 09:00:00||103||Mathematics spring test||
+||4||Old Legacy Test||2023-03-15 11:00:00||104||Test from old system||
+||5||Future AI Exam||2026-12-05 13:00:00||105||Advanced AI examination||
+Партиција test_instance_2024_2025 (октомври 2024 - септември 2025)
+||test_instance_id||title||scheduled_for||test_template_id||description||
+||1||Midterm Database||2024-11-15 10:00:00||101||Database concepts midterm||
+||3||Spring Math Test||2025-11-10 09:00:00||103||Mathematics spring test||
+Партиција test_instance_2025_2026 (октомври 2025 - септември 2026)
+||test_instance_id||title||scheduled_for||test_template_id||description||
+||2||Final Programming||2025-05-20 14:00:00||102||Final programming exam||
+Партиција test_instance_2026_2027 (октомври 2026 - септември 2027)
+||test_instance_id||title||scheduled_for||test_template_id||description||
+||(празна - нема записи во овој период)||||||
+Партиција test_instance_default (сите останати датуми)
+||test_instance_id||title||scheduled_for||test_template_id||description||
+||4||Old Legacy Test||2023-03-15 11:00:00||104||Test from old system||
+||5||Future AI Exam||2026-12-05 13:00:00||105||Advanced AI examination||
+
+
+------------------------------
+
+==== activity_in_task
+
+Оваа табела е партиционирана по колоната when_occured користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира активноста на студентите на годишно ниво.
+
+* Креирање на партициите
+{{{#!div
+{{{#!sql
+-- Академска година 2024/2025 (октомври 2024 - септември 2025)
+CREATE TABLE activity_in_task_2024_2025 PARTITION OF activity_in_task
+  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
+CREATE INDEX activity_in_task_person_id_idx_2024_2025 ON activity_in_task_2024_2025 USING btree (person_id);
+CREATE INDEX activity_in_task_task_in_test_instance_id_idx_2024_2025 ON activity_in_task_2024_2025 USING btree (task_in_test_instance_id);
+
+-- Академска година 2025/2026 (октомври 2025 - септември 2026)
+CREATE TABLE activity_in_task_2025_2026 PARTITION OF activity_in_task
+  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
+CREATE INDEX activity_in_task_person_id_idx_2025_2026 ON activity_in_task_2025_2026 USING btree (person_id);
+CREATE INDEX activity_in_task_task_in_test_instance_id_idx_2025_2026 ON activity_in_task_2025_2026 USING btree (task_in_test_instance_id);
+
+-- Академска година 2026/2027 (октомври 2026 - септември 2027)
+CREATE TABLE activity_in_task_2026_2027 PARTITION OF activity_in_task
+  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
+CREATE INDEX activity_in_task_person_id_idx_2026_2027 ON activity_in_task_2026_2027 USING btree (person_id);
+CREATE INDEX activity_in_task_task_in_test_instance_id_idx_2026_2027 ON activity_in_task_2026_2027 USING btree (task_in_test_instance_id);
+
+-- Default партиција за сите останати датуми
+CREATE TABLE activity_in_task_default PARTITION OF activity_in_task
+  DEFAULT;
+CREATE INDEX activity_in_task_person_id_idx_default ON activity_in_task_default USING btree (person_id);
+CREATE INDEX activity_in_task_task_in_test_instance_id_idx_default ON activity_in_task_default USING btree (task_in_test_instance_id);
+}}}
+}}}
+
+Главна партиционирана табела - activity_in_task
+||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
+||1||{"action": "started_task"}||START||2024-11-15 10:05:00||501||1001||
+||2||{"code": "SELECT * FROM..."}||CODE_SUBMIT||2025-02-20 14:30:00||502||1002||
+||3||{"answer": "42"}||ANSWER_SUBMIT||2025-11-10 09:15:00||503||1003||
+||4||{"action": "page_view"}||VIEW||2023-03-15 11:20:00||504||1004||
+||5||{"timeout": true}||TIMEOUT||2026-12-05 13:45:00||505||1005||
+
+Партиција activity_in_task_2024_2025 (октомври 2024 - септември 2025)
+||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
+||1||{"action": "started_task"}||START||2024-11-15 10:05:00||501||1001||
+||2||{"code": "SELECT * FROM..."}||CODE_SUBMIT||2025-02-20 14:30:00||502||1002||
+
+Партиција activity_in_task_2025_2026 (октомври 2025 - септември 2026)
+||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
+||3||{"answer": "42"}||ANSWER_SUBMIT||2025-11-10 09:15:00||503||1003||
+
+Партиција activity_in_task_2026_2027 (октомври 2026 - септември 2027)
+||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
+||(празна - нема записи во овој период)||||||
+
+Партиција activity_in_task_default (сите останати датуми)
+||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
+||4||{"action": "page_view"}||VIEW||2023-03-15 11:20:00||504||1004||
+||5||{"timeout": true}||TIMEOUT||2026-12-05 13:45:00||505||1005||
+
+----
+
+==== student_submit_solution
+
+Оваа табела е партиционирана по колоната submitted_on користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира решенијата на студентите на годишно ниво.
+
+* Креирање на партициите
+{{{#!div
+{{{#!sql
+-- Академска година 2024/2025 (октомври 2024 - септември 2025)
+CREATE TABLE student_submit_solution_2024_2025 PARTITION OF student_submit_solution
+  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
+CREATE INDEX student_submit_solution_student_started_test_id_idx_2024_2025 ON student_submit_solution_2024_2025 USING btree (student_started_test_id);
+CREATE INDEX student_submit_solution_task_in_test_instance_id_idx_2024_2025 ON student_submit_solution_2024_2025 USING btree (task_in_test_instance_id);
+
+-- Академска година 2025/2026 (октомври 2025 - септември 2026)
+CREATE TABLE student_submit_solution_2025_2026 PARTITION OF student_submit_solution
+  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
+CREATE INDEX student_submit_solution_student_started_test_id_idx_2025_2026 ON student_submit_solution_2025_2026 USING btree (student_started_test_id);
+CREATE INDEX student_submit_solution_task_in_test_instance_id_idx_2025_2026 ON student_submit_solution_2025_2026 USING btree (task_in_test_instance_id);
+
+-- Академска година 2026/2027 (октомври 2026 - септември 2027)
+CREATE TABLE student_submit_solution_2026_2027 PARTITION OF student_submit_solution
+  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
+CREATE INDEX student_submit_solution_student_started_test_id_idx_2026_2027 ON student_submit_solution_2026_2027 USING btree (student_started_test_id);
+CREATE INDEX student_submit_solution_task_in_test_instance_id_idx_2026_2027 ON student_submit_solution_2026_2027 USING btree (task_in_test_instance_id);
+
+-- Default партиција за сите останати датуми
+CREATE TABLE student_submit_solution_default PARTITION OF student_submit_solution
+  DEFAULT;
+CREATE INDEX student_submit_solution_student_started_test_id_idx_default ON student_submit_solution_default USING btree (student_started_test_id);
+CREATE INDEX student_submit_solution_task_in_test_instance_id_idx_default ON student_submit_solution_default USING btree (task_in_test_instance_id);
+}}}
+}}}
+
+Главна партиционирана табела - student_submit_solution
+||student_submit_solution_id||student_started_test_id||task_in_test_instance_id||submission||submitted_on||evaluation_simple||evaluation_complex||evaluation_exam||not_for_evaluation||ip_address||client_info||
+||1||201||1001||SELECT * FROM users;||2024-11-15 10:30:00||true||null||false||false||192.168.1.100||Chrome/119.0||
+||2||202||1002||def fibonacci(n): ...||2025-02-20 15:45:00||null||true||true||false||10.0.0.50||Firefox/121.0||
+||3||203||1003||import math; print(42)||2025-11-10 10:20:00||true||false||false||false||172.16.0.25||Safari/17.1||
+||4||204||1004||<old solution code>||2023-05-15 12:00:00||true||null||null||true||192.168.0.1||IE/11.0||
+||5||205||1005||AI generated solution||2026-12-05 14:15:00||null||null||true||false||203.0.113.42||Edge/130.0||
+
+Партиција student_submit_solution_2024_2025 (октомври 2024 - септември 2025)
+||student_submit_solution_id||student_started_test_id||task_in_test_instance_id||submission||submitted_on||evaluation_simple||evaluation_complex||evaluation_exam||not_for_evaluation||ip_address||client_info||
+||1||201||1001||SELECT * FROM users;||2024-11-15 10:30:00||true||null||false||false||192.168.1.100||Chrome/119.0||
+||2||202||1002||def fibonacci(n): ...||2025-02-20 15:45:00||null||true||true||false||10.0.0.50||Firefox/121.0||
+
+Партиција student_submit_solution_2025_2026 (октомври 2025 - септември 2026)
+||student_submit_solution_id||student_started_test_id||task_in_test_instance_id||submission||submitted_on||evaluation_simple||evaluation_complex||evaluation_exam||not_for_evaluation||ip_address||client_info||
+||3||203||1003||import math; print(42)||2025-11-10 10:20:00||true||false||false||false||172.16.0.25||Safari/17.1||
+
+Партиција student_submit_solution_2026_2027 (октомври 2026 - септември 2027)
+||student_submit_solution_id||student_started_test_id||task_in_test_instance_id||submission||submitted_on||evaluation_simple||evaluation_complex||evaluation_exam||not_for_evaluation||ip_address||client_info||
+||(празна - нема записи во овој период)||||||||||||
+
+Партиција student_submit_solution_default (сите останати датуми)
+||student_submit_solution_id||student_started_test_id||task_in_test_instance_id||submission||submitted_on||evaluation_simple||evaluation_complex||evaluation_exam||not_for_evaluation||ip_address||client_info||
+||4||204||1004||<old solution code>||2023-05-15 12:00:00||true||null||null||true||192.168.0.1||IE/11.0||
+||5||205||1005||AI generated solution||2026-12-05 14:15:00||null||null||true||false||203.0.113.42||Edge/130.0||