[Перевод] Проектирование в 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 Вы можете обрабатывать синхронизацию в коде, или в отдельной функции.
В следующей статье я погружусь в способы вызвать этот материал из кода!
