비전통적 PostgreSQL 최적화 기법

• PostgreSQL 쿼리 성능을 높이기 위한 비전통적 최적화 방법을 제시하며, 기존 인덱스 추가나 쿼리 재작성 외의 창의적 접근을 다룸
• 체크 제약조건 기반의 전체 테이블 스캔 제거, 함수 기반 인덱스로 낮은 카디널리티 최적화, Hash 인덱스를 통한 유니크 제약 구현 등 세 가지 주요 기법을 소개
• constraint_exclusion 설정을 활용해 잘못된 조건의 쿼리에서 불필요한 스캔을 방지하고, BI 환경에서의 효율을 높임
• 함수 기반 인덱스와 가상 생성 컬럼(virtual generated column) 을 이용해 인덱스 크기를 줄이고 쿼리 일관성을 확보함
• 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 인덱스는 대용량 텍스트 컬럼의 유니크 보장에 적합하며, 외래키가 필요 없는 경우 공간 효율적 대안으로 유용함