Query performance can be improved dramatically in certain situations
Отже, подивимось, як нам може допомогти партиціонування.
Коли і чому варто партиціонувати?
Коли
Дві основні умови, за яких доцільно робити партиціонування:
- таблиця дуже велика і робота з індексами не ефективна;
- дані основної (батьківської) таблиці можна розбити в партиції (дочірні таблиці) на групи для запитів.
Чи достатньо велика ваша таблиця для партиціонування, визначається індивідуально. Це залежить від потужностей і конфігурації машини та бази, частоти запису й інших факторів. Основний простий критерій — коректні найоптимальніші індекси не спрацьовують достатньо швидко для вашої системи для оптимізованого запиту, а розмір таблиці — мінімум декілька (10+) Gb. В іншому випадку треба передивитись оптимізацію запитів і вибір полів для індексування.
Чому
Якщо дуже спрощено: тому що читати з однієї (навіть декількох) маленьких таблиць швидше, ніж з однієї великої. Дослівно з документації:
«Ефективність запитів може бути значно покращена, коли більшість записів з великим доступом розташовані в одному розділі або в невеликій кількості розділів. Партиціонування замінює провідні стовпці індексів, зменшуючи розмір індексу та збільшуючи вірогідність того, що часто використовувані частини індексів будуть в кеші.
Коли запити або оновлення отримують доступ до великого відсотка одного розділу, продуктивність може бути покращена: через сканування цього розділу, замість читання з файлу індексу та випадкового доступу до записів, розподілених довільно по таблиці.
Що відбувається при UPDATE?
Під час UPDATE навіть однієї колонки весь запис помічається як «мертвий» і записується новий. Перевіримо:
-- create table
postgres=# CREATE TABLE test (x int, y text);
CREATE TABLE
-- insert rows
postgres=# INSERT INTO test SELECT generate_series(0,50000000);
INSERT 0 50000001
-- create index
postgres=# CREATE INDEX ON test (x);
CREATE INDEX
Розмір таблиці й індексу:
table_name | total | index | table
------------+---------+---------+---------
test | 2800 MB | 1071 MB | 1729 MB
Оновимо записи, не змінюючи значення проіндексованого поля:
postgres=# UPDATE test set y = y;
UPDATE 50000001
Розмір таблиці й індексу, не зміненого після update:
table_name | total | index | table
------------+---------+---------+---------
test | 8791 MB | 4025 MB | 4766 MB
До чого тут партиціонування? І таблиця, і файли індексів зростають не лише з INSERT, а і з UPDATE. Якщо ваша система робить регулярні оновлення записів навіть не проіндексованих полів — виконуйте періодичне перебудування індексів, врахуйте розмір таблиці. Якщо вона вже велика, то варто подумати про партиціонування.
Дочірні таблиці й дочірні індекси будуть зростати з оновленнями, але на маленькі обсяги це фактично ніяк не вплине.
Hash Partition in PostgreSQL 11+
Postgres, починаючи з 11 версії, підтримує hash partition. Тепер можна легко і автоматично розбити основну таблицю на декілька дочірніх.
Ось приклад hash-партиціонування.
Чому легко використовувати hash partitioning в postgres v11+?
- автоматичний розподіл записів;
- автоматичні індекси на дочірні таблиці.
Приклад партиціонування існуючої таблиці postgres з hash partitioning:
-- hash partition
CREATE TABLE part_hash_test (x int, y text) PARTITION BY hash (x);
-- create child partitions
CREATE TABLE part_hash_test_0 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE part_hash_test_1 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE part_hash_test_2 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE part_hash_test_3 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);
INSERT INTO part_hash_test SELECT generate_series(0,50000);
INSERT 0 50001
SELECT count(1),tableoid::regclass FROM part_hash_test GROUP by 2 order by 2 ;
count | tableoid
-------+------------------
12537 | part_hash_test_0
12473 | part_hash_test_1
12509 | part_hash_test_2
12482 | part_hash_test_3
Створення індексів на партиціонованій таблиці:
postgres# CREATE INDEX ON part_hash_test (x);
CREATE INDEX
\\d part_hash_test
Table "public.part_hash_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
y | text | | |
Partition key: HASH (x)
Indexes:
"part_hash_test_x_idx" btree (x)
Number of partitions: 4 (Use \\d+ to list them.)
Перевіримо, що успішно побудовано у дочірніх таблиць:
Table "public.part_hash_test_0"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
y | text | | |
Partition of: part_hash_test FOR VALUES WITH (modulus 4, remainder 0)
Indexes:
"part_hash_test_0_x_idx" btree (x)
# \\d part_hash_test_3
Table "public.part_hash_test_3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
y | text | | |
Partition of: part_hash_test FOR VALUES WITH (modulus 4, remainder 3)
Indexes:
"part_hash_test_3_x_idx" btree (x)
Подивимось, як же можна «розігнати» повільні запити на проіндексованій таблиці.
Розмір таблиці: 12Gb.
SELECT column_name,data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'items';
column_name | data_type
------------------------+-----------------------------
id | uuid
employee_id | uuid
person_id | uuid
start_date | date
end_date | date
status | character varying
created_by | uuid
updated_by | uuid
is_active | boolean
scope | character varying
division_id | uuid
inserted_at | timestamp without time zone
updated_at | timestamp without time zone
item_request_id | uuid
overlimit | boolean
item_number | character varying
Partition key: HASH (division_id)
Indexes:
"items_employee_id_index" btree (employee_id)
"items_division_id_index" btree (division_id)
...List of indexes ...
Partitions: part_hash_items_0 FOR VALUES WITH (modulus 32, remainder 0),
part_hash_items_1 FOR VALUES WITH (modulus 32, remainder 1),
part_hash_items_10 FOR VALUES WITH (modulus 32, remainder 10),
part_hash_items_11 FOR VALUES WITH (modulus 32, remainder 11),
.......TOTAL 32 partitions........
План виконання запитів на партиціонованій таблиці
Найцікавіша частина, чому ми партиціонуємо нашу 12-гігову таблицю.
Запит:
explain analyze select * from items where division_id = '....' and status = 'active';
План:
-> Bitmap Heap Scan on part_hash_items_31 (cost=42.23..3670.14 rows=18 width=2774) (never executed)
Recheck Cond: (division_id = $0)
Filter: ((status)::text = 'active'::text)
-> Bitmap Index Scan on part_hash_items_31_division_id_idx (cost=0.00..42.22 rows=3520 width=0) (never executed)
Index Cond: (division_id = $0)
-> Bitmap Heap Scan on part_hash_items_4 (cost=284.24..18289.60 rows=20908 width=401) (actual time=18.322..612.856 rows=15431 loops=1)
Recheck Cond: (division_id = $0)
Filter: ((status)::text = 'active'::text)
Rows Removed by Filter: 4413
Heap Blocks: exact=14789
-> Bitmap Index Scan on part_hash_items_4_division_id_idx (cost=0.00..279.01 rows=23652 width=0) (actual time=15.167..15.167 rows=19844 loops=1)
Index Cond: (division_id = $0)
-> Bitmap Heap Scan on part_hash_items_7 (cost=29.40..2499.34 rows=12 width=2774) (never executed)
Recheck Cond: (division_id = $0)
Filter: ((status)::text = 'active'::text)
-> Bitmap Index Scan on part_hash_items_7_division_id_idx (cost=0.00..29.39 rows=2396 width=0) (never executed)
Index Cond: (division_id = $0)
Звертаємо увагу на (never executed).
Ми шукали лише по відповідній дочірній таблиці і досягнули мети. Вимірюємо середню швидкість виконання запитів з pg_bench
Unpartitioned, with index:
latency average = 5220.797 ms
tps = 1.915597 (excluding connections establishing)
Partitioned, without index:
latency average = 1197.065 ms
tps = 8.357988 (excluding connections establishing)
Ок, досить повільно, треба добудувати індекс.
Partitioned, with index:
latency average = 370.156 ms
tps = 27.067386 (excluding connections establishing)
«Ручне» партиціонування в минулому
З незапам'ятних часів і аж до 10 версії включно Postgres підтримував 2 види партиціонування: list і range.
Для range- і list-партиціонування тригери або правила на інсерти зазвичай схожі на спагеті з правил і перевірок:
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
NEW.logdate < DATE '2008-02-01' ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Або old-school-style:
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
І перший, і другий варіанти керування записом не дуже корисні, коли вам треба партиціонування не за часом, а за полем з великою (навіть необмеженою) кількістю значень. Можна додати стандартну таблицю або динамічно створювати дочірні таблиці перед записом, чи як пропонують тут і тут. Однак краще уникати додаткових select перед кожним insert.
Правила партиціонування зі збільшенням кількості дочірніх перетворюються у «спагеті» з умов і перевірок. Описувати й зберігати їх часто — це непрактично, а відсутність правила для відповідної дочірньої таблиці призведе до неможливості запису і критичних помилок.
Ще немає коментарів