Хочется сразу отметить, что расширение только с версии PostgreSQL 9.2 contrib нормализирует SQL запросы. В версиях 9.1 и ниже SQL запросы сохраняются как есть, а значит «select * from table where id = 3» и «select * from table where id = 21» буду разными записями, что почти бесполезно для сбора полезной статистики.
Заключение
К счастью, PostgreSQL не требует особо сложной настройки. В большинстве случаев вполне достаточно будет увеличить объём выделенной памяти, настроить периодическое поддержание базы в порядке и проверить наличие необходимых индексов. Более сложные вопросы можно обсудить в специализированном списке рассылки.
Партиционирование
Введение
Партиционирование (partitioning, секционирование) это разбиение больших структур баз данных (таблицы, индексы) на меньшие кусочки. Звучит сложно, но на практике все просто.
Скорее всего у Вас есть несколько огромных таблиц (обычно всю нагрузку обеспечивают всего несколько таблиц СУБД из всех имеющихся). Причем чтение в большинстве случаев приходится только на самую последнюю их часть (т.е. активно читаются те данные, которые недавно появились). Примером тому может служить блог на первую страницу (это последние 510 постов) приходится 4050% всей нагрузки, или новостной портал (суть одна и та же), или системы личных сообщений, впрочем понятно. Партиционирование таблицы позволяет базе данных делать интеллектуальную выборку сначала СУБД уточнит, какой партиции соответствует Ваш запрос (если это реально) и только потом сделает этот запрос, применительно к нужной партиции (или нескольким партициям). Таким образом, в рассмотренном случае, Вы распределите нагрузку на таблицу по ее партициям. Следовательно выборка типа SELECT * FROM articles ORDER BY id DESC LIMIT 10 будет выполняться только над последней партицией, которая значительно меньше всей таблицы.
Итак, партиционирование дает ряд преимуществ:
На определенные виды запросов (которые, в свою очередь, создают основную нагрузку на СУБД) мы можем улучшить производительность;
Массовое удаление может быть произведено путем удаления одной или нескольких партиций (DROP TABLE гораздо быстрее, чем массовый DELETE);
Редко используемые данные могут быть перенесены в другое хранилище.
Теория
На текущий момент PostgreSQL поддерживает два критерия для создания партиций:
Партиционирование
по диапазону значений (range) таблица разбивается на «диапазоны» значений по полю или набору полей в таблице, без перекрытия диапазонов значений, отнесенных к различным партициям. Например, диапазоны дат;
Партиционирование по списку значений (list) таблица разбивается по спискам ключевых значений для каждой партиции.
Чтобы настроить партиционирование таблицы, достаточно выполните следующие действия:
Создается «мастер» таблица, из которой все партиции будут наследоваться. Эта таблица не будет содержать данные. Также не нужно ставить никаких ограничений на таблицу, если конечно они не будут дублироваться на партиции;
Создайте несколько «дочерних» таблиц, которые наследуют от «мастер» таблицы;
Добавить в «дочерние» таблицы значения, по которым они будут партициями. Стоить заметить, что значения партиций не должны пересекаться. Например:CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
неверно заданы партиции, поскольку непонятно какой партиции принадлежит значение 200;
Для каждой партиции создать индекс по ключевому полю (или нескольким), а также указать любые другие требуемые индексы;
При необходимости, создать триггер или правило для перенаправления данных с «мастер» таблицы в соответствующую партицию;
Убедиться, что параметр constraint_exclusion не отключен в postgresql.conf. Если его не включить, то запросы не будут оптимизированы при работе с партиционированием.
Практика использования
Теперь начнем с практического примера. Представим, что в нашей системе есть таблица, в которую мы собираем данные о посещаемости нашего ресурса. На любой запрос пользователя наша система логирует действия в эту таблицу. И, например, в начале каждого месяца (неделю) нам нужно создавать отчет за предыдущий месяц (неделю). При этом, логи нужно хранить в течении 3 лет. Данные в такой таблице накапливаются быстро, если система активно используется. И вот, когда в таблице уже миллионы, а то, и миллиарды записей, создавать отчеты становится все сложнее (да и чистка старых записей становится не легким делом). Работа с такой таблицей создает огромную нагрузку на СУБД. Тут нам на помощь и приходит партиционирование.
Настройка
Для примера, мы имеем следующую таблицу:
CREATE TABLE my_logs ( id SERIAL PRIMARY KEY, user_id INT NOT NULL, logdate TIMESTAMP NOT NULL, data TEXT, some_state INT );
Поскольку нам нужны отчеты каждый месяц, мы будем делить партиции по месяцам. Это поможет нам быстрее создавать отчеты и чистить старые данные.
«Мастер» таблица будет «my_logs», структуру которой мы указали выше. Далее создадим «дочерние» таблицы (партиции):
CREATE TABLE my_logs2010m10 ( CHECK ( logdate >= DATE '2010-10-01' AND logdate < DATE '2010-11-01' ) ) INHERITS (my_logs); CREATE TABLE my_logs2010m11 ( CHECK ( logdate >= DATE '2010-11-01' AND logdate < DATE '2010-12-01' ) ) INHERITS (my_logs); CREATE TABLE my_logs2010m12 ( CHECK ( logdate >= DATE '2010-12-01' AND logdate < DATE '2011-01-01' ) ) INHERITS (my_logs); CREATE TABLE my_logs2011m01 ( CHECK ( logdate >= DATE '2011-01-01' AND logdate < DATE '2010-02-01' ) ) INHERITS (my_logs);