RDBMS vs. Search Engine

• 검색 시스템을 구현하기 위해서는 ElasticSearch 같은 전문 검색 엔진을 사용할 수 있음
• 대부분의 기업 데이터베이스는 RDBMS로 구축
• RDBMS와 검색 엔진을 별도로 운용할 경우 데이터 정합성 문제가 발생할 수 있음
• RDBMS로 검색 시스템을 구축할 경우:
  • 검색 속도는 느려질 수 있음
  • 아키텍처 단순화로 인한 개발, 운영, 유지보수에 장점
• 검색 시스템의 규모를 바탕으로 판단

PostgreSQL 소개

• PostgreSQL (이하 Postgres)은 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)
• 안정성·확장성·표준 SQL 지원 측면에서 높은 평가
• 다양한 확장 기능을 통해 RDBMS 이외의 목적으로도 쉽게 사용할 수 있음
• OpenAI 등 해외 스타트업에서 널리 사용
• Supabase, Neon 등 클라우드 서비스로도 제공됨

PostgreSQL 공식 저장소 추가 및 17 버전 설치

• colab의 기본 Postgres 버전이 낮아 공식 저장소에서 17 버전을 설치

# 저장소 추가
!sudo apt update
!sudo apt install -y gnupg2 wget ca-certificates
!wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add
!echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
# PostgreSQL 17 설치
!sudo apt update
!sudo apt install -y postgresql-17 postgresql-contrib-17

서버 시작 및 사용자 추가

# 서버 시작
!service postgresql start
# 사용자 추가
!sudo -u postgres psql -c "CREATE USER colab WITH PASSWORD 'colab';"
!sudo -u postgres psql -c "CREATE DATABASE searchdb OWNER colab;"

colab에서 SQL 사용하기

• 패키지 설치

!pip install ipython-sql

• 패키지 사용

%load_ext sql
%config SqlMagic.style = '_DEPRECATED_DEFAULT'

• 셀 첫 줄에 %%sql 이라고 표시하면 SQL 코드를 사용할 수 있음
• 처음 사용할 때는 DB 접속 문자열 protocol://id:password@host:post/database 입력

%%sql postgresql+psycopg2://colab:colab@localhost:5432/searchdb
CREATE TABLE IF NOT EXISTS wikipages
(id INTEGER, url TEXT, title TEXT, text TEXT, title_nouns TEXT);
GRANT ALL PRIVILEGES ON TABLE wikipages TO colab;
TRUNCATE wikipages;

• 테이블 만들고 권한 부여, 내용 비우기

데이터 로딩

# CSV 로드
!sudo -u postgres psql -d searchdb -c "\copy wikipages FROM 'wikipedia_ko.csv' WITH (FORMAT csv, HEADER true);"

• \copy는 SQL 문이 아니라 Postgres 클라이언트인 psql의 명령이므로 sudo로 실행

성능 분석

• 첫 5행 보기(%%sql만 사용하고, 접속 문자열은 생략해도 됨)

%%sql
SELECT * FROM wikipages LIMIT 5;

• EXPLAIN: 질의 계획(query plan)만 수립

%%sql
EXPLAIN SELECT * FROM wikipages LIMIT 5;

• EXPLAIN ANALYZE: 질의 계획과 실제 실행 시간을 보여줌

%%sql
EXPLAIN ANALYZE SELECT * FROM wikipages LIMIT 5;

옵티마이저(Optimizer)

• 사용자가 입력한 구문을 최적화해주는 부분
• 비용 기반 옵티마이저:
  • 수많은 '후보 실행 계획'을 생성
  • 통계 정보(Statistics) 를 바탕으로 각 계획의 비용(Cost) 을 추정
  • 가장 비용이 낮은 계획을 최종 선택

LIKE를 이용한 검색

• SQL에서 title LIKE '대한민국%'는 대한민국으로 시작하는 모든 문자열을 검색

%%sql
SELECT * FROM wikipages WHERE title LIKE '대한민국%';

• EXPLAIN으로 실행계획을 확인하면 테이블 전체를 처음부터 끝까지 읽는 Seq Scan (순차 스캔)
• 순서대로 모든 행을 확인해야 하므로 느림

데이터베이스에서 인덱스(index)

• 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
• 장점
  • 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있음
  • 전반적인 시스템의 부하를 줄일 수 있음
• 단점
  • 인덱스를 관리하기 위해 DB의 저장공간이 필요
  • 데이터 변경 시 인덱스도 변경되어야 하므로 추가 작업이 필요
  • 잘못 사용하면 오히려 성능이 저하
• 인덱스가 있거나 없어도 SQL 문의 실행 결과는 같음(속도만 달라짐)
• 같은 의미면 인덱스를 사용하여 속도를 높일 수 있도록 질의 작성

인덱스의 자료 구조

• 분기(branch) 블록: 검색을 위한 용도
• 잎(leaf) 블록: 키 값(rowed)을 저장

인덱스를 이용한 검색

• indexed_title 컬럼 추가

%%sql
ALTER TABLE wikipages ADD COLUMN indexed_title TEXT;
UPDATE wikipages SET indexed_title = title;
CREATE INDEX idx_wikipages_indexed_title_pattern
ON wikipages(indexed_title text_pattern_ops);

인덱스된 제목을 LIKE 검색

%%sql
SELECT * FROM wikipages WHERE indexed_title LIKE '대한민국%';

참고자료

text_pattern_ops

• 인덱스를 만들 때는 순서대로 정렬이 필요
• 언어 로케일에 따라 정렬 방식이 서로 다름
• *_pattern_ops를 설정할 경우 언어 로케일 설정을 무시하고, 단순히 문자 순으로 정렬

연산자 설명