Як підтримувати tsvector в актуальному стані: згенерована колонка, тригер чи вираз в індексі?

Є три способи зберігати пошуковий вектор, і в кожного свої компроміси.

1. Індекс за виразом - нічого не зберігати:

CREATE INDEX posts_fts_idx ON posts USING gin (to_tsvector('english', title || ' ' || body));

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'queue');

• Не займає місця в таблиці.
• Вираз у запиті має дослівно збігатися з виразом в індексі (разом з конфігурацією - функція з одним аргументом нестабільна й в індексі не допускається).
• ts_rank і перевірки збігу обчислюють вектор заново для кожного рядка - повільно.

2. Згенерована колонка - найпростіший надійний варіант:

ALTER TABLE posts ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

• Завжди актуальна, без коду в застосунку.
• Вектор обчислено заздалегідь - ранжування швидке.
• Обмеження: лише колонки того самого рядка.

3. Тригер - коли вектор має включати дані з інших таблиць: назви тегів, ім'я автора, назву категорії.

CREATE FUNCTION posts_search_update() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.search := setweight(to_tsvector('english', NEW.title), 'A')
               || setweight(to_tsvector('english',
                      coalesce((SELECT string_agg(name, ' ') FROM tags t
                                JOIN post_tag pt ON pt.tag_id = t.id
                                WHERE pt.post_id = NEW.id), '')), 'B');
    RETURN NEW;
END $$;

Мінус - зміна в пов'язаній таблиці (перейменували тег) сама вектор не оновить: потрібні тригери й там або фонове перерахування.

coalesce обов'язковий: to_tsvector(NULL) дає NULL, а NULL || вектор - теж NULL. Один порожній опис - і весь документ випадає з пошуку.

Рекомендація: згенерована колонка за замовчуванням; тригер чи перерахування в черзі - коли потрібні дані з інших таблиць; індекс за виразом - для простих випадків без ранжування.

Докладніше в документації: Таблиці й індекси для пошуку