🔍 Full-text Search

-- Tạo tsvector từ text
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- Output: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- Tìm kiếm với tsquery
SELECT to_tsquery('english', 'quick & fox');
-- Output: 'quick' & 'fox'

-- Match
SELECT to_tsvector('english', 'The quick brown fox') 
       @@ to_tsquery('english', 'quick & fox');
-- Output: true

-- Tạo table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    search_vector tsvector,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tạo GIN index cho search_vector
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Trigger để auto-update search_vector
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Tìm kiếm với ranking
SELECT 
    id,
    title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query, 
        'StartSel=<mark>, StopSel=</mark>, MaxWords=50') AS snippet
FROM 
    articles,
    to_tsquery('english', 'golang & concurrency') AS query
WHERE 
    search_vector @@ query
ORDER BY 
    rank DESC
LIMIT 10;

-- Fuzzy search với similarity
SELECT *
FROM articles
WHERE title % 'programing'  -- sử dụng pg_trgm extension
ORDER BY similarity(title, 'programing') DESC;

# docker-compose.yml
version: '3.8'
services:
  elasticsearch:
    image: elasticsearch:8.11.0
    environment:
      - discovery.type=single-node
      - xpack.security.enabled=false
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    ports:
      - "9200:9200"
    volumes:
      - esdata:/usr/share/elasticsearch/data

  kibana:
    image: kibana:8.11.0
    ports:
      - "5601:5601"
    depends_on:
      - elasticsearch

volumes:
  esdata:

# Start
docker-compose up -d

# Test
curl http://localhost:9200

from elasticsearch import Elasticsearch

es = Elasticsearch("http://localhost:9200")

# Tạo index với mapping
es.indices.create(index="articles", body={
    "settings": {
        "analysis": {
            "analyzer": {
                "vietnamese": {
                    "type": "custom",
                    "tokenizer": "standard",
                    "filter": ["lowercase", "asciifolding"]
                }
            }
        }
    },
    "mappings": {
        "properties": {
            "title": {"type": "text", "analyzer": "vietnamese"},
            "content": {"type": "text", "analyzer": "vietnamese"},
            "tags": {"type": "keyword"},
            "created_at": {"type": "date"}
        }
    }
})

# Index document
es.index(index="articles", id=1, document={
    "title": "Hướng dẫn Golang",
    "content": "Go là ngôn ngữ lập trình của Google...",
    "tags": ["golang", "programming"],
    "created_at": "2024-01-15"
})

# Search
result = es.search(index="articles", body={
    "query": {
        "multi_match": {
            "query": "golang concurrency",
            "fields": ["title^2", "content"],
            "type": "best_fields",
            "fuzziness": "AUTO"
        }
    },
    "highlight": {
        "fields": {"content": {}}
    }
})

for hit in result["hits"]["hits"]:
    print(f"{hit['_score']}: {hit['_source']['title']}")