Search Autocomplete Across Scripts and Diacritics

A user in Dubai types Arabic into the TrendVidStream search bar. Someone in Copenhagen types "nyhe" meaning to find "nyheder" (news). PostgreSQL's pg_trgm extension handles both cases with the same index.

Enable pg_trgm and unaccent

CREATE EXTENSION IF NOT EXISTS pg_trgm ; CREATE EXTENSION IF NOT EXISTS unaccent ;

Enter fullscreen mode

Exit fullscreen mode

Indexing Multi-Script Titles

CREATE INDEX idx_videos_title_trgm ON videos USING GIN ( title gin_trgm_ops );

-- Unaccent-aware index for CE and Nordic regions CREATE INDEX idx_videos_title_unaccent_trgm ON videos USING GIN ( unaccent ( title ) gin_trgm_ops ) WHERE region IN ( 'CZ' , 'FI' , 'DK' , 'BE' , 'CH' );

CREATE INDEX idx_videos_channel_trgm ON videos USING GIN ( channel_title gin_trgm_ops );

Enter fullscreen mode

Exit fullscreen mode

Standard Autocomplete Query

SELECT video_id , title , channel_title , thumbnail_url , region , word_similarity ( $ 1 , title ) AS wsim FROM videos WHERE…