В частях первой и второй этой небольшой серии статей, я показал различные способы сохранения документа и последующего обновления его поискового поля. Кроме того, я показал как транзакционно сохранять множество документов. В этой статье я буду рассматривать варианты выполнения запросов.
Лучший способ поиска документов
В первой части мы создали таблицу, которая выглядит следующим образом:
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 Вы можете обрабатывать синхронизацию в коде, или в отдельной функции.
В следующей статье я погружусь в способы вызвать этот материал из кода!
ссылка на оригинал статьи http://habrahabr.ru/post/272545/
Добавить комментарий