Проектирование в PostgreSQL документо-ориентированного API: Находя искомое (Часть 3)

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

Лучший способ поиска документов

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

create table my_docs(
  id serial primary key,
  body jsonb not null,
  search tsvector,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
)

Поскольку у нас есть контроль над тем, как хранятся данные, мы можем написать свои собственные функции, чтобы извлекать эти данные многими веселыми способами! Все самое сложное позади (сохранение, обновление и т.д.) — теперь давайте повеселимся.

Извлекаем документ по ID

Каждый документ имеет поле id, связанное с ним полностью, благодаря функции save_document. Это все еще Postgres, поэтому каждому полю необходим первичный ключ и мы помещаем его внутрь самого документа. Я сделал свой integer'ом, но также можно использовать bigint снежинку Твиттера, если есть желание. На данный момент мы будем использовать тип serial.

Функция для этого достаточно прямолинейна:

create function find_document(tbl varchar, id int, out jsonb)
as $$
  //find by the id of the row
  var result = plv8.execute("select * from " + tbl + " where id=$1;",id);
  return result[0] ? result[0].body : null;

$$ language plv8;

select * from find_document('customers',20);

Это наипростейшая из всех возможных функция — она берет имя таблицы и ID, который необходимо найти и выполняет самый быстрый из всех запрос (который мы любим!): поиск по первичному ключу. Скорость: нам нравится.

Теперь давайте создадим одну для объемного запроса. Для этого я хочу вводить некий критерий и поучать назад первое совпадение. Это будет корректно работать, только если я буду сортировать результат, поэтому я добавляю ORDER BY и в качестве параметра по-умолчанию для него указываю ID:

create function find_document(
  tbl varchar, 
  criteria varchar, 
  orderby varchar default 'id'
)
returns jsonb
as $$
  var valid = JSON.parse(criteria); //this will throw if it invalid
  var results = plv8.execute("select body from " + 
                tbl + 
                " where body @> $1 order by body ->> '" + 
                orderby + "' limit 1;",criteria);
  return results[0] ? results[0].body : null
$$ language plv8;

select * from find_document('customers','{"last": "Conery"}', 'first');

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

Для Npgsql драйвера это не является проблемой. Для node_pg драйвера — это еще какая проблема. Поскольку я задаю значение по умолчанию для параметра orderby, некоторая путаница возникает при выборе, какую именно функцию запустить. Насколько я могу судить, node_pg драйвер заботится не типом аргументов функции, а только их количеством. Таким образом, если мы попробуем запустить функцию «поиска по id», упомянутую выше, то наша вторая функция выстрелит.

Вновь: у Npgsql (.NET драйвер) нет такой проблемы. Так что, если у вас есть проблемы, просто переименуйте одну из функций, или уберите значение по умолчанию для параметра.

Другое, на что мне хотелось бы обратить внимание, я задал для параметра criteria тип varchar/ Это было сделано, хоть технически и не правильно, но это делает API немного лучше. Если бы я задал его как jsonb, запрос бы пришлось выполнять в следующем виде:

select * from find_document('customers','{"last": "Conery"}'::jsonb, 'first');

Разница невелика, ведь мы будем использовать API преимущественно из кода (о чем пойдет речь в следующем посте).

Фильтрация

Давайте повторим то же самое, только для нескольких возвращаемых документов:

create function filter_documents(  
  tbl varchar, 
  criteria varchar, 
  orderby varchar default 'id'
)
returns setof jsonb
as $$
  var valid = JSON.parse(criteria);//this will throw if it invalid
  var results = plv8.execute("select body from " + 
                tbl + 
                " where body @> $1 order by body ->> '" + 
                orderby + 
                "'",criteria);

  var out = [];
  for(var i = 0;i < results.length; i++){
    out.push(results[i].body);
  }
  return out;
$$ language plv8;

select * from find_document('customer_docs','{"last": "Conery"}');

Это уже интереснее. Получаемый мной результат — это setof jsonb, это означает, что я должен вернуть некоторое количество строк jsonb. Не совсем понятно как это сделать с помощью PLV8, и может быть лучший способ, нежели мой — но это то, в чем я уверен что оно работает.

Как только я получаю результат (строки из нашей документо-ориентированной таблицы), необходимо запустить цикл, который будет брать и вставлять тело jsonb поля в массив, который я потом верну.

Все это работает потому, что поле body это jsonb, который в свою очередь является текстом. Это не javascript объект, потому что если бы он им был, то я получил бы ошибку (старая [Object object] парсинговая глупость).

SQL инъекции

Многие из вас заметят, что параметр orderby здесь конкатенируется непосредственно внутрь. Если вы позволяете своим пользователям писать SQL в вашу базу данных, то да, это проблема. Но, с надеждой, вы будете выполнять эту функцию из драйвера, который параметризирует ваши запросы за Вас, примерно до такого вида:

db.filter("customers", {
  last : "Conery", 
  orderBy : "a';DROP TABLE test; SELECT * FROM users WHERE 't' = 't"
}, function(err, res){
  console.log(err);
  console.log(res);
});

… не будет работать. Почему нет? Потому что в идеале вы делаете что-то такого вида:

select * from filter_documents($1, $2, $3);

Если же нет, вы получаете то, чего заслуживаете :).

Полнотекстовый поиск

Давайте закончим полнотекстовым поиском по нашим документам, как и следует. Это моя любимая часть:

create function search_documents(tbl varchar, query varchar)
returns setof jsonb
as $$
  var sql = "select body, ts_rank_cd(search,to_tsquery($1)) as rank from " + 
             tbl + 
            " where search @@ to_tsquery($1) " + 
            " order by rank desc;"

  var results = plv8.execute(sql,query);
  var out = [];
  for(var i = 0; i < results.length; i++){
    out.push(results[i].body);
  }
  return out;
$$ language plv8;

select * from search_documents('customers', 'jolene');

Это все довольно просто, если знать как работает индексирование для полнотекстового поиска в Postgres. Здесь мы просто работаем с search полем (которое индексировано GIN индексом для скорости), которое мы обновляем при каждом сохранении. Этот запрос быстр как молния и очень легок в обращении.

Делая индексы более гибкими

В двух функциях, которые принимают критерий (поиск и фильтрация), я использую оператор «содержания». Это маленький символ @>.

Этот оператор специфичен для jsonb и позволяет нам использовать GIN индекс на поле body. Этот индекс выглядит следующим образом:

create index idx_customers on customers using GIN(body jsonb_path_ops);

Особую пикантность здесь придает jsonb_path_ops. Он сообщает индексатору оптимизироваться для операций «содержания» jsonb (фактически: содержится ли этот кусок jsonb в другом куске jsonb). Это означает что индекс быстрее и меньше.

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

Скорость чтения и записи

Если Вы возьмете один PostgreSQL сервер против одного MongoDB сервера, MongoDB будет выглядеть намного глупее и Postgres будет курить почти на каждой метрике. Это связано с тем, что Postgres был так разработан — «масштабируемая» база данных.

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

• Индексирование в Postgres замедляет. Так что, если это рушит Вашу производительность записи, Вам следует настроить индекс только на то, что вы хотите индексировать (указывая (body -> my_field)) при задании GIN индекса
• Если вы обращаетесь к чему-то очень часто (например адрес электронной почты), просто скопируйте его в отдельную колонку и сделайте его UNIQUE Вы можете обрабатывать синхронизацию в коде, или в отдельной функции.

В следующей статье я погружусь в способы вызвать этот материал из кода!