Postgre(no)SQL или снова о хранении данных с гибкой структурой

Когда вопрос заходит о хранении в БД гибких (заранее не известных, часто изменяемых) структур данных, разработчики обычно обращаются к «великому и ужасному» EAV-паттерну, либо к ныне модным NOSQL базам данных.
Не так давно такая задача стала и передо мной.
— EAV. Вызывает у меня стойкую неприязнь, да и сказано и написано об этом было очень много всего негативного (Кайт, Фаулер, Карвин, Горман). Главный минус в том, что при написании запросов приходится оперировать уже не реальными сущностями («Сотрудник», «Дом», «Клиент», то для чего и предназначен SQL), а объектами, орагнизованными на более низком уровне (извините за сумбур). Поэтому это был самый не желательный вариант.
— NOSQL. Поначалу очень заинтересовал этот вариант (в частности MongoDB). После продолжительного использования реляционок, первое время начинаешь испытывать чувство тотальной свободы, от которого захватывает дыхание. Хранение документов любой структуры, моментальное создание новых коллекций, запросы к ним — красота! Но после непродолжительного использования эйфория начала спадать, а проблемы обнаруживаться:
— Бедный язык запросов (ИМХО) + отсутствие джойнов;
— Отсутствие схем (хорошая статья недавно была на эту тему (и не только на эту) habrahabr.ru/post/164361/);
— Отсутствие встроенной поддержки ссылочной целостности;
— Отсутствие прибамбасов в виде хранимых процедур/функций, триггеров, представлений и многого другого.
— В моем приложении помимо данных с гибкой(изменяемой) структурой также необходимо хранить обычные статические данные — таблица пользователей, посещений, сотрудников и т.д. Работать с которыми (опять же имхо) гораздо проще и (самое главное) надежнее в обычной реляционной базе (та же самая ссылочная целостность и пр.).

Первую проблему (частично) я пытался решать с помощью ORM (это был Spring Data), он позволял писать сносные запросы к объектам, однако для этого нужно заранее создать и скомпилить все классы (соответствующие нужным коллекциям) и в запросах оперировать уже ими. Для меня это не подходило, т.к. коллекции должны создаваться и изменяться часто и оперативно — «на ходу».
Вторую — с помощью создания отдельной коллекции для хранения структур всех остальных коллекций, чтобы проверять корректность вводимых данных и т.д.
До решения остальных проблемм дело не дошло, бросил…
Уже на данном этапе моя база стала напоминать очень хрупкое сооружение, полностью зависящее от приложения, плюс я должен был реализовывать вручную многие вещи, которые большинство реляционок могут делать и так, из коробки. Может это и нормально, но как то не привык я к этому, как то не по себе стало.

Далее я задумался о том, как здорово было бы совместить реляционную и NOSQL СУБД. С одной стороны вся мощь реляционки со всеми прилагающимися, с другой — легкость и элегантность документоориентированного решения. И действительно, что мешает хранить объекты с гибкой структурой в некой отдельной специальной таблице (таблицах) например в формате xml, а обращаться к ним с помощью XPATH, тем более, что многие современные СУБД имеют развитые средства работы с XML (включая индексирование).
Решил попробовать на небольшом примере с использованием Postgresql, что из этого получится, как будут выглядеть запросы:

Для начала хватит двух служебных таблиц, думаю комментарии излишни:

CREATE TABLE classes
(
  id integer NOT NULL,
  name text,
  is_closed boolean,
  obects_count integer,
  CONSTRAINT classes_pk PRIMARY KEY (id )
);

CREATE TABLE objects
(
  id integer NOT NULL,
  body xml,
  id_classes integer,
  CONSTRAINT objects_pk PRIMARY KEY (id ),
  CONSTRAINT classes_objects FOREIGN KEY (id_classes)
      REFERENCES classes (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX fki_classes_objects
  ON objects
  USING btree
  (id_classes );

Создаем две сущности для экспериметнов:

INSERT INTO classes(
            id, name, is_closed, obects_count)
    VALUES (1, 'customers', FALSE, 0);

INSERT INTO classes(
            id, name, is_closed, obects_count)
    VALUES (2, 'orders', FALSE, 0);

Подготовим две функции для генерации тестовых случайных данных (взяты на просторах Интернета):

CREATE OR REPLACE FUNCTION random(numeric, numeric)
  RETURNS numeric AS
$BODY$
   SELECT ($1 + ($2 - $1) * random())::numeric;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION random_string(length integer)
  RETURNS text AS
$BODY$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Заполнение таблицы случайными данными, объектами классов «Клиент» и «Заказ» (связь один ко многим, каждый клиент сделал по пять заказов):

DO $$
DECLARE
	customer_pk integer;
	order_pk integer;
BEGIN
	FOR i in 1..10000 LOOP
		customer_pk := nextval('objects_id_seq');
		order_pk := nextval('objects_id_seq');

		insert into objects (body, id_classes) values((
		'<Customers>
			<Customer>
				<ID>' || customer_pk || '</ID>
				<Name>' || random_string('10') || '</Name>
				<Partners>' || random_string('10') || '</Partners>
			</Customer>
		</Customers>')::xml, 1);


		for j in 1..5 LOOP

			insert into objects (body, id_classes) values((
			'<Orders>
				<Order>
					<ID>' || order_pk || '</ID>
					<Customer_id>' || customer_pk || '</Customer_id>
					<Cost>' || random(1, 1000) || '</Cost>
				</Order>
			</Orders>')::xml, 2);

		end loop;

	END LOOP;
END$$;

Первым запросом выберем максимальную стоимость заказа:

explain select max(((xpath('/Orders/Order/Cost/text()', O.body))[1])::text::float) as cost_of_order
  from Objects O
 where O.id_classes = 2;

/*
Aggregate  (cost=2609.10..2609.11 rows=1 width=32)
  ->  Seq Scan on objects o  (cost=0.00..2104.50 rows=50460 width=32)
        Filter: (id_classes = 2)
*/

Запрос получился немного заковыристым, но все же вполне понятным: сразу понятно к какой сущности выпоняется запрос и по какому атрибуту. Как ни странно получился фулл скан, однако ничто не мешает построить индекс по атрибуту Cost:

create index obj_orders_cost_idx on objects using btree (((xpath('/Orders/Order/Cost/text()', body))[1]::text::float));

И теперь запрос отрабатывает горазд быстрее и использует индекс:

/*
Result  (cost=0.15..0.16 rows=1 width=0)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.15 rows=1 width=32)
          ->  Index Scan Backward using obj_orders_cost_idx on objects o  (cost=0.00..7246.26 rows=50207 width=32)
                Index Cond: ((((xpath('/Orders/Order/Cost/text()'::text, body, '{}'::text[]))[1])::text)::double precision IS NOT NULL)
                Filter: (id_classes = 2)
*/

Теперь попробуем выбрать информацию о заказах нескольких конкретных сотрудников, т.е. связку двух таблиц:

explain select (xpath('/Customers/Customer/Name/text()', C.body))[1] as customer
     , (xpath('/Orders/Order/Cost/text()', O.body))[1] as cost_of_order
  from objects C
     , objects O
 where C.id_classes = 1
   and O.id_classes = 2
   and (xpath('/Orders/Order/Customer_id/text()', O.body))[1]::text::int = (xpath('/Customers/Customer/ID/text()', C.body))[1]::text::int
   and ((xpath('/Customers/Customer/ID/text()' ,C.body))[1])::text::int between 1997585 and 1997595;

/*
Hash Join  (cost=1873.57..6504.85 rows=12867 width=64)
  Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer)
  ->  Seq Scan on objects o  (cost=0.00..2104.50 rows=50460 width=32)
        Filter: (id_classes = 2)
  ->  Hash  (cost=1872.93..1872.93 rows=51 width=32)
        ->  Bitmap Heap Scan on objects c  (cost=196.38..1872.93 rows=51 width=32)
              Recheck Cond: (id_classes = 1)
              Filter: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595))
              ->  Bitmap Index Scan on fki_classes_objects  (cost=0.00..196.37 rows=10140 width=0)
                    Index Cond: (id_classes = 1)
*/

Ожидаемый фуллскан, теперь слегка проиндексируем:

create index obj_customers_id_idx on objects using btree (((xpath('/Customers/Customer/ID/text()', body))[1]::text::int));
create index obj_orders_id_idx on objects using btree (((xpath('/Orders/Order/ID/text()', body))[1]::text::int));
create index obj_orders_customerid_idx on objects using btree (((xpath('/Orders/Order/Customer_id/text()', body))[1]::text::int));

Теперь получается веселее:

/*
Hash Join  (cost=380.52..5011.80 rows=12867 width=64)
  Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer)
  ->  Seq Scan on objects o  (cost=0.00..2104.50 rows=50460 width=32)
        Filter: (id_classes = 2)
  ->  Hash  (cost=379.88..379.88 rows=51 width=32)
        ->  Bitmap Heap Scan on objects c  (cost=204.00..379.88 rows=51 width=32)
              Recheck Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595) AND (id_classes = 1))
              ->  BitmapAnd  (cost=204.00..204.00 rows=51 width=0)
                    ->  Bitmap Index Scan on obj_customers_id_idx  (cost=0.00..7.35 rows=303 width=0)
                          Index Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595))
                    ->  Bitmap Index Scan on fki_classes_objects  (cost=0.00..196.37 rows=10140 width=0)
                          Index Cond: (id_classes = 1)
*/

Этот запрос так же не потерял своей наглядности, однако его можно еще больше причесать: разобраться с преобразованием типов, оптимизировать xml структуру и т.д. Работы еше много, это просто небольшой пример.

Что можно сделать еще:

1. Гибкий поиск по атрибутам объектов любых классов;
2. Таблицу objects можно партицировать (хотябы частично), например хранить объекты больших классов физически отдельно.

Хранение данных в БД в формате xml естествеено не является новинкой, однако при поиске решения данного моего вопроса информации об этом было очень мало, не говоря уже о конкретных примерах. Надеюсь кому нибудь пригодиться или(и) услышать в комментариях отзывы и мнения людей, поработавших с похожей схемой.

Автор: mgramin

Источник