PostgreSQL Full-Text Search (FTS)

Built-in Axtarış: PostgreSQL-də daxili tam mətn axtarışı (Full-Text Search) dəstəyi.
Dil Dəstəyi: Müxtəlif dillər üçün text search konfiqurasiyaları.
Ranking: Axtarış nəticələrini relevantlığa görə sıralama.
Performance: GIN və GiST indeksləri ilə sürətli axtarış.
Fuzzy Search: Similarity və trigram axtarış dəstəyi.
Highlight: Axtarış nəticələrində uyğun gələn hissələri vurğulama.
Weighted Search: Müxtəlif field-lərə çəki verib axtarış etmək.
Phrase Search: Dəqiq ifadə axtarışı.

Əsas Konseptlər

tsvector

tsvector - sənədin axtarış üçün optimize edilmiş təsviri:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
-- Result: 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

tsquery

tsquery - axtarış sorğusu:

SELECT 'fat & rat'::tsquery;
-- Result: 'fat' & 'rat'

to_tsvector və to_tsquery

-- Mətni tsvector-a çevir
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- Query yarat
SELECT to_tsquery('english', 'quick & fox');
-- Result: 'quick' & 'fox'

PostgreSQL FTS vs Elasticsearch

Xüsusiyyət	PostgreSQL FTS	Elasticsearch
Setup	Daxili, əlavə setup lazım deyil	Ayrıca cluster quraşdırılmalı
Performans	Yaxşı (milyon sətirlərə qədər)	Excellent (milyardlarla sənəd)
Mürəkkəblik	Sadə	Kompleks
Dil Dəstəyi	20+ dil	30+ dil
Fuzzy Search	pg_trgm ilə	Built-in
Distributed Search	Məhdud	Excellent
Real-time	Var	Var
Memory Usage	Aşağı	Yüksək
Sənəd Scoring	Basic	Advanced
Aggregation	SQL ilə	Powerful DSL

Full-Text Search İstifadə Sahələri

Blog və CMS Sistemləri: Məqalə və content axtarışı
E-commerce: Məhsul axtarışı
Documentation: Sənəd və FAQ axtarışı
Forum və Social Media: Post və comment axtarışı
Knowledge Base: Bilgi bazası axtarışı
Email Systems: Email axtarışı
News Portals: Xəbər axtarışı

Əsas Əməliyyatlar

Sadə Axtarış

-- Cədvəl yarat
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Məlumat əlavə et
INSERT INTO articles (title, body) VALUES
('PostgreSQL Tutorial', 'Learn PostgreSQL database fundamentals'),
('Full-Text Search Guide', 'Complete guide to PostgreSQL FTS'),
('Advanced SQL Queries', 'Master complex SQL queries');

-- Sadə axtarış
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) 
      @@ to_tsquery('english', 'PostgreSQL');

tsvector Sütunu Əlavə Etmək

-- tsvector sütunu əlavə et
ALTER TABLE articles ADD COLUMN tsv tsvector;

-- tsvector-u yenilə
UPDATE articles 
SET tsv = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

-- İndeks yarat
CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- Axtarış et
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL & tutorial');

Trigger ilə Avtomatik Yeniləmə

-- Trigger function yarat
CREATE FUNCTION articles_tsv_trigger() RETURNS trigger AS $$
BEGIN
  NEW.tsv := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, ''));
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- Trigger əlavə et
CREATE TRIGGER tsvectorupdate 
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW 
EXECUTE FUNCTION articles_tsv_trigger();

-- Test et
INSERT INTO articles (title, body) 
VALUES ('New Article', 'This is a new article about databases');

Weighted Search (Çəkili Axtarış)

-- Müxtəlif field-lərə müxtəlif çəkilər ver
ALTER TABLE articles ADD COLUMN tsv_weighted tsvector;

UPDATE articles 
SET tsv_weighted = 
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B');

CREATE INDEX idx_articles_weighted ON articles USING GIN(tsv_weighted);

-- Axtarış et (title-də tapılanlar daha yüksək rank alacaq)
SELECT title, 
       ts_rank(tsv_weighted, query) AS rank
FROM articles, to_tsquery('english', 'PostgreSQL') query
WHERE tsv_weighted @@ query
ORDER BY rank DESC;

Query Operatorları

AND, OR, NOT Operatorları

-- AND (&)
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL & tutorial');

-- OR (|)
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL | MySQL');

-- NOT (!)
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL & !MySQL');

-- Combination
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', '(PostgreSQL | MySQL) & tutorial');

Phrase Search

-- Phrase axtarışı (<->)
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'full <-> text <-> search');

-- Distance ilə phrase axtarışı
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL <2> tutorial');

Prefix Search

-- Prefix axtarışı (:*)
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'data:*');
-- 'data', 'database', 'databases' tapacaq

Ranking və Sıralama

ts_rank

-- Sadə ranking
SELECT title, 
       ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'PostgreSQL') query
WHERE tsv @@ query
ORDER BY rank DESC;

ts_rank_cd (Cover Density)

-- Cover density ranking
SELECT title,
       ts_rank_cd(tsv, query) AS rank
FROM articles, to_tsquery('english', 'PostgreSQL & database') query
WHERE tsv @@ query
ORDER BY rank DESC;

Weighted Ranking

-- Weighted ranking (weights: D=0.1, C=0.2, B=0.4, A=1.0)
SELECT title,
       ts_rank(tsv_weighted, query, 1) AS rank
FROM articles, to_tsquery('english', 'PostgreSQL') query
WHERE tsv_weighted @@ query
ORDER BY rank DESC;

Highlighting (Vurğulama)

ts_headline

-- Axtarış nəticələrini vurğula
SELECT ts_headline('english', body, 
                   to_tsquery('english', 'PostgreSQL'),
                   'StartSel=<b>, StopSel=</b>') AS highlighted
FROM articles
WHERE tsv @@ to_tsquery('english', 'PostgreSQL');

Özelleşdirilmiş Highlighting

-- Daha çox kontekst göstər
SELECT ts_headline('english', body,
                   to_tsquery('english', 'PostgreSQL'),
                   'MaxWords=35, MinWords=15, ShortWord=3, 
                    StartSel=<mark>, StopSel=</mark>') AS highlighted
FROM articles
WHERE tsv @@ to_tsquery('english', 'PostgreSQL');

Fuzzy Search (pg_trgm)

Trigram Extension

-- Extension-u aktivləşdir
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Trigram indeks yarat
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);

-- Similarity axtarışı
SELECT title, similarity(title, 'PostgreSQL') AS sim
FROM articles
WHERE title % 'PostgreSQL'  -- % operatoru similarity threshold-u yoxlayır
ORDER BY sim DESC;

-- Fuzzy search
SELECT * FROM articles
WHERE title ILIKE '%postgre%'  -- Typo-ları da tapa bilir
   OR similarity(title, 'postgre') > 0.3;

Multi-Language Support

Dil Konfiqurasiyaları

-- Mövcud konfiqurasiyalara bax
SELECT cfgname FROM pg_ts_config;
-- english, simple, turkish, russian və s.

-- Müxtəlif dillərdə axtarış
SELECT to_tsvector('turkish', 'Bu bir Türkçe mətndir');
SELECT to_tsvector('russian', 'Это русский текст');

Custom Configuration

-- Custom konfiqurasiya yarat
CREATE TEXT SEARCH CONFIGURATION my_config (COPY = english);

-- Stop words əlavə et
CREATE TEXT SEARCH DICTIONARY my_stopwords (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = english
);

Performance Optimization

İndeks Növləri

-- GIN indeks (ümumi istifadə)
CREATE INDEX idx_gin ON articles USING GIN(tsv);

-- GiST indeks (daha az yer tutur, ancaq yavaşdır)
CREATE INDEX idx_gist ON articles USING GIST(tsv);

Partial İndeks

-- Yalnız aktiv məqalələr üçün
CREATE INDEX idx_active_articles_tsv 
ON articles USING GIN(tsv) 
WHERE active = true;

Statistika

-- Statistika yenilə
ANALYZE articles;

-- İndeks istifadəsinə bax
EXPLAIN ANALYZE
SELECT * FROM articles 
WHERE tsv @@ to_tsquery('english', 'PostgreSQL');

Best Practices

İndeksləmə: GIN indeks istifadə edin (GiST-dən sürətli)
Trigger: Avtomatik tsvector yeniləməsi üçün trigger istifadə edin
Weighted Search: Müxtəlif field-lərə müxtəlif çəkilər verin
Language: Düzgün dil konfiqurasiyası seçin
Normalization: Mətnləri lowercase-ə çevirin
Caching: Tez-tez istifadə olunan query-ləri cache-ləyin
Limit Results: LIMIT istifadə edərək nəticələri məhdudlaşdırın
Monitoring: Query performansını izləyin

FTS vs LIKE/ILIKE

-- LIKE (yavaş, indeks istifadə etmir)
SELECT * FROM articles WHERE body LIKE '%PostgreSQL%';

-- FTS (sürətli, indeks istifadə edir)
SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'PostgreSQL');

Real-World Nümunə

-- E-commerce məhsul axtarışı
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    category TEXT,
    price DECIMAL(10,2),
    tsv tsvector
);

-- Weighted tsvector (name-ə daha çox çəki)
CREATE OR REPLACE FUNCTION products_tsv_trigger() RETURNS trigger AS $$
BEGIN
  NEW.tsv := 
    setweight(to_tsvector('english', coalesce(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.category, '')), 'C');
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate 
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW 
EXECUTE FUNCTION products_tsv_trigger();

CREATE INDEX idx_products_tsv ON products USING GIN(tsv);

-- Axtarış query
SELECT name, price,
       ts_rank(tsv, query) AS rank,
       ts_headline('english', description, query, 
                   'MaxWords=20, StartSel=<b>, StopSel=</b>') AS snippet
FROM products, to_tsquery('english', 'laptop & gaming') query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 10;

Əlavə Resurslar

PostgreSQL FTS Documentation: https://www.postgresql.org/docs/current/textsearch.html
pg_trgm Extension: https://www.postgresql.org/docs/current/pgtrgm.html
Text Search Tutorial: https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/

Əsas Konseptlər​

tsvector​

tsquery​

to_tsvector və to_tsquery​

PostgreSQL FTS vs Elasticsearch​

Full-Text Search İstifadə Sahələri​

Əsas Əməliyyatlar​

Sadə Axtarış​

tsvector Sütunu Əlavə Etmək​

Trigger ilə Avtomatik Yeniləmə​

Weighted Search (Çəkili Axtarış)​

Query Operatorları​

AND, OR, NOT Operatorları​

Phrase Search​

Prefix Search​

Ranking və Sıralama​

ts_rank​

ts_rank_cd (Cover Density)​

Weighted Ranking​

Highlighting (Vurğulama)​

ts_headline​

Özelleşdirilmiş Highlighting​

Fuzzy Search (pg_trgm)​

Trigram Extension​

Multi-Language Support​

Dil Konfiqurasiyaları​

Custom Configuration​

Performance Optimization​

İndeks Növləri​

Partial İndeks​

Statistika​

Best Practices​

FTS vs LIKE/ILIKE​

Real-World Nümunə​

Əlavə Resurslar​

Əsas Konseptlər

tsvector

tsquery

to_tsvector və to_tsquery

PostgreSQL FTS vs Elasticsearch

Full-Text Search İstifadə Sahələri

Əsas Əməliyyatlar

Sadə Axtarış

tsvector Sütunu Əlavə Etmək

Trigger ilə Avtomatik Yeniləmə

Weighted Search (Çəkili Axtarış)

Query Operatorları

AND, OR, NOT Operatorları

Phrase Search

Prefix Search

Ranking və Sıralama

ts_rank

ts_rank_cd (Cover Density)

Weighted Ranking

Highlighting (Vurğulama)

ts_headline

Özelleşdirilmiş Highlighting

Fuzzy Search (pg_trgm)

Trigram Extension

Multi-Language Support

Dil Konfiqurasiyaları

Custom Configuration

Performance Optimization

İndeks Növləri

Partial İndeks

Statistika

Best Practices

FTS vs LIKE/ILIKE

Real-World Nümunə

Əlavə Resurslar