▲

비전통적인 PostgreSQL 최적화 기법

 (hakibenita.com)
3P by GN⁺ 2일전 | ★ favorite | 댓글 1개
  • PostgreSQL 쿼리 성능을 높이기 위한 기존 틀을 탈피한 최적화 방법으로 인덱스 추가나 쿼리 재작성이 아닌 3가지 창의적 접근방식 소개
  • 체크 제약조건 기반의 전체 테이블 스캔 제거, 함수 기반 인덱스로 낮은 카디널리티 최적화, Hash 인덱스를 통한 유니크 제약 구현
  • 1. constraint_exclusion 설정을 활용해 잘못된 조건의 쿼리에서 불필요한 스캔을 방지
  • 2. 함수 기반 인덱스와 가상 생성 컬럼(virtual generated column) 을 이용해 인덱스 크기를 줄이고 쿼리 일관성을 확보
  • 3. Hash 인덱스와 배타 제약(exclusion constraint) 을 결합해 대용량 텍스트 컬럼의 유니크 제약을 효율적으로 구현, 저장 공간을 크게 절감함

체크 제약조건 기반 전체 테이블 스캔 제거

  • plan 컬럼에 'free', 'pro' 값만 허용하는 CHECK 제약조건이 있음에도 'Pro'로 잘못된 쿼리를 실행하면 PostgreSQL은 전체 테이블을 스캔함
    • 실행 계획에서 10만 행을 모두 읽으며, 실제 결과는 0행
  • constraint_exclusion 파라미터를 'on'으로 설정하면 PostgreSQL이 제약조건을 고려해 스캔을 완전히 생략함
    • 실행 시간이 7.4ms에서 0.008ms로 단축됨
  • 기본값은 'partition'이며, 단순 쿼리에서는 오히려 플래닝 오버헤드가 커질 수 있음
    • 그러나 BI·리포팅 환경에서는 사용자가 잘못된 조건을 자주 입력하므로 'on' 설정이 유용함

함수 기반 인덱스로 낮은 카디널리티 최적화

  • 1천만 건의 판매 데이터를 가진 sale 테이블에서 일별 매출 집계 쿼리 수행 시 전체 스캔으로 627ms 소요
  • sold_at 컬럼에 B-Tree 인덱스 추가 시 187ms로 단축되지만, 인덱스 크기가 214MB로 커짐
  • date_trunc('day', sold_at) 표현식에 함수 기반 인덱스를 생성하면 크기가 66MB로 줄고, 실행 시간은 145ms로 더 빨라짐
    • 낮은 카디널리티 덕분에 인덱스 deduplication이 가능
  • 단, 쿼리 표현식이 인덱스 정의와 정확히 일치해야 하므로 표현식 일관성 유지가 필요
    • 이를 위해 동일 표현식을 포함한 VIEW를 만들거나,
    • PostgreSQL 18부터 지원되는 가상 생성 컬럼(virtual generated column) 을 추가해 일관성을 자동화할 수 있음
  • 가상 생성 컬럼을 사용하면 인덱스가 자동으로 활용되며, 작은 인덱스·빠른 쿼리·표현식 일관성을 모두 확보 가능
  • 단, PostgreSQL 18에서는 가상 컬럼 인덱스 생성은 아직 미지원이며, 향후 19 버전에서 지원 예정

Hash 인덱스로 유니크 제약 구현

  • 긴 URL을 저장하는 urls 테이블에서 중복 URL 방지를 위해 B-Tree 기반 유니크 인덱스를 생성하면 인덱스 크기가 154MB에 달함
  • Hash 인덱스는 실제 값을 저장하지 않고 해시값만 저장하므로 훨씬 작음
    • PostgreSQL은 기본적으로 유니크 Hash 인덱스를 지원하지 않지만,
    • 배타 제약(exclusion constraint) 을 이용해 EXCLUDE USING HASH (url WITH =) 형태로 유니크 제약을 우회 구현 가능
  • 이 방식으로도 중복 삽입 시 오류가 발생하며, 쿼리 성능도 B-Tree보다 빠름 (0.022ms vs 0.046ms)
  • 인덱스 크기는 32MB로, B-Tree 대비 5배 이상 작음
  • 단점:
    • 외래키 참조 불가 (REFERENCES 제약 불가능)
    • INSERT ... ON CONFLICT 구문과의 호환성 제한
    • ON CONFLICT ON CONSTRAINT 또는 MERGE 구문으로 대체 가능
  • Hash 인덱스는 대용량 텍스트 컬럼의 유니크 보장에 적합하며, 외래키가 필요 없는 경우 공간 효율적 대안으로 유용함
▲
GN⁺ 2일전  [-]
Hacker News 의견들

  • 인덱스가 214MB라서 테이블 전체의 절반 크기 정도임
    분석가 입장에서는 좋지만, 쓰기 성능 입장에서는 write amplification 문제가 생김
    인덱스는 읽기/쓰기 비율에 따라 설계가 달라지고, 그래서 데이터 웨어하우스나 리드 리플리카를 두는 이유가 됨
    너무 많은 사용자를 상대하는 경우라면 OLTP DB에 BI/OLAP 인덱스를 두지 않는 게 좋음

    • PostgreSQL이 clustered index(Oracle의 Index Organized Table)를 지원하면 좋겠다고 생각함
      테이블 접근 패턴이 일정하다면 테이블 자체가 인덱스가 되어 write amplification 없이 효율을 얻을 수 있음

  • 첫 번째 예시는 Plan을 enum 타입으로 정의하는 게 더 낫다고 생각함
    텍스트보다 가볍고, 잘못된 필터 입력 시 빈 결과 대신 에러로 처리되어 안정적임

  • 훌륭한 글이었음. PostgreSQL과 MySQL을 수십 년 써왔지만, 이 글을 보고도 여전히 가능성의 일부분만 알고 있었다는 걸 느낌

    • 나도 10년 넘게 Postgres를 써왔지만, 문서를 볼 때마다 여전히 표면만 긁고 있는 느낌임. 정말 강력한 시스템임
    • PostgreSQL은 마치 Emacs 같음. 겉보기엔 단순하지만 사실상 운영체제 수준의 유연함을 가짐

  • 글 마지막에 언급된 MERGE 구문이 가장 흥미로웠음
    평소엔 INSERT ... ON CONFLICT DO UPDATE로 upsert를 처리하지만, MERGE는 더 강력하고 다양한 상황에서 쓸 수 있을 것 같음

    • MERGE는 SQL 표준에 오래전부터 있었지만, Postgres는 MVCC 모델의 비원자성 문제 때문에 도입을 미뤘음
      pganalyze 블로그 글에서도 설명되어 있음
      개인적으로는 INSERT ... ON CONFLICT를 선호하고, 정말 필요할 때만 MERGE를 쓰며 에러 처리를 신중히 함
    • 동시성 측면에서는 INSERT ... ON CONFLICT가 더 예측 가능함
      modern-sql.com의 비교 글 참고
    • 대량 배치 삽입이라면 COPY INTO를 binary 포맷으로 사용하는 게 가장 빠름. 서버 측 오버헤드가 거의 없음

  • 글에서 다루지 않은 BRIN 인덱스가 흥미로웠음
    데이터가 단조 증가한다면 매우 작고 빠른 인덱스로 이상적임

    • 꼭 완전한 단조 데이터일 필요는 없음. 대부분 단조라면 충분히 잘 작동함
      예를 들어 서버에서 수신하는 timestamp 데이터처럼 약간 순서가 어긋나는 경우에도 좋음
      UUIDv7의 경우 pages_per_range를 조정해야 할 수도 있음

  • hash 인덱스에서 고유 제약을 못 거는 점이 늘 아쉬움
    단순히 exclusion constraint로 변환하는 glue 코드만 있으면 해결될 것 같은데, 왜 아직 없는지 궁금함

  • 해시 기반 고유성 검증은 충돌 처리가 안 되기 때문에 인덱스에서 지원되지 않음
    제안된 해결책도 같은 문제를 겪음

    • 전혀 사실이 아님. 인덱스는 해시만 저장하지만, 테이블에는 전체 값이 저장되어 있음
      Postgres는 해시와 실제 값이 모두 일치해야 중복으로 간주함
    • dbfiddle 예시에서도 확인 가능함

  • 글 내용이 신선했음. 가상 컬럼과 해시 인덱스가 흥미롭지만, 아직은 생태계에 완전히 통합되지 못한 느낌임

    • 가상 컬럼은 거의 완성 단계임. PostgreSQL 18에서 대부분 구현됨
      해시 인덱스는 오랫동안 제약이 많았지만 점차 개선 중이며, 자동 고유 제약이 남은 과제임

  • 저장된 생성 컬럼(stored generated column) 을 사용하면 바로 인덱스를 만들 수 있지 않나 생각함

    • 글에서는 이 방법을 피하려는 이유를 설명함
      PostgreSQL 14부터 지원되지만, 결과가 물리적으로 저장되어 추가 스토리지를 차지하기 때문임
    • 표현식 기반 partial index를 만드는 것도 가능하지 않을까 궁금함
    • 결국 저장 공간이 늘어나므로, 글의 예시에서는 피하려는 접근임

  • 클라우드로 옮긴 뒤로는 고정 서버 환경에서 pgsql을 직접 다루는 일이 줄었음
    글에 나온 SQL 구문 하이라이팅이 내장 기능인지, 아니면 별도 툴인지 궁금함

    • 나는 pgcli를 사용함. 트랜잭션 상태 표시, 자동완성, 하이라이팅 등 편의 기능이 많음
      다만 긴 쿼리 복사 시 줄바꿈 뒤에 자동으로 공백이 붙는 점이 불편함
    • IntelliJ 같은 IDE를 쓰면 구문 강조와 자동완성 기능을 함께 쓸 수 있음
답변달기