Пользователь
0,0
рейтинг
18 ноября 2009 в 03:23

Разработка → Все что нужно знать о секционировании (Часть 1)

Часть 2

Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации, но разве ж это меня остановит?).

Вступление


Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая — разделить таблицу на несколько частей меньшего размера. Различают два подвида — горизонтальное и вертикальное секционирование.

Горизонтальное секционирование

Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения — LOGS. Мы можем разбить ее на части — одна для логов за январь 2009, другая — за февраль 2009, и т.д.

Вертикальное секционирование

Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.

Поддержка секционирования в современных СУБД

Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.
  • Oracle — поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой — все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
  • Microsoft SQL Server — поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования — «Ну наконец-то!!:)», второе — «Работает, вроде все ок». Документация на msdn
  • MySQL — поддерживает начиная с версии 5.1. Очень хорошее описание на хабре
  • И так далее…

*-вру, конечно, есть стандартный набор сложностей — создать вовремя новую секцию, старую выкинуть и т.д., но все равно как-то все просто и понятно.

Секционирование в Postgresql


Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.

Постановка задачи

База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.
1. Создаем основную таблицу:
CREATE TABLE analytics.events
(
  event_id BIGINT DEFAULT nextval('analytics.seq_events') PRIMARY KEY,
  user_id UUID NOT NULL,
  event_type_id SMALLINT NOT NULL,
  event_time TIMESTAMP DEFAULT now() NOT NULL,
  url VARCHAR(1024) NOT NULL,
  referrer VARCHAR(1024),
  ip INET NOT NULL
);


* This source code was highlighted with Source Code Highlighter.

2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
(
  event_id BIGINT DEFAULT nextval('analytics.seq_events') PRIMARY KEY,
  CHECK ( event_time >= TIMESTAMP '2010-01-01 00:00:00' AND event_time < TIMESTAMP '2010-01-02 00:00:00' )
) INHERITS (analytics.events);


* This source code was highlighted with Source Code Highlighter.

При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.

3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);

* This source code was highlighted with Source Code Highlighter.

4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт — создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.event_time >= TIMESTAMP '2010-01-01 00:00:00' AND
    NEW.event_time < TIMESTAMP '2010-01-02 00:00:00') THEN
    INSERT INTO analytics.events_01012010 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Date % is out of range. Fix analytics.events_insert_trigger', NEW.event_time;
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;


* This source code was highlighted with Source Code Highlighter.

CREATE TRIGGER events_before_insert
  BEFORE INSERT ON analytics.events
  FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();


* This source code was highlighted with Source Code Highlighter.


5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE;


* This source code was highlighted with Source Code Highlighter.


Конец первой части

Итак, что мы имеем? Давайте по пунктам:
1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.

О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.

UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
Андрей Мешков @aymeshkov
карма
56,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (43)

  • +1
    Не надо калькировать английские термины. Особенно, этот: очень сильно режет слух. Используйте русские устоявшиеся аналоги или пишите на английском.
    • 0
      s/парти/сек/
      • +1
        можно, наверное, применить термин разбиение
        • +1
          Зачем, когда есть устоявшийся термин, использующийся в литературе.
    • 0
      Вы про секционирование? В литературе встречаются оба подхода. Эх, с русским конечно тяжело), во второй части буду чаще partitioning употреблять.
      • 0
        Я пока до комментариев не дошел, все думал: причем тут патриции?
        • 0
          Ок, заменил везде)
  • +1
    я не знаком с постгре, но разве не должна она уметь делать все автоматически без триггеров и тп? чтобы можно было задать условия как делить таблицу, а база уже сама разобралась что куда складывать и это все было прозрачно для запросов?
    • 0
      ну это и так прозрачно: все пишется в мастер-таблицу.

      проблема в том, что партицировать можно по разному: на основании любой хэш функции (операторов больше-меньше для сравнимых типов; или, например, юзеров делим на две часть — мальчиков направо, девочек налево), еще как угодно. так что как ни крути, придется писать свою хэш-функцию и делать под нее свой синтаксис в create table. а зачем, если все уже есть?

      плюс еще, что не бд не знает, что пора делать новую партицию — может быть куча вариантов: по времени, по количеству данных.
  • +3
    Получается, что именно секционирование как таковое в PostgreSQL отсутствует, но есть функционал для того, чтобы эмулировать его работу?

    А можно, например, «вывести из строя» одну из секций таблицы, не повредив работе запросов над остальными секциями (как в оракле ALTER TABLESPACE… OFFLINE)?
    • +1
      alter table… no inherits
      alter table… inherits
    • 0
      У вас по tablespace на partition?
      Вообще вы можете спокойно дропнуть хоть половину всех секций, работе с остальными это не повредит.

      И я бы не сказал, что это эмуляция секционирования. Это оно самое и есть, просто реализация отличается от Oracle, но Oracle и не является эталоном (что прискорбно).
  • НЛО прилетело и опубликовало эту надпись здесь
    • +1
      Ага, только не ставить, а покупать. Я так прикинул на shop.oracle.com, при самом дешевом раскладе (25 лицензий Named User Plus на 1 год и без поддержки) выходит 5900 долларов США (я не уверен, включены ли налоги). При этом надо понимать, что нет особого смысла покупать лиценизии на 1 год.

      А тут дешево и сердито. А, может, и не так уж сердито, почитаем продолжение.
      • НЛО прилетело и опубликовало эту надпись здесь
      • +1
        Ой как дешево, а если покупать в РФ у официальных дилеров, то от 12к$.
        • 0
          Это за 25x Named User Plus (NUP) Enterprise Edition 1 year + 25x NUP Partitioning без поддержки?
          Хотя, я, конечно, понимаю, что это бредовый вариант, и нечто нормальное действительно может начинаться с 20К.
          • +1
            Ой, сейчас пересчитал, Oracle EE, 25xNUP +25xNUP Partitioning:

            ИТОГО 38066.56 $

            Вот во всем Oracle хорош, но цена…
        • +1
          Ну… Зато если вы принимаете решение о покупке, то официальный дилер поделится лично с вами сладким секретом своей наценки ;)
          • 0
            :)))
      • 0
        Маловато будет! Скорее всего ведь процессорные лицензии потребуются, а это 8500 на каждый сокет. Не считая, опять же, поддержки и самой СУБД.

        Но по опыту — когда при использовании БД на Oracle встает вопрос секционирования, то речь идет о реально огромной БД, сотни Гб. А значит и железо серьезное, и система ценная. Приходится раскошеливаться.
        • 0
          Недавно облажались на работе, предполагая что цена считается по процессорам. Оказалось по ядрам =( в итоге система оказалась в два раза дороже =\
          • 0
            Да, там нужно внимательно всё читать. Standard — по сокетам, Enterprise — по ядрам с таблицей коэфициентов, муть…
    • 0
      Ну не скажите, административный ovehead добавляется и в oracle. Точно также нужно во время создавать/архивировать партиции. Разве что с триггером и CHECK constraints возиться не нужно. Вообще по сути в oracle каждая партиция неявно для пользователя является отдельной таблицей.
      • 0
        Отдельным сегментом, но не таблицей. Все-таки, нельзя сделать alter секции и изменить список столбцов в ней, например. Или навесить ограничение целостности только на одну секцию.
        • 0
          Поэтому и говорю — неявно для пользователя. Сама база работает с секцией как с отдельной таблицей, Кайт когда-то писал об этом.

          Ну а чтобы не начать ненароком спора об этом, сформулирую так: секции имеют очень много общего с обычными таблицами:).
      • 0
        А уж какой overhead добавляется при разработке приложения! Нужно по всем запросам к секционированным таблицам 3-жды проверять планы, условия поиска, думать о том, разрешать ли Ораклу распараллеливать их, и т.д.
        • 0
          ну что вы наговариваете:) будто планы запросов к обычным таблицам проверять не нужно, да и параллелятся они тоже на раз.
          • 0
            Я не наговариваю, я реалист :) Параллелится всё на раз, в каких-то ситуациях становится лучше в разы. В каких-то — хуже в разы, из-за большого оверхеда на создание параллельных потоков обработки и синхронизацию между ними.

            Приходится где-то разрешать параллельную обработку, где-то запрещать, где-то переписывать запросы.

            В общем, появляется много новой интересной работы :)
  • +1
    есть замечание, что неплохо было-бы написать про управление партициями не через триггер, а через rule, и когда какой метод предпочтительней использовать
    • 0
      Да я хотел, но очень уж много материала получается, так что это во второй части.
  • 0
    краем глаза замечаю «вечер/день/утро» — секунду не могу понять, что за странный путь такой.
    надо больше спать!
  • 0
    А умеет ли PostgreSQL распараллеливать выполнение запросов, затрагивающих несколько секций?
    • 0
      Нет, не умеет. Вообще у postgresql есть один большой минус, он выполняет запрос в одном потоке, и совсем не умеет параллелить, так что каждый раз приходится изобретать велосипед. Ходит слух, что enterpriseDB параллелит выполнение запросов.

      В защиту постгре скажу, что распараллеливание запросов в Oracle не всегда приносит тот результат, который от него ждешь.
      • 0
        Угу, я как раз выше об этом же вам и ответил :)
      • 0
        Параллелизм вообще штука противоречивая. Как хорошо заметил Кайт, распараллеливание — это способ максимизировать использование ресурсов системы. Получается, что оно нужно далеко не везде, а может, и наоборот навредить. Хотя для административных задач оно подходит хорошо.

        Возвращаясь к теме, мне кажется, что если в штатном порядке работы приложения возникает необходимость производить параллельные запросы к секциям таблиц, то, возможно, что-то не так в архитектуре БД, или же таблица секционирована как-то неоптимально. Мало приходит в голову случаев, когда это было бы необходимо.
        • 0
          Да, не везде, но как опция совсем не повредит. Надо сказать, что в базах, заточенных под анализ данных, распараллеливание какого-нибудь SELECT FROM GROUP BY по секциям очень полезно.
  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      Незачто:) Дождитесь только второй части, будет проще)
  • 0
    update1 — зря
    update2 — нет правда никто документацию не читает?
    • 0
      аа, вы сведете меня с ума:) но писать топик на английском не выход:)

      • 0
        забей
  • 0
    Спасибо за статью, жду вторую… Когда хотел сделать нормальное секционирование в одном из проектов, напугался описанных вами сложностей и написал скрипт, который каждый месяц создает новую таблицу, а старую переименовывает, добавляя номер месяца и года. Решение рабочее, правда приходится модифицировать запрос на выборку, учитывая желаемый пользователем диапазон дат.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.