PostgreSQL 15 + pgvector 조합 튜닝

pgvector를 PostgreSQL 15 위에서 굴리는데, 일반 OLTP 테이블이랑 섞여 있다 보니 메모리 경합이 심하다. 한 인스턴스에서 같이 굴리는 게 맞나 싶은 순간들이 꽤 있다.

튜닝한 파라미터

-- 16 vCPU, 64GB RAM 기준
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 128MB            -- vector scan 중 sort 때문에 올림
maintenance_work_mem = 4GB  -- HNSW build 때 엄청 먹음
max_parallel_workers_per_gather = 4
random_page_cost = 1.1      -- NVMe 기준

처음엔 work_mem 기본값(4MB)으로 하다가 vector ORDER BY에서 temp file 쓰는 게 찍혀서 올렸다. EXPLAIN (ANALYZE, BUFFERS) 찍어보면 Sort Method: external merge Disk: 580000kB 이러고 있음. 디스크 소트는 답이 없다.

파티셔닝

vector 테이블이 현재 190만 row인데 월별로 파티셔닝해서 핫/콜드 분리. 최근 3개월만 HNSW 인덱스 걸고 나머지는 IVFFlat. 쿼리는 union all로 합쳐 top-k 뽑음. 프론트 쿼리 latency p95가 110ms → 42ms.

뜻밖의 이슈

VACUUM이 HNSW 인덱스가 있는 테이블에서 이상하게 느리다. autovacuum_vacuum_cost_limit을 2000으로 올리니 따라잡긴 하는데, 이건 pg_cron으로 야간에 manual VACUUM ANALYZE 돌리는 게 편했다.

그리고 PG 15는 MERGE가 들어왔는데, upsert 패턴에서 INSERT ... ON CONFLICT보다 가독성 좋다. 다만 vector 업데이트 시엔 인덱스 재계산이 비싸서, 주기적 배치로 "지우고 다시 넣기" 전략이 오히려 빠른 경우도 있었음.

DB 한 대가 RAG 백엔드 + 서비스 OLTP 겸직하는 구조는 오래 못 갈 듯. 분리 계획을 세우는 중.

PostgreSQL 15 Merge 실전

PG 15 베타에 MERGE 구문이 들어왔다. 드디어. 그동안 INSERT ... ON CONFLICT DO UPDATE(upsert)로 대부분 해결했지만 조건 분기가 복잡해지면 지저분해졌음.

MERGE 구문 기본

MERGE INTO inventory AS t
USING stock_delta AS s
  ON t.sku = s.sku
WHEN MATCHED AND t.qty + s.delta = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.delta
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.delta);

하나의 statement로 INSERT/UPDATE/DELETE 분기. 표준 SQL이라 오라클/SQL Server 쓰던 사람이 익숙할 것.

upsert 대비 차이점

• DELETE까지 가능
• source가 subquery일 때 표현이 더 깔끔
• 조건 분기가 여러 개일 때 가독성 좋음
• 단, RETURNING이 MERGE에서는 아직 지원 안 됨. 이건 15 beta 기준.
  → upsert 써야 할 이유가 여전히 있음.

동시성

MERGE가 "concurrent-safe"하냐는 질문엔 조심해야 한다. MATCHED 판단과 INSERT 사이에 race가 존재할 수 있음. unique constraint 없이 MERGE만 믿으면 중복 insert 나올 수 있음. 여전히 PK/UNIQUE 제약 같이 써야 안전함.

공식 문서에도 "MERGE does not seek locks for the matched rows ... you should ensure appropriate isolation or constraints"류의 안내가 있다.

실사용

대량 CSV 동기화(외부에서 들어오는 재고 파일) 처리에 써봤는데 확실히 코드가 줄어듦. 기존에 5개 구문으로 나눠썼던 게 하나로 정리됨. 성능은 upsert 대비 큰 차이는 없었는데, 여러 분기의 경우 플랜이 좀 더 깔끔하게 나옴.

15는 아직 beta. 정식 릴리즈는 가을쯤. 지금부터 익히면 나중에 편하다.

PostgreSQL 14 B-tree 개선 체감

PostgreSQL 14 베타 써봤다. 릴리즈 노트 중 개인적으로 기대했던 B-tree 개선 체감 결과 메모.

무슨 개선인가

B-tree의 중복 키 처리 효율화. 13까지는 같은 key 값에 대해 heap ctid까지 섞어서 저장하는 식이었는데, 14에서 "bottom-up index deletion"이 들어감. 업데이트가 자주 일어나는 테이블에서 인덱스 비대화(bloat) 속도가 많이 줄어든다.

테스트 시나리오

주문 테이블 유사본. status 컬럼 자주 업데이트(pending → paid → shipped 등), 이 컬럼에 B-tree index. 100만 행 기준 24시간 부하 돌림.

-- 인덱스 사이즈 변화
SELECT pg_size_pretty(pg_relation_size('orders_status_idx'));

결과:

버전	시작	24h 후
PG 13.3	42 MB	186 MB
PG 14b1	42 MB	78 MB

즉 vacuum 주기가 같을 때 블로팅 폭이 눈에 띄게 줄었다. 이건 꽤 큰 변화다. 다음으로 vacuum 빈도 낮추고도 공간 회수 잘 되는지 볼 예정.

다른 14 변화

• 파이프라인 모드(extended protocol 기반) — libpq 수준에서 여러 쿼리 한 번에 보내고 응답을 나중에 받을 수 있음. 배치 INSERT 쓸 때 유리.
• VACUUM이 emergency 모드로 autovacuum 실패 시 aggressive하게 동작
• LZ4 column compression — TOAST 쪽. 압축률은 조금 떨어져도 해제 속도가 빠름.

정식 GA는 가을쯤 될 듯. 지금은 스테이지에서만 돌리는 중.

PostgreSQL 9.6 병렬 쿼리 체감

PostgreSQL 9.6 올리고 가장 기대했던 병렬 쿼리 체감 테스트.

세팅. 기본값은 max_parallel_workers_per_gather = 2. 늘려봐야 의미 있는 쿼리에서만 효과 봄.

max_worker_processes = 8
max_parallel_workers_per_gather = 4

테스트 쿼리: 1억 행 테이블에서 aggregate. 컬럼 인덱스 없음 (full scan 상황).

SELECT country, COUNT(*) FROM sales_log GROUP BY country;

-- 9.5 (직렬): 38.2 s
-- 9.6 (4 worker): 11.6 s

약 3.3배. 워커 수만큼 그대로 스케일 안 되는건 마스터 프로세스에서 집계/합치는 비용 때문. 그래도 체감은 확실.

병렬이 실제 적용되는지는 EXPLAIN ANALYZE에서 Gather 노드가 보이는지로 확인. 없으면 옵티마이저가 병렬을 안 택한거.

제약 많음:

• CTE는 기본적으로 병렬 안됨
• Trigger 있는 테이블은 제약
• 사용자 정의 함수 대부분 PARALLEL UNSAFE로 간주되어 병렬 안됨. CREATE FUNCTION ... PARALLEL SAFE 명시해야 함
• 작은 테이블엔 병렬이 오히려 오버헤드. min_parallel_relation_size 파라미터로 임계값 조정 가능

OLAP 성격의 쿼리 (집계, 리포트)에는 진짜 효과 있음. OLTP (짧은 쿼리 많은 경우)엔 영향 없음.

추가로 9.6에서 눈에 띄는 것: freeze 튜닝 자동화, replication slot 사용성 개선. upsert는 이미 9.5에 들어왔고 이번엔 내부 최적화 위주라고 보면 됨. 우리 DWH 목적 DB에는 다음 달 중 9.6 반영 예정.