Здравствуйте, уважаемые читатели Хабра!
Это вторая часть (первая здесь) о создании основного функционала MVP (Minimum Value Product) системы по управлению цифровыми активами для базы данных PostGIS. Полный перечень возможностей разрабатываемого проекта представлен на картинке ниже.
В этой публикации рассмотрим применение трёх видов поиска по тексту в PostgreSQL:
Классический
Полнотекстовый
Семантический
Содержание
Введение
В предыдущей части был разобран функционал для работы с геометрией объектов. Но так как у записей в таблице есть ещё содержащие полезную информацию числовые и текстовые поля, то мы не можем просто так отбросить их. Для полноценного нахождения объектов необходимо осуществить поиск по всем полям.
Напомню, что в базе данных находятся более 1200 никак несвязанных между собой таблиц с суммарным кол-вом 300 млн. записей.
Ещё раз посмотрим на произвольную таблицу. У неё есть как числовые, так и текстовые поля; а также геометрическое поле, представленное в WKB-формате.
Что имеем:
Таблицы никак не связаны друг с другом
Нет уникального ключа в таблицах, по которому можно точно определить объект. То есть не получится всегда находить два одинаковых объекта в двух разных таблицах по даже какому-нибудь составному ключу.
Колонок в таблице может быть много и их наименования могут меняться.
Учитывая всё вышеперечисленное, приходим к выводу, что поиск по отдельным колонкам или по их группам является плохой затеей. Но что, если объединить все значения колонок за исключением геометрического, в одно текстовое поле? Тогда получим возможность производить поиск объектов по этому собранному полю. И причём нам уже не придётся знать имена многочисленных столбцов. Таким образом, мы абстрагируемся от числовых и текстовых наименований колонок, заменяя их одним объединённым текстовым полем.
Ниже приведён SQL-запрос для объединения значений колонок:
SELECT CONCAT_WS(' ', COALESCE(ogc_fid::TEXT, ''), COALESCE(orbis_id::TEXT, ''), COALESCE(num_utv_do::TEXT, ''), COALESCE(name_ter_s::TEXT, ''), COALESCE(ogc_fid::TEXT, ''), -- ... COALESCE(shape_leng::TEXT, '') ) as text FROM _1072._4035;
Здесь функция COALESCE преобразует каждое поле в TEXT , но, если оно равно NULL, подставляет пустую строку. CONCAT_WS - объединяет все значения через пробел.
Кстати, геометрическое поле также можно преобразовать в текст (WKT, Well-Known Text). Интересной выглядит идея с поиском подстрок и подсчётом расстояния Дамерау-Левенштейна. Однако, будем работать с текстом и геометрией отдельно.
Получившиеся в результате преобразований данные можно загрузить во внешнюю векторную или полнотекстовую БД. Например, движок Elasticsearch с алгоритмами BM-25 и TF-IDF отлично справится с полнотекстовым поиском. Однако, такой подход как минимум потребует учёта согласованности данных двух БД. К счастью, в PostrgeSQL есть инструменты для поиска по тексту. Далее воспользуемся этим и рассмотрим их применение на практике.
Создание таблиц для текстовых поисковых запросов
Таблица text_table для классического поиска
Создадим таблицу с именем text_table.
CREATE TABLE text_table ( schema_name text, table_name text, id TEXT PRIMARY KEY, text TEXT );
Здесь:
schema_name – имя схемы;
table_name – имя таблицы;
id – первичный ключ в schema_name.table_name;
text – объединённые значения числовых и текстовых колонок;
Пример вставки одной записи в таблицу:
INSERT INTO text_table (schema_name, table_name, id, text) VALUES ('osnova_marketinga_2024', 'airport_poi', '1', '5 5 Жуковский);
Заполним таблицу данными из исходных таблиц:
Никаких дальнейших преобразований с полем text производить не будем. Рассмотрим применяемые операторы и возможные с ними поисковые запросы.
Операторы LIKE и ILIKE
Оба оператора используются для поиска строк, соответствующих заданному текстовому шаблону (паттерну). Они работают только с символьными типами данных (например, TEXT, VARCHAR, CHAR).
Вместо точного значения они используют шаблон, который может содержать специальные символы-заполнители (wildcards):
% (знак процента) — соответствует любой последовательности из нуля или более символов.
_ (нижнее подчеркивание) — соответствует ровно одному любому символу.
LIKE и ILIKE — это инструменты для нечеткого (паттернного) поиска в текстовых полях. LIKE проводит поиск с учетом регистра, а ILIKE — без учета регистра. Они незаменимы при фильтрации данных, когда нужно найти частичные совпадения, а не точное равенство строк.
Примеры:
Давайте найдём все объекты, в которых встречается название района "Южное Бутово"
SELECT * FROM text_tabel WHERE text LIKE '%Южное Бутово%' -- WHERE text ILIKE '%Южное Бутово%'
ILIKE по сравнению с LIKE нашёл дополнительный объект, в текстовом поле которого искомая строка была написана целиком в верхнем регистре.
POSIX-регулярные выражения
Операторы ~, ~, !~, !~ в SQL — это операторы для сопоставления строк с POSIX-регулярными выражениями. Они работают только с символьными типами данных (TEXT, VARCHAR и т.д.).
В качестве правого аргумента оператор принимает строку, содержащую регулярное выражение POSIX. Это выражение может включать:
Специальные символы (., *, +, ?, ^, $)
Классы символов ([0-9], [A-Za-z])
Группировку и альтернативы (|)
Квантификаторы ({n,m}) и многое другое
Различие между операторами
~— проверяет, соответствует ли строка регулярному выражению. Чувствителен к регистру.~*— проверяет, соответствует ли строка регулярному выражению. Без учёта регистра.!~— проверяет, НЕ соответствует ли строка регулярному выражению. Чувствителен к регистру.!~*— проверяет, НЕ соответствует ли строка регулярному выражению. Без учёта регистра.
Пример:
Найдем все строки, в которых есть упоминание “бутово”, но нет “южного” и “северного”.
SELECT * FROM text_tabel WHERE text ~* '^(?!.*(южн|северн)).*бутово'
Здесь ^ — начало строки; (?!.*(южн|северн)) — отрицательная опережающая проверка: "не должно быть в любом месте строки "южн" ИЛИ "северн""; .*бутово — затем где-то в строке есть "бутово"
Создание индекса
Для ускорения не только LIKE/ILIKE с любыми шаблонами, но и некоторых регулярных выражений, существуют специальные Trigram Indexes (индексы по триграммам). Для их использования необходимо включить расширение pg_trgm:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
Этот тип индекса разбивает текст на триграммы - последовательности из 3-х символов, и идеально подходит для нечёткого поиска и поиска по подстрокам. Например, слово "тест" разбивается на триграммы: " т", " те", "тес", "ес", "ст", "ст ". PostgreSQL добавляет пробелы в начале и конце для учета границ слов.
Навесим индекс с оператором gin_trgm_ops типа GIN или GIST.
CREATE INDEX ON text_table USING GIN (text gin_trgm_ops);
На Python создание индекса можно реализовать в виде следующего метода класса:
def create_index(self, index_type='gin', operator_type='gin_trgm_ops') -> None: """ Создаёт поисковый индекс для поля text :param index_type: тип индекса :param operator_type: тип оператора :return None """ operator_types = ('gin_trgm_ops',) if operator_type not in operator_types: raise Exception(f'Неверно указан операторный класс для поиска по тексту! Выберите из {operator_types}') index_types = ('gin', 'gist') if index_type not in index_types: raise Exception(f'Неверно указан тип индекса! Выберите из {index_types}') sql_query = f"""CREATE INDEX ON "{self.schema_name}"."{self.table_name}" USING {index_type} (text {operator_type});""" self.data_getter_obj.execute_query(sql_query)
Таблица embedding_table для векторизованного поиска
В таблице embedding_table будут храниться векторизованные представления, вычисленные на основе поля text таблицы text_table.
Векторное представление (векторное вложение слов, вложение слов или эмбеддинги) — общее название для различных подходов к моделированию языка и обучению представлений в обработке естественного языка, направленных на сопоставление словам (и, возможно, фразам) из некоторого словаря векторов из Rn для
n, значительно меньшего количества слов в словаре. Подробнее здесь.
CREATE TABLE embedding_table ( schema_name text, table_name text, id TEXT PRIMARY KEY, embedding VECTOR(384) );
В дальнейшей работе понадобится расширение pgvector. Оно предоставляет тип VECTOR с требуемыми для поиска операторами. Команда для установки:
CREATE EXTENSION IF NOT EXISTS vector;
Первые три колонки в таблице embedding_table такие же, как и в text_table, а значение для колонки embedding вычисляется с помощью следующей команды на Python:
embedding = self.sentence_model.encode(text)
где
from sentence_transformers import SentenceTransformer self.sentence_model = SentenceTransformer(llm_model_path)
и llm_model_path – путь к сохранённой LLM модели.
Также следует указать размерность вектора (384 в нашем случае), выдаваемого языковой моделью.
Различные языковые модели можно найти и скачать для локального использования из репозитория Hugging Face. В таком случае для использования LLM не понадобится интернет-соединение со сторонним сервисом. Это позволит бесплатно и конфиденциально (не отправляем свои ценные данные во вне) применять LLM. В нашем случае была выбрана мультиязычная модель paraphrase-multilingual-MiniLM-L12-v2.
Таким образом, вычисляем на бэкенде эмбеддинги от поля text таблицы text_table и добавляем их в таблицу embedding_table.
INSERT INTO embedding_table (schema_name, table_name, id, embedding) VALUES ('osnova_marketinga_2024', 'background', '3', ARRAY [0.16436727, -0.10393087,...])
Реализация на Python методов класса таблицы embedding_table для её заполнения
def insert_rows_from_table(self, schema_name: str, table_name: str, object_id_name: str = 'id') -> None: """ Добавляет строки в таблицу на основе значений строк таблицы schema_name.table_name :param schema_name: имя схемы :param table_name: имя таблицы :return None """ # в цикле вычисляем эмбеддинг и получаем идентификатор объекта for embedding, object_id_name in zip( self.get_row_embedding_from_table(schema_name, table_name), self.data_getter_obj.get_column_value(schema_name, table_name, object_id_name)): object_id = str(object_id_name) embedding = self.correct_embedding_for_sql(embedding) # втсавляем запись в таблицу self.insert_one_row(schema_name, table_name, object_id, embedding) def correct_embedding_for_sql(self, embedding: list) -> str: """ Возвращает в виде строки эмбеддинг в правильном формате для записи в БД. :param embedding - входной эмбеддинг :return str """ embedding_str = ', '.join([str(float(x)) for x in embedding]) return embedding_str def insert_one_row(self, schema_name: str, table_name: str, object_id: str, embedding: list) -> None: """ Добавляет одну строку в таблицу :param schema_name: имя схемы объекта :param table_name: имя таблицы объекта :param object_id: значение идентификатора объекта :param embedding: эмбеддинг :return None """ sql_query = f'''INSERT INTO "{self.schema_name}"."{self.table_name}" (schema_name, table_name, id, embedding) VALUES ('{schema_name}', '{table_name}', '{object_id}', ARRAY [{embedding}])''' self.data_getter_obj.execute_query(sql_query)
Варианты векторного поиска
В pgvector поддерживаются три основные метрики сравнения векторов:
L2 (евклидово расстояние, vector_l2_ops). Классическое расстояние между двумя точками в многомерном пространстве. Хорошо работает, если векторы не нормализованы и распределение значений относительно равномерное.
Cosine similarity (косинусное сходство, vector_cosine_ops). В отличие от предыдущей метрики измеряет не абсолютное расстояние, а угол между двумя векторами. Подходит для текстовых эмбеддингов, где важна направленность, а не масштаб. Требует нормализации векторов.
Inner product (внутреннее произведение, vector_ip_ops). Скалярное произведение двух векторов. Может использоваться в задачах ранжирования.
Универсальный оператор <-> будет осуществлять поиск по той метрике, на основе которой построен индекс.
Создание индекса
Создадим индекс для поля embedding. Выбрать можно из типов операторов:'vector_l2_ops', 'vector_cosine_ops', 'vector_ip_ops'; и типов индексов 'ivfflat', 'hnsw'.
CREATE INDEX ON embedding_table USING ivfflat (embedding vector_l2_ops);
Реализация на Python метода класса по созданию индекса
def create_index(self, index_type='ivfflat', operator_type='vector_l2_ops') ->None: """ Создаёт индекс для поиска по полю embedding :param index_type: тип индекса :param operator_type: тип оператора :return None """ operator_types = ('vector_l2_ops', ' vector_cosine_ops', 'vector_ip_ops') if operator_type not in operator_types: raise Exception(f'Неверно указан операторный класс для сравнения векторов! Выберите из {operator_types}') index_types = ('ivfflat', 'hnsw') if index_type not in index_types: raise Exception(f'Неверно указан тип индекса! Выберите из {index_types}') sql_query = f"""CREATE INDEX ON "{self.schema_name}"."{self.table_name}" USING {index_type} (embedding {operator_type});""" self.data_getter_obj.execute_query(sql_query)
Напишем две функции для поиска объектов по эмбеддингам.
Функция поиска топ наиболее похожих объектов
Найдём топ N (N = 10, например) наиболее похожих по смыслу описаний объектов на: "Территориальная схема развития территории Новомосковского административного округа города Москвы «Солнцево-Бутово-Видное» на участке от Бутово"
Векторизованное представление текста получим через backend и вставим в следующий sql-запрос.
В результате найденные объекты отранжированы по возрастанию меры близости (distance) - самые похожие находятся наверху.
Реализация на Python
def find_top_similar_objects(self, text:str, top_n: int = 10) -> List[Tuple]: """ Функция для нахождения топ-N наиболее похожих объектов по тексту :param text: входная строка текста :param top_n: кол-во топ результатов :return: список строк """ embedding = self.get_embedding_from_text(text) embedding_str = self.correct_embedding_for_sql(embedding) sql_query = f""" SELECT *, embedding <-> '[{embedding_str}]' AS distance FROM "{self.schema_name}"."{self.table_name}" ORDER BY embedding <-> '[{embedding_str}]' LIMIT {top_n};""" query_result = self.data_getter_obj.get_query_result(sql_query) return query_result
Функция поиска наиболее ближайших по расстоянию объектов
Аналогично предыдущему примеру выберем те подходящие объекты, векторные представления которых находятся на не более чем заданном расстоянии от искомого эмбеддинга.
Реализация на Python
def find_similar_objects_by_distance(self, text: str, distance: float = 2.0) -> List[Tuple]: """ Функция для нахождения наиболее похожих по тексту объектов, находящихся на расстоянии не более чем distance :param text: входная строка текста :param distance: расстояние/дистанция :return список строк """ embedding = self.get_embedding_from_text(text) embedding_str = self.correct_embedding_for_sql(embedding) sql_query = f""" WITH subquery AS ( SELECT *, embedding <-> '[{embedding_str}]' AS distance FROM "{self.schema_name}"."{self.table_name}" ORDER BY embedding <-> '[{embedding_str}]' ) SELECT * FROM subquery WHERE distance <= {distance}"""" query_result = self.data_getter_obj.get_query_result(sql_query) return query_result
Таблица indexed_text_table для полнотекстового поиска
И теперь рассмотрим создание и заполнение таблицы indexed_text_table, предназначенной для полнотекстового поиска.
CREATE TABLE indexed_text_table ( schema_name TEXT, table_name TEXT, id TEXT, indexed_text TSVECTOR );
Первые три колонки те же самые что и у двух предыдущих таблиц, а поле indexed_text формируется на основе поля text таблицы text_table посредством оператора ts_vector.
INSERT INTO indexed_text_table (schema_name, table_name, id, indexed_text) VALUES ('osnova_marketinga_2024', 'airport_poi', '1', to_tsvector('russian', '1 1 Шереметьево');
Реализация заполнения таблицы на Python
def insert_one_row(self, schema_name: str, table_name: str, object_id: str, text: str)->None: """ Добавляет одну строку объекта в таблицу :param schema_name: имя схемы :param table_name: имя таблицы :param object_id: значение идентификатора объекта :param text: входная строка текста """ sql_query = f'''INSERT INTO "{self.schema_name}"."{self.table_name}" (schema_name, table_name, id, indexed_text) VALUES ('{schema_name}', '{table_name}', '{object_id}', to_tsvector('{self.configuration}', '{text}'))''' self.data_getter_obj.execute_query(sql_query)
В функции to_tsvector требуется указать необходимую конфигурацию для индексирования текста: simple, russian, english и т.д. От неё зависит как будет разбиваться текст на слова, как они будут нормализовываться и какие слова буду проигнорированы. Сперва текст режется на токены, приводятся к нижнему регистру. Например, из фразы "Несколько котиков сидят на лавке" получим следующий набор токенов: "несколько", "котиков", "сидят", "на", "лавке". Затем каждое слово прогоняется через стеммер - алгоритм, который обрезает окончания и суффиксы слов, сводя их к общей основе (стему), чтобы разные формы одного слова считались одинаковыми при поиске: котиков → кот. Стоп-слова (союзы, предлоги, частицы, местоимения. и, в, на и т.д.) просто выбрасываются. На выходе получается отсортированный список уникальных лексем с их позициями в тексте. И если проводить аналогию, то инвертированный индекс — это цифровая версия предметного указателя в конце обычной бумажной книги.
Пример:
Напишем поисковой запрос для нахождения объектов, в описании которых есть упоминания о поликлиниках или больницах. Отранжируем результат, чтобы наиболее релевантные строки оказались наверху в поисковой выдаче. И добавим колонку с исходным текстом.
ts_rank и ts_rank_cd — это функции ранжирования в PostgreSQL для полнотекстового поиска. Они оценивают релевантность документа поисковому запросу.
ts_rank- оценивает частоту встречаемости ключевых слов из запроса в документе.ts_rank_cd- оценивает "плотность" найденных ключевых слов, т.е. как близко друг к другу они находятся в документе.
Реализация на Python
def find_top_similar_objects(self, query: str, top_n:int = 10, rank_function='ts_rank')->None: """ Функция для нахождения топ N наиболее похожих объектов по тексту :param query: входной запрос для поиска объектов :param top_n: кол-во топ результатов :param rank_function: функция для ранжирования результатов ("ts_rank", "ts_rank_cd") :return список строк """ rank_functions = ("ts_rank", "ts_rank_cd") if rank_function not in rank_functions: raise Exception(f'Неверно указана функция ранжирования для поиска! Выберите из {rank_functions}') sql_query = f"""SELECT *, {rank_function}(indexed_text, to_tsquery('{self.configuration}', '{query}')) as rank FROM "{self.schema_name}"."{self.table_name}" WHERE indexed_text @@ to_tsquery('{self.configuration}','{query}') ORDER BY rank DESC LIMIT {top_n}""" query_result = self.data_getter_obj.get_query_result(sql_query) return query_result
Создание индекса
И навесим индекс на поле indexed_text для оптимизации типа gin или rum
CREATE INDEX ON indexed_text_table USING GIN (indexed_text); -- CREATE INDEX ON indexed_text_table USING RUM (indexed_text rum_tsvector_ops);
Реализация на Python
def create_index(self, index_type='gin')->None: """ Создаёт индекс для поиска по полю indexed_text :param index_type: тип индекса :return:None """ index_types = ("gin", "rum") if index_type not in index_types: raise Exception(f'Неверно указан тип индекса! Выберите из {index_types}') if index_type == "gin": sql_query = f"""CREATE INDEX ON "{self.schema_name}"."{self.table_name}" USING rum (indexed_text rum_tsvector_ops);""" else: sql_query = f"""CREATE INDEX ON "{self.schema_name}"."{self.table_name}" USING gin (indexed_text);""" self.data_getter_obj.execute_query(sql_query)
Заключение
В итоге создаём три таблицы:
Для классического поиска таблицу text_table. Она хранит объединённый текст. В ней колонка text типа TEXT, содержит значение объединённых колонок объектов. По text будет производиться поиск с помощью операторов LIKE, ILIKE и POSIX-регулярных выражений. Это поле схоже с типом wildcard в Elasticsearch.
Для смыслового поиска таблицу embedding_table. Она хранит эмбеддинги. В ней колонка embedding типа VECTOR, содержит векторизованные представления поля text таблицы text_table. В нашей реализации по embedding будет производиться поиск с помощью универсального оператора
<->,который сравнивает вектора в зависимости от выбранной метрики.Для полнотекстового поиска таблицу text_indexed_table. Она хранит индексированное представление. В ней будет колонка indexed_text типа TSVECTOR, содержащая токены с их позициями, извлеченными из поля text таблицы text_table. По indexed_text будет производиться поиск с помощью оператора to_tsquery. Процессы индексирования и поиска аналогичны работе с полнотекстовой поисковой базой данной Elasticsearch, где используются классические алгоритмы ранжирования BM-25 и TF-IDF.
Таким образом, мы можем находить одинаковые и похожие объекты как по геометрии, так и по тексту.
В следующей части рассмотрим как с помощью тематического моделирования провести анализ текстовых данных и визуализировать семантические связи между таблицами.
Спасибо за внимание!
Ссылки, источники и полезные материалы
Статья с практическими примерами использования регулярных выражений в PostgreSQL
Хорошая статья про pgvector в PostrgreSQL
Ещё одна статья про векторный поиск и эмбеддинги
Документация по pgvector
Статья про полнотекстовый поиск
Документация про полнотекстовый поиск в PostrgreSQL
