Intermediate LevelPostgreSQL
How does the pg_trgm extension enable fuzzy string matching and ILIKE index speedups?
❓ Interview Prompt: Explain `pg_trgm` extension, breaking strings into 3-character trigrams, similarity matching (`%`), and accelerating `LIKE '%pattern%'` with GIN/GiST.
Standard B-Tree indexes CANNOT accelerate wildcards at the start of string searches ('WHERE name ILIKE '%smith%''). The 'pg_trgm' extension solves this by breaking strings into 3-character trigrams:
Features:
•
Indexes trigrams using GIN ('gin_trgm_ops').
•
Accelerates 'LIKE '%pattern%'', 'ILIKE', and fuzzy string similarity queries ('show_limit()', '%' operator).
Code Implementation Example
sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Create GIN trigram index accelerating ILIKE '%search%'
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
-- Fast indexed fuzzy search!
SELECT name, similarity(name, 'smth') AS score
FROM users
WHERE name % 'smth'
ORDER BY score DESC;💡Accelerating wildcards and fuzzy searches using pg_trgm GIN indexes.
🎯 Interview Key Takeaway
Use pg_trgm GIN indexes to accelerate LIKE '%pattern%' wildcards and fuzzy text matching.
#pg_trgm#Trigrams#ILIKE#Fuzzy Search#Intermediate
Back to all PostgreSQL Questions→