Changes between Initial Version and Version 1 of DBA_partitioning


Ignore:
Timestamp:
06/07/25 11:08:21 (3 days ago)
Author:
216049
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • DBA_partitioning

    v1 v1  
     1= Партиционирање
     2
     3Партиционирањето на бази на податоци во PostgreSQL е техника за поделба на големи табели на помали, полесно управливи делови наречени партиции. Секоја партиција содржи дел од податоците врз основа на одредени критериуми како што се опсег на датуми, хеш вредности или листа на вредности. Оваа техника значително ги подобрува перформансите на пребарувањето бидејќи PostgreSQL може да пребарува само во релевантните партиции наместо во целата табела. Партиционирањето исто така олеснува одржувањето на податоците, овозможувајќи побрзо бришење на стари записи и поефикасно архивирање. PostgreSQL поддржува неколку типови партиционирање вклучувајќи опсег (range), листа (list) и хеш (hash) партиционирање.
     4
     5Во PostgreSQL, клучот за партиционирање мора да биде дел од примарниот клуч или од било кој уникатен индекс (unique index). Ова е потребно бидејќи PostgreSQL мора да може да гарантира единственост низ сите партиции. Ако примарниот клуч не го содржи клучот за партиционирање, базата не може да провери дали вредноста е единствена без да ги провери сите партиции, со што се губи смислата на партиционирањето. На пример, ако партиционираме по датум, примарниот клуч мора да содржи и датум колона за да се обезбеди ефикасна проверка на уникатност.
     6
     7Партиционирањето е паметно за многу големи табели (обично милиони или милијарди записи) кои содржат временски податоци или други предвидливи шеми на пристап. Најчесто се користи за табели со логови, трансакции или историски податоци каде што барањата редовно филтрираат по датум, регион или категорија. Особено е корисно ако редовно се бришат или архивираат стари податоци, бидејќи може да се избришат цели партиции наместо да се брише запис по запис, и кога се извршуваат операции кои можат да се паралелизираат низ партиции.
     8
     9== Предности
     10
     11Главната предност е значително подобрување на перформансите - барањата стануваат побрзи бидејќи PostgreSQL пребарува само во релевантните партиции наместо во целата табела. Одржувањето на податоците станува поедноставно, особено за бришење на стари записи или архивирање на одредени периоди. Индексирањето е поефикасно бидејќи секоја партиција има помали индекси. Паралелното процесирање е подобрено кога се извршуваат операции на повеќе партиции истовремено.
     12
     13== Недостатоци
     14
     15Сложеноста на системот значително се зголемува, потребно е внимателно планирање и одржување. Пребарубањето кое не го вклучува клучот за партиционирање може да биде побавно бидејќи мора да се пребаруваат сите партиции. Прераспределувањето на податоци помеѓу партиции може да биде сложено и скапо.
     16
     17
     18== Постапка за патиционирање
     19
     20Кога работиме со реални податоци и реален систем, табелата која ја партиционираме веќе постои и има милиони податоци во неа. Така што не би можеле само да креираме партиции за веќе постоечката табела. Еве една пример постапка за партиционирање на табела во активен продукциски ситстем:
     21
     221. Таргет табелата треба да се преименува (table_name -> table_name_old)
     232. Индексите на таргет табелата треба да се преименуваат
     243. Направи нова партиционирана табела со старото име на таргет табелата
     254. Напрви ги потребните индекси на новата партиционирана таблеа
     265. Напрви партиција за идните податоци
     276. Закачи ја старата табела како партиција на новата табела
     287. Инкрементално распределувај ги стартите податоци по партиции (бидејќи се милиони записи ова е спор процес)
     29
     30{{{#!div
     31{{{#!sql
     32
     33begin;
     34
     35-- чекор 1
     36alter table large_table rename to large__table_legacy;
     37
     38-- чекор 2
     39alter index large_table_idx rename to large_table_legacy_idx;
     40...
     41
     42-- чекор 3
     43create table large_table (
     44  id   bigserial not null,
     45  date date      not null,
     46  ...
     47) partition by range (date);
     48
     49-- чекор 4
     50create index large_table_idx on large_table (some_column);
     51...
     52
     53-- чекор 5
     54create table large_table_2025_06
     55partition of large_table
     56for values from ('2025-06-01') to ('2025-07-01');
     57
     58-- чекор 6
     59do
     60$$
     61declare
     62  earliest date;
     63  latest date;
     64begin
     65
     66  select min(date) into earliest from large_table_legacy;
     67  latest := '2025-06-01'::date;
     68
     69  -- мора да сме сигурни дека нема податоци што не го задоволуваат овој услов!!!
     70  alter table large_table_legacy
     71  add constraint large_table_legacy_date
     72  check (date >= earliest and date < latest)
     73  not valid;
     74
     75  -- не би требало рачно да се менува ова но бидејќи сме сигурни дека е исполнет овој услов
     76  -- можеме да го направиме ова, со ова избегнуваме full table scan што би требало да се
     77  -- случи при додавање на constraint (и би траело предолго да се валидира поради обемот
     78  -- на табелата)
     79  update pg_constraint
     80  set convalidated = true
     81  where conname = 'large_table_legacy_date';
     82
     83  alter table large_table
     84  attach partition large_table_legacy
     85  for values from (earliest) to (latest);
     86
     87end;
     88$$ language plpgsql;
     89
     90commit;
     91
     92-- чекор 7
     93with ltl_rows as (
     94    delete from large_table_legacy ltl
     95    where (date >= '2024-01-01' and date < '2025-01-01')
     96    returning ltl.*)
     97insert
     98into large_table
     99select *
     100from ltl_rows;
     101
     102}}}
     103}}}
     104
     105== Пример од базата за полагање на испити
     106
     107==== test_instance:
     108
     109Оваа табела е партиционирана по колоната scheduled_for користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по година (2024, 2025, 2026...), или уште подобро по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира на годишно ниво.
     110
     111* Креирање на партициите
     112{{{#!div
     113{{{#!sql
     114-- Академска година 2024/2025 (октомври 2024 - септември 2025)
     115CREATE TABLE test_instance_2024_2025 PARTITION OF test_instance
     116  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
     117CREATE INDEX test_instance_test_collection_id_idx_2024_2025 ON test_instance_2024_2025 USING btree (test_collection_id);
     118
     119-- Академска година 2025/2026 (октомври 2025 - септември 2026)
     120CREATE TABLE test_instance_2025_2026 PARTITION OF test_instance
     121  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
     122CREATE INDEX test_instance_test_collection_id_idx_2025_2026 ON test_instance_2025_2026 USING btree (test_collection_id);
     123
     124-- Академска година 2026/2027 (октомври 2026 - септември 2027)
     125CREATE TABLE test_instance_2026_2027 PARTITION OF test_instance
     126  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
     127CREATE INDEX test_instance_test_collection_id_idx_2026_2027 ON test_instance_2026_2027 USING btree (test_collection_id);
     128
     129-- Default партиција за сите останати датуми
     130CREATE TABLE test_instance_default PARTITION OF test_instance
     131  DEFAULT;
     132CREATE INDEX test_instance_test_collection_id_idx_default ON test_instance_default USING btree (test_collection_id);
     133}}}
     134}}}
     135
     136
     137Главна партиционирана табела - test_instance
     138||test_instance_id||title||scheduled_for||test_template_id||description||
     139||1||Midterm Database||2024-11-15 10:00:00||101||Database concepts midterm||
     140||2||Final Programming||2025-05-20 14:00:00||102||Final programming exam||
     141||3||Spring Math Test||2025-11-10 09:00:00||103||Mathematics spring test||
     142||4||Old Legacy Test||2023-03-15 11:00:00||104||Test from old system||
     143||5||Future AI Exam||2026-12-05 13:00:00||105||Advanced AI examination||
     144Партиција test_instance_2024_2025 (октомври 2024 - септември 2025)
     145||test_instance_id||title||scheduled_for||test_template_id||description||
     146||1||Midterm Database||2024-11-15 10:00:00||101||Database concepts midterm||
     147||3||Spring Math Test||2025-11-10 09:00:00||103||Mathematics spring test||
     148Партиција test_instance_2025_2026 (октомври 2025 - септември 2026)
     149||test_instance_id||title||scheduled_for||test_template_id||description||
     150||2||Final Programming||2025-05-20 14:00:00||102||Final programming exam||
     151Партиција test_instance_2026_2027 (октомври 2026 - септември 2027)
     152||test_instance_id||title||scheduled_for||test_template_id||description||
     153||(празна - нема записи во овој период)||||||
     154Партиција test_instance_default (сите останати датуми)
     155||test_instance_id||title||scheduled_for||test_template_id||description||
     156||4||Old Legacy Test||2023-03-15 11:00:00||104||Test from old system||
     157||5||Future AI Exam||2026-12-05 13:00:00||105||Advanced AI examination||
     158
     159
     160------------------------------
     161
     162==== activity_in_task
     163
     164Оваа табела е партиционирана по колоната when_occured користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира активноста на студентите на годишно ниво.
     165
     166* Креирање на партициите
     167{{{#!div
     168{{{#!sql
     169-- Академска година 2024/2025 (октомври 2024 - септември 2025)
     170CREATE TABLE activity_in_task_2024_2025 PARTITION OF activity_in_task
     171  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
     172CREATE INDEX activity_in_task_person_id_idx_2024_2025 ON activity_in_task_2024_2025 USING btree (person_id);
     173CREATE 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);
     174
     175-- Академска година 2025/2026 (октомври 2025 - септември 2026)
     176CREATE TABLE activity_in_task_2025_2026 PARTITION OF activity_in_task
     177  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
     178CREATE INDEX activity_in_task_person_id_idx_2025_2026 ON activity_in_task_2025_2026 USING btree (person_id);
     179CREATE 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);
     180
     181-- Академска година 2026/2027 (октомври 2026 - септември 2027)
     182CREATE TABLE activity_in_task_2026_2027 PARTITION OF activity_in_task
     183  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
     184CREATE INDEX activity_in_task_person_id_idx_2026_2027 ON activity_in_task_2026_2027 USING btree (person_id);
     185CREATE 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);
     186
     187-- Default партиција за сите останати датуми
     188CREATE TABLE activity_in_task_default PARTITION OF activity_in_task
     189  DEFAULT;
     190CREATE INDEX activity_in_task_person_id_idx_default ON activity_in_task_default USING btree (person_id);
     191CREATE INDEX activity_in_task_task_in_test_instance_id_idx_default ON activity_in_task_default USING btree (task_in_test_instance_id);
     192}}}
     193}}}
     194
     195Главна партиционирана табела - activity_in_task
     196||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
     197||1||{"action": "started_task"}||START||2024-11-15 10:05:00||501||1001||
     198||2||{"code": "SELECT * FROM..."}||CODE_SUBMIT||2025-02-20 14:30:00||502||1002||
     199||3||{"answer": "42"}||ANSWER_SUBMIT||2025-11-10 09:15:00||503||1003||
     200||4||{"action": "page_view"}||VIEW||2023-03-15 11:20:00||504||1004||
     201||5||{"timeout": true}||TIMEOUT||2026-12-05 13:45:00||505||1005||
     202
     203Партиција activity_in_task_2024_2025 (октомври 2024 - септември 2025)
     204||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
     205||1||{"action": "started_task"}||START||2024-11-15 10:05:00||501||1001||
     206||2||{"code": "SELECT * FROM..."}||CODE_SUBMIT||2025-02-20 14:30:00||502||1002||
     207
     208Партиција activity_in_task_2025_2026 (октомври 2025 - септември 2026)
     209||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
     210||3||{"answer": "42"}||ANSWER_SUBMIT||2025-11-10 09:15:00||503||1003||
     211
     212Партиција activity_in_task_2026_2027 (октомври 2026 - септември 2027)
     213||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
     214||(празна - нема записи во овој период)||||||
     215
     216Партиција activity_in_task_default (сите останати датуми)
     217||activity_in_task_id||payload||type||when_occured||person_id||task_in_test_instance_id||
     218||4||{"action": "page_view"}||VIEW||2023-03-15 11:20:00||504||1004||
     219||5||{"timeout": true}||TIMEOUT||2026-12-05 13:45:00||505||1005||
     220
     221----
     222
     223==== student_submit_solution
     224
     225Оваа табела е партиционирана по колоната submitted_on користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела би било партиционирање по академска година (1.10.2024 - 30.09.2025, 1.10.2025 - 30.09.2026...). На овој начин може лесно и брзо да се пребарува и архивира решенијата на студентите на годишно ниво.
     226
     227* Креирање на партициите
     228{{{#!div
     229{{{#!sql
     230-- Академска година 2024/2025 (октомври 2024 - септември 2025)
     231CREATE TABLE student_submit_solution_2024_2025 PARTITION OF student_submit_solution
     232  FOR VALUES FROM ('2024-10-01') TO ('2025-10-01');
     233CREATE INDEX student_submit_solution_student_started_test_id_idx_2024_2025 ON student_submit_solution_2024_2025 USING btree (student_started_test_id);
     234CREATE 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);
     235
     236-- Академска година 2025/2026 (октомври 2025 - септември 2026)
     237CREATE TABLE student_submit_solution_2025_2026 PARTITION OF student_submit_solution
     238  FOR VALUES FROM ('2025-10-01') TO ('2026-10-01');
     239CREATE INDEX student_submit_solution_student_started_test_id_idx_2025_2026 ON student_submit_solution_2025_2026 USING btree (student_started_test_id);
     240CREATE 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);
     241
     242-- Академска година 2026/2027 (октомври 2026 - септември 2027)
     243CREATE TABLE student_submit_solution_2026_2027 PARTITION OF student_submit_solution
     244  FOR VALUES FROM ('2026-10-01') TO ('2027-10-01');
     245CREATE INDEX student_submit_solution_student_started_test_id_idx_2026_2027 ON student_submit_solution_2026_2027 USING btree (student_started_test_id);
     246CREATE 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);
     247
     248-- Default партиција за сите останати датуми
     249CREATE TABLE student_submit_solution_default PARTITION OF student_submit_solution
     250  DEFAULT;
     251CREATE INDEX student_submit_solution_student_started_test_id_idx_default ON student_submit_solution_default USING btree (student_started_test_id);
     252CREATE INDEX student_submit_solution_task_in_test_instance_id_idx_default ON student_submit_solution_default USING btree (task_in_test_instance_id);
     253}}}
     254}}}
     255
     256Главна партиционирана табела - student_submit_solution
     257||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||
     258||1||201||1001||SELECT * FROM users;||2024-11-15 10:30:00||true||null||false||false||192.168.1.100||Chrome/119.0||
     259||2||202||1002||def fibonacci(n): ...||2025-02-20 15:45:00||null||true||true||false||10.0.0.50||Firefox/121.0||
     260||3||203||1003||import math; print(42)||2025-11-10 10:20:00||true||false||false||false||172.16.0.25||Safari/17.1||
     261||4||204||1004||<old solution code>||2023-05-15 12:00:00||true||null||null||true||192.168.0.1||IE/11.0||
     262||5||205||1005||AI generated solution||2026-12-05 14:15:00||null||null||true||false||203.0.113.42||Edge/130.0||
     263
     264Партиција student_submit_solution_2024_2025 (октомври 2024 - септември 2025)
     265||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||
     266||1||201||1001||SELECT * FROM users;||2024-11-15 10:30:00||true||null||false||false||192.168.1.100||Chrome/119.0||
     267||2||202||1002||def fibonacci(n): ...||2025-02-20 15:45:00||null||true||true||false||10.0.0.50||Firefox/121.0||
     268
     269Партиција student_submit_solution_2025_2026 (октомври 2025 - септември 2026)
     270||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||
     271||3||203||1003||import math; print(42)||2025-11-10 10:20:00||true||false||false||false||172.16.0.25||Safari/17.1||
     272
     273Партиција student_submit_solution_2026_2027 (октомври 2026 - септември 2027)
     274||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||
     275||(празна - нема записи во овој период)||||||||||||
     276
     277Партиција student_submit_solution_default (сите останати датуми)
     278||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||
     279||4||204||1004||<old solution code>||2023-05-15 12:00:00||true||null||null||true||192.168.0.1||IE/11.0||
     280||5||205||1005||AI generated solution||2026-12-05 14:15:00||null||null||true||false||203.0.113.42||Edge/130.0||