PostgreSQL 전문 검색

소개

이 랩에서는 PostgreSQL 에서 전문 검색 (full-text search) 을 구현하는 방법을 배웁니다. 전문 검색은 자연어 문서를 특정 단어나 구문으로 검색하는 기능을 제공합니다. 먼저 articles 테이블을 생성하고 샘플 데이터를 추가합니다. 그런 다음, 처리된 텍스트를 저장하기 위한 특수 데이터 타입인 tsvector 타입의 search_vector 열을 추가합니다. 검색 속도를 높이기 위한 인덱스를 생성하고 tsquery를 사용하여 쿼리를 수행합니다. 마지막으로, 원본 텍스트가 수정될 때마다 검색 데이터를 자동으로 업데이트하는 트리거를 설정하여 검색 인덱스를 최신 상태로 유지합니다.

테이블 생성 및 샘플 데이터 삽입

이 단계에서는 PostgreSQL 데이터베이스에 연결하고, articles 테이블을 생성하며, 몇 가지 샘플 데이터를 삽입합니다. 이 테이블은 전문 검색을 구현하고 테스트하기 위한 기반이 될 것입니다.

먼저 터미널을 열고 postgres 사용자로 PostgreSQL 대화형 쉘 (psql) 에 연결합니다. 이 랩의 이후 모든 SQL 명령은 이 쉘 내에서 실행됩니다.

sudo -u postgres psql

이제 postgres=#와 같이 보이는 PostgreSQL 프롬프트가 표시됩니다.

다음으로 articles 테이블을 생성합니다. 이 테이블은 각 기사의 ID, 제목 및 본문을 저장합니다.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

이제 articles 테이블에 세 개의 샘플 기사를 삽입합니다. 이 데이터는 다음 단계에서 검색 쿼리에 사용됩니다.

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

데이터가 올바르게 삽입되었는지 확인하려면 SELECT 쿼리를 실행하여 테이블의 모든 레코드를 확인합니다.

SELECT * FROM articles;

방금 삽입한 세 개의 행이 아래 출력과 유사하게 표시되어야 합니다.

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

TSVECTOR 열 및 GIN 인덱스 추가

전문 검색을 수행하려면 tsvector 타입의 열이 필요합니다. tsvector 값은 동일한 단어의 다양한 변형을 병합하도록 정규화된 고유한 단어 (lexeme) 의 정렬된 목록입니다. 이 단계에서는 tsvector 열을 추가하고 검색 쿼리를 가속화하기 위해 해당 열에 인덱스를 생성합니다.

먼저 articles 테이블에 TSVECTOR 타입의 search_vector라는 새 열을 추가합니다.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

다음으로, 각 기사의 title과 content를 tsvector로 변환하여 이 새 열을 채웁니다. to_tsvector 함수가 이 변환에 사용됩니다. 이 함수는 일반 단어 (stop words) 를 제거하고 단어를 기본 형태 (stemming) 로 축소하여 텍스트를 처리합니다.

UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

이 명령에서 'english'는 텍스트 검색 구성을 지정하고, ||는 제목과 내용을 결합하는 연결 연산자입니다.

전문 검색 속도를 크게 높이려면 search_vector 열에 GIN(Generalized Inverted Index) 을 생성합니다. GIN 인덱스는 tsvector와 같은 복합 값을 인덱싱하는 데 매우 효과적입니다.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

\d 명령을 사용하여 테이블 구조를 검사하여 열과 인덱스가 추가되었는지 확인할 수 있습니다.

\d articles

출력에는 이제 search_vector 열과 articles_search_idx GIN 인덱스가 포함되어야 합니다.

                                     Table "public.articles"
    Column     |          Type          | Collation | Nullable |                 Default
---------------+------------------------+-----------+----------+-----------------------------------------
 id            | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title         | character varying(255) |           | not null |
 content       | text                   |           |          |
 search_vector | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_idx" gin (search_vector)

전문 텍스트 검색 수행

tsvector 열과 GIN 인덱스가 준비되었으므로 이제 효율적인 전문 검색을 수행할 수 있습니다. 쿼리는 텍스트 쿼리를 나타내는 tsquery 데이터 타입을 사용하여 구성됩니다. @@ 연산자는 tsquery를 tsvector와 일치시키는 데 사용됩니다.

먼저 search라는 단어를 포함하는 기사를 검색해 보겠습니다. to_tsquery 함수는 검색 문자열을 tsquery로 변환합니다.

SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');

이 쿼리는 "Full Text Search"에 대한 기사를 반환합니다.

             title              |                                       content
----------------------------------+-----------------------------------------------------------------------
 Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)

쿼리 내에서 연산자를 사용할 수도 있습니다. & (AND) 연산자는 지정된 모든 용어를 포함하는 문서를 찾습니다. PostgreSQL과 performance를 모두 포함하는 기사를 검색해 보겠습니다.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

이것은 성능 튜닝에 대한 기사를 반환합니다.

             title
-------------------------------
 PostgreSQL Performance Tuning
(1 row)

| (OR) 연산자는 지정된 용어 중 하나 이상을 포함하는 문서를 찾습니다. tutorial 또는 tuning을 포함하는 기사를 검색해 보겠습니다.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');

이 쿼리는 두 개의 기사를 반환합니다.

             title
-------------------------------
 PostgreSQL Tutorial
 PostgreSQL Performance Tuning
(2 rows)

트리거를 사용하여 업데이트 자동화

현재 search_vector 열은 정적입니다. 기사의 제목이나 내용을 업데이트해도 search_vector는 변경 사항을 자동으로 반영하지 않습니다. 이를 해결하기 위해 articles 테이블에 대한 모든 INSERT 또는 UPDATE 작업 전에 search_vector 열을 자동으로 업데이트하는 트리거를 생성할 수 있습니다.

먼저 트리거에 의해 실행될 함수를 정의합니다. 이 함수는 새 행 또는 업데이트된 행에 대한 search_vector를 업데이트합니다.

CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

함수 내의 NEW 변수는 삽입되거나 업데이트되는 행을 참조합니다.

다음으로 트리거 자체를 생성합니다. 이 트리거는 각 행이 삽입되거나 업데이트되기 전에 articles_tsvector_update 함수를 호출합니다.

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

이제 트리거를 테스트해 보겠습니다. 먼저 데이터베이스 인덱싱에 대한 새 기사를 삽입합니다.

INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');

트리거로 인해 이 새 기사의 search_vector가 자동으로 채워졌습니다. indexes라는 단어를 검색하여 이를 확인할 수 있습니다.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');

새 기사가 결과에 나타나야 합니다.

        title
---------------------
 Database Indexing
(1 row)

이는 트리거가 올바르게 작동하여 전문 검색 인덱스가 데이터와 동기화된 상태를 유지함을 확인합니다.

요약

이 실습에서는 PostgreSQL 에서 전문 검색을 구현하는 기본 사항을 배웠습니다. 테이블을 성공적으로 생성하고, 데이터를 채우고, tsvector 열을 추가하여 전문 검색을 위해 구성했습니다. 텍스트를 처리하기 위해 to_tsvector 함수를 사용하고 검색 성능을 최적화하기 위해 GIN 인덱스를 사용하는 방법을 배웠습니다. 또한 다양한 연산자를 사용하여 tsquery로 검색을 수행하는 연습을 했습니다. 마지막으로 검색 인덱스를 데이터와 동기화된 상태로 유지하는 프로세스를 자동화하기 위해 트리거를 구현했으며, 이는 안정적인 검색 시스템을 유지하는 데 중요한 단계입니다.