[Перевод] Партиционирование в PostgreSQL – Что? Зачем? Как?
Функцией партиционирования таблиц в PostgreSQL, к сожалению, активно пользуются пока не многие. На мой взгляд, очень достойно о ней рассказывает в своей работе Hubert Lubaczewski (depesz.com). Предлагаю вам еще один перевод его статьи! 
В последнее время я заметил, что всё чаще и чаще сталкиваюсь с кейсами, где можно было бы использовать партиционирование. И хотя, теоретически, большинство людей знает о его существовании, на самом деле эту фичу не слишком хорошо понимают, а некоторые её даже побаиваются.
Так что я постараюсь объяснить в меру своих знаний и возможностей, что это такое, зачем его стоит использовать и как это сделать.
Как вы наверняка знаете, в PostgreSQL есть таблицы, а в таблицах есть данные. Иногда это всего несколько строк, а иногда — миллиарды.
Партиционирование — это метод разделения больших (исходя их количества записей, а не столбцов) таблиц на много маленьких. И желательно, чтобы это происходило прозрачным для приложения способом.
Одной из редко используемых фич PostgreSQL является тот факт, что это объектно-реляционная база данных. И «объект» здесь ключевое слово, потому что объекты (или, скорее, классы) знают то, что называется «наследование». Именно это используется для партиционирования.
Давайте посмотрим, о чём речь.
Я создам обычную таблицу users:
$ create table users (
id serial primary key,
username text not null unique,
password text,
created_on timestamptz not null,
last_logged_on timestamptz not null
);
Теперь, для полноты картины, давайте добавим несколько строк и дополнительный индекс:
$ insert into users (username, password, created_on, last_logged_on)
select
random_string( (random() * 4 + 5)::int4),
random_string( 20 ),
now() - '2 years'::interval * random(),
now() - '2 years'::interval * random()
from
generate_series(1, 10000);
$ create index newest_users on users (created_on);
Итак, у нас получилась тестовая таблица:
$ \d
Table "public.users"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE CONSTRAINT, btree (username)
"newest_users" btree (created_on)
С какими-то случайными данными:
$ select * from users limit 10;
id | username | password | created_on | last_logged_on
----+----------+----------------------+-------------------------------+-------------------------------
1 | ityfce3 | 2ukgbflj_l2ndo3vilt2 | 2015-01-02 16:56:41.346113+01 | 2015-04-15 12:34:58.318913+02
2 | _xg_pv | u8hy20aifyblg9f3_rf2 | 2014-09-27 05:41:05.317313+02 | 2014-08-07 14:46:14.197313+02
3 | uvi1wo | h09ae85v_f_cx0gf6_8r | 2013-06-17 18:48:44.389313+02 | 2014-06-03 06:53:49.640513+02
4 | o6rgs | vzbrkwhnsucxco5pjep0 | 2015-01-30 11:33:25.150913+01 | 2013-11-05 07:18:47.730113+01
5 | nk61jw77 | lidk_mnpe_olffmod7ed | 2014-06-15 07:18:34.597313+02 | 2014-03-21 17:42:44.763713+01
6 | 3w326_2u | pyoqg87feemojhql7jrn | 2015-01-20 05:41:54.133313+01 | 2014-09-07 20:33:23.682113+02
7 | m9rk9mnx | 6pvt94s6ol46kn0yl62b | 2013-07-17 15:13:36.315713+02 | 2013-11-12 10:53:06.123713+01
8 | adk6c | egfp8re0z492e6ri8urz | 2014-07-23 11:41:11.883713+02 | 2013-10-22 07:19:36.200513+02
9 | rsyaedw | ond0tie9er92oqhmdj39 | 2015-05-11 16:45:40.472513+02 | 2013-08-31 17:29:18.910913+02
10 | prlobe46 | _3br5v97t2xngcd7xz4n | 2015-01-10 20:13:29.461313+01 | 2014-05-04 06:25:56.072513+02
(10 rows)
Теперь, когда таблица готова, я могу создать партиции, что означает — наследованные таблицы:
$ create table users_1 () inherits (users);
$ \d users_1
Table "public.users_1"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Inherits: users
Таким образом, у нас получилась новая таблица, у которой есть определенные интересные свойства:
- она использует тот же sequence, что и основная таблица, для своей колонки id;
- все столбцы имеют одинаковое определение, включая ограничения not null;
- нет ни первичного ключа, ни ограничений уникальности для имени пользователя, ни индекса для created_on.
Давайте попробуем ещё раз, но на этот раз с более «взрывным» эффектом:
$ drop table users_1;
$ create table users_1 ( like users including all );
$ \d users_1
Table "public.users_1"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Indexes:
"users_1_pkey" PRIMARY KEY, btree (id)
"users_1_username_key" UNIQUE CONSTRAINT, btree (username)
"users_1_created_on_idx" btree (created_on)
Теперь у нас есть все индексы и ограничения, но мы потеряли информацию о наследовании. Но мы можем добавить её позже с помощью:
$ alter table users_1 inherit users;
$ \d users_1
Table "public.users_1"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Indexes:
"users_1_pkey" PRIMARY KEY, btree (id)
"users_1_username_key" UNIQUE CONSTRAINT, btree (username)
"users_1_created_on_idx" btree (created_on)
Inherits: users
Мы могли бы сделать это в один шаг, но тогда появляются разные неприятные уведомления:
$ drop table users_1;
$ create table users_1 ( like users including all ) inherits (users);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "username" with inherited definition
NOTICE: merging column "password" with inherited definition
NOTICE: merging column "created_on" with inherited definition
NOTICE: merging column "last_logged_on" with inherited definition
$ \d users_1
Table "public.users_1"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Indexes:
"users_1_pkey" PRIMARY KEY, btree (id)
"users_1_username_key" UNIQUE CONSTRAINT, btree (username)
"users_1_created_on_idx" btree (created_on)
Inherits: users
В любом случае, теперь у нас есть две таблицы — основная и первая партиция.
Если я произведу какое-либо действие — выборка/обновление/удаление — с пользователями, обе таблицы будут просканированы:
$ explain analyze select * from users where id = 123;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..16.47 rows=2 width=66) (actual time=0.008..0.009 rows=1 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
Planning time: 0.327 ms
Execution time: 0.031 ms
(7 rows)
Но если я обращусь к партиции напрямую, запрос будет выполнен только на ней:
$ explain analyze select * from users_1 where id = 123;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 123)
Planning time: 0.162 ms
Execution time: 0.022 ms
(4 rows)
Если бы мы хотели, мы могли бы обратиться только к таблице пользователей без её партиций, используя ключевое слово ONLY:
$ explain analyze select * from only users where id = 123;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = 123)
Planning time: 0.229 ms
Execution time: 0.031 ms
(4 rows)
Вы могли заметить, что я сказал, что выборка/обновление/удаление работает на всех партициях. А что насчет вставок? Вставке требуется добавить куда-нибудь данные, так что она всегда работает так, как будто было использовано ONLY. Поэтому, если мне нужно добавить строку в users_1, я должен сделать так:
INSERT INTO users_1 ...
Выглядит как-то не слишком хорошо, но не волнуйтесь, есть способы это обойти.
Давайте попробуем произвести настоящее партиционирование. Для начала нам нужно решить, каким будет ключ партиционирования — другими словами, по какому алгоритму будут выбираться партиции.
Есть пара наиболее очевидных:
- партиционирование по дате — например, выбирать партиции, основываясь на годе, в котором пользователь был создан;
- партиционирование по диапазону идентификаторов — например, первый миллион пользователей, второй миллион пользователей, и так далее;
- партиционирование по чему-нибудь другому — например, по первой букве имени пользователя.
Есть еще пара других, не так часто используемых вариантов, вроде «партиционирования по хэшу от имени пользователя».
Почему стоит использовать одну схему, а не другую? Давайте разберемся в их достоинствах и недостатках:
- партиционирование по дате:
- достоинства:
- легко понять;
- количество строк в данной таблице будет достаточно стабильным;
- недостатки:
- требует поддержки — время от времени нам придётся добавлять новые партиции;
- поиск по имени пользователя или id потребует сканирования всех партиций;
- достоинства:
- партиционирование по id:
- достоинства:
- легко понять;
- количество строк в партиции будет на 100% стабильным;
- недостатки:
- требует поддержки — время от времени нам придётся добавлять новые партиции;
- поиск по имени пользователя или id потребует сканирования всех партиций;
- достоинства:
- партиционирование по первой букве имени пользователя:
- достоинства:
- легко понять;
- никакой поддержки — есть строго определенный набор партиций и нам никогда не придется добавлять новые;
- недостатки:
- количество строк в партициях будет стабильно расти;
- в некоторых партициях будет существенно больше строк, чем в других (больше людей с никами, начинающимися на «t*», чем на «y*»);
- поиск по id потребует сканирования всех партиций;
- достоинства:
- партиционирование по хэшу имени пользователя:
- достоинства:
- никакой поддержки — есть строго определенный набор партиций и нам никогда не придется добавлять новые;
- строки будут равно распределяться между партициями;
- недостатки:
- количество строк в партициях будет стабильно расти;
- поиск по id потребует сканирования всех партиций;
- поиск по имени пользователя будет сканировать только одну партицию, но только при использовании дополнительных условий.
- достоинства:
Последний недостаток подхода с хэшированными именами пользователей весьма интересен. Давайте посмотрим, что там происходит.
Для начала мне нужно создать побольше партиций:
$ create table users_2 ( like users including all );
$ alter table users_2 inherit users;
...
$ create table users_10 ( like users including all );
$ alter table users_10 inherit users;
Теперь у таблицы users есть 10 партиций:
$ \d users
Table "public.users"
Column | Type | Modifiers
----------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
password | text |
created_on | timestamp with time zone | not null
last_logged_on | timestamp with time zone | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE CONSTRAINT, btree (username)
"newest_users" btree (created_on)
Number of child tables: 10 (Use \d+ to list them.)
В PostgreSQL есть опция constraint_exclusion. И если её настроить на «on» или «partition», PostgreSQL будет пропускать партиции, которые не могут содержать совпадающие строки.
В моём Pg это установлено по умолчанию:
$ show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)
Итак, поскольку у всех моих партиций и базовой таблицы нет никаких осмысленных ограничений, так что любой запрос будет сканировать сразу все 11 таблиц (основную и 10 партиций):
$ explain analyze select * from users where id = 123;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.009..0.013 rows=1 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_2_pkey on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_3_pkey on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_5_pkey on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_6_pkey on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_7_pkey on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_8_pkey on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_9_pkey on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_10_pkey on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
Planning time: 1.321 ms
Execution time: 0.087 ms
(25 rows)
Это не слишком эффективно, но мы можем поставить ограничение.
Допустим, наши партиции были сформированы методом партиционирования по id, и в каждой партиции хранится 100,000 идентификаторов.
Мы можем добавить несколько ограничений:
$ alter table users_1 add constraint partition_check check (id >= 0 and id < 100000);
$ alter table users_2 add constraint partition_check check (id >= 100000 and id < 200000);
...
$ alter table users_10 add constraint partition_check check (id >= 900000 and id < 1000000);
Теперь повторяем предыдущий запрос:
$ explain analyze select * from users where id = 123;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..16.47 rows=2 width=66) (actual time=0.008..0.009 rows=1 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
Planning time: 1.104 ms
Execution time: 0.031 ms
(7 rows)
Он сканирует только 2 таблицы: основную (в которой сейчас находятся все данные, и нет ограничений, так что её нельзя исключить) и подходящую партицию.
Здорово, правда?
Мы можем без проблем добавить подобные условия партиционирования по имени пользователя или created_on. Но посмотрите, что происходит, когда ключ партиционирования более сложный:
$ alter table users_1 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 0);
$ alter table users_2 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 1);
...
$ alter table users_10 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 9);
На случай, если вы не в курсе, hashtext () берет строку и возвращает целое число в диапазоне от -2147483648 до 2147483647.
Благодаря простой арифметике мы знаем, что abs (hashtext (string)) % 10 всегда будет выдавать значение в диапазоне 0…9, и его легко посчитать для любого параметра.
Знает ли об этом PostgreSQL?
$ explain analyze select * from users where username = 'depesz';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.023..0.023 rows=0 loops=1)
-> Index Scan using users_username_key on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_1_username_key on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_2_username_key on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_3_username_key on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_4_username_key on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_5_username_key on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_6_username_key on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_7_username_key on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_8_username_key on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_9_username_key on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
-> Index Scan using users_10_username_key on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (username = 'depesz'::text)
Planning time: 1.092 ms
Execution time: 0.095 ms
(25 rows)
Нет. Не знает. По сути, PostgreSQL может сделать автоматическое исключение партиций только для проверок, основанных на диапазоне (или равенстве). Ничего основанного на функциях. Даже простой модуль от числа — это уже перебор:
$ alter table users_1 drop constraint partition_check, add constraint partition_check check ( id % 10 = 0);
$ alter table users_2 drop constraint partition_check, add constraint partition_check check ( id % 10 = 1);
...
$ alter table users_10 drop constraint partition_check, add constraint partition_check check ( id % 10 = 9);
$ explain analyze select * from users where id = 123;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.009..0.016 rows=1 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_2_pkey on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_3_pkey on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_5_pkey on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_6_pkey on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_7_pkey on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_8_pkey on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_9_pkey on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (id = 123)
-> Index Scan using users_10_pkey on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
Planning time: 0.973 ms
Execution time: 0.086 ms
(25 rows)
Это печально. Потому что у ключей партиционирования, основанных на модулях чисел, есть одно огромное (на мой взгляд) преимущество — стабильное число партиций. Вам не придется создавать их в будущем, если только вы не надумаете произвести партиционирование заново при достижении какого-то более высокого объема данных.
Значит ли это, что вы не можете использовать сложные (основанные на функциях или модулях от чисел) ключи партиционирования? Нет. Вы можете использовать их, но тогда запросы получатся более сложными:
$ explain analyze select * from users where id = 123 and id % 10 = 123 % 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..16.48 rows=2 width=66) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.31 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = 123)
Filter: ((id % 10) = 3)
-> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = 123)
Filter: ((id % 10) = 3)
Planning time: 1.018 ms
Execution time: 0.033 ms
(9 rows)
Здесь я добавил еще одно условие, вот такое:
id % 10 = 123 % 10
PostgreSQL может в процессе разбора выражения переписать его:
id % 10 = 3
потому что он знает, что оператор % для целых чисел является иммутабельным. И теперь, как часть запроса, у меня есть точный ключ партиционирования — id % 10 = 3. Таким образом, Pg может использовать только те партиции, у которых либо нет ключа партиционирования (то есть, базовую таблицу), либо есть ключ, соответствующий запросу.
Стоит ли вводить дополнительное усложнение — решать вам.
Если вы предпочитаете не менять запросы, и вас не затруднит добавлять новые партиции время от времени, то вам стоит ознакомиться с PG Partition Manger, написанным моим бывшим коллегой Keith Fiske — это набор функций, которые вы запускаете вручную для определения партиций, и еще одна, которую вы запускаете по крону, и она берет на себя создание новых партиций для будущих данных.
Я уже упоминал вставки, но не объяснил, как обойти проблему с вставками, которые должны добавиться к партициям.
В целом, это работа для триггера. Pg_partman от Кита создает такие триггеры за вас, но я хочу, чтобы вы понимали, что происходит, и не использовали pg_partman как «черный ящик», а скорее как вспомогательный инструмент, который делает нудную работу за вас.
Сейчас моя схема партиционирования основана на модуле от числа (насколько я знаю, partman так сделать не может), так что давайте напишем подходящую функцию триггера. Она будет вызываться при вставке данных в таблицу users и должна без ошибок перенаправлять вставку в соответствующую партицию. Итак, пишем:
$ create function partition_for_users() returns trigger as $$
DECLARE
v_parition_name text;
BEGIN
v_parition_name := format( 'users_%s', 1 + NEW.id % 10 );
execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW;
return NULL;
END;
$$ language plpgsql;
А теперь определение триггера:
$ create trigger partition_users before insert on users for each row execute procedure partition_for_users();
Попробуем добавить строку:
$ insert into users (username, password, created_on, last_logged_on)
values (
'depesz',
random_string( 20 ),
now() - '2 years'::interval * random(),
now() - '2 years'::interval * random()
);
$ select currval('users_id_seq');
currval
---------
10003
(1 row)
Посмотрим, видны ли данные:
$ select * from users where username = 'depesz';
id | username | password | created_on | last_logged_on
-------+----------+----------------------+-------------------------------+-------------------------------
10003 | depesz | bp7zwy8k3t3a37chf1hf | 2014-10-24 02:45:51.398824+02 | 2015-02-05 18:24:57.072424+01
(1 row)
Выглядит хорошо, но где они находятся? В основной таблице?
$ select * from only users where username = 'depesz';
id | username | password | created_on | last_logged_on
----+----------+----------+------------+----------------
(0 rows)
Нет. Так может, в нужной партиции?
$ select * from users_4 where username = 'depesz';
id | username | password | created_on | last_logged_on
-------+----------+----------------------+-------------------------------+-------------------------------
10003 | depesz | bp7zwy8k3t3a37chf1hf | 2014-10-24 02:45:51.398824+02 | 2015-02-05 18:24:57.072424+01
Да. Триггер сработал. Но у этого метода есть один недостаток. А именно — «RETURNING» не работает:
$ insert into users (username, password, created_on, last_logged_on)
values (
'test',
random_string( 20 ),
now() - '2 years'::interval * random(),
now() - '2 years'::interval * random()
)
returning *;
id | username | password | created_on | last_logged_on
----+----------+----------+------------+----------------
(0 rows)
Так происходит, потому что, с точки зрения исполнителя, вставка ничего не вернула — триггер вернул NULL.
Мне пока не удалось найти удачное решение этой проблемы. В своих кейсах я просто предпочитаю получать первоначальное значение ключа заранее, используя nextval (), а потом вставляю готовое значение — так что оно уже имеется после вставки:
$ select nextval('users_id_seq');
nextval
---------
10005
(1 row)
$ insert into users (id, username, password, created_on, last_logged_on)
values (
10005,
'test',
random_string( 20 ),
now() - '2 years'::interval * random(),
now() - '2 years'::interval * random()
);
Ко всему этому есть одно уточнение. Маршрутизация всех вставок через триггер замедляет их, ведь для каждой строки PG нужно будет выполнять еще один «insert».
Для срочных объемных вставок лучшим решением будет заставить их работать напрямую с партициями. Поэтому, например, вместо
COPY users FROM stdin;
....
\.
вы предварительно выясняете, сколько идентификаторов вам нужно, к примеру, вот таким способом:
select nextval('users_id_seq') from generate_series(1, 100);
А потом выдаете подходящие:
COPY users_p1 FROM stdin;
....
\.
COPY users_p2 FROM stdin;
....
\.
...
Не самый удобный способ, но он может быть полезен, если вы импортируете большие объемы данных в партиционированные таблицы.
Итак, теперь вы должны понимать, что такое партиционирование, и как оно работает. Следующий вопрос в заголовке был: зачем?
Ответить на него относительно легко: для обеспечения производительности или упрощения обслуживания.
В качестве простого примера, возьмем таблицу users, в которой 1 миллиард строк (1,000,000,000).
Поиск в ней будет прогрессивно дорожать даже с учётом индексирования, просто потому, что глубина индексов будет расти.
Это видно даже в моей маленькой тестовой таблице.
Давайте сбросим все партиции и триггер партиционирования:
$ drop table users_1;
$ drop table users_2;
...
$ drop table users_10;
$ drop trigger partition_users on users;
Теперь в таблице users 10,000 строк. Простой поиск по имени пользователя занимает 0.020 мс — это лучшее время из трех попыток.
Если я добавлю больше строк:
$ insert into users (username, password, created_on, last_logged_on)
select
random_string( (random() * 4 + 5)::int4),
random_string( 20 ),
now() - '2 years'::interval * random(),
now() - '2 years'::interval * random()
from
generate_series(1, 100000);
тот же поиск займёт 0.025 мс. Увеличение времени поиска на 0.005 мс может быть небольшим, но у нас по-прежнему всего лишь 110,000 строк, и в системе нет других таблиц, так что вся таблица с индексами помещается в память.
Конечно, ваше партиционирование должно быть осмысленным. Например, если вы обычно осуществляете поиск по имени пользователя, то бессмысленно делать партиционирование по id — Pg придется искать по всем партициям (это может стать осмысленным в будущем, но об этом я расскажу в самом конце статьи).
То есть вам нужно определиться с тем, что вы обычно запрашиваете — будь то поиск по какому-то ключу или, возможно, вы обычно просматриваете только свежие данные? И партиционировать таким образом, чтобы ограничить количество партиций, которые Pg нужно просканировать.
Важно то, что партиционирование делает вашу жизнь проще, особенно если вы в большей степени администратор баз данных, нежели программист. Любые задачи по техобслуживанию (создание индекса, vacuum, pg_reorg/pg_repack, pg_dump) могут быть эффективно разбиты на столько подзадач, сколько у вас имеется партиций. Так что вместо одной многочасовой транзакции для переупаковки большой таблицы у вас будет 20 гораздо более быстрых и использующих меньше места на диске транзакций, а результат, в целом, окажется тем же самым!
Конечно, хорошими новостями дело не ограничивается. В партиционировании есть один большой недостаток: у вас не может быть внешних ключей, указывающих на партиционированную таблицу.
Это просто не работает. Вы могли бы завести внешние ключи, указывающие прямо на партиции, но это (обычно) является бессмысленным.
Большая ли это проблема лично для вас, зависит от вашего юзкейса. Мне кажется, что в большинстве случаев, когда мы достигаем таблиц достаточно больших для того, чтобы партиционирование было оправдано, приложение протестировано достаточно хорошо, и мы можем смириться с отсутствием внешнего ключа. К тому же, мы всегда можем добавить задачу в крон для тестирования наличия «плохих» значений.
Теперь мы знаем, что такое партиционирование, как оно работает и зачем используется. Остался последний вопрос: как преобразовать таблицу в партиционированную. Обычно, приложение не создается с партиционированными таблицами — в начале это не имеет смысла. Но, вскоре, у вас появится какая-нибудь таблица с множеством строк и вы подумаете: «Надо было партиционировать её сразу при создании».
Но может быть, мы всё ещё можем её партиционировать, когда приложение уже работает? С минимумом проблем?
Давайте посмотрим. Для теста я создал базу данных pgbench на 97 ГБ. Большая её часть, 83 ГБ, находится в таблице pgbench_accounts, которая содержит 666,600,000 записей.
Схема у этой таблицы вот такая:
Table "public.pgbench_accounts"
Column | Type | Modifiers
----------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
И все запросы к ней основываются на колонке aid, которая содержит значения от 1 до 666,600,000.
Так что давайте партиционируем её, основываясь на диапазоне значений aid.
Допустим, я помещу в каждую партицию 10 миллионов строк, тогда мне потребуется 67 партиций.
Но как я могу проверить, что мои действия не нарушат работу? Очень просто. Я запущу pgbench в цикле. Мне не интересны точные отчеты о скоростях, достаточно информации о том, как сильно моя работа влияет на то, что делает pgbench.
С этими мыслями я запустил функцию:
$ while true
do
date
pgbench -T 10 -c 2 bench
done 2>&1 | tee pgbench.log
Она будет прогонять 10-секундные тесты и сохранять статистические данные в файл, так что я смогу позже проследить взаимосвязь результата с моей работой по партиционированию.
Когда всё готово, я создам партиции с проверками в нужных местах:
do $$
declare
i int4;
aid_min INT4;
aid_max INT4;
begin
for i in 1..67
loop
aid_min := (i - 1) * 10000000 + 1;
aid_max := i * 10000000;
execute format('CREATE TABLE pgbench_accounts_p_%s ( like pgbench_accounts including all )', i );
execute format('ALTER TABLE pgbench_accounts_p_%s inherit pgbench_accounts', i);
execute format('ALTER TABLE pgbench_accounts_p_%s add constraint partitioning_check check ( aid >= %s AND aid <= %s )', i, aid_min, aid_max );
end loop;
end;
$$;
партиции готовы, и я могу убедиться, что проверки используются:
$ explain analyze select * from pgbench_accounts where aid = 123;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.57..16.75 rows=2 width=224) (actual time=6.468..6.473 rows=1 loops=1)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..8.59 rows=1 width=97) (actual time=6.468..6.469 rows=1 loops=1)
Index Cond: (aid = 123)
-> Index Scan using pgbench_accounts_p_1_pkey on pgbench_accounts_p_1 (cost=0.14..8.16 rows=1 width=352) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: (aid = 123)
Planning time: 3.475 ms
Execution time: 6.497 ms
(7 rows)
Теперь нужно добавить триггер-«маршрутизатор»:
$ create function partition_for_accounts() returns trigger as $$
DECLARE
v_parition_name text;
BEGIN
v_parition_name := format( 'pgbench_accounts_p_%s', 1 + ( NEW.aid - 1 ) / 10000000 );
execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW;
return NULL;
END;
$$ language plpgsql;
$ create trigger partition_users before insert on pgbench_accounts for each row execute procedure partition_for_accounts();
Это всё замечательно, но он сработает только для свежевставленных строк, а у меня уже 666 миллионов строк в исходной таблице. Что же с этим делать?
Мне нужно их переместить. Это относительно просто в теории, но есть пара подводных камней:
- Ни в коем случае обе строки не должны быть одновременно видимы ни для каких транзакций (то есть, из основной таблицы и из партиции).
- Я не могу удалить все строки и вставить их в партиции, потому что это заблокирует всю базовую таблицу на время перемещения.
Вторую проблему можно смягчить, если работать с партиями данных. Но мы не можем использовать для этого SQL.
Время от времени кто-нибудь спрашивает о том, как разделить большую операцию на порции и вызывать её одной функцией sql, которая будет итеративно обрабатывать порции данных. У этого подхода есть одна фундаментальная проблема: вызов функции — это транзакция. Поэтому всё, что делает эта функция, будет происходить в одной транзакции. Так что проблема блокировки не решается.
Но мы можем использовать для этого psql (или ruby, perl, python — не важно), перемещая с каждой порцией лишь небольшое количество строк и, таким образом, блокируя основную таблицу на короткий момент времени.
В целом, единичный запрос будет выглядеть так:
with x as (delete from only pgbench_accounts where aid between .. and .. returning *)
insert into appropriate_partition select * from x;
Я выбрал размер порции — 1000, это достаточно малое значение, чтобы процесс не затянулся, и достаточно большое, чтобы итоговое количество порций не было чрезмерным (666 тысяч).
Теперь давайте создадим пакетный файл:
\pset format unaligned
\pset tuples_only true
\o /tmp/run.batch.migration.sql
SELECT
format(
'with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= %s AND aid <= %s returning *) INSERT INTO pgbench_accounts_p_%s SELECT * FROM x;',
i,
i + 999,
( i - 1 ) / 10000000 + 1
)
FROM
generate_series( 1, 666600000, 1000 ) i;
\o
Когда я запустил это в psql, он создал файл /tmp/run.batch.migration.sql, который достаточно объемен (97 ГБ), поскольку содержит 666,600 запросов, подобных этим:
with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= 1 AND aid <= 1000 returning *) INSERT INTO pgbench_accounts_p_1 SELECT * FROM x;
with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= 1001 AND aid <= 2000 returning *) INSERT INTO pgbench_accounts_p_1 SELECT * FROM x;
with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= 2001 AND aid <= 3000 returning *) INSERT INTO pgbench_accounts_p_1 SELECT * FROM x;
Теперь, когда всё подготовлено, я могу запустить процесс (конечно, с помощью «screen» или в «tmux», чтобы ничего не потерялось, если ssh соединение с сервером оборвется):
$ psql -d bench -f /tmp/run.batch.migration.sql
Это займет некоторое время. В случае с моей тестовой базой данных средний пакет обрабатывается за ~ 92 мс, а значит, у меня впереди 17 часов перемещения данных.
В реальности ушло всего 7 часов. Неплохо.
По окончании таблица pgbench_accounts всё еще весит ~ 83Гб (думаю, моему диску не хватает скорости, чтобы справиться с pgbench, перемещением и vacuum).
Но я проверил и, похоже, что все строки переместились в партиции:
$ select count(*) from only pgbench_accounts;
count
-------
0
(1 row)
Как насчет скорости pgbench во время процесса перемещения?
Было 4 фазы:
- До начала работы по перемещению.
- После создания партиций.
- После создания триггера.
- Во время перемещения.
Результаты?
phase | min | avg | max
---------+-----------+------------------+-----------
Phase 1 | 28.662223 | 64.0359512839506 | 87.219148
Phase 2 | 21.147816 | 56.2721418360656 | 75.967217
Phase 3 | 23.868018 | 58.6375074477612 | 75.335558
Phase 4 | 5.222364 | 23.6086916565574 | 65.770852
(4 rows)
Да, перемещение всё замедлило. Но отметьте, пожалуйста, что это обычный персональный компьютер с SATA дисками, а не SSD, постоянно находящийся под высокой нагрузкой — pgbench прогонял запросы так быстро, как мог.
Кроме того, некоторое замедление произошло из-за того, что vacuum не слишком хорошо справляется с удалениями. По-моему, результат абсолютно приемлемый.
По окончании я мог бы сделать:
$ truncate only pgbench_accounts;
И потом, чтобы проверить, всё ли ОК:
$ select count(*) from pgbench_accounts;
count
-----------
666600000
(1 row)
Всё это было проделано без каких-либо ошибок и без прерывания работы «настоящего приложения».
В конце добавлю, что партиционирование скоро (относительно) станет ещё круче. С недавних пор мы можем хранить партиции на разных серверах. И сейчас ведется работа (хотя вряд ли это обновление появится раньше версии 9.6) над тем, чтобы дать возможность проводить параллельные сканирования, что существенно улучшит весь процесс.
Надеюсь, этот текст будет вам полезен.
Какие еще аспекты партиционирования таблиц в PostgreSQL вы бы хотели обсудить? Будем рады дополнить программу докладов конференции PG Day'16 Russia наиболее интересными для вас темами! Мы уже открыли продажи early bird билетов, спешите зарегистрироваться по самой низкой цене!
