Я создаю новую базу данных PostgreSQL 9, которая будет содержать миллионы (или, возможно, миллиарды) строк. Поэтому я решил разделить данные, используя наследование PostgreSQL.
Я создал главную таблицу вот так (упрощенно для примера) :
CREATE TABLE mytable
(
user_id integer,
year integer,
CONSTRAINT pk_mytable PRIMARY KEY (user_id, year)
);
И 10 таблиц разделов:
CREATE TABLE mytable_0 () INHERITS (mytable);
CREATE TABLE mytable_1 () INHERITS (mytable);
...
CREATE TABLE mytable_9 () INHERITS (mytable);
Я знаю, что доступ к строкам всегда будет осуществляться из приложения с использованием условия уникального user_id. Поэтому я хотел бы распределить данные "довольно" равномерно по 10 таблицам, используя правило, основанное на user_id.
Для настройки запросов к главной таблице моей первой идеей было использование ограничения проверки по модулю :
ALTER TABLE mytable_0 ADD CONSTRAINT mytable_user_id_check CHECK (user_id % 10 = 0);
ALTER TABLE mytable_1 ADD CONSTRAINT mytable_user_id_check CHECK (user_id % 10 = 1);
...
Проблема в том, что когда я запрашиваю главную таблицу "mytable" с условием на user_id, анализатор PostgreSQL проверяет все таблицы и не получает пользы от ограничения проверки:
EXPLAIN SELECT * FROM mytable WHERE user_id = 12345;
"Result (cost=0.00..152.69 rows=64 width=36)"
" -> Append (cost=0.00..152.69 rows=64 width=36)"
" -> Seq Scan on mytable (cost=0.00..25.38 rows=6 width=36)"
" Filter: (user_id = 12345)"
" -> Seq Scan on mytable_0 mytable (cost=0.00..1.29 rows=1 width=36)"
" Filter: (user_id = 12345)"
" -> Seq Scan on mytable_1 mytable (cost=0.00..1.52 rows=1 width=36)"
" Filter: (user_id = 12345)"
...
" -> Seq Scan on mytable_9 mytable (cost=0.00..1.52 rows=1 width=36)"
" Filter: (user_id = 12345)"
Тогда как если я использую классический CHECK CONSTRAINT, как этот (и перераздел, который соответствует этому правилу) :
ALTER TABLE mytable_0 ADD CONSTRAINT mytable_user_id_check CHECK (user_id BETWEEN 1 AND 10000);
ALTER TABLE mytable_1 ADD CONSTRAINT mytable_user_id_check CHECK (user_id BETWEEN 10001 AND 20000);
...
он будет сканировать только таблицы, которые соответствуют условию (mytable и mytable_1 в этом примере) :
"Result (cost=0.00..152.69 rows=64 width=36)"
" -> Append (cost=0.00..152.69 rows=64 width=36)"
" -> Seq Scan on mytable (cost=0.00..25.38 rows=6 width=36)"
" Filter: (user_id = 12345)"
" -> Seq Scan on mytable_1 mytable (cost=0.00..1.52 rows=1 width=36)"
" Filter: (user_id = 12345)"
Но использование такого ограничения проверки трудно поддерживать, потому что диапазон пользователей, которые будут заполнены в таблицах, будет меняться в течение многих лет. Сначала тысячи, а в ближайшем будущем, возможно, миллионы или больше...
Какое правило я могу использовать, чтобы разделить данные поровну на 10 таблиц, которые могут выиграть от проверочного ограничения, чтобы SELECT на главной таблице сканировал только нужную таблицу...?
Спасибо,
Нико