max / makenotwork

752 B · 17 lines History Blame Raw

1	-- Full-text search: pg_trgm for fuzzy matching + tsvector for ranking.
2
3	CREATE EXTENSION IF NOT EXISTS pg_trgm;
4
5	-- Trigram GIN indexes for ILIKE/similarity queries
6	CREATE INDEX idx_threads_title_trgm ON threads USING GIN (title gin_trgm_ops);
7	CREATE INDEX idx_posts_body_trgm ON posts USING GIN (body_markdown gin_trgm_ops);
8
9	-- tsvector columns for full-text search ranking
10	ALTER TABLE threads ADD COLUMN search_tsv tsvector
11	GENERATED ALWAYS AS (to_tsvector('english', title)) STORED;
12	ALTER TABLE posts ADD COLUMN search_tsv tsvector
13	GENERATED ALWAYS AS (to_tsvector('english', body_markdown)) STORED;
14
15	CREATE INDEX idx_threads_search_tsv ON threads USING GIN (search_tsv);
16	CREATE INDEX idx_posts_search_tsv ON posts USING GIN (search_tsv);
17