Партиціонування без правил і тригерів у Postgres

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.

Правила партиціонування зі збільшенням кількості дочірніх перетворюються у «спагеті» з умов і перевірок. Описувати й зберігати їх часто — це непрактично, а відсутність правила для відповідної дочірньої таблиці призведе до неможливості запису і критичних помилок.