В первой и второй частях этой короткой серии статей я показал различные способы сохранение документа и последующие обновление поля поиска .
Кроме того, я показал, как сохранять множество документов транзакционно.
В этой статье я рассмотрю варианты выполнения запросов.
Лучший способ поиска документов
В первой части мы создали таблицу, которая выглядит следующим образом:Поскольку у нас есть контроль над тем, как хранятся данные, мы можем написать собственные функции для получения этих данных разными интересными способами! Вся тяжелая работа окончена (сохранение, обновление и т. д.) — теперь давайте немного повеселимся.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() )
Получение документа по идентификатору
В каждом документе есть поле идентификатор связан с ним полностью, благодаря функции сохранить_документ .Это по-прежнему Postgres, поэтому каждому полю нужен первичный ключ, и мы помещаем его внутрь самого документа.
Я сделал свое целое число, но вы также можете использовать bigint Твиттер снежинка , если хотите.
Сейчас мы будем использовать тип сериал .
Функция для этого довольно проста: 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 в качестве параметра по умолчанию: 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');
Более того, мы можем ожидать странного поведения в зависимости от используемого нами драйвера.
Первое, что хотелось бы отметить, это то, что я перегружаю функцию найти_документ потому что Postgres позволяет мне это сделать.
Это, в свою очередь, означает, что единственная разница между нашей исходной функцией, которая выполняет поиск по идентификатору, и этой функцией — это список аргументов.
Это не проблема для драйвера Npgsql. Для драйвера node_pg это еще одна проблема.
Поскольку я устанавливаю значение по умолчанию для параметра Сортировать по , возникает некоторая путаница при выборе функции для запуска.
Насколько я могу судить, драйвер node_pg не заботится о типе аргументов функции.
но только по их количеству .
Таким образом, если мы попытаемся запустить упомянутую выше функцию «поиск по идентификатору», то сработает наша вторая функция.
Еще раз: Npgsql (драйвер .
NET) не имеет этой проблемы.
Поэтому если у вас возникли проблемы, просто переименуйте одну из функций или удалите значение параметра по умолчанию.
Еще я хотел бы отметить, что я установил параметр критерии тип варчар / Это сделано, хоть и технически не правильно, но делает 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"}');
Это уже интереснее.
Результат, который я получаю, набор jsonb , это означает, что мне нужно вернуть некоторое количество строк jsonb .
Не совсем понятно, как это сделать с помощью PLV8, и может быть есть способ лучше моего — но я уверен, что именно этот сработает. Как только я получу результат (строки из нашей документо-ориентированной таблицы), мне нужно запустить цикл, который возьмет и вставит тело jsonb поля в массив, который я затем верну.
Все это работает, потому что поле body — это jsonb, которое, в свою очередь, является текстом.
Это не объект javascript, потому что если бы это было так, я бы получил ошибку (старая глупость анализа [объекта объекта]).
SQL-инъекция
Многие из вас заметят, что параметр Сортировать по здесь он объединяется непосредственно внутри.Если вы разрешаете пользователям писать 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. Здесь мы просто работаем с поиск поле (которое индексируется индексом GIN для скорости), которое мы обновляем при каждом сохранении.
Этот запрос выполняется молниеносно и очень прост в использовании.
Делаем индексы более гибкими
В двух функциях, принимающих критерий (поиск и фильтр), я использую оператор «содержание» .Это маленький символ @> .
Этот оператор специфичен для jsonb и позволяет нам использовать индекс GIN в поле тело .
Этот индекс выглядит следующим образом: 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 происходит медленно.
Поэтому, если это снижает производительность записи, вам следует установить индекс только для того, что вы хотите индексировать (указав (тело -> мое_поле) ) при указании индекса GIN
- Если вы часто обращаетесь к чему-то (например, к адресу электронной почты), просто скопируйте это в отдельный столбец и сделайте УНИКАЛЬНЫЙ Синхронизацию можно выполнить в коде или в отдельной функции.
-
Композитные Материалы
19 Oct, 24 -
Эволюция Аналитической Инфраструктуры
19 Oct, 24
