Налаштовуємо повнотекстовий пошук в PostgreSQL та SQLAlchemy

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Ви використовуете PostgreSQL та SQLAlchemy на проєкті, але ще не встигли розібратись, які є built in можливості у PostgreSQL для фільтрації записів по текстових полях? Тоді Ви в правильному місці!

Привіт, мене звуть Марк, я Back-end Engineer в компанії Welltech і сьогодні я розповім дещо про Text Search в PostgreSQL з використанням SQLAlchemy.

У цій статті ми розглянемо різні способи фільтрації і пошуку користувачів в базі даних, використовуючи SQLAlchemy та PostgreSQL. Ми почнемо з простих прикладів фільтрації, а потім перейдемо до гнучкіших запитів з використанням можливостей pg_trgm та tsvector.

Основи фільтрації по текстових полях

Версії використані в прикладах PostgreSQL 14 та SQLAlchemy 1.4.

Уявімо, що у вашому проєкті є така супер проста таблиця:


class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)  # first name + last name
    bio = Column(String)

Якщо вам потрібно знайти всіх користувачів за іменем та прізвищем, і ви точно знаєте, що в стовпчику name ім’я завжди йде перед прізвищем, то немає нічого простішого за такий запит:


session.query(Users).filter(Users.name == "Mary Hamilton")

Поки що виглядає занадто просто, щоб продовжувати читати цю статтю далі, але зачекайте, ми будемо поступово ускладнювати наші вимоги до фільтрації.

Наприклад, якщо стоїть трохи складніша задача — знайти всіх користувачів з відповідним іменем, тобто, не зважаючи на прізвище, — тоді на допомогу приходить оператор LIKE. І наш запит тепер виглядає так:


session.query(Users).filter(Users.name.like("Mary%"))
Або така версія, якщо ім’я може бути після прізвища:

session.query(Users).filter(Users.name.like("%Mary%"))

І звичайно, можна використати ILIKE, якщо ми не знаємо, в якому регістрі записані імена:


session.query(Users).filter(Users.name.ilike("%Mary%"))

Майте на увазі, що використання ILIKE без правильного індексу може бути значно повільнішим, ніж простий LIKE, що може бути відчутно на великих об’ємах даних. Тому якщо ви з якихось причин не можете побудувати індекс і у вашій таблиці декілька мільйонів записів, то подумайте про приведення search term та оригінального тексту до нижнього регістру при збереженні в БД.

Модуль pg_trgm

Модуль pg_trgm у PostgreSQL — це розширення, яке дозволяє виконувати пошук за допомогою триграм. Триграми — це послідовності з трьох символів, на які розбивається текст. Наприклад слово «apple» буде розбито на такі триграми [" a", " ap«, «app», «ppl», «ple», «le »]. В такому вигляді PostgreSQL може порівняти те, що ви шукаєте (search term) з даним у вашій БД та знайти відповідні записи.

До цього моменту наші запити все ще доволі прості, але вже з’являються нюанси. Наприклад, якщо фільтрація з LIKE працює повільно і ви хочете її прискорити, побудувавши простий GIN індекс по стовпчику name, то такий індекс буде використаний у випадку з першим запитом LIKE Mary%, але у другому запиті (LIKE %Mary%) простий індекс на жаль вже не спрацює. Річ у тім, що PostgreSQL не може використовувати звичайний GIN-індекс для запитів LIKE з wildcard на початку (%LIKE), оскільки структура GIN-індексу не призначена для ефективного пошуку шаблонів, які починаються з wildcard, тому PostgreSQL повинен виконувати повне сканування таблиці. Саме тут нам на допомогу приходять триграми.

Для активації функціоналу триграм нам треба підключити модуль pg_trgm.
Активувати його ви можете за допомогою такого SQL:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Для цього модулю необхідно мати встановленим системний пакет postgresql-contrib для Debian/Ubuntu/Fedora або аналогічний для вашої ОС.

Тепер можна описати сам індекс:


from sqlalchemy import Index, text

class Users(Base):
    …
    name = Column(String)

    __table_args__ = (
        Index(
            "users_name_idx",
            text("name gin_trgm_ops"),
            postgresql_using="gin"
        ),
    )

Після виконання міграції наші LIKE запити працюють значно швидше.

Але якщо ми хочемо зробити наш пошук не таким і, наприклад, шукаючи всіх «Lori Johnson» в нашій таблиці, ми очікуємо що наш запит також знайде всіх користувачів з ім’ям «Larry Johnson»?

Для такої задачі нам добре підійде функція similarity з того ж модулю pg_trgm. Використовуючи цю функцію, ми можемо вказувати, наскільки точні відповіді ми хочемо отримати, наприклад:


from sqlalchemy import func

similatiry_threshold = 0.6
session.query(Users).filter(func.similarity(Users.name, "Lori Hamilton") > similatiry_threshold)

Думаю ідея тут зрозуміла, чим більше юзерів зі схожими іменами ми хочемо знайти, тим менший поріг схожості нам треба вказати.

Не складно оновити попередній запит, якщо ім’я та прізвище зберігаються окремо:


session.query(Users).filter(
    or_(
        func.similarity(Users.first_name, search_text) > similarity_threshold,
        func.similarity(Users.second_name, search_text) > similarity_threshold 
     )
 )

Варто зазначити, що PostgreSQL не може використати звичайний індекс для прискорення запита з similarity, і вам знову потрібен індекс, створений з оператором gin_trgm_ops, який був використаний у прикладі створення індексу вище.

Звичайно у задачах на реальних проєктах нам завжди треба сортувати результати за релевантністю, тобто отримати тільки найактуальніші записи або принаймні отримати їх першими у видачі.
Наприклад: нам треба знайти 10 користувачів з ім’ям схожим на «Charles Bradley», при цьому користувачі з найбільш схожими іменами мають бути першими у видачі.


similarity = func.similarity(Users.name, "Charles Bradley").label("similarity")
session.query(Users).order_by(similarity.desc()).limit(10)

Ми також можемо обмежити результати за рівнем схожості, замість використання limit:


session.query(Users).order_by(similarity.desc()).filter(similarity > threshold)

Якщо ви хочете до кожного об’єкту додати прораховану similarity як окремий атрибут, то це можна зробити так:


session.query(Users).add_column(similarity)

Також для аналогічного сортування можна використати subquery:


similarity = func.similarity(Users.name, "Charles Bradley").label("similarity")
subquery = session.query(Users).add_column(similarity).subquery()
session.query(
    Users.id,
    Users.name,
    subquery.c.similarity
).order_by(subquery.c.similarity.desc()).limit(10).all()

Текстовий пошук з використанням tsvector

Тип tsvector представляє документ у формі, оптимізованій для текстового пошуку. Своєю чергою текстовий запит (search term) буде представлений як тип tsquery.

tsvector — це відсортований список різних лексем, тобто слів, що були нормалізовані для об’єднання різних варіантів одного слова. Наприклад речення «The quick brown fox jumps over the lazy dog.» після трансформації у тип tsvector буде виглядати так:

'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2. 

Кожна лексема зберігається з цифрою, яка вказує на порядок слова в оригінальному тексті, що допомагає ранжувати результати пошуку. Також зверніть увагу, що слова «jumps» та «lazy» були приведені до своїх кореневих форм «jump» та «lazi» відповідно. tsvector не зберігає стоп-слова, тобто найуживаніші слова, тож серед лексем у нашому прикладі немає слів «the» та «over».

Подібні трансформації відрізняються у різних мовах, тому при використанні tsvector треба вказати мову оригінального тексту. Таким чином tsvector не підходить для пошуку по стовпцях, що містять тексти на різних мовах.

Давайте відразу перейдемо до прикладів, найпростіший варіант використання tsvector/tsquery в алхімії виглядає так:


session.query(Users).filter(Users.name.match("Hugh"))

Щоб Python код не вводив вас в оману, тут варто зазначити що в SQL немає MATCH оператору, тому що цей код генерує такий SQL запит:


SELECT users.id FROM users WHERE users.name @@ to_tsquery("Hugh")

Якщо ж треба знайти всіх за ім’ям та прізвищем одночасно, тоді наш код буде виглядати так:


session.query(Users).filter(Users.name.match("Hugh & Laurie"))

Або так, якщо ви хочете знайти всіх, у кого збігається ім’я АБО прізвище:


session.query(Users).filter(Users.name.match("Hugh | Laurie")).

Тепер погляньмо на гнучкіший варіант пошуку по стовпчику Users.bio з сортуванням.


from sqlalchemy import func, desc
        
ts_query = func.to_tsquery(" & ".join(query.split())) 
ts_vector = func.to_tsvector("english", Users.bio)
ts_rank = func.ts_rank(ts_vector, ts_query)

session.query(Users, ts_rank.label("rank"))
                .filter(ts_vector.op("@@")(ts_query))
                .order_by(desc("rank"))

Знов таки, для запиту з використання tsvector, ви можете додати в вашу таблицю стовпчик типу tsvector і побудувати по цьому стовпчику індекс.

SQLAlchemy не має окремого типу tsvector, тому нам потрібно створити власний тип і потім додати стовпчик цього типу до вашої моделі.

Також щоб не додавати логіку наповнення цього стовпчику через SQL тригери або event listeners алхімії, ми можемо використати Computed конструкт з алхімії, який додасть GENERATED ALWAYS AS до DDL нашого стовпчика.


from sqlalchemy.types import TypeDecorator
from sqlalchemy.dialects.postgresql import TSVECTOR

class TSVector(TypeDecorator):
    impl = TSVECTOR

class Users(Base):
    ...

    __ts_vector__ = Column(
        TSVector(), Computed("to_tsvector('english', bio)", persisted=True)
    )
    __table_args__ = (
        Index("idx__ts_vector", __ts_vector__, postgresql_using="gin")
    )

Варто зазначити, що якщо в стовпчику можуть бути відсутні значення, тоді вам треба використати COALESCE та замінити Computed на такий:


Computed("to_tsvector('english', COALESCE(bio, ''))")

Також ви можете побудувати індекс одночасно за декількома стовпчиками ось так:


Computed("to_tsvector('english', COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))")

Заключна частина

Ще один модуль який дозволяє робити потужні та гнучкі text search запити це модуль fuzzystrmatch. Наприклад за допомогою цього модулю ви можете зробити «sounds like» пошук, використовуючи Double Metaphone або Soundex та багато іншого. Огляд цього модулю зробив би цю статтю занадто великою, тому ми не будемо його розглядати, але майте його на увазі коли перед вами стоять подібні задачі.

А поки це все, що я хотів розказати про Text Search в PosqtgreSQL. В цій статті ми розглянули базові можливості модуля pg_tgrm та text search типи, такі як tsvector та tsquery, які забезпечують ефективний повнотекстовий пошук серед великих обсягів даних.

Ви можете адаптувати та оптимізувати приклади з цієї статті, враховуючи специфіку вашої бази даних та потреби вашого проєкту. Сподіваюсь, що це буде для вас корисно.

👍ПодобаєтьсяСподобалось14
До обраногоВ обраному6
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Дякую! Не знав що таке є. Думав в подібних кейсах треба відразу на Elastic переходити.

Дякую за висвітлення цікавих можливостей PG.

Можете використовувати для підсвітки коду на Python:

<div class="hl-wrap python"><pre>
class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)  # first name + last name
    bio = Column(String)
</pre></div>
замість:
<pre>
class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)  # first name + last name
    bio = Column(String)
</pre>

Дякую за підказку, стало краще!

Підписатись на коментарі