Optimizing search with ILIKE, pg_trgm, and migrations

Content search commonly uses ILIKE '%term%' over post titles, descriptions, slugs, and lesson bodies. Escaping the query and limiting its length are safety contracts; because of the leading and trailing wildcards, a normal B-tree index is not enough.

Indexes from real query shapes

The admin console's SQL SSOT and 007_create_lessons.sql declare the pg_trgm extension and GIN indexes in the final schema.

• posts: title, description, and the Admin-search slug
• lessons: title and content_md for published rows
• existing databases: the migration runner adds the same extension and indexes idempotently

Composite and partial indexes that narrow published, language, content_kind, and series_slug serve list and detail queries separately. A trigram index is not a replacement for those access paths.

Why not blindly restore tsvector

On the default PostgreSQL image, to_tsvector('korean', ...) can make schema initialization roll back when that text-search configuration does not exist. Assuming an unavailable analyzer while the product contract is still ILIKE creates a larger outage than the search optimization prevents. A full-text switch needs an explicit analyzer, language, ranking, and reindexing contract first.

Verification order

Compare representative terms on staging:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
WHERE published = TRUE AND language = 'ko'
  AND title ILIKE '%docker%';

The planner will not always choose Bitmap/GIN. With very few rows or a two-character term, a sequential scan may be cheaper. Check write time, vacuum, and backup size after adding the indexes; the mere presence of an index is not proof of a performance result.

Completion criteria

• SQL SSOT and the existing-database migration use the same index names and operators.
• Query escaping, length limits, and 429 behavior remain intact.
• Staging query plans and write/backup costs are recorded for representative terms.
• Faster search never exposes unpublished content.