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

Question

Добавить индекс CREATE INDEX idx_author_name ON authors(name), использовать покрывающие индексы (INCLUDE) для частых запросов, настроить частичные индексы если данные фильтруются, и переписать запросы для исключения LIKE '%...'. Для миллионов записей — рассмотреть полнотекстовый поиск.

YeaHub · Accepted Answer

1. Добавление индексовПроблема: Полнотекстовый поиск (WHERE name LIKE '%Толстой%') без индекса сканирует всю таблицу.Решение:-- Базовый индекс (ускоряет точные совпадения и "starts with") CREATE INDEX idx_author_name ON authors(name); -- Для поиска по части строки (только для "starts with") SELECT * FROM authors WHERE name LIKE 'Толстой%'; -- Использует индекс2. Оптимизация для LIKE с любым положениемПроблема: LIKE '%Толстой' не использует обычный индекс.Решение:-- Индекс для триграмм (расширение pg_trgm) CREATE EXTENSION pg_trgm; CREATE INDEX idx_author_name_trgm ON authors USING gin(name gin_trgm_ops); -- Теперь запрос использует индекс: SELECT * FROM authors WHERE name LIKE '%Толстой';3. Покрывающие индексыДля частых запросов с дополнительными полями:-- Индекс "покрывает" все данные запроса CREATE INDEX idx_author_cover ON authors(name) INCLUDE (id, birth_date);4. Частичные индексыЕсли данные фильтруются (например, только активные авторы):CREATE INDEX idx_active_authors ON authors(name) WHERE is_active = true;5. Полнотекстовый поиск для сложных запросовДля миллионов записей и морфологического поиска:ALTER TABLE authors ADD COLUMN name_tsvector tsvector; UPDATE authors SET name_tsvector = to_tsvector('russian', name); CREATE INDEX idx_fts_author ON authors USING gin(name_tsvector); -- Поиск по словам в любом порядке и формах SELECT * FROM authors WHERE name_tsvector @@ to_tsquery('russian', 'Толстой');6. Оптимизация Java-кодаИспользуйте пагинацию и кэширование:// Spring Data JPA + Пагинация Page authors = authorRepository.findByNameContaining("Толстой", PageRequest.of(0, 20)); // Кэширование (Spring Cache) @Cacheable("authors") public List findAuthors(String name) { return authorRepository.searchByName(name); }7. Мониторинг и настройка PostgreSQLАнализ запросов:EXPLAIN ANALYZE SELECT * FROM authors WHERE name LIKE '%Толстой%';Настройка памяти: Увеличьте shared_buffers и work_mem в postgresql.conf.