확장 — pgvector·PostGIS·pg_stat_statements

24편의 마지막. PostgreSQL 이 다른 DB 와 차이나는 한 가지 — 확장(EXTENSION) 생태계. 임베딩 검색·지리 정보·쿼리 분석·시계열까지, 한 줄 CREATE EXTENSION 으로 새 능력이 붙습니다. 본 편에서는 가장 자주 만나는 3종을 중심으로 PG 의 확장성 생태계를 회고합니다.

EXTENSION 기본

-- 설치 가능한 확장 보기
SELECT name, default_version, installed_version, comment
FROM   pg_available_extensions
ORDER BY name;

-- 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

-- 제거
DROP EXTENSION pg_stat_statements;

-- 일부는 shared_preload_libraries 에 등록 + 재시작 필요
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

pgvector — LLM 임베딩 검색

CREATE EXTENSION vector;

-- 임베딩 컬럼 (예: OpenAI text-embedding-3-small = 1536차원)
CREATE TABLE docs (
  id      BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- 코사인 유사도 인덱스 (HNSW — PG14+ pgvector 0.5+)
CREATE INDEX ix_docs_emb ON docs USING hnsw (embedding vector_cosine_ops);

-- 입력 (앱에서 임베딩 모델 호출 후 저장)
INSERT INTO docs (content, embedding)
VALUES ('PostgreSQL 은 강력한 오픈소스 RDBMS', '[0.1, 0.2, ...]'::vector);

-- 유사 검색 — "이 질문과 가장 비슷한 문서 5개"
SELECT id, content, embedding <=> '[0.15, 0.18, ...]'::vector AS distance
FROM   docs
ORDER BY embedding <=> '[0.15, 0.18, ...]'::vector
LIMIT  5;

-- 연산자
-- <->  : L2 거리
-- <=>  : 코사인 거리
-- <#>  : 내적

pg_stat_statements — 느린 쿼리 자동 추적

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'   # 재시작 필요
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

-- 가장 시간 많이 쓴 쿼리 10개
SELECT query,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 통계 리셋
SELECT pg_stat_statements_reset();

17편에서 잠깐 본 이 도구가 운영 환경 튜닝의 가장 강력한 도구. 어떤 쿼리가 진짜 느린지·총 시간 차지하는지가 한 화면에.

PostGIS — 지리 정보

CREATE EXTENSION postgis;

-- 좌표 컬럼
CREATE TABLE places (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT,
  geom  GEOMETRY(POINT, 4326)        -- WGS84 위경도
);

-- 공간 인덱스 (GiST)
CREATE INDEX ix_places_geom ON places USING GIST (geom);

-- 입력
INSERT INTO places (name, geom) VALUES
  ('서울시청', ST_SetSRID(ST_MakePoint(126.9780, 37.5666), 4326)),
  ('부산역', ST_SetSRID(ST_MakePoint(129.0420, 35.1149), 4326));

-- "현재 위치에서 5km 이내"
SELECT name, ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) AS m
FROM   places
WHERE  ST_DWithin(
         geom::geography,
         ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
         5000)
ORDER BY geom <-> ST_SetSRID(ST_MakePoint($1, $2), 4326)
LIMIT 20;

지도 검색·배달·물류·여행 서비스가 거의 PostGIS 위에 서 있음. 별도 GIS DB 안 띄우고 같은 PG 에 둠.

pgcrypto — 해시·암호화

CREATE EXTENSION pgcrypto;

-- bcrypt 비밀번호 해시
INSERT INTO users (email, pw)
VALUES ('[email protected]', crypt('mypassword', gen_salt('bf', 10)));

-- 검증
SELECT id FROM users
WHERE  email = '[email protected]'
  AND  pw = crypt('mypassword', pw);

-- UUID 생성
SELECT gen_random_uuid();   -- pgcrypto / built-in (PG 13+)

-- 대칭 암호화
SELECT pgp_sym_encrypt('비밀', 'key');
SELECT pgp_sym_decrypt(encrypted_col::bytea, 'key');

주요 확장 — 한 표

📚 24편 시리즈 회고 — 무엇을 익혔나

마지막 한 줄

"PostgreSQL 의 학습 곡선은 평평하지만 끝이 안 보인다." 24편으로 90% 의 일상 운영을 다룰 수 있고, 깊게 들어갈수록 진짜 강력함이 드러납니다. 다음 시즌은 직접 서비스에 적용하면서.