Данными командами мы создаем таблицы «my_logs2010m10», «my_logs2010m11» и т.д., которые копируют структуру с «мастер» таблицы (кроме индексов). Также с помощью «CHECK» мы задаем диапазон значений, который будет попадать в эту партицию (хочу опять напомнить, что диапазоны значений партиций не должны пересекаться!). Поскольку партиционирование будет работать по полю «logdate», мы создадим индекс на это поле на всех партициях:
CREATE INDEX my_logs2010m10_logdate ON my_logs2010m10 (logdate); CREATE INDEX my_logs2010m11_logdate ON my_logs2010m11 (logdate); CREATE INDEX my_logs2010m12_logdate ON my_logs2010m12 (logdate); CREATE INDEX my_logs2011m01_logdate ON my_logs2011m01 (logdate);
Далее для удобства создадим функцию, которая будет перенаправлять новые данные с «мастер» таблицы в соответствующую партицию.
CREATE OR REPLACE FUNCTION my_logs_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2010-10-01' AND NEW.logdate < DATE '2010-11-01' ) THEN INSERT INTO my_logs2010m10 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2010-11-01' AND NEW.logdate < DATE '2010-12-01' ) THEN INSERT INTO my_logs2010m11 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2010-12-01' AND NEW.logdate < DATE '2011-01-01' ) THEN INSERT INTO my_logs2010m12
VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2011-01-01' AND NEW.logdate < DATE '2011-02-01' ) THEN INSERT INTO my_logs2011m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the my_logs_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
В функции ничего особенного нет: идет проверка поля «logdate», по которой направляются данные в нужную партицию. При не нахождении требуемой партиции вызываем ошибку. Теперь осталось создать триггер на «мастер» таблицу для автоматического вызова данной функции:
CREATE TRIGGER insert_my_logs_trigger BEFORE INSERT ON my_logs FOR EACH ROW EXECUTE PROCEDURE my_logs_insert_trigger();
Партиционирование настроено и теперь мы готовы приступить к тестированию.
Тестирование
Для начала добавим данные в нашу таблицу «my_logs»:
INSERT INTO my_logs (user_id,logdate, data, some_state) VALUES(1, '2010-10-30', '30.10.2010 data', 1); INSERT INTO my_logs (user_id,logdate, data, some_state) VALUES(2, '2010-11-10', '10.11.2010 data2', 1); INSERT INTO my_logs (user_id,logdate, data, some_state) VALUES(1, '2010-12-15', '15.12.2010 data3', 1);
Теперь проверим где они хранятся:
partitioning_test=# SELECT * FROM ONLY my_logs; id | user_id | logdate | data | some_state ----+---------+---------+------+------------ (0 rows)
Как видим в «мастер» таблицу данные не попали она чиста. Теперь проверим а есть ли вообще данные:
partitioning_test=# SELECT * FROM my_logs; id | user_id | logdate | data | some_state ----+---------+---------------------+------------------+------------ 1 | 1 | 2010-10-30 00:00:00 | 30.10.2010 data | 1 2 | 2 | 2010-11-10 00:00:00 | 10.11.2010 data2 | 1 3 | 1 | 2010-12-15 00:00:00 | 15.12.2010 data3 | 1 (3 rows)
Данные при этом выводятся без проблем. Проверим партиции, правильно ли хранятся данные:
partitioning_test=# Select * from my_logs2010m10; id | user_id | logdate | data | some_state ----+---------+---------------------+-----------------+------------ 1 | 1 | 2010-10-30 00:00:00 | 30.10.2010 data | 1 (1 row) partitioning_test=# Select * from my_logs2010m11; id | user_id | logdate | data | some_state ----+---------+---------------------+------------------+------------ 2 | 2 | 2010-11-10 00:00:00 | 10.11.2010 data2 | 1 (1 row)
Отлично! Данные хранятся на требуемых нам партициях. При этом запросы к таблице «my_logs» менять не нужно:
partitioning_test=# SELECT * FROM my_logs WHERE user_id = 2; id | user_id | logdate | data | some_state ----+---------+---------------------+------------------+------------ 2 | 2 | 2010-11-10 00:00:00 | 10.11.2010 data2 | 1 (1 row) partitioning_test=# SELECT * FROM my_logs WHERE data LIKE '%0.1%'; id | user_id | logdate | data | some_state ----+---------+---------------------+------------------+------------ 1 | 1 | 2010-10-30 00:00:00 | 30.10.2010 data | 1 2 | 2 | 2010-11-10 00:00:00 | 10.11.2010 data2 | 1 (2 rows)
Управление партициями
Обычно при работе с партиционированием старые партиции перестают получать данные и остаются неизменными. Это дает огромное преимущество над работой с данными через партиции. Например, нам нужно удалить старые логи за 2008 год, 10 месяц. Нам достаточно выполнить:
DROP TABLE my_logs2008m10;
поскольку DROP TABLE работает гораздо быстрее, чем удаление миллионов записей индивидуально через DELETE. Другой вариант, который более предпочтителен, просто удалить партицию из партиционирования, тем самым оставив данные в СУБД, но уже не доступные через «мастер» таблицу:
ALTER TABLE my_logs2008m10 NO INHERIT my_logs;
Это удобно, если мы хотим эти данные потом перенести в другое хранилище или просто сохранить.
Важность «constraint_exclusion» для партиционирования
Параметр constraint_exclusion отвечает за оптимизацию запросов, что повышает производительность для партиционированых таблиц. Например, выполним простой запрос: