Оптимизация данных - Разделенные таблицы

Работа с очень большими таблицами происходит медленнее, чем с небольшими. Потому полезно создавать так называемые разделенные таблицы. В таких таблицах данные физически размещаются в нескольких таблицах-потомках, но запросы на изменение/выборку данных обращаются только к одной родительской таблице. Для этого нужно создать одну родительскую таблицу, необходимое число наследующих от нее все поля (или не все, но это редкость) подтаблиц (дочерних таблиц) с указанными ограничениями и написать набор правил (rule). Например, можно создавать подтаблицу на каждый календарный месяц, тогда месячные отчеты будут обращаться лишь к одной таблице (бывают исключения, когда требуется обрабатывать и старые данные, но это уже повод создавать некоторые дополнительные таблицы и строить отчеты уже по ним). Особенно хорош этот путь в тех случаях, когда объем индексов превосходит объем самих данных. При запросе к разделенной таблице планировщик запроса обратится только к тем подтаблицам, в которых могут находиться нужные данные, соответственно и индексы будут прочитаны только те, которые созданы для выбранных подтаблиц.

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

Для того, чтобы использовать разделенные таблицы следует настроить следующий параметр в файле настроек нужного кластера PostgreSQL:

constraint_exclusion = on

В версии 8.1 этот параметр отключен (off), в версии 8.2 и выше не знаю, так что проверьте. После изменения значения параметра перезапустите кластер.

Вот пример родительской таблицы и 3 правила, которые работают при вставке данных в таблицу. Если в эту таблицу вставить данные за январь 2006-го года, сработает правило out_insert_out_yy06mm01 и данные будут записаны в таблицу data.out_yy06mm01. Для данных за февраль сработает правило out_insert_out_yy06mm01 и данные попадут в таблицу data.out_yy06mm01... Главное, чтоб существовали нужные правила на главную таблицу и нужные подтаблицы. Если подходящее правило не будет найдено, данные попадут в саму таблицу data."out", что скорее всего не входит в Ваши планы. Потому будьте внимательны!

CREATE TABLE data."out"
(
  save_date timestamp NOT NULL DEFAULT now(),
  user_id int4 NOT NULL DEFAULT 0,
  object_id int8 NOT NULL,
  question_id int8 NOT NULL,
  answer_id int8 NOT NULL,
  text text NOT NULL
) 
WITHOUT OIDS;

CREATE OR REPLACE RULE out_insert_out_yy06mm01 AS
ON INSERT TO data."out"
WHERE new.save_date >= '2006-01-01'::date::timestamp without time zone 
  AND new.save_date < ('2006-01-01'::date + '1 mon'::interval) 
DO INSTEAD  
  INSERT INTO data.out_yy06mm01 
    (save_date, user_id, object_id, question_id, answer_id, text) 
  VALUES 
    (new.save_date, new.user_id, new.object_id, new.question_id, 
      new.answer_id, new.text);

CREATE OR REPLACE RULE out_insert_out_yy06mm02 AS
ON INSERT TO data."out"
WHERE new.save_date >= '2006-02-01'::date::timestamp without time zone 
  AND new.save_date < ('2006-02-01'::date + '1 mon'::interval) 
DO INSTEAD  
  INSERT INTO data.out_yy06mm02 
    (save_date, user_id, object_id, question_id, answer_id, text) 
  VALUES 
    (new.save_date, new.user_id, new.object_id, new.question_id, 
      new.answer_id, new.text);

А вот определения подтаблиц:

CREATE TABLE data.out_yy06mm01
(
-- Inherited:   save_date timestamp NOT NULL DEFAULT now(),
-- Inherited:   user_id int4 NOT NULL DEFAULT 0,
-- Inherited:   object_id int8 NOT NULL,
-- Inherited:   question_id int8 NOT NULL,
-- Inherited:   answer_id int8 NOT NULL,
-- Inherited:   text text NOT NULL,
  CONSTRAINT "$1" CHECK (
    save_date >= '2006-01-01'::date::timestamp without time zone 
    AND 
    save_date < ('2006-01-01'::date + '1 mon'::interval))
) INHERITS (data."out") 
WITHOUT OIDS;

CREATE TABLE data.out_yy06mm02
(
-- Inherited:   save_date timestamp NOT NULL DEFAULT now(),
-- Inherited:   user_id int4 NOT NULL DEFAULT 0,
-- Inherited:   object_id int8 NOT NULL,
-- Inherited:   question_id int8 NOT NULL,
-- Inherited:   answer_id int8 NOT NULL,
-- Inherited:   text text NOT NULL,
  CONSTRAINT "$1" CHECK (
    save_date >= '2006-02-01'::date::timestamp without time zone 
    AND 
    save_date < ('2006-02-01'::date + '1 mon'::interval))
) INHERITS (data."out") 
WITHOUT OIDS;
ALTER TABLE data.out_yy06mm02 OWNER TO merch;

Индексы должны быть созданы и для главной таблицы и для всех подтаблиц.

Теперь Вы можете вставлять данные в таблицу data."out" и выбирать их из нее же (по умолчанию при операции SELECT также показываются данные из унаследованных таблиц).

Посмотрим план выполнения простого запроса:
explain select * from data.out where object_id=-1;

получим примерно такой результат:

Result  (cost=1.01..215.83 rows=3726 width=68)
  ->  Append  (cost=1.01..215.83 rows=3726 width=68)
        ->  Bitmap Heap Scan on "out"  (cost=1.01..8.62 rows=4 width=68)
              Recheck Cond: (object_id = -1)
              ->  Bitmap Index Scan on out_object_id_idx  
(cost=0.00..1.01 rows=4 width=0)
                    Index Cond: (object_id = -1)
        ->  Bitmap Heap Scan on out_yy06mm01 "out"  
(cost=1.01..8.62 rows=4 width=68)
              Recheck Cond: (object_id = -1)
              ->  Bitmap Index Scan on out_yy06mm01_object_id_idx  
(cost=0.00..1.01 rows=4 width=0)
                    Index Cond: (object_id = -1)
        ->  Bitmap Heap Scan on out_yy06mm02 "out"  
(cost=1.01..8.62 rows=4 width=68)
              Recheck Cond: (object_id = -1)
              ->  Bitmap Index Scan on out_yy06mm02_object_id_idx  
(cost=0.00..1.01 rows=4 width=0)
                    Index Cond: (object_id = -1)

Как видим, поиск идет по основной таблице и всем подтаблицам. Поскольку мы создали индексы по всем таблицам, везде видим Index Scan, что доказывает - все сделано правильно, при поиске используется индекс.

А теперь проверим, что при поиске по дате действительно будет выигрыш:
explain select * from data.out where save_date between '2006-01-01'::date and '2006-01-05'::date;

Result  (cost=1.02..17.29 rows=8 width=68)
  ->  Append  (cost=1.02..17.29 rows=8 width=68)
        ->  Bitmap Heap Scan on "out"  (cost=1.02..8.64 rows=4 width=68)
              Recheck Cond: ((save_date >= '2006-01-01'::date) 
AND (save_date <= '2006-01-05'::date))
              ->  Bitmap Index Scan on out_save_date_idx  
(cost=0.00..1.02 rows=4 width=0)
                    Index Cond: ((save_date >= '2006-01-01'::date) 
AND (save_date <= '2006-01-05'::date))
        ->  Bitmap Heap Scan on out_yy06mm01 "out"  
(cost=1.02..8.64 rows=4 width=68)
              Recheck Cond: ((save_date >= '2006-01-01'::date) 
AND (save_date <= '2006-01-05'::date))
              ->  Bitmap Index Scan on out_yy06mm01_save_date_idx  
(cost=0.00..1.02 rows=4 width=0)
                    Index Cond: ((save_date >= '2006-01-01'::date) 
AND (save_date <= '2006-01-05'::date))

Порядок, поиск идет только по главной таблице и по нужной дочерней, таблица data.out_yy06mm02 не затрагивается. При использовании десятков и сотен дочерних таблиц выигрыш огромный.

В том случае, если требуется запретить вставку данных в родительскую таблицу вне указанного для подтаблиц диапазона, можно добавить такие правила для родительской таблицы:

CREATE OR REPLACE RULE out_insert_out_before AS
ON INSERT TO data."out"
WHERE new.save_date < '2006-01-01'::date::timestamp without time zone
DO INSTEAD  
  SELECT elog('Попытка вставить данные вне допустимого диапазона');

CREATE OR REPLACE RULE out_insert_out_after AS
ON INSERT TO data."out"
WHERE new.save_date >= '2008-01-01'::date::timestamp without time zone
DO INSTEAD  
  SELECT elog('Попытка вставить данные вне допустимого диапазона');

Теперь данные, для которых нет подтаблицы будут игнорироваться с выдачей отладочного предупреждения в лог PostgreSQL. Команда

insert into data.out (object_id, user_id, question_id, answer_id, save_date, text) 
values (-1, -1, -1, -1, '2008-01-01'::timestamp, '');

вернет пустую строку, при этом в логе можно наблюдать запись

NOTICE: Попытка вставить данные вне допустимого диапазона

Здесь функция elog определена следующим образом:

CREATE OR REPLACE FUNCTION public.elog(text)
  RETURNS void AS
$BODY$
 
    elog NOTICE $1
 
$BODY$
  LANGUAGE 'pltcl' IMMUTABLE;

При этом обязанность проверять корректность создаваемых правил ложится на разработчика, будьте внимательны, при ошибке в правилах данные могут попасть не в ту таблицу или пропасть совсем.


Создание разделенных таблиц

Заметил в форумах нездоровую активность изобретателей велосипедов, которые предлагают всем желающим на этих изобретениях прокатиться. Потому выкладываю пример создания частей разделенной таблицы и соответствующих правил на эти части.

CREATE OR REPLACE FUNCTION data.build_partitional()
  RETURNS void AS
$BODY$
  set schema {data}
 
  set table_parent {out}
  # части данных, разбитые по месяцам
  foreach year {06 07} {
    foreach month {01 02 03 04 05 06 07 08 09 10 11 12} {
      set table  "${schema}.${table_parent}_yy${year}mm${month}"
      set name   "${table_parent}_yy${year}mm${month}"
      spi_exec "CREATE TABLE $table \
( CONSTRAINT \"\$1\" CHECK (save_date >= '20${year}-${month}-01'::date::timestamp \
  without time zone \
        AND save_date < ('20${year}-${month}-01'::date + '1 \
  month'::interval)::timestamp without time zone) \
) INHERITS (${schema}.${table_parent}) \
WITHOUT OIDS"
 
      spi_exec "CREATE OR REPLACE RULE out_insert_$name AS \
ON INSERT TO ${schema}.${table_parent} \
WHERE new.save_date >= '20${year}-${month}-01'::date::timestamp without time zone \
        AND new.save_date < ('20${year}-${month}-01'::date + '1 month'::interval)::timestamp without time zone \
DO INSTEAD  INSERT INTO $table (save_date, user_id, object_id, question_id, \
  answer_id, text) \
VALUES (new.save_date, new.user_id, new.object_id, new.question_id, \
new.answer_id, new.text)"
 
    }
  }
$BODY$
  LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;

После создания подтаблиц создать требуемые индексы можно следующей функцией.

CREATE OR REPLACE FUNCTION data.build_partitional_index()
  RETURNS void AS
$BODY$
  set schema {data}
 
  set table_parent {out}
  set table  "${schema}.${table_parent}"
  set name   "${table_parent}"
 
  # части данных, разбитые по месяцам
  foreach year {06 07} {
    foreach month {01 02 03 04 05 06 07 08 09 10 11 12} {
      set table  "${schema}.${table_parent}_yy${year}mm${month}"
      set name   "${table_parent}_yy${year}mm${month}"
 
      spi_exec "CREATE INDEX ${name}_answer_id_idx ON $table USING btree (answer_id)"
    }
  }
$BODY$
  LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;