Elasticsearch 없이 전문 검색

CREATE TABLE articles (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    tsv       tsvector GENERATED ALWAYS AS (
                to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
              ) STORED,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX articles_tsv_gin ON articles USING gin (tsv);

WITH q AS (SELECT websearch_to_tsquery('english', 'postgres performance') AS query)
SELECT id, title,
       ts_rank(tsv, q.query)                            AS score,
       ts_headline('english', body, q.query,
                   'StartSel=<mark>, StopSel=</mark>')   AS snippet
FROM   articles, q
WHERE  tsv @@ q.query
ORDER  BY score DESC
LIMIT  10;