# 비전통적인 PostgreSQL 최적화 기법 > Clean Markdown view of GeekNews topic #26018. Use the original source for factual precision when an external source URL is present. ## Metadata - GeekNews HTML: [https://news.hada.io/topic?id=26018](https://news.hada.io/topic?id=26018) - GeekNews Markdown: [https://news.hada.io/topic/26018.md](https://news.hada.io/topic/26018.md) - Type: GN+ - Author: [neo](https://news.hada.io/@neo) - Published: 2026-01-21T21:32:48+09:00 - Updated: 2026-01-21T21:32:48+09:00 - Original source: [hakibenita.com](https://hakibenita.com/postgresql-unconventional-optimizations) - Points: 6 - Comments: 1 ## Summary PostgreSQL의 **비전통적 최적화 기법**은 단순한 인덱스 추가나 쿼리 수정 대신 제약조건과 인덱스 구조 자체를 재해석합니다. `constraint_exclusion` 설정으로 잘못된 조건의 쿼리에서 전체 테이블 스캔을 제거하고, **함수 기반 인덱스**와 **가상 생성 컬럼**을 결합해 인덱스 크기와 쿼리 일관성을 동시에 확보합니다. 또한 **Hash 인덱스와 배타 제약**을 이용해 대용량 텍스트 컬럼의 유니크 제약을 효율적으로 구현함으로써, 저장 공간을 크게 줄이면서도 성능을 유지합니다. ## Topic Body - **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 인덱스는 **대용량 텍스트 컬럼의 유니크 보장**에 적합하며, 외래키가 필요 없는 경우 **공간 효율적 대안**으로 유용함 ## Comments ### Comment 49640 - Author: neo - Created: 2026-01-21T21:32:48+09:00 - Points: 2 ###### [Hacker News 의견들](https://news.ycombinator.com/item?id=46692116) - 인덱스가 **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 블로그 글](https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-conflict)에서도 설명되어 있음 개인적으로는 `INSERT ... ON CONFLICT`를 선호하고, 정말 필요할 때만 `MERGE`를 쓰며 **에러 처리**를 신중히 함 - 동시성 측면에서는 `INSERT ... ON CONFLICT`가 더 예측 가능함 [modern-sql.com의 비교 글](https://modern-sql.com/caniuse/merge#illogical-errors) 참고 - 대량 배치 삽입이라면 `COPY INTO`를 **binary 포맷**으로 사용하는 게 가장 빠름. 서버 측 오버헤드가 거의 없음 - 글에서 다루지 않은 **BRIN 인덱스**가 흥미로웠음 데이터가 단조 증가한다면 매우 작고 빠른 인덱스로 이상적임 - 꼭 완전한 단조 데이터일 필요는 없음. 대부분 단조라면 충분히 잘 작동함 예를 들어 서버에서 수신하는 **timestamp 데이터**처럼 약간 순서가 어긋나는 경우에도 좋음 UUIDv7의 경우 `pages_per_range`를 조정해야 할 수도 있음 - **hash 인덱스**에서 고유 제약을 못 거는 점이 늘 아쉬움 단순히 **exclusion constraint**로 변환하는 glue 코드만 있으면 해결될 것 같은데, 왜 아직 없는지 궁금함 - 해시 기반 고유성 검증은 **충돌 처리**가 안 되기 때문에 인덱스에서 지원되지 않음 제안된 해결책도 같은 문제를 겪음 - 전혀 사실이 아님. 인덱스는 해시만 저장하지만, 테이블에는 **전체 값**이 저장되어 있음 Postgres는 해시와 실제 값이 모두 일치해야 중복으로 간주함 - [dbfiddle 예시](https://dbfiddle.uk/Iu-u886S)에서도 확인 가능함 - 글 내용이 신선했음. **가상 컬럼**과 **해시 인덱스**가 흥미롭지만, 아직은 생태계에 완전히 통합되지 못한 느낌임 - 가상 컬럼은 거의 완성 단계임. **PostgreSQL 18**에서 대부분 구현됨 해시 인덱스는 오랫동안 제약이 많았지만 점차 개선 중이며, **자동 고유 제약**이 남은 과제임 - **저장된 생성 컬럼(stored generated column)** 을 사용하면 바로 인덱스를 만들 수 있지 않나 생각함 - 글에서는 이 방법을 피하려는 이유를 설명함 PostgreSQL 14부터 지원되지만, 결과가 **물리적으로 저장되어 추가 스토리지를 차지**하기 때문임 - 표현식 기반 **partial index**를 만드는 것도 가능하지 않을까 궁금함 - 결국 저장 공간이 늘어나므로, 글의 예시에서는 피하려는 접근임 - 클라우드로 옮긴 뒤로는 고정 서버 환경에서 **pgsql을 직접 다루는 일**이 줄었음 글에 나온 **SQL 구문 하이라이팅**이 내장 기능인지, 아니면 별도 툴인지 궁금함 - 나는 **pgcli**를 사용함. 트랜잭션 상태 표시, 자동완성, 하이라이팅 등 편의 기능이 많음 다만 긴 쿼리 복사 시 줄바꿈 뒤에 자동으로 공백이 붙는 점이 불편함 - **IntelliJ 같은 IDE**를 쓰면 구문 강조와 자동완성 기능을 함께 쓸 수 있음