Партиционирање
Партиционирањето на бази на податоци во PostgreSQL е техника за поделба на големи табели на помали, полесно управливи делови наречени партиции. Секоја партиција содржи дел од податоците врз основа на одредени критериуми како што се опсег на датуми, хеш вредности или листа на вредности. Оваа техника значително ги подобрува перформансите на пребарувањето бидејќи PostgreSQL може да пребарува само во релевантните партиции наместо во целата табела. Партиционирањето исто така олеснува одржувањето на податоците, овозможувајќи побрзо бришење на стари записи и поефикасно архивирање. PostgreSQL поддржува неколку типови партиционирање вклучувајќи опсег (range), листа (list) и хеш (hash) партиционирање.
Во PostgreSQL, клучот за партиционирање мора да биде дел од примарниот клуч или од било кој уникатен индекс (unique index). Ова е потребно бидејќи PostgreSQL мора да може да гарантира единственост низ сите партиции. Ако примарниот клуч не го содржи клучот за партиционирање, базата не може да провери дали вредноста е единствена без да ги провери сите партиции, со што се губи смислата на партиционирањето. На пример, ако партиционираме по датум, примарниот клуч мора да содржи и датум колона за да се обезбеди ефикасна проверка на уникатност.
Партиционирањето е паметно за многу големи табели (обично милиони или милијарди записи) кои содржат временски податоци или други предвидливи шеми на пристап. Најчесто се користи за табели со логови, трансакции или историски податоци каде што барањата редовно филтрираат по датум, регион или категорија. Особено е корисно ако редовно се бришат или архивираат стари податоци, бидејќи може да се избришат цели партиции наместо да се брише запис по запис, и кога се извршуваат операции кои можат да се паралелизираат низ партиции.
Предности
Главната предност е значително подобрување на перформансите - барањата стануваат побрзи бидејќи PostgreSQL пребарува само во релевантните партиции наместо во целата табела. Одржувањето на податоците станува поедноставно, особено за бришење на стари записи или архивирање на одредени периоди. Индексирањето е поефикасно бидејќи секоја партиција има помали индекси. Паралелното процесирање е подобрено кога се извршуваат операции на повеќе партиции истовремено.
Недостатоци
Сложеноста на системот значително се зголемува, потребно е внимателно планирање и одржување. Пребарубањето кое не го вклучува клучот за партиционирање може да биде побавно бидејќи мора да се пребаруваат сите партиции. Прераспределувањето на податоци помеѓу партиции може да биде сложено и скапо.
Постапка за патиционирање
Кога работиме со реални податоци и реален систем, табелата која ја партиционираме веќе постои и има милиони податоци во неа. Така што не би можеле само да креираме партиции за веќе постоечката табела. Еве една пример постапка за партиционирање на табела во активен продукциски ситстем:
- Таргет табелата треба да се преименува (table_name -> table_name_old)
- Индексите на таргет табелата треба да се преименуваат
- Направи нова партиционирана табела со старото име на таргет табелата
- Напрви ги потребните индекси на новата партиционирана таблеа
- Напрви партиција за идните податоци
- Закачи ја старата табела како партиција на новата табела
- Инкрементално распределувај ги стартите податоци по партиции (бидејќи се милиони записи ова е спор процес)
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...). На овој начин може лесно и брзо да се пребарува и архивира на годишно ниво.
- Креирање на партициите
-- Академска година 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...). На овој начин може лесно и брзо да се пребарува и архивира активноста на студентите на годишно ниво.
- Креирање на партициите
-- Академска година 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...). На овој начин може лесно и брзо да се пребарува и архивира решенијата на студентите на годишно ниво.
- Креирање на партициите
-- Академска година 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 |
Тест
Во табелата test_instance се додаваат 5.000.000 тест записи со вредност за scheduled_for во ранг од 2022 до 2025 година. Истите квериња се извршени на партиционирана табела и на непартиционирана табела.
- Прво квери:
explain (analyse, buffers) select * from test_instance where scheduled_for between '2024-01-01' and '2024-03-01';
- Второ квери
explain (analyse, buffers) select * from test_instance where scheduled_for between '2024-01-01' and '2024-03-01' and scheduled_until between '2024-01-01' and '2024-10-01';
explain (analyse, buffers) select * from test_instance where scheduled_for between '2024-01-01' and '2025-01-01';
Без партиционирање
Со партиционирање
Seq Scan on test_instance_2023_2024 test_instance (cost=0.00..35022.53 rows=168312 width=112) (actual time=0.075..313.242 rows=167605 loops=1) Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2024-03-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 855497 Buffers: shared hit=3186 read=16490 Planning Time: 0.103 ms Execution Time: 533.276 ms
Gather (cost=1000.00..31034.15 rows=18323 width=112) (actual time=9.510..73.809 rows=18628 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=3218 read=16458
-> Parallel Seq Scan on test_instance_2023_2024 test_instance (cost=0.00..28201.85 rows=7635 width=112) (actual time=7.168..45.342 rows=6209 loops=3)
Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2024-03-01 00:00:00'::timestamp without time zone) AND (scheduled_until >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_until <= '2024-10-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 334825
Buffers: shared hit=3218 read=16458
Planning Time: 0.111 ms
Execution Time: 99.826 ms
Append (cost=0.00..63966.17 rows=1024027 width=112) (actual time=0.042..4192.731 rows=1023818 loops=1)
Buffers: shared hit=5077 read=27983
-> Seq Scan on test_instance_2023_2024 test_instance_1 (cost=0.00..35022.53 rows=766644 width=112) (actual time=0.039..1100.165 rows=766431 loops=1)
Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2025-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 256671
Buffers: shared hit=3154 read=16522
-> Seq Scan on test_instance_2024_2025 test_instance_2 (cost=0.00..23823.50 rows=257383 width=112) (actual time=0.019..430.753 rows=257387 loops=1)
Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2025-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 438580
Buffers: shared hit=1923 read=11461
Planning Time: 0.144 ms
Execution Time: 5525.093 ms
